Execute update with dictionary/dataframe values in SQLAlchemy

Question

I'm trying to update user_id and date_synced in my user_table. I'm using MySQL. My table is as follows: User(user_id, mail, active, activity_level, date_synced).These values come from my DataFrameUsers:

user_id date_synced
1   2019-05-20 20:48:04
8   2019-05-20 20:48:04

Converted to dict with dictUsers = dfUsers.to_dict():

{'date_synced': {1: '2019-05-20 20:48:04', 8: '2019-05-20 20:48:04'}}

When i execute:

conn.execute(user_table.update(), [ 
        dictUsers
    ]
)

I get the error:

ProgrammingError: (pymysql.err.ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'1: "\'2019-05-20 20:48:04\'", 8: "\'2019-05-20 20:48:04\'"}\' at line 1')
[SQL: UPDATE `User` SET date_synced=%(date_synced)s]
[parameters: {'date_synced': {1: '2019-05-20 20:48:04', 8: '2019-05-20 20:48:04'}}]

Expected outcome would be updated in the database.

How can I insert this dictionary using SQLAlchemy? Doesn't have to be a dict, could be converted. I just want it to work. Thanks!

Maybe you can refer [How to use pandas to do upsert in SqlAlchemy](https://stackoverflow.com/questions/51905337/how-to-use-pandas-to-do-upsert-in-sqlalchemy). — giser_yugang, May 21 '19 at 03:51
@giser_yugang This seems to be only working for postgres. I am using mySQL. I tried to adjust it to MySQL but was not succesful in doing so. — Some Name, May 22 '19 at 08:56
@SomeName do you need insert values or update `date_synced` by `user_id`? — Danila Ganchar, May 22 '19 at 15:27

score 2 · Accepted Answer · answered May 23 '19 at 12:46

You have a wrong format of parameters. sqlalchemy doesn't now how to search row and what update. Correct format:

[
    {'user_id': 1, 'date_synced': '2019-05-20 20:48:04'},
    {'user_id': 8, 'date_synced': '2019-05-20 20:48:04'}
]

So you need just to convert dictUsers. Just an example:

from sqlalchemy.sql.expression import bindparam, update
from sqlalchemy.sql.expression import func

data = {'date_synced': {1: '2019-05-20 20:48:05', 2: '2019-05-20 20:48:05'}}
params = []
for user_id, date_synced in data['date_synced'].items():
    params.append({'_id': user_id, 'date_synced': date_synced})

stmt = update(User).\
    where(User.user_id == bindparam('_id')).\
    values({
        'date_synced': bindparam('date_synced'),
    })

db.session.execute(stmt, params)
db.session.commit()

Hope this helps.

Thanks! This got me a long way. – Some Name May 28 '19 at 09:17 — Some Name, May 28 '19 at 09:17

Execute update with dictionary/dataframe values in SQLAlchemy

1 Answers1