重复密钥更新的 SQLAlchemy [英] SQLAlchemy ON DUPLICATE KEY UPDATE

问题描述

是否有一种优雅的方法可以在 SQLAlchemy 中执行 INSERT ... ON DUPLICATE KEY UPDATE ?我的意思是语法类似于 inserter.insert().execute(list_of_dictionaries) ?

Is there an elegant way to do an INSERT ... ON DUPLICATE KEY UPDATE in SQLAlchemy? I mean something with a syntax similar to inserter.insert().execute(list_of_dictionaries) ?

推荐答案

ON DUPLICATE KEY UPDATE post version-1.2 for MySQL

此功能现在仅内置于 SQLAlchemy for MySQL 中.somada141 下面的回答有最好的解决方案:https://stackoverflow.com/a/48373874/319066

ON DUPLICATE KEY UPDATE post version-1.2 for MySQL

This functionality is now built into SQLAlchemy for MySQL only. somada141's answer below has the best solution: https://stackoverflow.com/a/48373874/319066

如果您希望生成的 SQL 实际上包含 ON DUPLICATE KEY UPDATE，最简单的方法是使用 @compiles 装饰器.

If you want the generated SQL to actually include ON DUPLICATE KEY UPDATE, the simplest way involves using a @compiles decorator.

代码(链接自关于该主题的好线程在 reddit 上) 例如可以在 在 github 上找到:

The code (linked from a good thread on the subject on reddit) for an example can be found on github:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert

@compiles(Insert)
def append_string(insert, compiler, **kw):
    s = compiler.visit_insert(insert, **kw)
    if 'append_string' in insert.kwargs:
        return s + " " + insert.kwargs['append_string']
    return s


my_connection.execute(my_table.insert(append_string = 'ON DUPLICATE KEY UPDATE foo=foo'), my_values)

但请注意，在这种方法中，您必须手动创建 append_string.您可能会更改 append_string 函数，以便它自动将插入字符串更改为带有ON DUPLICATE KEY UPDATE"字符串的插入，但由于懒惰，我不会在这里这样做.

But note that in this approach, you have to manually create the append_string. You could probably change the append_string function so that it automatically changes the insert string into an insert with 'ON DUPLICATE KEY UPDATE' string, but I'm not going to do that here due to laziness.

SQLAlchemy 不提供接口到 ON DUPLICATE KEY UPDATE 或 MERGE 或其 ORM 层中的任何其他类似功能.尽管如此，它有 session.merge() 可以复制功能的函数仅当相关键是主键时.

SQLAlchemy does not provide an interface to ON DUPLICATE KEY UPDATE or MERGE or any other similar functionality in its ORM layer. Nevertheless, it has the session.merge() function that can replicate the functionality only if the key in question is a primary key.

session.merge(ModelObject) 首先通过发送 SELECT 查询(或通过本地查找)来检查是否存在具有相同主键值的行.如果是，它会在某处设置一个标志，表明 ModelObject 已经在数据库中，并且 SQLAlchemy 应该使用 UPDATE 查询.请注意，merge 比这复杂得多，但它可以很好地复制主键的功能.

session.merge(ModelObject) first checks if a row with the same primary key value exists by sending a SELECT query (or by looking it up locally). If it does, it sets a flag somewhere indicating that ModelObject is in the database already, and that SQLAlchemy should use an UPDATE query. Note that merge is quite a bit more complicated than this, but it replicates the functionality well with primary keys.

但是，如果您想要具有非主键(例如，另一个唯一键)的 ON DUPLICATE KEY UPDATE 功能怎么办?不幸的是，SQLAlchemy 没有任何这样的功能.相反，您必须创建类似于 Django 的 get_or_create() 的东西.另一个 StackOverflow 答案涵盖了它，为了方便起见，我将在此处粘贴一个修改后的工作版本.

But what if you want ON DUPLICATE KEY UPDATE functionality with a non-primary key (for example, another unique key)? Unfortunately, SQLAlchemy doesn't have any such function. Instead, you have to create something that resembles Django's get_or_create(). Another StackOverflow answer covers it, and I'll just paste a modified, working version of it here for convenience.

def get_or_create(session, model, defaults=None, **kwargs):
    instance = session.query(model).filter_by(**kwargs).first()
    if instance:
        return instance
    else:
        params = dict((k, v) for k, v in kwargs.iteritems() if not isinstance(v, ClauseElement))
        if defaults:
            params.update(defaults)
        instance = model(**params)
        return instance

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