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

How to retrieve inserted id after inserting row in SQLite using Python? I have table like this:

如何使用Python在SQLite中插入行后检索插入的id ?我有这样的桌子:

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

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?

我插入一个新行，其中示例数据用户名=“test”，密码=“test”。如何以安全的方式检索生成的id ?这是一个网站解决方案，两个人可能同时插入数据。我知道我能读到最后一行，但我不认为这是交易安全的。谁能给我一些建议吗?

1 个解决方案

#1

188

You could use cursor.lastrowid (see "Optional DB API Extensions"):

您可以使用游标。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

If two people are inserting at the same time, as long as they are using different cursors, cursor.lastrowid will return the id for the last row that cursor inserted:

如果两个人同时插入，只要他们使用不同的游标，游标。lastrowid将返回光标插入的最后一行的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

Note that lastrowid returns None when you insert more than one row at a time with executemany:

注意，当使用executemany一次插入多个行时，lastrowid不返回任何值:

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

#1

188