bulk_create raises peewee.OperationalError: near ",": syntax error

464 views
Skip to first unread message

Mario Gee

unread,
Aug 28, 2020, 5:37:39 AM8/28/20
to peewee-orm
Hi
I am failing to use bulk_create on one CentOS6 server. Inserting one record works as expected.
I bulk_create  success on Windows10 Py3.8, Ubuntu Py3.6/2.6 with the very same code.
What could make a difference there? Any hints appreciated
cheers
mario 

demo code
---
import datetime
import random
#from peewee.peewee import SqliteDatabase, Model, IntegerField
from peewee import SqliteDatabase, Model, IntegerField

class BaseModel(Model):
    sc_id = IntegerField() 


print("db SqliteDatabase")
test_db = SqliteDatabase(':memory:')
MODELS = [BaseModel]
test_db.bind(MODELS, bind_refs=False, bind_backrefs=False)
test_db.connect()
print("db connected")
test_db.create_tables(MODELS)

user0 = BaseModel(sc_id=1)
user0.save()
BaseModel.get()

users = [ BaseModel(sc_id=1),
  BaseModel(sc_id=2)]
  
  
with test_db.atomic():
BaseModel.bulk_create(users, batch_size=500)

read_list = BaseModel.select()
print(len(read_list))




Problem
---
Python 3.6.4 (default, Jan 16 2018, 15:57:06)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-18)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>> import datetime
>>> import random
>>> from peewee import SqliteDatabase, Model, IntegerField
>>>
>>> class BaseModel(Model):
...     sc_id = IntegerField()
...
>>>
>>> print("db SqliteDatabase")
db SqliteDatabase
>>> test_db = SqliteDatabase(':memory:')
>>> MODELS = [BaseModel]
>>> test_db.bind(MODELS, bind_refs=False, bind_backrefs=False)
>>> test_db.connect()
True
>>> print("db connected")
db connected
>>> test_db.create_tables(MODELS)
>>>
>>> user0 = BaseModel(sc_id=1)
>>> user0.save()
1
>>> BaseModel.get()
<BaseModel: 1>
>>>
>>> users = [ BaseModel(sc_id=1),
...               BaseModel(sc_id=2)]
>>>
...
... with test_db.atomic():
...     BaseModel.bulk_create(users, batch_size=500)
...
Traceback (most recent call last):
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: near ",": syntax error

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 4, in <module>
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 6326, in bulk_create
    res = cls.insert_many(accum, fields=fields).execute()
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 1886, in inner
    return method(self, database, *args, **kwargs)
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 1957, in execute
    return self._execute(database)
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 2707, in _execute
    return super(Insert, self)._execute(database)
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 2442, in _execute
    cursor = database.execute(self)
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 3114, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 3106, in execute_sql
    self.commit()
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 2873, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/var/wwwps/aging_portal/peewee/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: near ",": syntax error
>>> read_list = BaseModel.select()
>>> print(len(read_list))
1
>>>
>>>

Charles Leifer

unread,
Aug 28, 2020, 7:09:59 AM8/28/20
to peewe...@googlegroups.com
Your sqlite is probably old and doesn't support inserting multiple rows at one time. I'd suggest updating the sqlite you have installed.

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/peewee-orm/53714d8c-5df0-4896-938e-bfbb4efa65d3n%40googlegroups.com.

Mario Gee

unread,
Aug 31, 2020, 2:24:57 AM8/31/20
to peewee-orm

I tried the github master branch and the 3.13.3 tag. Both ended with the same  OperationalError. But the same code works on other machines. is there any change it could be due to the machine localization or the python versions 5.6.4 vs 5.6.9 ?

Or when it is data related I also tried to debug the peewee.py : The query that gets rendered is: 
INSERT INTO "user" ("sc_id", "name", "email", "type") VALUES (753, 'Yoshi', 'd...@ff.ff', 'sc_user'), (754, 'Yoshi2', 'd...@ff.ff', 'sc_user')

Charles Leifer

unread,
Aug 31, 2020, 8:25:35 AM8/31/20
to peewe...@googlegroups.com
import sqlite
sqlite3.sqlite_version

This should tell you what version of sqlite you are running.

Mario Gee

unread,
Aug 31, 2020, 11:53:34 AM8/31/20
to peewee-orm
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.6.20'

Mario Gee

unread,
Aug 31, 2020, 11:55:49 AM8/31/20
to peewee-orm
Yes, thanks.
This is probably the reason why my environments react differently. Thanks again!

On Friday, 28 August 2020 at 13:09:59 UTC+2 Charles wrote:

Charles Leifer

unread,
Aug 31, 2020, 12:05:19 PM8/31/20
to peewe...@googlegroups.com
That's the problem, as I stated in my original email to you.

Bulk insert wasn't added until 3.7.11


Reply all
Reply to author
Forward
0 new messages