Issue: insert_many fails when inserting too many rows under Python 3.5.1
Author: zweger
State: closed
Created: 2016-05-31 16:53:30

It seems the underlying behavior of peewee.Model.insert_many differs when running under more modern versions of sqlite3 (and thus with more recent Python versions).

Under older versions of SQLite, peewee's `insert_many` behavior is to insert one row per SQL statement. But under SQLite versions past 3.7.11.0, peewee will attempt to insert every row in a single statement.

However, SQLite sets a limit on the number of SQL variables: `SQLITE_MAX_VARIABLE_NUMBER`, which defaults to 999. Thus the maximum number of rows SQLite can actually insert at a time is `999 // num_columns`.

See also #536, which I believe may have been running into the same problem. The issue isn't necessarily that the caller needs a suggestion for how many rows it would be best to insert at a time, but rather that when trying to insert too many things, the insert fails.

Here's a MWE:

```
import unittest

from peewee import *


db = SqliteDatabase(':memory:')


class Alpha(Model):
    a = IntegerField()
    b = IntegerField()
    c = IntegerField()
    d = IntegerField()
    e = IntegerField()
    f = IntegerField()
    g = IntegerField()
    h = IntegerField()
    i = IntegerField()

    class Meta:
        database = db

db.create_table(Alpha)


class TestInsertMany(unittest.TestCase):
    def setUp(self):
        self.rows = [{'a': i, 'b': i, 'c': i, 'd': i, 'e': i, 'f': i, 'g': i, 'h': i, 'i': i} for i in range(1000)]

    def test_insert_many(self):
        Alpha.insert_many(self.rows).execute()
```

Under Python 2.7.11, this works. But under 3.5.1 I get the following error:

(This is from master. I get the same behavior under 2.8.1).

```
$ python -m unittest playhouse.tests.test_insert_many
E
======================================================================
ERROR: test_insert_many (playhouse.tests.test_insert_many.TestInsertMany)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "peewee.py", line 3586, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: too many SQL variables

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "playhouse/tests/test_insert_many.py", line 32, in test_insert_many
    Alpha.insert_many(self.rows).execute()
  File "peewee.py", line 3345, in execute
    cursor = self._execute()
  File "peewee.py", line 2748, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "peewee.py", line 3593, in execute_sql
    self.commit()
  File "peewee.py", line 3417, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "peewee.py", line 132, in reraise
    raise value.with_traceback(tb)
  File "peewee.py", line 3586, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: too many SQL variables

----------------------------------------------------------------------
Ran 1 test in 0.213s

FAILED (errors=1)
```


Fortunately, if you run into this, it looks like pysqlite supports modifying the limit.
- https://www.sqlite.org/limits.html#max_variable_number
- https://www.sqlite.org/c3ref/limit.html
- http://pysqlite.readthedocs.io/en/latest/sqlite3.html#sqlite3.Connection.set_limit

So in your peewee code, you could just write:

``` python
conn = peewee_db.get_conn()
conn.set_limit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER, 1337)
```

I don't think this is a peewee issue, however hopefully this code helps any other people who stumble on this issue.


Unfortunately, the version of sqlite3 which Python 3.5.1 distributes (2.6.0) does not support the `set_limit` function. Moreover, pysqlite does not support Python 3 (`pip install` gives `pysqlite is not supported on Python 3. When using Python 3, use the sqlite3 module from the standard library.`). So I'd expect it to be nontrivial to get a Python 3 install where you could do that.

However, SQLite only allows you to _lower_ this limit. From the docs:

> The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.

And indeed, setting the limit to a larger number under Python 2.7.11 with pysqlite 2.8.2 has no effect.

If this is intended behavior, then I see this as a bug in peewee's documentation, which make no mention of this limit, or that it differs across Python versions for which peewee claims support.

Otherwise, for ways to actually resolve this: I'm dubious that there are actually any performance differences between the multi-valued insert statement and compound inserts. (See [here](http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg68113.html)). Perhaps peewee should always just use the compound insert form for SQLite? If there are indeed performance differences, then peewee should do multi-valued inserts inside a loop.


Wow, nice info, thank you for the thorough comment and for digging into this. I can't believe how janky the Python 3.5 sqlite3 library is, what an embarassment.

Looking at https://www.sqlite.org/compile.html it does seem that the upper limit can be changed using compile-time directives.

I'll take a look at the docs.


0cf8988


Just ran into this as well, it also happens when using apsw. Some platforms compile sqlite with higher defaults, e.g. homebrew / Debian / Ubuntu use 250000 (cf [Formula/sqlite.rb](https://github.com/Homebrew/homebrew-core/blob/master/Formula/sqlite.rb#L48%29)).

For apsw, use 

``` shell
$ CFLAGS="-DSQLITE_MAX_VARIABLE_NUMBER=250000" python setup.py fetch  \
     --all build --enable-all-extensions install
```