CODEWITHSUNDEEP
pythonsqlite
Whymarrh
Whymarrh

Reputation: 13575

SQLite insert or ignore and return original _rowid_

I've spent some time reading the SQLite docs, various questions and answers here on Stack Overflow, and this thing, but have not come to a full answer.

I know that there is no way to do something like INSERT OR IGNORE INTO foo VALUES(...) with SQLite and get back the rowid of the original row, and that the closest to it would be INSERT OR REPLACE but that deletes the entire row and inserts a new row and thus gets a new rowid.

Example table:

CREATE TABLE foo(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    data TEXT
);

Right now I can do:

sql = sqlite3.connect(":memory:")
# create database
sql.execute("INSERT OR IGNORE INTO foo(data) VALUES(?);", ("Some text.", ))
the_id_of_the_row = None
for row in sql.execute("SELECT id FROM foo WHERE data = ?", ("Some text.", )):
    the_id_of_the_row = row[0]

But something ideal would look like:

the_id_of_the_row = sql.execute("INSERT OR IGNORE foo(data) VALUES(?)", ("Some text", )).lastrowid

What is the best (read: most efficient) way to insert a row into a table and return the rowid, or to ignore the row if it already exists and just get the rowid? Efficiency is important because this will be happening quite often.

Is there a way to INSERT OR IGNORE and return the rowid of the row that the ignored row was compared to? This would be great, as it would be just as efficient as an insert.

Upvotes: 25

Views: 15489

Answers (2)

Whymarrh
Whymarrh

Reputation: 13575

The way that worked the best for me was to insert or ignore the values, and the select the rowid in two separate steps. I used a unique constraint on the data column to both speed up selects and avoid duplicates.

sql.execute("INSERT OR IGNORE INTO foo(data) VALUES(?);" ("Some text.", ))
last_row_id = sql.execute("SELECT id FROM foo WHERE data = ?;" ("Some text. ", ))

The select statement isn't as slow as I thought it would be. This, it seems, is due to SQLite automatically creating an index for the unique columns.

Upvotes: 17

CL.
CL.

Reputation: 180200

INSERT OR IGNORE is for situations where you do not care about the identity of the record; where the goal is only to have some record with that specific value.

If you want to know whether a new record is inserted or not, you have to check by hand:

the_id_of_the_row = None
for row in sql.execute("SELECT id FROM foo WHERE data = ?", ...):
    the_id_of_the_row = row[0]
if the_id_of_the_row is None:
    c = sql.cursor()
    c.execute("INSERT INTO foo(data) VALUES(?)", ...)
    the_id_of_the_row = c.lastrowid

As for efficiency: when SQLite checks the datacolumn for duplicates, it has to do exactly the same query that you're doing with the SELECT, and once you've done that, the access path is in the cache, so performance should not be a problem. In any case, it is necessary to execute two separate INSERT/SELECT queries (in either order, both your and my code work, but yours is simpler).

Upvotes: 0

Related Questions