Issue: peewee.OperationalError: too many SQL variables PLUS
Author: cloud-admin
State: closed
Created: 2018-06-22 01:06:58

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.

```python
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?***

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?

(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.

```python

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()
```

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.

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

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)