使用sqlalchemy核心的MySQL多表更新查询？

Question

我想用一个查询更新两个表。你知道吗

我想要一些类似的东西

UPDATE tblReceipt, tblReturn
   SET tblReceipt.ReturnedDate = tblReturn.CreatedDate, 
       tblReturn.ReturnerName = tblReceipt.Name
 WHERE tblReturn.Id = tblReceipt.ReturnId
   AND tblReceipt.returned = TRUE

我目前有以下内容，但我不知道如何添加第二个表引用。有没有简单的方法？你知道吗

    update(Receipt)
    .values(ReturnedDate=Return.CreatedDate, ReturnName=Receipt.Name) 
    .where(Return.Id==Receipt.ReturnId) 
    .where(Receipt.Returned == True)

Answer 1

从^{}的文档中：

The keys within values can be either Column objects or their string identifiers (specifically the “key” of the Column, normally but not necessarily equivalent to its “name”). Normally, the Column objects used here are expected to be part of the target Table that is the table to be updated. However when using MySQL, a multiple-table UPDATE statement can refer to columns from any of the tables referred to in the WHERE clause.

我的。你知道吗

我已经基于上面的示例创建了一个示例核心模式，但并不精确，不过应该足以用于：

from sqlalchemy_app import engine

import sqlalchemy as sa

metadata = sa.MetaData()

receipt = sa.Table(
    "receipt",
    metadata,
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("something", sa.Integer),
)

returns = sa.Table(
    "returns",
    metadata,
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("receipt_id", sa.Integer, sa.ForeignKey('receipt.id')),
    sa.Column("somethingelse", sa.Integer)
)


if __name__ == "__main__":
    metadata.drop_all(engine)
    metadata.create_all(engine)
    with engine.connect() as conn:
        conn.execute(
            receipt.insert(
                values=[{"id": 1, "something": 1}]
            )
        )
        conn.execute(
            returns.insert(
                values=[{"id": 1, "receipt_id": 1, "somethingelse": 99}]
            )
        )
        conn.execute(
            sa.update(receipt)
            .values({receipt.c.something: 3, returns.c.somethingelse: 4})
            .where(sa.and_(receipt.c.id == 1, returns.c.id == 1))
        )

根据文档，.values()中引用的两列的父表在.where()子句中引用。你知道吗

下面是它生成的update语句：

2019-08-17 11:27:39,573 INFO sqlalchemy.engine.base.Engine UPDATE receipt, returns SET returns.somethingelse=%(returns_somethingelse)s, receipt.something=%(something)s WHERE receipt.id = %(id_1)s AND returns.id = %(id_2)s
2019-08-17 11:27:39,582 INFO sqlalchemy.engine.base.Engine {'returns_somethingelse': 4, 'something': 3, 'id_1': 1, 'id_2': 1}

请注意，如果您只是打印查询来检查它，您将得到如下结果：

UPDATE receipt SET somethingelse=:returns_somethingelse, something=:something FROM returns WHERE receipt.id = :id_1 AND returns.id = :id_2

…由于这是mysql特有的行为，因此必须使用mysql方言进行编译：

from sqlalchemy.dialects import mysql
print(statement.compile(dialect=mysql.dialect())

将打印：

UPDATE receipt, returns SET returns.somethingelse=%s, receipt.something=%s WHERE receipt.id = %s AND returns.id = %s