Getting the id of the last record inserted for Postgresql SERIAL KEY with Python

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.

Answer 5

this question has been asked many times on stackoverflow and no answer I have seen is comprehensive. Googling 'sqlalchemy insert get id of new row' brings up a lot of them.

There are three levels to SQLAlchemy. Top: the ORM. Middle: Database abstraction (DBA) with Table classes etc. Bottom: SQL using the text function.

To an OO programmer the ORM level looks natural, but to a database programmer it looks ugly and the ORM gets in the way. The DBA layer is an OK compromise. The SQL layer looks natural to database programmers and would look alien to an OO-only programmer.

Each level has it own syntax, similar but different enough to be frustrating. On top of this there is almost too much documentation online, very hard to find the answer.

I will describe how to get the inserted id AT THE SQL LAYER for the RDBMS I use.

Table: User(user_id integer primary autoincrement key, user_name string)
conn: Is a Connection obtained within SQLAlchemy to the DBMS you are using.


SQLite
======
insstmt = text(
    '''INSERT INTO user (user_name)
    VALUES (:usernm) ''' )
# Execute within a transaction (optional)
txn = conn.begin()
result = conn.execute(insstmt, usernm='Jane Doe')
# The id!
recid = result.lastrowid
txn.commit()


MS SQL Server
=============
insstmt = text(
    '''INSERT INTO user (user_name) 
    OUTPUT inserted.record_id
    VALUES (:usernm) ''' )
txn = conn.begin()
result = conn.execute(insstmt, usernm='Jane Doe')
# The id!
recid = result.fetchone()[0]
txn.commit()


MariaDB/MySQL
=============
insstmt = text(
    '''INSERT INTO user (user_name)
    VALUES (:usernm) ''' )
txn = conn.begin()
result = conn.execute(insstmt, usernm='Jane Doe')
# The id!
recid = conn.execute(text('SELECT LAST_INSERT_ID()')).fetchone()[0]
txn.commit()


Postgres
========
insstmt = text(
    '''INSERT INTO user (user_name)
    VALUES (:usernm) 
    RETURNING user_id ''' )
txn = conn.begin()
result = conn.execute(insstmt, usernm='Jane Doe')
# The id!
recid = result.fetchone()[0]
txn.commit()

Answer 6

result.inserted_primary_key

Worked for me. The only thing to note is that this returns a list that contains that last_insert_id.

Answer 7

Make sure you use fetchrow/fetch to receive the returning object

insert_stmt = user.insert().values(name="homer", country_id="123").returning(user.c.id)

row_id = await conn.fetchrow(insert_stmt)

Answer 8

For Postgress inserts from python code is simple to use "RETURNING" keyword with the "col_id" (name of the column which you want to get the last inserted row id) in insert statement at end

syntax -

 from sqlalchemy import create_engine
 conn_string = "postgresql://USERNAME:PSWD@HOSTNAME/DATABASE_NAME"
 db = create_engine(conn_string)
 conn = db.connect()
 INSERT INTO emp_table (col_id, Name ,Age) 
        VALUES(3,'xyz',30) RETURNING  col_id;
 or
 (if col_id column is auto increment)
 insert_sql = (INSERT INTO emp_table (Name ,Age) 
        VALUES('xyz',30) RETURNING  col_id;)     
 result = conn.execute(insert_sql)
 [last_row_id] = result.fetchone()
 print(last_row_id)
 #output = 3

ex -