使用Python在SQLite中插入行后如何检索插入的ID? [英] How to retrieve inserted id after inserting row in SQLite using Python?
问题描述
如何在使用 Python 在 SQLite 中插入行后检索插入的 id?我有这样的桌子:
How to retrieve inserted id after inserting row in SQLite using Python? I have table like this:
id INT AUTOINCREMENT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)
我插入一个包含示例数据 username="test"
和 password="test"
的新行.如何以交易安全的方式检索生成的 id?这是针对网站解决方案,其中两个人可能同时插入数据.我知道我可以获得最后读取的行,但我认为这不是事务安全的.有人可以给我一些建议吗?
I insert a new row with example data username="test"
and password="test"
. How do I retrieve the generated id in a transaction safe way? This is for a website solution, where two people may be inserting data at the same time. I know I can get the last read row, but I don't think that is transaction safe. Can somebody give me some advice?
推荐答案
您可以使用 cursor.lastrowid(请参阅可选数据库 API扩展名"):
You could use cursor.lastrowid (see "Optional DB API Extensions"):
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
,cursor.lastrowid
将返回id
对于 cursor
插入的最后一行:
If two people are inserting at the same time, as long as they are using different cursor
s, cursor.lastrowid
will return the id
for the last row that cursor
inserted:
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
:
Note that lastrowid
returns None
when you insert more than one row at a time with executemany
:
cursor.executemany('INSERT INTO foo (username,password) VALUES (?,?)',
(('baz','bar'),('bing','bop')))
print(cursor.lastrowid)
# None
这篇关于使用Python在SQLite中插入行后如何检索插入的ID?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!