Python，SQLite3：当提交干预时，游标返回重复 [英] Python, SQLite3: cursor returns duplicates when a commit intervenes

问题描述

此Python代码创建一个表，在其中插入三行，并遍历这些行，在光标完全耗尽之前插入提交。为什么它返回五行而不是三行？如果删除了中间提交，则返回的行数按预期为三。或者是否希望提交（甚至不触及有问题的表）使光标无效？

编辑：添加了一个被遗忘的commit（这会使问题消失）和插入到一个不相关的表（这使问题再次出现）。

 ＃！/ usr / bin / env python3 
 
将sqlite3导入为sq 
 
 db = sq.connect（'：memory：'）
 
 db.execute（'CREATE TABLE tbl（col INTEGER）'）
 db.execute（'CREATE TABLE tbl2（col INTEGER）'）
 db.executemany（'INSERT INTO tbl ）VALUES（？）'，[（0，），（1，），（2，）]）
 db.commit（）
 
 print（'count ='+ str db.execute（'SELECT count（*）FROM tbl'）。fetchone（）[0]）
 
＃读取并打印刚刚插入到tbl 
中的值。执行（'SELECT col FROM tbl'）：
 print（col）
 db.execute（'INSERT INTO tbl2 VALUES（？）'，col）
 db.commit b 
 print（'count ='+ str（db.execute（'SELECT count（*）FROM tbl'）。fetchone（）[0]））
  pre> 
 
 
输出为：
  count = 3 
（0，）
（1，）
（0，）
（1，）
（2，）
 count = 3 
  
通常，插入N行，迭代器返回N + 2行，显然总是前两个重复。 p> 
 
解决方案
您的后续评论扰乱了我（特别是因为很清楚你是对的）。所以我花了一些时间研究python _sqlite.c库的源代码（ https ：//svn.python.org/projects/python/trunk/Modules/_sqlite/ ）。 
 
 
 
我认为问题是sqlite  Connection 对象如何处理游标。在内部， Connection 对象维护一个游标和准备语句的列表。嵌套的 db.execute（'INSERT ...'）调用重置与连接目的。 
 
 
 
解决方案是不依赖快捷方式execute（）方法的自动游标管理，并显式保存对运行 Cursor 。 游标维护自己的与连接对象分开的预备语句列表。
 
 
 
您可以在db.execute（）调用中显式创建游标或调用fetchall（）。后期示例：
 导入sqlite3为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]）
 
＃读取并打印刚刚插入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 
  
如果 fetchall（）方法是内存禁止，那么您可能需要回退依赖于两个数据库连接之间的隔离（ https://www.sqlite.org/isolation.html ）。示例：
  db1 = sq.connect（'temp.db'）
 
 db1.execute 'CREATE TABLE tbl2（col INTEGER）'）
 db1.executemany（'INSERT INTO tbl（col）VALUES（？）'，'CREATE TABLE tbl（col INTEGER）'）
 db1.execute （0），（1，），（2，）]）
 db1.commit（）
 
 print（'count ='+ str（db1.execute *）FROM tbl'）。fetchone（）[0]）
 
 db2 = sq.connect（'temp.db'）
 
＃读取并打印值insert 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 ]））
  
 
This Python code creates a table, inserts three rows into it and iterates through the rows, with intervening commits before the cursor has been fully exhausted. Why does it return five rows instead of three? If the intervening commit is removed, the number of returned rows is three as expected. Or is it expected that a commit (which doesn't even touch the table in question) invalidates a cursor?


Edit: Added a forgotten commit (which makes the issue disappear) and an insert to an unrelated table (which makes the issue appear again).
#!/usr/bin/env python3

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'):
    print(col)
    db.execute('INSERT INTO tbl2 VALUES (?)', col)
    db.commit()

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))
The output is:
count=3
(0,)
(1,)
(0,)
(1,)
(2,)
count=3
Generally, with N rows inserted, N+2 rows are returned by the iterator, apparently always with the first two duplicated.
解决方案
Your followup comment disturbed me (particularly because it was clear you were right). So I spent some time studying the source code to the python _sqlite.c library (https://svn.python.org/projects/python/trunk/Modules/_sqlite/).  

I think the problem is how the sqlite Connection object is handling cursors. Internally, Connection objects maintain a list of cursors AND prepared statements. The nested db.execute('INSERT ...') call resets the list of prepared statements associated to the Connection object. 

The solution is to not rely on the shortcut execute() method's automatic cursor management, and to explicitly hold a reference to the running Cursor. Cursors maintain their own prepared statement lists which are separate from Connection objects.

You can either explicitly create a cursor OR invoke fetchall() on the db.execute() call.  Example of the later:
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]))
The output is as expected:
count=3
(0,)
(1,)
(2,)
count=3
If the fetchall() approach is memory prohibitive, then you may need to fall back to relying on isolation between two database connections (https://www.sqlite.org/isolation.html).  Example:
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]))


                        
这篇关于Python，SQLite3：当提交干预时，游标返回重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！