CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
HermSang
HermSang

Reputation: 107

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

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)

Upvotes: 0

Views: 311

Answers (1)

Dev611
Dev611

Reputation: 89

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

Upvotes: 1

Related Questions