如何使用sqlalchemy在子句中编写多列 [英] How to write multi column in clause with sqlalchemy

问题描述

请建议是否可以使用SQLAlchemy在子句中编写查询多列?

Please suggest is there way to write query multi-column in clause using SQLAlchemy?

以下是实际查询的示例:

Here is example of the actual query:

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

我有一个包含两列主键的表，我希望避免再添加一个仅用作索引的键.

I have a table that has two columns primary key and I'm hoping to avoid adding one more key just to be used as an index.

PS我正在使用mysql DB.

PS I'm using mysql DB.

更新:该查询将用于批处理-因此，我需要在in子句中放入几百对.使用IN子句方法，我希望知道我可以在一个查询中坚持多少对的固定限制.像Oracle默认有1000个枚举限制.

Update: This query will be used for batch processing - so I would need to put few hundreds pairs into the in clause. With IN clause approach I hope to know fixed limit of how many pairs I can stick into one query. Like Oracle has 1000 enum limit by default.

使用AND/OR组合可能会受到查询长度(以字符为单位)的限制.这将是可变的且难以预测.

Using AND/OR combination might be limited by the length of the query in chars. Which would be variable and less predictable.

推荐答案

我最终使用了基于test()的解决方案:在((:: a1，:b1)，(:a2 ,: b2)，...)，命名为bind vars，并使用bind vars的值生成字典.

I ended up using the test() based solution: generated "(a,b) in ((:a1, :b1), (:a2,:b2), ...)" with named bind vars and generating dictionary with bind vars' values.

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的另一种选择，但这会使整个数据库对于其他数据库引擎的可移植性甚至不如在子句中使用multicolumn.

Another option I thought about using mysql upserts but this would make whole included even less portable for the other db engine then using multicolumn in clause.

更新 SQLAlchemy具有

Update SQLAlchemy has sqlalchemy.sql.expression.tuple_(*clauses, **kw) construct that can be used for the same purpose. (I haven't tried it yet)

这篇关于如何使用sqlalchemy在子句中编写多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！