Getting the Most Out of Sqlite3 with Python
> 1. Use Bulk Operations<p>> If you need to insert a lot of rows at once in your database, you really should not use execute. The sqlite3 module provides a way to bulk insertions: executemany.<p>Note that this is actually part of DBAPI 2.0[0], so pretty much every Python database driver should have an executemany(). Worst case scenario, it's implemented on top of execute() and you get little to no benefits (that's the case for psycopg2 currently[1]), best case scenario it can use much more efficient methods (seems to be the case for sqlite3).<p>> 3. Cursors Can Be Iterated Upon<p>That is an optional extension but part of the DBAPI 2.0 spec as well[2], and most drivers should implement it<p>>     for row in connection.execute('SELECT * FROM events'):<p>The return value of execute() is spec-undefined, so beware that specific method, it is absolutely not portable, neither is execute/executemany on the connection object.<p>> 7. Use Placeholders to Interpolate Python Values<p>Warning: the actual placeholder is driver-dependent and not portable, you can query the driver for the preferred param style[3], you can also ask it for its thread-safety guarantees (the sqlite3 module advertises module-level thread-safety but not connection-level let alone cursor-level)<p>[0] <a href="https://www.python.org/dev/peps/pep-0249/#executemany" rel="nofollow">https://www.python.org/dev/peps/pep-0249/#executemany</a><p>[1] <a href="http://initd.org/psycopg/docs/cursor.html#cursor.executemany" rel="nofollow">http://initd.org/psycopg/docs/cursor.html#cursor.executemany</a><p>[2] <a href="https://www.python.org/dev/peps/pep-0249/#next" rel="nofollow">https://www.python.org/dev/peps/pep-0249/#next</a><p>[3] <a href="https://www.python.org/dev/peps/pep-0249/#paramstyle" rel="nofollow">https://www.python.org/dev/peps/pep-0249/#paramstyle</a>