CODEWITHSUNDEEP
pythonsqlsqliteinsert
xralf
xralf

Reputation: 3762

Value of lastrowid after "insert or ignore"

I am using sqlite, and I have a Python code as follows:

...
cur.execute("insert or ignore into books (title, authors, ...) \
values (:title, :authors, ..."), locals())
...
bookId = cur.lastrowid

If the ignore part of the select statement applies

then the value of cur.lastrowid is 0.

But this is not what I want. I'd like to get books.id value from the database in any case.

Should I use select statement or is there smarter way to achieve it?

My temporary solution:

if bookId == 0:
    cur.execute("select id from books where \
    title = :title and authors = :authors", locals())
    bookId = cur.fetchone()[0]

Upvotes: 6

Views: 1949

Answers (2)

Alkanshel
Alkanshel

Reputation: 4449

Try using on duplicate key update id=LAST_INSERT_ID(id) for the PK. Seems like lastrowID will be correct from then on out.

Upvotes: 4

CL.
CL.

Reputation: 180260

There is no better way. To read some existing value from the database, there's only SELECT.

Upvotes: 6

Related Questions