CODEWITHSUNDEEP
pythonmysqlsqlalchemyon-duplicate-key
Mina
Mina

Reputation: 21

SQLAlchemy MySQL ON DUPLICATE KEY UPDATE

I am trying to use SQLALchemy MySQL ON_DUPLICATE_KEY_UPDATE() function, but it is not working as expected.

from sqlalchemy.dialects.mysql.dml import Insert 

new_record={'id': 'val1', 'col1': 'new val'}

# Here id is the primary key
# my_table is a Table object
Insert(my_table).on_duplicate_key_update(new_record)

This code works without throwing an error, but the existing record in the table with primary key value 'val1' is not being updated.

I looked into SQLAlchemy documentation about on duplicate key update, but I am not able to understand how to call the function.

Upvotes: 2

Views: 4002

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

The first thing to note is that you've only created, and discarded, an insert statement object. In order to execute it you have to pass it to one of the execute() methods of SQLAlchemy. The statement is also lacking the values to first try and insert.

from sqlalchemy.dialects.mysql.dml import Insert 

new_record = {'id': 'val1', 'col1': 'new val'}

# Here id is the primary key
# my_table is a Table object
stmt = Insert(my_table).values(new_record)
stmt = stmt.on_duplicate_key_update(col1=stmt.inserted.col1)

# This will actually execute the statement
engine.execute(stmt)

Upvotes: 4

Related Questions