Get the inserted primary key ids using bulk_save_objects

Question

How can I get the inserted Ids (primary key generated) after using session.bulk_save_objects?

I tried this:

for x in y:
  obj = Post(...)
  obj_list.append(obj)

session.bulk_save_objects(obj_list)
session.commit()

for i in obj_list:
  print(i.id)

The ids are None. The rows are successfully inserted.

Answer 1

you need to add return_defaults = True in bulk_save_object method like below to get primary key of records

for x in y:
   obj = Post(...)
   obj_list.append(obj)
session.bulk_save_objects(obj_list,return_defaults = True)
session.commit()

for i in obj_list:
   print(i.id)

Answer 2

A more performant way to do this rather than using bulk_save_objects which emits an insert statement per value is to use sqlalchemy.dialects.postgresql.insert with a returning clause.

insert_stmt = insert(Table).values(dictionary_mappings).returning(Table.id)
ids = db.session.execute(insert_stmt).fetchall()