Returning a value with psycopg2

Question

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!

Answer 1

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.

Answer 2

RETURNING works on Postgresql >= 8.2