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

8

44

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?

Lucubration answered 21/12, 2011 at 12:14 Comment(2)
What does this have to do with SQLAlchemy?Frazer
For mysql and sqlalchemy, use result.lastrowid. I know the question is aimed for postgresql, Google don't, may be because of the title.Lambert
P
36

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()
Pinnatiped answered 21/12, 2011 at 13:58 Comment(8)
Definitely what I needed. The returned object was a ResultProxy in both cases, so I had to modify the code a little (by calling fetchone()) to get it to work.Lucubration
Alternatively, user.insert().returning(user.c.id).values({'name': 'Homer', 'country_id': 123})Celisse
This solution doesn't work with sqlite. It results in sqlalchemy.exc.CompileError: RETURNING is not supported by this dialect's statement compiler.. The other solution (lastrowid) works in that case.Abdomen
@PrahladYeri: This question is about Postgres, not SQLite.Pinnatiped
@ErwinBrandstetter I understand that, but the whole point of sqlalchemy is database abstraction, so I just noted that it may not be possible in this scenario (getting lastrowid).Abdomen
I also had to fetchall() since returning object was <sqlalchemy.engine.result.ResultProxy object at 0x0000029C26D1BEF0>Gibberish
To clarify the previous comments, if you modify your query to include a RETURNING id so you have something like this: result_obj = conn.execute("INSERT INTO user (name, country_id) VALUES ('Homer', 123) RETURNING id") then you can get the last inserted id by using fetchone() and getting items out of the tuple returned: last_inserted_id = result_obj.fetchone()[0]Camporee
the connection closed automatics after insert, no fetchone or fetchall works any helpful idea to what I can doBein
G
33

User lastrowid

result = conn.execute("INSERT INTO user (name, country_id) VALUES ('Homer', 123)")
result.lastrowid
Genoa answered 21/12, 2011 at 13:8 Comment(2)
Sorry i dont have pgsql at my desk i used mysql :)Genoa
More specifically, it doesn't work with tables with no oid, which is currently the default. And anyway it's often not the oid you are looking for: RETURNING is a way superior solution.Emilioemily
K
18

Current SQLAlchemy documentation suggests

result.inserted_primary_key should work!

Khaddar answered 30/12, 2017 at 21:38 Comment(0)
S
11

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.
Savagism answered 6/7, 2020 at 22:46 Comment(1)
For me this only works if my model class name is the same as the __tablename__ - if they are different I get an error when trying to access usrIDAugmentation
F
9

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()
Formularize answered 15/8, 2019 at 23:9 Comment(2)
Interesting, but couldn't get it to work for sql server - using SA, and pymssql.Decade
The connection string i use is mssql+pyodbc://MIPLTP041/dbname?driver=SQL+Server+Native+Client+10.0. Maybe pymssql uses one of the other protocols mentioned above?Formularize
E
0
result.inserted_primary_key

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

Evocator answered 30/10, 2018 at 19:57 Comment(0)
J
0

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)
Janeenjanek answered 27/12, 2018 at 19:28 Comment(0)
H
0

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 - enter image description here

Henri answered 2/10, 2022 at 11:26 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.