SqlAlchemy: getting the id of the last record inserted

Question

I am using SQLAlchemy without the ORM, i.e. using hand crafted SQL statememts to directly interact with the backend db. I am using PG as my backend db (psycopg2 as DB driver) in this instance - I don't know if that affects the answer.

I have statements like this (for brevity, assume that conn is a valid connection to the db):

conn.execute("INSERT INTO user (name, country_id) VALUES ('Homer', 123)")

Assume also that the user table consists of the columns (id [SERIAL PRIMARY KEY], name, country_id)

How may I obtain the id of the new user (ideally, without hitting the db again?)

Answer 1

You might be able to use the RETURNING clause of the INSERT statement like this:

result = conn.execute("INSERT INTO user (name, country_id) VALUES ('Homer', 123)
                       RETURNING *")

If you only want the resulting id:

result = conn.execute("INSERT INTO user (name, country_id) VALUES ('Homer', 123)
                        RETURNING id")
[new_id] = result.fetchone()