0

I am looking to insert/update a large volume of data stored in either a list of tuples or list of dictionaries into an existing table using SQLAlchemy. What is the most efficient way to do this?

What I have right now is this,

session_maker = sqlalchemy.orm.sessionmaker()
session_maker .configure(bind=engine)
inspector = sqlalchemy.inspect(engine)
session = session_maker()

meta_data = sqlalchemy.MetaData(engine)
MyTable = sqlalchemy.Table('MyTable', meta_data, autoload=True, autoload_with=engine)

MyTable.insert().execute([{'key1': 'value1', 'key2': 'value2'}, {'key1': 'value3', 'key2': 'value4'}])

I have also heard of bulk_insert_mappings() and bulk_save_objects(). I would, however, like to avoid creating any other objects as explained here Bulk insert with SQLAlchemy ORM and use either a list of tuples or dictionaries as mentioned above.

How can I do bulk updates?

Minura Punchihewa
  • 1,498
  • 1
  • 12
  • 35
  • Searching for previous questions and answers mentioning "upsert" may give you some ideas. – Gord Thompson Nov 12 '21 at 12:43
  • I am not looking to perform upserts. I am looking for efficient ways to implement bulk-inserts and bulk-updates. – Minura Punchihewa Nov 12 '21 at 13:48
  • And what leads you to believe that previous answers about upserts would not offer valuable insights regarding "efficient ways to implement bulk-inserts and bulk-updates"? – Gord Thompson Nov 12 '21 at 14:01
  • Yes, understood. I have done what you have suggested. From what I tried, `.insert().execute()` seems to be the best (in terms of performance) for bulk inserts. Do you know what I could use for bulk updates, again with a similar list of dictionaries? The only thing I could get to work was `session.bulk_update_mappings()`, but this is really slow. – Minura Punchihewa Nov 13 '21 at 18:16

1 Answers1

1

You should try using bulk_update_mappings/bulk_insert_mappings. Also, you have an option of using a parameter called fast_executemany on the engine creation. Please, refer to my answer at: Using bulk_update_mappings in SQLAlchemy to update multiple rows with different values.

Kaleb Roncatti
  • 41
  • 7