On Monday, 23 October, 2017 08:36, David Raymond <david.raym...@tomtom.com>
wrote:
NB: References to pysqlite2 refer to the builtin sqlite3 wrapper in python.
This wrapper was called pysqlite2 long before it was included in the standard
library. It is still updated to fix bugs and accommodate changes in the SQL
DDL/DML spoken by SQLite3, updates which are eventually included in the sqlite3
module in the standard library.
>Basic but good. execute will prepare the statement each time through,
>whereas executemany will prepare once and then just bind for each run
>through, which is where the time saving comes from. Depending on how
>complex what you're doing is though it can be hard, or more often
>awkward to create an iterator for executemany.
execute vs executemany is merely syntactic sugar. If and only if you have
disabled statement caching will the statement be recompiled each time. In all
cases the sequence of sequences parameter is used as a binding to multiple
calls to execute. executemany is the equivalent of:
executemany(sql, param)
->
for args in param:
execute(sql, args)
and has no (or very little) benefit over doing the same thing in Python itself
(yes, the iteration is run in C, but it is still entirely calling into the
Python VM to do all its work, so there is very little if any benefit there --
the only real benefit over the above is that locating the cached statement is
only done once when you use executemany and the same C stmt pointer is used
over and over again without looking it up in the cache, which does save a few
cycles -- however since since the bulk of the time is spent inside the I/O
bound SQLite3 library code where the GIL is released, this effectively makes
minimal difference in the long run).
>The other thing I'd add is that you can upgrade your SQLite version
>by replacing the sqlite3.dll file in your \pythonxx\DLLs folder and
>get the latest new SQLite features. Just remember that if you're then
>passing your script off to someone else to run that they might still
>have the originally installed .dll from 6 years ago and won't be able
>to handle new feature X.
Plus of course you have to deal with the brain-dead and horrendous prayers
required to handle transactions properly with pysqlite2 unless you are sure to
always open your database with isolation_level=None to turn off the automagic
faddling about, which is sure to trip you up if you try to do anything
complicated.
If you are going to use SQLite with Python, you might want to look at APSW.
You can build APSW as either a wrapper for the system SQLITE3.DLL/SQLITE3.SO
library (like pysqlite2) or as a completely self-contained library containing
both SQLite3 and APSW
https://github.com/rogerbinns/apsw
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Sunday, October 22, 2017 12:47 PM
>To: SQLite mailing list
>Subject: [sqlite] Article about using sqlite3 in Python
>
>I don’t know enough about Python to evaluate this, but the sqlite3
>side is sound, and some readers might find it useful.
>
><https://remusao.github.io/posts/2017-10-21-few-tips-sqlite-perf.html>
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
