如果是新的，則獲取插入項的ID，如果不是新的，則獲取現有的ID-有解無憂

由于列t是UNIQUE,foo不會被插入兩次：

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data(id INTEGER PRIMARY KEY AUTOINCREMENT, t TEXT UNIQUE);")
c = conn.cursor()
c.execute("INSERT OR IGNORE INTO data(t) VALUES (?)", ("foo", ))
print(c.lastrowid)  # 1
c.execute("INSERT OR IGNORE INTO data(t) VALUES (?)", ("bar", ))
print(c.lastrowid)  # 2
c.execute("INSERT OR IGNORE INTO data(t) VALUES (?)", ("foo", ))
print(c.lastrowid)  # 2, how to get 1 instead, 
                    #    because "foo" already exists with ID=1?

如何獲取最后一行已經存在的item的ID，而不是之前成功插入的ID？

沒有額外SELECT的可能需要額外的時間嗎？

注意：這也不起作用：

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data(id INTEGER PRIMARY KEY AUTOINCREMENT, t TEXT UNIQUE);")
c = conn.cursor()
for r in c.execute("INSERT OR IGNORE INTO data(t) VALUES (?) RETURNING *;", ("foo", )):
    print(r)
print(c.lastrowid)
for r in c.execute("INSERT OR IGNORE INTO data(t) VALUES (?) RETURNING *;", ("bar", )):
    print(r)
print(c.lastrowid)
for r in c.execute("INSERT OR IGNORE INTO data(t) VALUES (?) RETURNING *;", ("foo", )):
    print(r)
print(c.lastrowid)

uj5u.com熱心網友回復：

是的，INSERT 陳述句有 RETURNING 子句。有關詳細資訊，請參閱檔案https://www.sqlite.org/lang_returning.html

請注意，如果您使用 INSERT OR IGNORE，則在發生沖突時不會發生任何事情并且不會回傳任何內容。您需要使用 UPSERT 語法，在發生沖突時強制進行虛擬更新，以確保您取回現有行。

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data(id INTEGER PRIMARY KEY AUTOINCREMENT, t TEXT UNIQUE);")
c = conn.cursor()
for r in c.execute("INSERT INTO data(t) VALUES (?) ON CONFLICT DO UPDATE SET t=t RETURNING *;", ("foo", )):
    print(r)
for r in c.execute("INSERT INTO data(t) VALUES (?) ON CONFLICT DO UPDATE SET t=t RETURNING *;", ("bar", )):
    print(r)
for r in c.execute("INSERT INTO data(t) VALUES (?) ON CONFLICT DO UPDATE SET t=t RETURNING *;", ("foo", )):
    print(r)

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/519837.html

標籤：Pythonsqlite

上一篇：SQLite–LIKE與IN運算子？

下一篇：列是串列或集合的SQL資料庫