Issue: inconsistent programming error
Author: Agrisolum
State: closed
Created: 2014-04-06 18:01:25

I have a large number of rows that I want to add (~150000) to a MySQL db

The rows have 9 float fields and a PrimaryKeyField.

If I try to add them all in one go:

```
db.SmapDataPoint.insert_many(dict_data_gen).execute()
```

The database 'goes away' (peewee.OperationalError: (2006, 'MySQL server has gone away')).

If I split up the data and apply several insert_manys then I receive a ProgrammingError

```
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "smap\job.py", line 80, in run2
    db.SmapDataPoint.insert_many(islice(dict_data_gen, p[0], p[1])).execute()
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2150, in execute
    return self.database.last_insert_id(self._execute(), self.model_class)
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 1753, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2315, in execute_s
ql
    self.commit()
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2186, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2306, in execute_s
ql
    cursor.execute(sql, params or ())
  File "D:\Coding\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in e
xecute
    self.errorhandler(self, exc, value)
  File "D:\Coding\Python27\lib\site-packages\MySQLdb\connections.py", line 36, i
n defaulterrorhandler
    raise errorclass, errorvalue
peewee.ProgrammingError: (1064, "You have an error in your SQL syntax; check the
 manual that corresponds to your MySQL server version for the right syntax to us
e near '' at line 1")
```

The point at which it occurs is dependent on the chunk size and not upon the actual data.  ie if I change the chunk size the error will occur on the 'n'th execute() but the insert_many chunk will not necessarily contain the same data as on a previous attempt with a different chunk size.

The failure is consistent if the chunk size remains constant.

```
def save_to_db(filename)

    data = <list of tuples generated from data in filename> # tuples are all same length
    dict_data_gen = (dict(zip(dbfields, row)) for row in data)

    db.connect()
    print 'updating db...'

    num_rows = len(data)

    slice_length = 22000

    slice_points = list()
    for i in xrange(0, num_rows, slice_length):
        slice_points.append((i, min(i + slice_length, num_rows)))

    for p in slice_points:
        print "insert_many for region {}:{}".format(p[0], p[1])
        #db.DataPoint.insert_many(islice(dict_data_gen, p[0], p[1])).execute()
        db.DataPoint.insert_many(dict_data_gen).execute()


    print 'Job {} complete!'.format(filename)

```

here is my model

```
class DataPoint(BaseModel):
    id = PrimaryKeyField()
    latitude = FloatField(db_column='Latitude')
    longitude = FloatField(db_column='Longitude')
    #datetime = DateTimeField()
    bare_soil_roughness_retrieved = FloatField()
    radar_vegetation_index = FloatField()
    soil_moisture_snapshot = FloatField()
    soil_moisture_snapshot_dvz = FloatField(db_column='soil_moisture_snapshot_DVZ')
    soil_moisture_snapshot_shi = FloatField()
    soil_moisture_time_series = FloatField()
    soil_moisture_time_series_kvz = FloatField(db_column='soil_moisture_time_series_KVZ')
```

I appreciate any help you can give on this - it may be down to a configuration issue on my side?


I tried the same thing with a local sqlite db (_much_ slower...) and this happened:

```
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "smap\job.py", line 82, in run2
    db.SmapDataPoint.insert_many(islice(dict_data_gen, p[0], p[1])).execute()
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2149, in execute
    return last_id
UnboundLocalError: local variable 'last_id' referenced before assignment
```


Pushed 46b9e4a850d3929644ca4312d0b7807baad99276 which should fix the unbound local error. Do you mind trying that out and updating the ticket? Sorry for the inconvenience.


Yes, that fixes the unbound error :)


Does it fix the other error as well when running with MySQL?


I just wrote a little test script and ran it against sqlite and mysql without issue:

``` python
#!/usr/bin/env python

import random

from peewee import *

db = SqliteDatabase('test.db')
# or db = MySQLDatabase('peewee_test')

class Value(Model):
    number = FloatField()

    class Meta:
        database = db


Value.drop_table(True)
Value.create_table()

numbers = [
    random.random() for i in range(1000*1000)]

# Bulk insert in groups of 500.
chunk_size = 500
for i in range(0, len(numbers), chunk_size):
    vals = [{'number': j} for j in numbers[i:i+chunk_size]]
    Value.insert_many(vals).execute()

print 'Inserted %s rows.' % Value.select().count()
```


I reproduced by adjusting your script thus:

```
import random
from itertools import islice
from peewee import *

#db = SqliteDatabase('test.db')
# or db = MySQLDatabase('peewee_test')


#db = SqliteDatabase("e:/temp/peewee.db")
db = MySQLDatabase(  <my ec2 RDS credentials> )


class Value_dbg(Model):
    id = PrimaryKeyField()
    number = FloatField()
    number2 = FloatField()
    number3 = FloatField()

    class Meta:
        database = db


Value_dbg.drop_table(True)
Value_dbg.create_table()

big_len = 150000
numbers = ((random.random(), random.random(), random.random()) for i in xrange(big_len))

keys=('number', 'number2', 'number3')

# Bulk insert in groups of 500.
chunk_size = 10000
for i in range(0, big_len, chunk_size):
    print '{}...{}'.format(i, i+chunk_size)

    vals = (dict(zip(keys, row)) for row in islice(numbers,i,i+chunk_size))

    Value_dbg.insert_many(vals).execute()

print 'Inserted %s rows.' % Value_dbg.select().count()
```

```
0...10000
10000...20000
20000...30000
30000...40000
40000...50000
50000...60000
Traceback (most recent call last):
  File "smap\job_test.py", line 46, in <module>
    Value_dbg.insert_many(vals).execute()
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2151, in execute
    return self.database.last_insert_id(self._execute(), self.model_class)
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 1753, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2316, in execute_s
ql
    self.commit()
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2187, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "D:\Coding\Python27\lib\site-packages\peewee.py", line 2307, in execute_s
ql
    cursor.execute(sql, params or ())
  File "D:\Coding\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in e
xecute
    self.errorhandler(self, exc, value)
  File "D:\Coding\Python27\lib\site-packages\MySQLdb\connections.py", line 36, i
n defaulterrorhandler
    raise errorclass, errorvalue
peewee.ProgrammingError: (1064, "You have an error in your SQL syntax; check the
 manual that corresponds to your MySQL server version for the right syntax to us
e near '' at line 1")
```


You've got a bug in your code. `numbers` is a generator, but you're calling `itertools.islice` on it, causing it to be exhausted.

So the first iteration, you are grabbing the first 10K numbers from the generator...that's fine. The second iteration, though, you are actually advancing the generator by 20K. The third iteration takes 30K off the generator, etc. If you modify this line then it works as you'd expect:

``` python
# ORIGINAL
vals = (dict(zip(keys, row)) for row in islice(numbers,i,i+chunk_size))

# NEW
vals = (dict(zip(keys, row)) for row in islice(numbers, 0, chunk_size))
```


ah, sorry! first time using itertools/generators.

Thanks for quick responses.