Python/postgres/psycopg2: getting ID of row just inserted

Question

I'm using Python and psycopg2 to interface to postgres.

When I insert a row...

sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES ("
sql_string += hundred_name + ", '" + hundred_slug + "', " + status + ");"
cursor.execute(sql_string)

... how do I get the ID of the row I've just inserted? Trying:

hundred = cursor.fetchall() 

returns an error, while using RETURNING id:

sql_string = "INSERT INTO domes_hundred (name,name_slug,status) VALUES ("
sql_string += hundred_name + ", '" + hundred_slug + "', " + status + ") RETURNING id;"
hundred = cursor.execute(sql_string)

simply returns None.

UPDATE: So does currval (even though using this command directly into postgres works):

sql_string = "SELECT currval(pg_get_serial_sequence('hundred', 'id'));"
hundred_id = cursor.execute(sql_string)

Can anyone advise?

thanks!

Disposal

Answer 1

cursor.execute("INSERT INTO .... RETURNING id")
id_of_new_row = cursor.fetchone()[0]

And please do not build SQL strings containing values manually. You can (and should!) pass values separately, making it unnecessary to escape and SQL injection impossible:

sql_string = "INSERT INTO domes_hundred (name,name_slug,status) VALUES (%s,%s,%s) RETURNING id;"
cursor.execute(sql_string, (hundred_name, hundred_slug, status))
hundred = cursor.fetchone()[0]

See the psycopg docs for more details: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

Diplo

Just to clarify, the id in RETURNING id should be the field name of the serial / primary key field. – Babysit

cursor fetchone gives me "no results to fetch". – Unofficial

@Unofficial did you figure this out? – Patronage

@AlisonS @Unofficial I had the same error, but adding RETURNING id to the end of the INSERT query fixed it for me. – Mcguinness

Maybe just a little note, but important point to mention for everyone: Make sure you are only using cursor .execute() and not a cursor .mogrify() before the execute() command, otherwise (as in my case) cursor.fetchone() will not have any results! By using only cursor .execute() without "anything" before that command you will receive an id. – Hoeve

This is based on the assumption you are using the regular cursor. If you use the dict cursor the code should be adpated: id = cursor.fetchone()["id"] – Heartsick

Answer 2

I ended up here because I had a similar problem, but we're using Postgres-XC, which doesn't yet support the RETURNING ID clause. In that case you can use:

cursor.execute('INSERT INTO ........')
cursor.execute('SELECT LASTVAL()')
lastid = cursor.fetchone()['lastval']

Just in case it was useful for anyone!

Tantalite

Just remember - doing it in two statements like that runs a (very small) risk of race conditions, if something inserts a row into the database directly after you, but before your lastval() command returns the current value of the sequence. – Diogenes

Good post. If you are using something like psycopg2.extras.RealDictCursor that returns the results as dictionary you need to do something like this. Of course while keeping @Dave Thomas comment in mind. – Townsman

Answer 3

Consider a RETURNING clause http://www.postgresql.org/docs/8.3/static/sql-insert.html

Praetor

Answer 4

For me, neither ThiefMaster's answer worked nor Jamie Brown's. What worked for me was a mix of both, and I'd like to answer here so it can help someone else.

What I needed to do was:

cursor.execute('SELECT LASTVAL()')
id_of_new_row = cursor.fetchone()[0]

The statement lastid = cursor.fetchone()['lastval'] didn't work for me, even after cursor.execute('SELECT LASTVAL()'). The statement id_of_new_row = cursor.fetchone()[0] alone didn't work either.

Maybe I'm missing something.

Bonitabonito

Answer 5

ThiefMaster's approach worked for me, for both INSERT and UPDATE commands.

If cursor.fetchone() is called on a cursor after having executed an INSERT/UPDATE command but lacked a return value (RETURNING clause) an exception will be raised: ProgrammingError('no results to fetch'))

insert_query = """
    INSERT INTO hundred (id, name, name_slug, status)
        VALUES (DEFAULT, %(name)s, %(name_slug)s, %(status)s)
        RETURNING id;
"""

insert_query_values = { 
    "name": "",
    "name_slug": "",
    "status": ""
}

connection = psycopg2.connect(host="", port="", dbname="", user="", password="")

try:
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(insert_query, insert_query_values)
            num_of_rows_affected = cursor.rowcount
            new_row_id = cursor.fetchone()

except psycopg2.ProgrammingError as ex:
    print("...", ex)
    raise ex

finally:
    connection.commit()
    connection.close()

Hatchet