你的后续评论让我感到不安(特别是因为很明显你是对的)。所以我花了一些时间研究 python sqlite.c 库的源代码(https://svn.python.org/projects/python/trunk/Modules/_sqlite/ https://svn.python.org/projects/python/trunk/Modules/_sqlite/).
我认为问题是sqlite如何Connection
对象正在处理游标。在内部,Connection
对象维护游标和准备语句的列表。嵌套的db.execute('INSERT ...')
调用重置与关联的准备好的语句列表Connection
目的。
解决方案是不依赖快捷方式execute()方法的自动游标管理,并显式保存对正在运行的程序的引用。Cursor
. Cursors
维护自己的准备好的语句列表,这些列表与Connection
对象。
您可以显式创建游标或在 db.execute() 调用上调用 fetchall() 。后者的例子:
import sqlite3 as sq
db = sq.connect(':memory:')
db.execute('CREATE TABLE tbl (col INTEGER)')
db.execute('CREATE TABLE tbl2 (col INTEGER)')
db.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)])
db.commit()
print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))
# Read and print the values just inserted into tbl
for col in db.execute('SELECT col FROM tbl').fetchall():
print(col)
db.execute('INSERT INTO tbl2 VALUES (?)', col)
db.commit()
print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))
输出如预期:
count=3
(0,)
(1,)
(2,)
count=3
If the fetchall()
方法会限制内存,那么您可能需要依靠两个数据库连接之间的隔离(https://www.sqlite.org/isolation.html https://www.sqlite.org/isolation.html)。例子:
db1 = sq.connect('temp.db')
db1.execute('CREATE TABLE tbl (col INTEGER)')
db1.execute('CREATE TABLE tbl2 (col INTEGER)')
db1.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)])
db1.commit()
print('count=' + str(db1.execute('SELECT count(*) FROM tbl').fetchone()[0]))
db2 = sq.connect('temp.db')
# Read and print the values just inserted into tbl
for col in db1.execute('SELECT col FROM tbl').fetchall():
print(col)
db2.execute('INSERT INTO tbl2 VALUES (?)', col)
db2.commit()
print('count=' + str(db1.execute('SELECT count(*) FROM tbl').fetchone()[0]))