How to increase SQLITE_MAX_VARIABLE_NUMBER at compilation-time?

Question

I'm using Python and the Peewee module to manage a Sqlite3 database. In my program I need to do bulk inserts otherwise the addition of new rows to my traffic table takes to much time, however, I already managed to insert multiple rows at once, but now I am in the necessity to increase the SQLITE_MAX_VARIABLE_NUMBER whose default value is 999, in order to reduce the execution time.

I'm adding around 100000 entries to the table but it takes around 40 seconds to finish.

Any idea of how to modify this limit?

Thanks

Answer 1 · 2016-09-07T10:46:50Z

September 7, 2016 10:46am

It is not possible to change SQLITE_MAX_VARIABLE_NUMBER for sqlite3 program unless you change source files and re-compile sqlite3 program your self, see best answer here:

http://stackoverflow.com/questions/15312590/what-is-the-limit-of-sql-variables-one-can-specify-in-a-single-execsql-query

But If you use peewee, here is how guy changes it

http://stackoverflow.com/questions/35616602/peewee-operationalerror-too-many-sql-variables-on-upsert-of-only-150-rows-8-c

And looking from documentation as I understand correctly you can change SQLITE_MAX_VARIABLE_NUMBER directly (check Note in documentation below):

http://docs.peewee-orm.com/en/latest/peewee/querying.html#bulk-inserts

That's all I could get. I'm not a very good Python user, to check all of these, but hopefully those links could be of some help.

Welcome to the Treehouse Community

Looking to learn something new?

Daniel Alvarez

Daniel Alvarez

How to increase SQLITE_MAX_VARIABLE_NUMBER at compilation-time?

1 Answer

Alexander Nikiforov

Alexander Nikiforov