2

I have a table containing user data and I would like to update information for many of the users using a list of dictionaries. At the moment I am using a for loop to send an update statement one dictionary at a time, but it is slow and I am hoping that there is a bulk method to do this.

user_data = [{'user_id' : '12345', 'user_name' : 'John'}, {'user_id' : '11223', 'user_name' : 'Andy'}]   

connection = engine.connect()
metadata = MetaData()

for row in user_data:
        stmt = update(users_table).where(users_table.columns.user_id == row['user_id'])
        results = connection.execute(stmt, row)

Thanks in advance!

Venetian
  • 513
  • 1
  • 6
  • 11

1 Answers1

2
from sqlalchemy.sql.expression import bindparam

connection = engine.connect()

stmt = users_table.update().\
where(users_table.c.id == bindparam('_id')).\
values({
    'user_id': bindparam('user_id'),
    'user_name': bindparam('user_name'),
})

connection.execute(stmt, [
{'user_id' : '12345', 'user_name' : 'John', '_id': '12345'},
{'user_id' : '11223', 'user_name' : 'Andy', '_id': '11223'}

])
Palmer
  • 493
  • 4
  • 13
  • Hope this will help! – Palmer Feb 18 '17 at 02:43
  • Thanks! In this example, should I change 'id' to 'user_id' and '_id' to 'user_id' in the where statement? So it would be: `where(users_table.c.user_id == bindparam('user_id')).` Otherwise I get: `StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter '_id` – Venetian Feb 18 '17 at 03:30
  • @Venetian Depending on what database you are using, this may not drastically improve the performance. For example, look at [this question](http://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query) (PostgreSQL). – univerio Feb 18 '17 at 05:01