peewee.OperationalError: too many SQL variables PLUS

[cloud-admin]

Hi there:

I'm using peewee to insert files into SQLite, I meet this error: peewee.OperationalError: too many SQL variables . I tried Google, some said it's because the SQLITE_MAX_VARIABLE_NUMBER.

So i searched and using this code to get the SQLITE_MAX_VARIABLE_NUMBER.

def max_sql_variables():
    """Get the maximum number of arguments allowed in a query by the current
    sqlite3 implementation. Based on `this question
    `_

    Returns
    -------
    int
        inferred SQLITE_MAX_VARIABLE_NUMBER
    """
    import sqlite3
    db = sqlite3.connect(':memory:')
    cur = db.cursor()
    cur.execute('CREATE TABLE t (test)')
    low, high = 0, 100000
    while (high - 1) > low: 
        guess = (high + low) // 2
        query = 'INSERT INTO t VALUES ' + ','.join(['(?)' for _ in range(guess)])
        args = [str(i) for i in range(guess)]
        try:
            cur.execute(query, args)
        except sqlite3.OperationalError as e:
            if "too many SQL variables" in str(e):
                high = guess
            else:
                raise
        else:
            low = guess
    cur.close()
    db.close()
    return low

SQLITE_MAX_VARIABLE_NUMBER = max_sql_variables()

And i get the value is 999. Then I tried the following steps:

... code omit...
for _fileList in _list:
    print "Insert %s Records" % len( _fileList )
    FileLite.fromFileList( _fileList )

(1) I split my file list to 400, But it still said peewee.OperationalError: too many SQL variables
(2) I split my files list to 100,it still said peewee.OperationalError: too many SQL variables
(3) I split my files list into 80, It's OK!
(4) I increase the value to 84, It seems OK and insert some data, but when the loops goes on, the peewee.OperationalError: too many SQL variables throws, HOW COULD THIS HAPPEN?

[coleifer]

This is documented on SQLite's website, and the default is 999: https://www.sqlite.org/limits.html#max_variable_number

This is also documented in the bulk inserts section of peewee docs: http://docs.peewee-orm.com/en/latest/peewee/querying.html#bulk-inserts

SQLite users should be aware of some caveats when using bulk inserts. Specifically, your SQLite3 version must be 3.7.11.0 or newer to take advantage of the bulk insert API. Additionally, by default SQLite limits the number of bound variables in a SQL query to 999. This value can be modified by setting the SQLITE_MAX_VARIABLE_NUMBER flag.

As for your question, how many fields are on your model? Can you share a more verbose example of what's going on in the fromFileList method?

[cloud-admin]

(1) sqlite3.sqlite_version = 3.24.0, I think it's OK.
(2) The Code is simple: Parse Each File in list, Convert to Dict map to Model, Then Call replace many.

class FileInfo( BaseModel ):
    #
    # Multi-Database REQUIRED!
    class Meta:
        database = Proxy()
    #
    size = IntegerField()
    md5  = CharField( unique = True , max_length = 32 )
    sha1 = CharField( unique = True , max_length = 40 )
    #
    author    = CharField()
    summary   = TextField()
    title     = TextField()
    content   = TextField()
    reference = TextField()

class FileLite(object):
    #
    @classmethod
    def fileToDict( cls ,  _file ):
        #
        _dict = {}
        #
        _dict['md5']  = md5( _file )
        _dict['sha1'] = sha1( _file )
        _dict['size'] = fileSize( _file )
        #
        #
        _fileFormat = FileFormat( _file )
        #
        _dict['author']    = _fileFormat.getAuthor()
        _dict['summary']   = _fileFormat.getSummary()
        _dict['title']     = _fileFormat.getTitle()
        _dict['content']   = _fileFormat.getContent()
        _dict['reference'] = _fileFormat.getReference()
        #
        return _dict
    #
    @classmethod
    def fromFileList( cls , fileList ):
        #
        _fileInfo = []
        #
        for _file in fileList:
            _fileInfo.append( cls.fileToDict( _file ) )
        #
        return FileInfo.replace_many( _fileInfo ).execute()

[coleifer]

Right, so you have X fields-per-model (looks like 8, but maybe you left some out for the example?) -- so 999 / 8 ~= 124 objects you could insert in one go max.

[coleifer]

As far as I can tell this is not a Peewee issue, but running into limits imposed by SQLite.

[vadimkantorov]

It would be great if peewee could automatically batch up inserts upon the insert_many call (peewee is in a better position to count accurately the number of used SQL variables)