Getting the Id of the Last Record Inserted for Postgresql Serial Key With Python

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

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

Unable to get last inserted id in PostgreSQL using Python

Because PostgreSQL doc says:

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

(emphasize mine) as your column is not explicitely declared as SERIAL nor BIGSERIAL, pg_get_serial_sequence returns nothing. If you want to use pg_get_serial_sequence, you must declare:

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

---

Anyway, generated keys are a common issue in PostgreSQL. I ran into it in Java, but it should be the same in Python.

Instead of the common idiom of giving access to last generated key from a cursor, PostgreSQL has a specific extension with RETURNING. Extract from PostreSQL documentation for INSERT

[ 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.

So here my advice would be to stick to your current declaration but change your insertion code to:

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

PostgreSQL function for last inserted ID

( tl;dr : goto option 3: INSERT with RETURNING )

Recall that in postgresql there is no "id" concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type).

If you are interested in getting the id of a newly inserted row, there are several ways:

---

Option 1: CURRVAL(<sequence name>);.

For example:

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

The name of the sequence must be known, it's really arbitrary; in this example we assume that the table persons has an id column created with the SERIAL pseudo-type. To avoid relying on this and to feel more clean, you can use instead pg_get_serial_sequence:

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval(pg_get_serial_sequence('persons','id'));

Caveat: currval() only works after an INSERT (which has executed nextval() ), in the same session.

---

Option 2: LASTVAL();

This is similar to the previous, only that you don't need to specify the sequence name: it looks for the most recent modified sequence (always inside your session, same caveat as above).

---

Both CURRVAL and LASTVAL are totally concurrent safe. The behaviour of sequence in PG is designed so that different session will not interfere, so there is no risk of race conditions (if another session inserts another row between my INSERT and my SELECT, I still get my correct value).

However they do have a subtle potential problem. If the database has some TRIGGER (or RULE) that, on insertion into persons table, makes some extra insertions in other tables... then LASTVAL will probably give us the wrong value. The problem can even happen with CURRVAL, if the extra insertions are done intto the same persons table (this is much less usual, but the risk still exists).

---

Option 3: INSERT with RETURNING

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;

This is the most clean, efficient and safe way to get the id. It doesn't have any of the risks of the previous.

Drawbacks? Almost none: you might need to modify the way you call your INSERT statement (in the worst case, perhaps your API or DB layer does not expect an INSERT to return a value); it's not standard SQL (who cares); it's available since Postgresql 8.2 (Dec 2006...)

---

Conclusion: If you can, go for option 3. Elsewhere, prefer 1.

Note: all these methods are useless if you intend to get the last inserted id globally (not necessarily by your session). For this, you must resort to SELECT max(id) FROM table (of course, this will not read uncommitted inserts from other transactions).

Conversely, you should never use SELECT max(id) FROM table instead one of the 3 options above, to get the id just generated by your INSERT statement, because (apart from performance) this is not concurrent safe: between your INSERT and your SELECT another session might have inserted another record.

Python/postgres/psycopg2: getting ID of row just inserted

cursor.execute("INSERT INTO .... RETURNING id")
id_of_new_row = cursor.fetchone()[0]

And please do not build SQL strings containing values manually. You can (and should!) pass values separately, making it unnecessary to escape and SQL injection impossible:

sql_string = "INSERT INTO domes_hundred (name,name_slug,status) VALUES (%s,%s,%s) RETURNING id;"
cursor.execute(sql_string, (hundred_name, hundred_slug, status))
hundred = cursor.fetchone()[0]

See the psycopg docs for more details: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

sqlalchemy flush() and get inserted id?

Your sample code should have worked as it is. SQLAlchemy should be providing a value for f.id, assuming its an autogenerating primary-key column. Primary-key attributes are populated immediately within the flush() process as they are generated, and no call to commit() should be required. So the answer here lies in one or more of the following:

1. The details of your mapping
2. If there are any odd quirks of the backend in use (such as, SQLite doesn't generate integer values for a composite primary key)
3. What the emitted SQL says when you turn on echo

Get/use autoincremented Primary Key of a new record as a Foreign Key for a record in another table

You can use inserted_primary_key to find the primary key of the most recently inserted record.

https://kite.com/python/docs/sqlalchemy.engine.ResultProxy.inserted_primary_key

According to the docs it's auto-populated depending on your databse backend, here's an example from https://overiq.com/sqlalchemy-101/crud-using-sqlalchemy-core/

ins = insert(customers)

r = conn.execute(ins, 
    first_name = "Tim", 
    last_name = "Snyder", 
    username = "timsnyder", 
    email = "timsnyder@mail.com",
    address = '1611 Sundown Lane',
    town = 'Langdale'
)
r.inserted_primary_key

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

pygresql - insert and return serial

The documentation in PyGreSQL says that if you call dbconn.query() with and insert/update statement that it will return the OID. It goes on to say something about lists of OIDs when there are multiple rows involved.

First of all; I found that the OID features did not work. I suppose knowing the version numbers of the libs and tools would have helped, however, I was not trying to return the OID.

Finally; by appending "returning id", as suggested by @hacker, pygresql simply did the right thing and returned a record-set with the ID in the resulting dictionary (see code below).

sql = "insert into job_runners (hostname) values ('localhost') returning id"
rv = dbconn.query(sql)
id = rv.dictresult()[0]['id']

---

Related Topics