Fetch a 'return'd value from an executemany statement

Question

Using Postgresql 9.5 with psycopg2.

Using 'returning', I can retrieve the 'id' of a single row I insert.

sql = "insert into %s (%s) values (%s) returning id" % (table_name, columns, values_template)        
values = tuple(row_dict[key] for key in keys)
cur.execute(sql, values)
id_new_row = cur.fetchone()[0]

I was hoping to also retrieve the id of the last of many rows inserted using the executemany statement.

cur.executemany(insert_sql , data_dict)
#get id of the last row inserted
id_new_row = cur.fetchone()[0]

But this throws a DatabaseError, "no results to fetch".

Is this just a fundamentally impossible thing to do, or am I not calling for the result correctly?

Answer 1

I think that what you're trying to do is impossible.

For a start, you are already on dodgy ground as far as PEP 249 is concerned. Specifically it says this about executemany.

Use of this method for an operation which produces one or more result sets constitutes undefined behavior, and the implementation is permitted (but not required) to raise an exception when it detects that a result set has been created by an invocation of the operation.

If you look at the psycopg2 source code, you can see that executemany sets the no_result flag. When you track that down to the code that handles the query you can see that it discards any results (lines 1582-1595 at the time of posting).