SQLAlchemy强制in_()使用文字 [英] SQLAlchemy force in_() to use literals

问题描述

是否有一种方法可以强制过滤器中的SQLAlchemy in_()运算符将 literal 值传递给数据库，而不是将每个元素的单独绑定变量传递给数据库?

Is there a way to force the SQLAlchemy in_() operator in a filter to pass literal values to the database instead of individual bound variables for each element?

我必须对IN子句使用可能数量众多的值进行(MySQL)查询. (这里不提供IN (SELECT ...)子查询.)类似:

I have to do a (MySQL) query with a potentially-large number of values for an IN clause. (An IN (SELECT ...) sub-query is not an option here.) Something like:

query\
    .join(something)\
    .join(somethingelse)\
    .with_entities(somecol, someothercol)\
    .filter(somecol.in_(long_list_of_ids_in_a_list_variable))\
    .order_by(somecol)\
    .all()

为此，SQLAlchemy将以下内容发送到MySQL:

For this SQLAlchemy sends the following to MySQL:

SELECT ... FROM ... JOIN ... 
WHERE id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s, %(id_5)s, %(id_6)s,
...
%(id_49)s, %(id_50)s)
ORDER BY ...
{'id_1': 718, 'id_2': 714, 'id_3': 713, 'id_4': 715, 'id_5': 712, 'id_6': 711,
...
'id_49': 657, 'id_50': 656}

随着IN项的数量变大，该语句变长且笨拙(例如，至少在调试过程中). (我真的不认为通过使用变量重用MySQL查询计划会带来任何潜在的收益.)

As the number of IN items gets larger this statement gets long and unwieldy (e.g. during debugging at least). (I don't really think any potential gain here for MySQL query plan re-use by using variables is significant.)

我希望它像内联文字一样发送这些值:

I would like it to send these values just as in-line literals:

WHERE id IN (718, 714, 713, 715, 712, 711,
...
657, 656)

是否有一种方法可以做到这一点(使用in_()或其他合适的构造)?请注意，我仅希望将查询的IN段变成这样的文字，而不是查询的所有其他部分.

Is there a way to accomplish this (with in_() or some other suitable construct)? Note that I only wish to turn the IN segment of the query into literal like this, not all other parts of the query.

[我确实查看了 SQLAlchemy过滤器in_运算符，但没有得到合适的答案从那里来.]

[I did look at SQLAlchemy filter in_ operator, but did not get a suitable answer for mine from there.]

推荐答案

您可以使用

You could use a text construct for your IN clause (mindful of sql injection risk):

from sqlalchemy_app import Base, Session, engine
import sqlalchemy as sa
from sqlalchemy.sql import text


class Something(Base):

    id = sa.Column(sa.Integer, primary_key=True)


class SomethingElse(Base):

    id = sa.Column(sa.Integer, primary_key=True)
    something_id = sa.Column(sa.Integer, sa.ForeignKey("something.id"))


if __name__ == "__main__":
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    s = Session()
    in_clause = text("something.id IN (1, 2, 3)")
    s.query(Something).join(SomethingElse).filter(in_clause).all()

发出此查询:

2019-08-10 14:23:32,329 INFO sqlalchemy.engine.base.Engine SELECT something.id AS something_id
FROM something INNER JOIN somethingelse ON something.id = somethingelse.something_id
WHERE something.id IN (1, 2, 3)

这篇关于SQLAlchemy强制in_()使用文字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！