Menu
I'm using SQLAlchemy Core with a MySQL database but am having a hard time finding a solution for INSERT IGNORE / DUPLICATE KEY UPDATE. I hate to write a one-off query manually in the code if there's a way to handle this. Most solutions or discussions I've found center around the ORM, but not the core. And even some of those are dead links. Is it even possible?
522
The ON DUPLICATE KEY UPDATE clause of INSERT supported by MySQL is now supported using a MySQL-specific version of the Insert object This wont be avaliable with sqlalchemy.insert (). This is a bit hacky but works just fine. MySQL will suppress the error for duplicate primary key and gives a warning. Do you know what data does (values) receive?
The ON DUPLICATE KEY UPDATE clause of INSERT supported by MySQL is now supported using a MySQL-specific version of the Insert object, via sqlalchemy.dialects.mysql.dml.insert () . This Insert subclass adds a new method Insert.on_duplicate_key_update () that implements MySQL’s syntax:
The rendered UPDATE statement will emit the SET clause for each referenced column maintaining this order. Deprecated since version 1.4: The update.preserve_parameter_orderparameter will be removed in SQLAlchemy 2.0. Use the Update.ordered_values()method with a list of tuples. New in version 1.0.10. See also
For DBAPIs which do not natively support returning values (i.e. cx_oracle), SQLAlchemy will approximate this behavior at the result level so that a reasonable amount of behavioral neutrality is provided. Note that not all databases/DBAPIs support RETURNING.
Another method is:
stmt = YourModel.__table__.insert().prefix_with(" ignore").values(data)
session.execute(stmt)
shaktimaan's answer works great for MySQL INSERT IGNORE. If you found this page looking for SQLite INSERT IGNORE, remember to do INSERT OR IGNORE
https://www.sqlite.org/lang_insert.html
stmt = insert(/* table_name*/ ).values(/*your values*/).prefix_with('OR IGNORE')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
