Get the ID of inserted item if new, or the existing ID if it's not new-CodePudding

Since the column t is UNIQUE, foo won't be inserted twice:

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?

How to get the ID of the already-existing item in the last line instead of the ID of the previous successful insertion?

Is it possible without an additional SELECT that would take additional time?

Note: this doesn't work either:

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)

CodePudding user response：

Yes, there is the RETURNING clause for the INSERT statement. Please see docs for details https://www.sqlite.org/lang_returning.html

Note, if you use INSERT OR IGNORE, nothing happens when conflict occurs and nothing is returned. You need to use the UPSERT syntax, to force a dummy update in case of a conflict to make sure you get the existing row back.

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)