python - 无法使用 Python 在 PostgreSQL 中获取最后插入的 ID

Question

我有一个表test_table，其中包含一个自动递增的字段id。现在我想要的是在运行 INSERT 语句后立即获取最后插入的 id。此时此刻，我所有的尝试都没有结果(顺便说一句，我在 MySQL 中得到了完全相同的行为，只有在 SQLite 中一切正常)。这是一些重现这种意外行为的代码:

>>> import psycopg2
>>> cnx = psycopg2.connect(host="127.0.0.1", dbname="reestr", user="postgres", password="postgres")
>>> cur = cnx.cursor()
>>> cur.execute("INSERT INTO test_table (field_1) VALUES ('hello')")
>>> cur.execute("INSERT INTO test_table (field_1) VALUES ('hello')")
>>> cur.execute("SELECT CURRVAL(pg_get_serial_sequence('test_table','id'))")
>>> res = cur.fetchone()
>>> res
(None,)
>>> cur.execute("SELECT id, field_1 FROM test_table")
>>> res = cur.fetchall()
>>> res
[(1, 'hello'), (2, 'hello')]

我们清楚地看到，id 列自动递增，但是，出于某些疯狂的原因，SELECT CURRVAL(pg_get_serial_sequence('test_table','id')) 不返回任何内容。这有什么问题？顺便说一句，我还尝试在一个事务中显式运行这些查询并得到完全相同的结果。

Answer 1

因为 PostgreSQL 文档说:

pg_get_serial_sequence(table_name, column_name) | text | get name of the sequence that a serial or bigserial column uses

(强调我的)因为您的列没有明确声明为 SERIAL 或 BIGSERIAL，pg_get_serial_sequence 不返回任何内容。如果你想使用pg_get_serial_sequence，你必须声明:

CREATE TABLE test_table (id SERIAL, field_1 VARCHAR(128))

---

无论如何，生成的键是 PostgreSQL 中的一个常见问题。我在Java中遇到过，但在Python中应该是一样的。

PostgreSQL 有一个特定的扩展名 RETURNING，而不是从游标访问最后生成的键的常见习惯用法。摘自 INSERT 的 PostreSQL 文档

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

...The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number... The syntax of the RETURNING list is identical to that of the output list of SELECT.

因此，我的建议是坚持您当前的声明，但将您的插入代码更改为:

>>> cur.execute("INSERT INTO test_table (field_1) VALUES ('hello') RETURNING id")
>>> res = cur.fetchone()
>>> last_inserted_id = res[0]