python - SQLAlchemy ORM 通过检查列表中是否有其他表值来更新值

Question

我有一个汉字(日语字符)列表，如下所示:

kanji_n3 = ['政', '議', '民', '連']  # But then with 367 Kanji

我有 2 个表格:TableKanji 和 TableMisc。 TableMisc 有一个名为“jlpt”的列，其中一些当前的值为 2，但必须更新为值 3，如果汉字位于 kanji_n3 中。

表类.py

import sqlalchemy as sqla
from sqlalchemy.orm import relationship
import sqlalchemy.ext.declarative as sqld
sqla_base = sqld.declarative_base()

class TableKanji(sqla_base):
    __tablename__ = 'Kanji'

    id = sqla.Column(sqla.Integer, primary_key=True)
    character = sqla.Column(sqla.String, nullable=False)

    misc = relationship("TableMisc", back_populates='kanji')

class TableMisc(sqla_base):
    __tablename__ = 'Misc'

    kanji_id = sqla.Column(sqla.Integer, sqla.ForeignKey('Kanji.id'), primary_key=True)
    jlpt = sqla.Column(sqla.Integer)

    kanji = relationship("TableKanji", back_populates="misc")

所以我提出的查询是 kanjiorigin_index.py:

import sqlalchemy as sqla
import sqlalchemy.orm as sqlo
from tableclass import TableKanji, TableMisc

kanji_n3 = ['政', '議', '民', '連']  # But then with 367 Kanji

session.query(TableMisc)\
    .filter(TableMisc.jlpt == 2).filter(TableKanji.character in kanji_n3)\
    .update({TableMisc.jlpt: TableMisc.jlpt + 1}, synchronize_session='fetch')

此操作成功运行，但没有更新任何内容。输出:

2016-10-18 04:05:53,908 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2016-10-18 04:05:53,908 INFO sqlalchemy.engine.base.Engine ()
2016-10-18 04:05:53,908 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2016-10-18 04:05:53,909 INFO sqlalchemy.engine.base.Engine ()
2016-10-18 04:05:53,909 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-10-18 04:05:53,909 INFO sqlalchemy.engine.base.Engine SELECT "Misc".kanji_id AS "Misc_kanji_id" 
FROM "Misc" 
WHERE 0 = 1
2016-10-18 04:05:53,909 INFO sqlalchemy.engine.base.Engine ()
2016-10-18 04:05:53,910 INFO sqlalchemy.engine.base.Engine UPDATE "Misc" SET jlpt=("Misc".jlpt + ?) WHERE 0 = 1
2016-10-18 04:05:53,910 INFO sqlalchemy.engine.base.Engine (1,)
2016-10-18 04:05:53,911 INFO sqlalchemy.engine.base.Engine COMMIT

问题

如何更新当前值为 2 且 TableKanji.character 位于 kanji_n3 中的 TableMisc.jlpt？我的 in kanji_n3 语句不能像这样工作吗？我还尝试添加 .outerjoin(TableKanji)，但这会导致:

sqlalchemy.exc.InvalidRequestError: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called

Answer 1

看来您的意图是对连接的表进行更新。并非所有数据库都支持此功能。

首先你应该使用in_方法而不是 in 运算符。

您可以先进行选择，然后更新所有选定的记录，如下所示:

records = session.query(TableMisc).\
    join(TableKanji).\
    filter(TableMisc.jlpt == 2).\
    filter(TableKanji.character.in_(kanji_n3)).\
    all()

for record in records:
    record.jlpt += 1

session.commit()