Sqlite3 游标实时更新? [英] Sqlite3 cursors live updating?

问题描述

谁能给我解释一下:

import sqlite3

db = sqlite3.connect(':memory:')

db.execute('create table t1 (id integer primary key, val text)')
db.execute('create table t2 (id integer primary key, val text)')

c = db.cursor()
c.execute('insert into t1 values (?, ?)', (1, 'a'))
c.execute('insert into t2 values (?, ?)', (1, 'b'))
c.execute('insert into t1 values (?, ?)', (2, 'c'))
c.execute('insert into t2 values (?, ?)', (2, 'd'))

c.execute('''select t1.id, t1.val, t2.val
             from t1
             left join t2 using (id)
             where t1.id is not null

             union all

             select t2.id, t1.val, t2.val
             from t2
             left join t1 using (id)
             where t2.id is not null
             and t1.id is null

             ''')
for row in c:
    print(row[0])
    if row[0] == 1:
        c2 = db.cursor()
        c2.execute('delete from t1 where id = ?', (row[0],))

如果我注释掉最后三行，输出是:

If I comment out the last three lines, the output is:

1
2

但是如果我取消最后三行的注释，输出是:

But if I uncomment the last three lines, the output is:

1
2
1

即.第一个游标已更新为第二个游标中执行的 DML 的结果.

ie. the first cursor has been updated with the results of DML executed in the second cursor.

这是预期的行为吗?有什么办法可以防止吗?

Is this expected behaviour? Is there some way to prevent it?

我正在运行 Python 3.6.3(根据 Ubuntu 17.10)，以防万一.

I'm running Python 3.6.3 (as per Ubuntu 17.10), in case that makes a difference.

推荐答案

如果可能，SQLite 会根据需要计算结果行.但这并不总是可行的，因此无法保证.

SQLite computes results rows on demand, if possible. But this is not always possible, so there is no guarantee.

您永远不应该修改您当前正在另一个查询中读取的任何表.(数据库可能会以不明显的方式扫描表，因此即使更改其他行也可能会更改枚举.)

You should never modify any table that you are currently reading in another query. (The database might scan the table in unobvious ways, so even changes to other rows might change the enumeration.)

如果您打算进行此类修改，则必须在进行更改之前读取所有行，例如 for row in c.fetchall().或者，分步读取表格，重新搜索最后一个查询离开的位置，即:

If you intend to do such modifications, you have to read all rows before doing the changes, e.g., for row in c.fetchall(). Alternatively, read the table in single steps that re-search for the place where the last query left, i.e.:

SELECT ... FROM MyTable WHERE ID > :LastID ORDER BY ID LIMIT 1;

这篇关于Sqlite3 游标实时更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！