Menu
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?
239
Update table elements in SQLAlchemy. Get the books to table from the Metadata object initialized while connecting to the database. Pass the update query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.
all() method. The Query object, when asked to return full entities, will deduplicate entries based on primary key, meaning if the same primary key value would appear in the results more than once, only one object of that primary key would be present.
Choosing Between SQLAlchemy Core and ORM The two modes use slightly different syntax, but the biggest difference between Core and ORM is the view of data as schema or business objects. SQLAlchemy Core has a schema-centric view, which like traditional SQL is focused around tables, keys, and index structures.
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}, ]) 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() If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
