使用Python获取Postgresql SERIAL KEY插入的最后一条记录的id

Question

我正在使用SQLAlchemy，但并没有使用ORM，也就是说，我使用手动编写的SQL语句直接与后端数据库进行交互。在这个实例中，我使用PG作为后端数据库（使用psycopg2作为DB驱动程序），我不知道这是否会影响答案。以下是我使用的一些语句，为了简洁起见，假设conn是一个有效的数据库连接：

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

假设用户表由列 (id [SERIAL PRIMARY KEY], name, country_id) 组成。

我如何在不再次访问数据库的情况下获取新增用户的id？

- Homunculus Reticulli

---

这与SQLAlchemy有何关联？ - Zoran Pavlovic

针对 mysql 和 sqlalchemy，请使用 result.lastrowid。我知道这个问题是针对 postgresql 的，但 Google 可能不知道，可能是因为标题。 - am.rez

Answer 1

您可以像这样使用INSERT语句的RETURNING子句：

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

如果你只想要结果的id：

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

- Erwin Brandstetter

---

肯定是我需要的。在这两种情况下，返回的对象都是 ResultProxy，因此我必须稍微修改代码（通过调用 fetchone()）才能使其正常工作。 - Homunculus Reticulli

另外，user.insert().returning(user.c.id).values({'name': 'Homer', 'country_id': 123})。 - Pakman

这个解决方案不适用于 sqlite。它会导致 sqlalchemy.exc.CompileError: RETURNING is not supported by this dialect's statement compiler.。在这种情况下，另一个解决方案(lastrowid)可以使用。 - Prahlad Yeri

@ErwinBrandstetter 我明白，但 sqlalchemy 的整个目的是数据库抽象化，所以我只是指出在这种情况下（获取 lastrowid）可能不可能。 - Prahlad Yeri

为了澄清先前的评论，如果您修改查询以包括“RETURNING id”，则会得到以下内容： result_obj = conn.execute("INSERT INTO user (name, country_id) VALUES ('Homer', 123) RETURNING id") 那么您可以通过使用“fetchone（）”并从返回的元组中获取项目来获得最后插入的ID：last_inserted_id = result_obj.fetchone()[0]。 - Everett

Answer 2

用户lastrowid

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

- Nilesh

---

抱歉，我桌子上没有pgsql，我用的是mysql :) - Nilesh

更具体地说，它无法处理没有 oid 的表格，这是当前的默认设置。而且，通常你要找的不是 oid：使用 RETURNING 是一种更加优越的解决方案。 - piro

感谢您提供的MySQL解决方案。 - Pakman

Answer 3

目前的SQLAlchemy 文档建议使用 result.inserted_primary_key ！

- Milind Dalvi

Answer 4

Python + SQLAlchemy

提交（commit）后，您可以在对象中获得已更新的主键列id（自增长）。

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.

- Arthur Zennig

---

只有当我的模型类名称与 __tablename__ 相同时，这才对我起作用 - 如果它们不同，当尝试访问 usrID 时我会收到一个错误。 - Dipole

Answer 5

这个问题在stackoverflow上被问了很多次，但我看到的答案都不太全面。谷歌搜索“sqlalchemy insert get id of new row”会出现很多相关信息。

SQLAlchemy有三个层次：顶层是ORM，中间是数据库抽象（DBA）层，包括Table类等，底层是使用text函数的SQL。

对于面向对象的程序员来说，ORM层看起来很自然，但对于数据库程序员来说，它看起来很丑陋，ORM会妨碍他们工作。DBA层是一个可以接受的妥协。SQL层对于数据库程序员来说很自然，但对仅了解面向对象编程的程序员来说则很陌生。

每个层次都有自己的语法，类似但又有不同之处，这让人感到沮丧。此外，在网上还有几乎过多的文档，很难找到答案。

我将描述如何在我使用的关系型数据库的SQL层面获取插入的id。

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

- Mark Kortink

---

有趣的是，但无法在SQL Server上运行 - 使用SA和pymssql。 - Marc

我使用的连接字符串是mssql+pyodbc://MIPLTP041/dbname?driver=SQL+Server+Native+Client+10.0。也许pymssql使用上述其他协议之一？ - Mark Kortink

Answer 6

result.inserted_primary_key

对我有用。唯一需要注意的是，这将返回一个包含last_insert_id的列表。

- Walter Schweitzer

Answer 7

对于Python代码从PostgreSQL插入数据，使用"RETURNING"关键字很容易获取插入最后一行的列ID（要获取的列名）

语法：

 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

例如 -

- itsmtech

Answer 8

确保您使用fetchrow/fetch来接收returning对象

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

row_id = await conn.fetchrow(insert_stmt)

- Shadycorp