重复密钥更新时的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发布了适用于MySQL的1.2版

此功能现在仅内置于MySQL的SQLAlchemy中. 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.

代码(通过主题上的关于此主题的好链接链接)作为示例，可以在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在其ORM层中未提供与ON DUPLICATE KEY UPDATE或MERGE或任何其他类似功能的接口.不过，它具有 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查询.请注意，合并比这要复杂得多，但是它可以使用主键很好地复制功能.

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屋！