How does one enable or disable statement caching then? For something as basic
as just inserting integers executemany is about 3 times faster.
Python version: 2.7.14 (v2.7.14:84471935ed, Sep 16 2017, 20:19:30) [MSC v.1500
32 bit (Intel)]
Python sqlite3 module version: 2.6.0
sqlite3.dll version: 3.20.1
executemany takes: 8.674 seconds for 10,000,000 inserts
execute takes 30.173 seconds for 10,000,000 inserts
import sqlite3
import sys
import time
numInts = 10000000
def genFunc():
for x in xrange(numInts):
yield (x,)
def execute():
startTime = time.clock()
with sqlite3.connect(":memory:", isolation_level = None) as conn:
conn.execute("begin transaction;")
conn.execute("create table foo (bar int);")
for x in xrange(numInts):
conn.execute("insert into foo values (?);", (x,))
conn.commit()
conn.close()
endTime = time.clock()
return endTime - startTime
def executemany():
startTime = time.clock()
with sqlite3.connect(":memory:", isolation_level = None) as conn:
conn.execute("begin transaction;")
conn.execute("create table foo (bar int);")
conn.executemany("insert into foo values (?);", genFunc())
conn.commit()
conn.close()
endTime = time.clock()
return endTime - startTime
if __name__ == "__main__":
print "Python version: {0}".format(sys.version)
print "Python sqlite3 module version: {0}".format(sqlite3.version)
print "sqlite3.dll version: {0}".format(sqlite3.sqlite_version)
print "executemany takes: {0:,.3f} seconds for {1:,d}
inserts".format(executemany(), numInts)
print "execute takes {0:,.3f} seconds for {1:,d} inserts".format(execute(),
numInts)
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Keith Medcalf
Sent: Monday, October 23, 2017 11:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] Article about using sqlite3 in Python
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).
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
