SQLAlchemy 核心 - 插入忽略和重复密钥更新 [英] SQLAlchemy Core - INSERT IGNORE and ON DUPLICATE KEY UPDATE

问题描述

我将 SQLAlchemy Core 与 MySQL 数据库一起使用，但很难找到 INSERT IGNORE/DUPLICATE KEY UPDATE 的解决方案.如果有办法处理这个问题，我讨厌在代码中手动编写一次性查询.我发现的大多数解决方案或讨论都围绕 ORM，而不是核心.甚至其中一些是死链接.甚至有可能吗?

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?

推荐答案

我知道现在有点晚了..但如果有人仍在寻找解决方案.

I know it is a bit late.. but if someone is still looking for solutions.

对于:关于重复密钥更新

ins = insert(/* table_name*/ ).values(/*your values(with PK)*/)
do_update_stmt = ins.on_duplicate_key_update(/*your values(with out PK)*/)
connection.execute(do_update_stmt)

重复关键更新文档

MySQL 支持的 INSERT 的 ON DUPLICATE KEY UPDATE 子句现在支持使用 MySQL 特定的 版本的 Insert 对象

The ON DUPLICATE KEY UPDATE clause of INSERT supported by MySQL is now supported using a MySQL-specific version of the Insert object

这不适用于 sqlalchemy.insert().

对于:插入忽略

这有点麻烦，但效果很好.

This is a bit hacky but works just fine.

 ins_address_stmt = insert(/* table_name*/ ).values(/*your values*/). \
            prefix_with('IGNORE')

插入prefix_with

MySQL 会抑制重复主键的错误并给出警告.

MySQL will suppress the error for duplicate primary key and gives a warning.

这篇关于SQLAlchemy 核心 - 插入忽略和重复密钥更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！