如何在使用Python插入SQLite行后检索插入的id？

Question

如何在Python中插入行后检索已插入行的id？我有这样的表：

id INT AUTOINCREMENT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)

我插入了一行示例数据username="test"和password="test"。如何以事务安全的方式检索生成的ID？这是用于网站解决方案的，可能同时有两个人插入数据。我知道可以获取最后读取的行，但我认为那不是事务安全的。有人能给我一些建议吗？

- Jane

Answer 1

您可以使用cursor.lastrowid（参见“可选的DB API扩展”）：

connection=sqlite3.connect(':memory:')
cursor=connection.cursor()
cursor.execute('''CREATE TABLE foo (id integer primary key autoincrement ,
                                    username varchar(50),
                                    password varchar(50))''')
cursor.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('test','test'))
print(cursor.lastrowid)
# 1

如果两个人同时插入数据，只要他们使用不同的“光标”，“cursor.lastrowid”将返回“cursor”插入的最后一行的“id”：

cursor.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('blah','blah'))

cursor2=connection.cursor()
cursor2.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('blah','blah'))

print(cursor2.lastrowid)        
# 3
print(cursor.lastrowid)
# 2

cursor.execute('INSERT INTO foo (id,username,password) VALUES (?,?,?)',
               (100,'blah','blah'))
print(cursor.lastrowid)
# 100

注意，当您使用 executemany 一次插入多行时，lastrowid 返回 None：

cursor.executemany('INSERT INTO foo (username,password) VALUES (?,?)',
               (('baz','bar'),('bing','bop')))
print(cursor.lastrowid)
# None

- unutbu

---

还有last_insert_rowid() SQL 函数，它使你能够在下一个插入语句中将最后一行的 ID 作为外键完全以 SQL 的方式插入。 - Martijn Pieters

似乎Python的sqlite3的lastrowid是在底层使用“last_insert_rowid”。这并不能保证线程安全，但似乎是唯一的选择。请参阅https://dev59.com/HXI95IYBdhLWcg3w2Rzz。 - rogerdpack

这是指向 cursor.lastrowid 的直接链接。 - CGFoX

顺便提一下，这适用于INSERT，但不适用于UPDATE。但是在更新行时，您可能已经拥有相应的行ID了（只是我花了一段时间才弄清楚...）。 - CGFoX

“id”是什么意思？它是指整数类型的id列吗？它如何知道，如果我某个表没有id列怎么办？ - gamesguru

Answer 2

感谢在评论中的@Martijn Pieters提供的所有贡献：

您可以使用函数last_insert_rowid()：

last_insert_rowid()函数返回调用该函数的数据库连接中插入的最后一行的ROWID。 last_insert_rowid() SQL函数是sqlite3_last_insert_rowid() C/C++接口函数的包装器。

SQLite 3.35的RETURNING子句：

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT
);

INSERT INTO users (first_name, last_name)
VALUES ('Jane', 'Doe')
RETURNING id;

该语句用于在INSERT、UPDATE和DELETE语句中返回插入行的请求列。 Python 用法：

cursor.execute('INSERT INTO users (first_name, last_name) VALUES (?,?)'
               ' RETURNING id',
               ('Jane', 'Doe'))
row = cursor.fetchone()
(inserted_id, ) = row if row else None

- Stefan van den Akker

---

LAST_INSERT_ROWID() 函数是 SQLite 特有的。cursor.lastrowid 属性是 Python DB-API 规范的一部分，因此更具可移植性。 - Bill Weinman

请注意，RETURNING 子句可与 cursor.execute 一起使用，但无法与 cursor.executemany 一起使用。Python 将显示“executemany() 只能执行 DML 语句”的错误信息。 - Dominik

根据你的程序，如果存在多个线程可能同时写入数据库，你还需要小心处理竞争条件。在这种情况下，你不能假设last_insert_rowid是你刚插入的行。幸运的是，现在可以使用RETURNING来解决这个问题，但这种方法并不是在所有地方都可用。 - laurent

SQLite一次只允许一个写入者，所以这应该不可能@laurent。不是吗？ - Pieter-Jan Briers

如果在你写入数据和调用last_insert_rowid之间有其他程序向数据库写入数据，那么这种情况是可能发生的。 - laurent