jeanc jeanc - 7 months ago 24
Python Question

Bulk update in SQLAlchemy Core using WHERE

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

conn.execute(addresses.insert(), [
{'user_id': 1, 'email_address' : 'jack@yahoo.com'},
{'user_id': 1, 'email_address' : 'jack@msn.com'},
{'user_id': 2, 'email_address' : 'www@www.org'},
{'user_id': 2, 'email_address' : 'wendy@aol.com'},
])


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

conn.execute(addresses.insert(), [
{'user_id': 1, 'email_address' : 'jack@yahoo.com', 'id':12},
{'user_id': 1, 'email_address' : 'jack@msn.com', 'id':13},
{'user_id': 2, 'email_address' : 'www@www.org', 'id':14},
{'user_id': 2, 'email_address' : 'wendy@aol.com', '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?

van van
Answer

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' : 'jack@yahoo.com', '_id':1},
    {'user_id': 1, 'email_address' : 'jack@msn.com', '_id':2},
    {'user_id': 2, 'email_address' : 'www@www.org', '_id':3},
    {'user_id': 2, 'email_address' : 'wendy@aol.com', '_id':4},
])