SQLAlchemy 过滤器 in_ 运算符 [英] SQLAlchemy filter in_ operator

问题描述

我正在尝试对 sqlalchemy 中的查询执行简单的过滤操作，如下所示:

I am trying to do a simple filter operation on a query in sqlalchemy, like this:

q = session.query(Genotypes).filter(Genotypes.rsid.in_(inall))

哪里

inall 是一个字符串列表基因型映射到一个表:类基因型(对象):通过

inall is a list of strings Genotypes is mapped to a table: class Genotypes(object): pass

Genotypes.mapper = mapper(Genotypes, kg_table, properties={'rsid': getattr(kg_table.c, 'rs#')})

这对我来说似乎很简单，但是当我通过执行 q.first() 执行上述查询时出现以下错误:

This seems pretty straightforward to me, but I get the following error when I execute the above query by doing q.first():

"sqlalchemy.exc.OperationalError: (OperationalError) 太多的 SQL变量 u'SELECT" 后跟 inall 中 1M 项的列表清单.但它们不应该是 SQL 变量，只是一个列表成员资格是过滤条件.

"sqlalchemy.exc.OperationalError: (OperationalError) too many SQL variables u'SELECT" followed by a list of the 1M items in the inall list. But they aren't supposed to be SQL variables, just a list whose membership is the filtering criteria.

我的过滤操作有误吗?

(数据库是sqlite)

(the db is sqlite)

推荐答案

如果您从中获取 rsid 的表在同一个数据库中可用，我会使用 subquery 将它们传递到您的 Genotypes 查询而不是在 Python 代码中传递一百万个条目.

If the table where you are getting your rsids from is available in the same database I'd use a subquery to pass them into your Genotypes query rather than passing the one million entries around in your Python code.

sq = session.query(RSID_Source).subquery()
q = session.query(Genotypes).filter(Genotypes.rsid.in_(sq))

问题是，为了将该列表传递给 SQLite(或任何数据库，实际上)，SQLAlchemy 必须将 in 子句的每个条目作为变量传递.SQL 大致翻译为:

The issue is that in order to pass that list to SQLite (or any database, really), SQLAlchemy has to pass over each entry for your in clause as a variable. The SQL translates roughly to:

-- Not valid SQLite SQL
DECLARE @Param1 TEXT;
SET @Param1 = ?;
DECLARE @Param2 TEXT;
SET @Param2 = ?;
-- snip 999,998 more

SELECT field1, field2, -- etc.
FROM Genotypes G
WHERE G.rsid IN (@Param1, @Param2, /* snip */)

这篇关于SQLAlchemy 过滤器 in_ 运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！