如何使用SQLAlchemy编写多列IN子句

Question

请问是否有办法使用SQLAlchemy将多列写入where子句？

以下是实际查询的示例：

SELECT  url FROM pages WHERE (url_crc, url) IN ((2752937066, 'http://members.aye.net/~gharris/blog/'), (3799762538, 'http://www.coxandforkum.com/'));

我有一张表格，它有两列主键。我希望避免添加另一个仅用作索引的键。

顺便说一下，我正在使用mysql数据库。

更新：这个查询将用于批处理 - 所以我需要将几百对数据放入in子句中。使用IN子句的方法，我希望知道可以将多少对数据置于一个查询中的固定限制值。就像Oracle默认有1000个枚举限制一样。

使用AND/OR组合可能会受到查询长度（以字符计算）的限制。这与变量和不太可预测的情况有关。

- vvladymyrov

Answer 1

假设您已经在 Page 中定义了您的模型，这里是使用 tuple_ 的示例:

keys = [
    (2752937066, 'http://members.aye.net/~gharris/blog/'),
    (3799762538, 'http://www.coxandforkum.com/')
]
    
select([
    Page.url
]).select_from(
    Page
).where(
    tuple_(Page.url_crc, Page.url).in_(keys)
)

或者，使用查询API：

session.query(Page.url).filter(tuple_(Page.url_crc, Page.url).in_(keys))

- ernematthew

Answer 2

我认为在 sqlalchemy 中目前不可能实现这一点，并且并非所有 RDMBS 都支持此功能。
但是您可以将其转换为 OR(AND...) 条件：

filter_rows = [
    (2752937066, 'http://members.aye.net/~gharris/blog/'),
    (3799762538, 'http://www.coxandforkum.com/'),
    ]
qry = session.query(Page)
qry = qry.filter(or_(*(and_(Page.url_crc == crc, Page.url == url) for crc, url in filter_rows)))
print qry

应该生成类似以下（对于SQLite）的内容：

SELECT  pages.id AS pages_id, pages.url_crc AS pages_url_crc, pages.url AS pages_url
FROM    pages
WHERE   pages.url_crc = ? AND pages.url = ? OR pages.url_crc = ? AND pages.url = ?
-- (2752937066L, 'http://members.aye.net/~gharris/blog/', 3799762538L, 'http://www.coxandforkum.com/')

或者，您可以将两个列合并为一个：

filter_rows = [
    (2752937066, 'http://members.aye.net/~gharris/blog/'),
    (3799762538, 'http://www.coxandforkum.com/'),
    ]
qry = session.query(Page)
qry = qry.filter((func.cast(Page.url_crc, String) + '|' + Page.url).in_(["{}|{}".format(*_frow) for _frow in filter_rows]))
print qry

下面是针对SQLite的输出结果，因此您可以使用IN：

SELECT  pages.id AS pages_id, pages.url_crc AS pages_url_crc, pages.url AS pages_url
FROM    pages
WHERE   (CAST(pages.url_crc AS VARCHAR) || ? || pages.url) IN (?, ?)
-- ('|', '2752937066|http://members.aye.net/~gharris/blog/', '3799762538|http://www.coxandforkum.com/')

- van

---

这是一个可行的想法。但不符合我的需求。我已经添加了更多细节来解释问题。 - vvladymyrov

更新了带有IN版本的答案。 - van

谢谢更新。我考虑将两列合并为一个字符串，但这样会很慢，因为在这种情况下不会使用索引。 - vvladymyrov

我明白了。鉴于任务的“批处理”特性，也许您应该考虑使用纯SQL，甚至尽量避免使用IN子句，因为它在大多数数据库上并不出名快速。祝你好运，请分享你的决定。谢谢。 - van

Answer 3

我最终采用了基于test()的解决方案：使用命名绑定变量生成"(a,b) in ((:a1, :b1), (:a2,:b2), ...)"，并生成带有绑定变量值的字典。

params = {}
for counter, r in enumerate(records):
    a_param = "a%s" % counter
    params[a_param] = r['a']
    b_param = "b%s" % counter
    params[b_param] = r['b']
    pair_text = "(:%s,:%s)" % (a_param, b_param)
    enum_pairs.append(pair_text)
multicol_in_enumeration = ','.join(enum_pairs)
multicol_in_clause = text(
    " (a,b) in (" + multicol_in_enumeration + ")")
q = session.query(Table.id, Table.a,
                            Table.b).filter(multicol_in_clause).params(params)

我考虑过另一种选项，即使用mysql的upserts，但这会使整个包含关系对于其他数据库引擎来说更加不可移植，而使用多列in子句则不会。

更新 SQLAlchemy拥有sqlalchemy.sql.expression.tuple_(*clauses, **kw)语句，可以用于同样的目的。（我还没有尝试过）

- vvladymyrov