CODEWITHSUNDEEP
pythonsql
user984003
user984003

Reputation: 29567

Python/sqlite: get PK of newly inserted row

I am inserting a row using raw SQL and need to get the Primary key of the newly inserted row. I cannot assume that any of the non-PK inserted data are unique nor can I assume that the row is the last inserted row.

If it's not possible using raw SQL, how about with "regular" inserts? I am thinking that I will have to create a new customID field altogether. I would then create a random number variable and insert this as the unique ID. Since I inserted it, I know what it is. But that seems to defeat the purpose of having the PK as a unique identifier. Is there a way to pull the next ID of a sequence (as in Oracle)?

insert_sql = "insert into my_table(title, text) values ('the title', 'the text')"

cursor = connection.cursor()
cursor.execute(insert_sql)
transaction.commit_unless_managed()

Upvotes: 3

Views: 3142

Answers (1)

glglgl
glglgl

Reputation: 91059

... nor can I assume that the row is the last inserted row.

Globally, you cannot assume it. But related to your connection, you can - so you can use SELECT LAST_INSERT_ID() or just cursor.connection.insert_id(). Maybe even cursor.lastrowid, but I'm not sure if it that would work as well.

Upvotes: 4

Related Questions