Ideally I'd like to be able to do something like:
id_of_new_row = cursor.lastrowid()
in which I get the id of the newly created or modified row. But this isn't available through psycopg2. Alternatively, I've tried this:
id_of_new_row = cursor.execute('INSERT INTO this_table (value1, value2, value3) VALUES (%s, %s, %s) RETURNING id', (some_value1, some_value2, some_value3))
which doesn't work, probably because it won't know the id until after the commit is made...
Help!
Sure it will, it'll know the ID as soon as the command finishes, that's how RETURNING is implemented. You need to actually fetch it though, so something like:
cursor.execute("INSERT INTO .... RETURNING id")
id_of_new_row = cursor.fetchone()[0]
should work in your scenario.
RETURNING works on Postgresql >= 8.2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With