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!

Danila Ganchar · Accepted Answer · 2019-05-23 12:46:09Z

3

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.

answered May 23, 2019 at 12:46

Danila Ganchar

11.5k13 gold badges69 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execute update with dictionary/dataframe values in SQLAlchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related