How to access a postgresql defined variable from Python

Question

I have a python3 code, where I'm inserting data into a postgres table, where the table has a SERIAL integer column, whose value I would like to return after insert.

I have tried using 'Returning', but did not get the ID returned in response

query = INSERT INTO table ({{table_columns}}) VALUES ({{table_values}}) RETURNING id;
response = cur.execute(query)

Found help in using into but that was still returning nothing, also tried selecting the defined variable value.

DO $$
DECLARE tableId integer;
BEGIN
INSERT INTO table ({{table_columns}}) VALUES ({{table_values}}) RETURNING id INTO tableId;
 SELECT tableId;
END $$;

This threw an error.

response = cur.execute(query)
psycopg2.errors.SyntaxError: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement

How to access the value returned in tableId outside from Python after query execution ? Any other alternative solution of returning the id value is also welcome.

Postgres Version 11.2

• user330315: A DO black can not return anything. You will need to wrap your INSERT into a function if you want to do that.
• user7070613: The error is due to this; ` SELECT tableId;. That would need to be PERFORM tableid` to make plpgsql happy. It is not going to solve the issue of DO blocks not returning anything. Also in a regular function it would not help either as you would not actually be returning the value. That would require RETURN tableId; and the correct RETURNS at the top of the function definition.

Answer 1

To retrieve the id using RETURNING, call the cursor's fetchone or fetchall method* after executing** the statement.

>>> conn = psycopg2.connect(dbname='test')
>>> cur = conn.cursor()
>>> cur.execute("""create table t04 (id serial, name varchar)""")
>>> conn.commit()
>>> cur.execute("""insert into t04 (name) values ('Alice') returning id""")
>>> cur.fetchone()
(1,)

---

* fetchone returns a tuple, fetchall returns a list of tuples.

** returning doesn't work with cursor.executemany, for psycopg2 at least. However pyscopg2's executemany is no more efficient that calling execute multiple times, so just call execute.

• Hari Shreyas: If there is another record inserted (from another thread) between the time of execute() and fetchone(), will it fetch the correct id value or the latest id value ?
• snakecharmerb: I did a basic test using a single connection and cursors in separate threads. Both cursors reported the correct returning value. Obviously I'd encourage you to do your own testing if you want to rely on this behaviour in a production system.