Bulk update in SQLAlchemy Core using WHERE

Question

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?

Answer 1

Read Inserts, Updates and Deletes section of the documentation. Following code should get you started:

from sqlalchemy.sql.expression 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}, ]) 

Answer 2

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()