How to use SQLAlchemy engine to execute multiple postgreSQL statements at once?

Question

I am trying to generate a list of SQL statements to execute, but the current code I believe commits them one by one:

    for batch in batches: # batch is a list of dictionary objects, batches is a list of lists
                
        for obj in batch: # for dictionary object in list
            
            with engine.connect() as connection:
                overwrite_failure = context['retry_failure'] == True and obj['status'] in ['success', 'critical failure']
    
                if overwrite_failure:
                    # load_deletes returns a string query which is escaped and formatted by .execute()
                    query_one, query_two = load_deletes(obj, table_name, columns, unique_cols, should_overwrite, identifier)
                    
                    connection.execute(query_one, obj)
                    connection.execute(query_two, obj)
                    
                else:
                  # load_updates returns a string query which is escaped and formatted by .execute()
                    query = load_updates(obj, table_name, columns, unique_cols, should_overwrite)
                    connection.execute(query, obj)

Dev611 · Accepted Answer · 2021-10-12 19:58:21Z

1

Init the DB connection outside the loops. Also make sure the auto_commit setting is off.

answered Oct 12, 2021 at 19:58

Dev611

891 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to use SQLAlchemy engine to execute multiple postgreSQL statements at once?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related