0

I'm trying to update values using a dictionary. My user_id is set. I want to update date_synced where user_id=user_id. User table looks like this: User(user_id, mail, active, activity_level, date_synced).

Dictionary dictUsers looks like:

[{'user_id': 1, 'date_synced': '2019-05-22 10:42:25'},
 {'user_id': 8, 'date_synced': '2019-05-22 10:42:25'}]

What I've tried:

sql = 'UPDATE User SET {}'.format(', '.join('{}'.format(k) for k in dictUsers))
conn.execute(sql, dictUsers.values())

My error:

AttributeError: 'list' object has no attribute 'values'

Expected output is date_synced updated in my User table. How can I do this?

Some Name
  • 561
  • 6
  • 18
  • 1. This has nothing to do with `mysql`. 2. Print out `sql` to see if it is what you expected 3. dictUsers is a `list`, `list` do not have `values` method. – ch33hau May 22 '19 at 09:22
  • 2
    generating SQL like this is almost guaranteed to have SQL injection attacks, look at using [parameterised queries](https://stackoverflow.com/a/902417/1358308) – Sam Mason May 22 '19 at 09:23
  • @ch33hau 'UPDATE User SET {'user_id': 1, 'date_synced': '2019-05-22 10:42:25'}, {'user_id': 8, 'date_synced': '2019-05-22 10:42:25'}' This is what I get. – Some Name May 22 '19 at 09:25
  • @SamMason A problem for when the query works – Some Name May 22 '19 at 09:25
  • yea I know, and this is an invalid sql, you might want to fix your `sql` string before fixing the next line. – ch33hau May 22 '19 at 09:26
  • I'd suggest figuring out what an `UPDATE` statement looks like my crafting it by hand first, looking up the docs as needed, then write code to replicate it as much as needed – Sam Mason May 22 '19 at 09:27
  • @ch33hau Problem is I can't find a lot about updating with a dictionary. I know what a normal update statement looks like with multiple values, but I can't recreate it with a dictionary. – Some Name May 22 '19 at 09:31

1 Answers1

0

Try something like this (from the official documentation):

stmt = users.update().\
        values(name='ed wood').\
        where(users.c.id == addresses.c.id)
conn.execute(stmt)

In your case this should be something like this. You can loop this over your dictionary to do for every user:

stmt = user.update().\
        values(date_synced=dictUsers[i].date_synced).\
        where(user.c.user_id == dictUsers[i].user_id)
conn.execute(stmt)
Flourid
  • 101
  • 1
  • When I loop over with 'for key, value in dictUsers.items():' I still get the same error. List object has no attribute items. – Some Name May 22 '19 at 09:50