SqlAlchemy: getting the id of the last record inserted

Question

I am using SQLAlchemy without the ORM, i.e. using hand-crafted SQL statements to directly interact with the backend database. I am using PG as my backend database (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 database:

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 database 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() 

Answer 2

User lastrowid

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

Answer 3

Current SQLAlchemy documentation suggests

result.inserted_primary_key should work!

Answer 4

Python + SQLAlchemy

after commit, you get the primary_key column id (autoincremeted) updated in your object.

db.session.add(new_usr)
db.session.commit() #will insert the new_usr data into database AND retrieve id
idd = new_usr.usrID # usrID is the autoincremented primary_key column. 
return jsonify(idd),201 #usrID = 12, correct id from table User in Database.