Python sqlite3: UPDATE失败提示“没有这样的列”

Question

以下这个脚本在执行UPDATE命令时出现了异常。我认为对于这个简单的UPDATE命令，db_2.foo.bar的值应该从1变成2。我的猜测是，我在UPDATE语句中可能有一些微妙的语法错误（或者是Python或sqlite3本身的bug）；不过，我仔细查看了sqlite3的文档，特别是“UPDATE”和“expression”页面，没发现我有什么做错的地方。

db_1.foo_bar= (1,)
Traceback (most recent call last):
  File "try2.py", line 29, in <module>
    db_2.execute( 'UPDATE foo SET bar = bar + db_1.foo.bar WHERE rowid = db_1.foo.rowid' ) 
sqlite3.OperationalError: no such column: db_1.foo.bar

有没有什么建议或者解决方法？

import sqlite3

# Create db_1, populate it and close it:
open( 'db_1.sqlite', 'w+' )
db_1 =  sqlite3.connect( 'db_1.sqlite' )
db_1.execute( 'CREATE TABLE foo(bar INTEGER)' )
db_1.execute( 'INSERT INTO foo (bar) VALUES (1)' )
db_1.commit()
db_1.close()

# Create db_2:
open( 'db_2.sqlite', 'w+' )
db_2 =  sqlite3.connect( 'db_2.sqlite' )
db_2.execute( 'CREATE TABLE foo(bar INTEGER)' )

# Attach db_1 to db_2 connection:
db_2.execute( 'ATTACH "db_1.sqlite" AS db_1' )

# Populate db_2 from db_1:
db_2.execute( 'INSERT INTO foo SELECT ALL * FROM db_1.foo' )

# Show that db_1.foo.bar exists:
cur_2 = db_2.cursor()
cur_2.execute( 'SELECT bar from db_1.foo' )
for result in cur_2.fetchall():
    print 'db_1.foo_bar=', result

# However, the following claims that db_1.foo.bar does not exist:
db_2.execute( 'UPDATE foo SET bar = bar + db_1.foo.bar WHERE rowid = db_1.foo.rowid' ) 

db_2.execute( 'DETACH db_1')
db_2.commit()
db_2.close()

Answer 1

嗯……问题可能是因为我在查看3.7.3版本的文档，而我安装的版本是3.6.16。我正在调查这个情况。

Answer 2

要用来自不同表的数据更新 foo，你可以使用嵌套的 SELECT 表达式。注意，foo.rowid 是外部表的行 ID，而 t.rowid 是内部表的行 ID：

cur_2.execute( '''\
    UPDATE foo SET bar = bar +
        IFNULL( (SELECT t.bar 
                 FROM db_1.foo AS t
                 WHERE foo.rowid = t.rowid), 0)''' )  

为了测试是否正确匹配了 rowids，我稍微修改了一下你的代码，让 db_1.foo 的 rowids 和 db_2.foo 的 rowids 不匹配：

import sqlite3

# Create db_1, populate it and close it:
open( 'db_1.sqlite', 'w+' )
db_1 =  sqlite3.connect( 'db_1.sqlite' )
db_1.execute( 'CREATE TABLE foo(bar INTEGER)' )
db_1.execute( 'INSERT INTO foo (rowid,bar) VALUES (2,1)' )
db_1.execute( 'INSERT INTO foo (rowid,bar) VALUES (3,2)' )
db_1.commit()
db_1.close()

# Create db_2:
open( 'db_2.sqlite', 'w+' )
db_2 =  sqlite3.connect( 'db_2.sqlite' )
cur_2 = db_2.cursor()
cur_2.execute( 'CREATE TABLE foo(bar INTEGER)' )

# Attach db_1 to db_2 connection:
cur_2.execute( 'ATTACH "db_1.sqlite" AS db_1' )

# Populate db_2 from db_1:
cur_2.execute( 'INSERT INTO foo SELECT * FROM db_1.foo' )

注意，foo 的 rowids 是 1 和 2：

cur_2.execute( 'SELECT rowid,bar from foo' )
for result in cur_2.fetchall():
    print('foo: {0}'.format(result))
    # foo: (1, 1)
    # foo: (2, 2)

注意，db_1.foo 的 rowids 是 2 和 3：

# Show that db_1.foo.bar exists:
cur_2.execute( 'SELECT rowid,bar from db_1.foo' )
for result in cur_2.fetchall():
    print('db_1.foo: {0}'.format(result))
    # db_1.foo: (2, 1)
    # db_1.foo: (3, 2)

cur_2.execute( '''\
    UPDATE foo SET bar = bar +
        IFNULL( (SELECT t.bar 
                 FROM db_1.foo AS t
                 WHERE foo.rowid = t.rowid), 0)''' )  

在更新之后，行 ID 为 1 的数据没有变化，而行 ID 为 2 的数据已经被更新了。

cur_2.execute( 'SELECT rowid,bar from foo' )
for result in cur_2.fetchall():
    print('foo after update: {0} '.format(result))
    # foo after update: (1, 1) 
    # foo after update: (2, 3) 

cur_2.execute('DETACH db_1')
db_2.commit()
db_2.close()

我发现这些页面对构建这个答案很有帮助：这里和 这里，不过任何错误都是我自己的。