Bulk update in SQLAlchemy Core using WHERE

Each Answer to this Q is separated by one/two green lines.

I have managed to work with the bulk insert in SQLAlchemy like:

conn.execute(addresses.insert(), [ 
   {'user_id': 1, 'email_address' : '[email protected]'},
   {'user_id': 1, 'email_address' : '[email protected]'},
   {'user_id': 2, 'email_address' : '[email protected]'},
   {'user_id': 2, 'email_address' : '[email protected]'},
])

What I need now is something equivalent for update. I have tried this:

conn.execute(addresses.insert(), [ 
   {'user_id': 1, 'email_address' : '[email protected]', 'id':12},
   {'user_id': 1, 'email_address' : '[email protected]', 'id':13},
   {'user_id': 2, 'email_address' : '[email protected]', 'id':14},
   {'user_id': 2, 'email_address' : '[email protected]', 'id':15},
])

expecting that each row gets updated according to the ‘id’ field, but it doesn’t work. I assume that it is because I have not specified a WHERE clause, but I don’t know how to specify a WHERE clause using data that is included in the dictionary.

Can somebody help me?

Read the Updating and Deleting Rows with Core section of the tutorial. The following code should get you started:

from sqlalchemy import bindparam
stmt = addresses.update().\
    where(addresses.c.id == bindparam('_id')).\
    values({
        'user_id': bindparam('user_id'),
        'email_address': bindparam('email_address'),
    })

conn.execute(stmt, [
    {'user_id': 1, 'email_address' : '[email protected]', '_id':1},
    {'user_id': 1, 'email_address' : '[email protected]', '_id':2},
    {'user_id': 2, 'email_address' : '[email protected]', '_id':3},
    {'user_id': 2, 'email_address' : '[email protected]', '_id':4},
])

The session has function called bulk_insert_mappings and bulk_update_mappings: documentation.

Be aware that you have to provide primary key in mappings

# List of dictionary including primary key
user_mappings = [{
    'user_id': 1, # This is pk?
    'email_address': '[email protected]',
    '_id': 1
}, ...]

session.bulk_update_mappings(User, user_mappings)
session.commit()

@Jongbin Park’s solution DID work for me with a composite primary key. (Azure SQL Server).

update_vals = []
update_vals.append(dict(Name="name_a", StartDate="2020-05-26 20:17:32", EndDate="2020-05-26 20:46:03", Comment="TEST COMMENT 1"))
update_vals.append(dict(Name="name_b", StartDate="2020-05-26 21:31:16", EndDate="2020-05-26 21:38:37", Comment="TEST COMMENT 2"))
s.bulk_update_mappings(MyTable, update_vals)
s.commit()

where Name, StartDate, and EndDate are all part of the composite pk. ‘Comment’ is the value to update in the db


The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .