如何使用 SQLAlchemy 获取与三元组条件列表匹配的行 [英] How to get rows which match a list of 3-tuples conditions with SQLAlchemy

问题描述

有一个三元组列表:

[(a, b, c), (d, e, f)]

我想从一个表中检索所有行，其中 3 列与元组匹配.对于这个例子，查询 WHERE 子句可能是这样的:

I want to retrieve all the rows from a table where 3 columns matches the tuples. FOr this example, the query WHERE clause could be something like this :

   (column_X = a AND column_Y = b AND column_Z = c)
OR (column_X = d AND column_Y = e AND column_Z = f)

如何使用 SQLAlchemy 创建这样的请求?就我而言，3 元组列表将包含数百个元素，我正在寻找最佳的可扩展解决方案.

How can I create such a request using SQLAlchemy ? In my case the 3-tuples list will contains hundred of elements, and I'm looking for the best scallable solution.

感谢您的帮助，

推荐答案

最简单的方法是使用 SQLAlchemy 提供的 tuple_ 函数:

Easiest way would be using SQLAlchemy-provided tuple_ function:

from sqlalchemy import tuple_

session.query(Foo).filter(tuple_(Foo.a, Foo.b, Foo.c).in_(items))

这适用于 PostgreSQL，但与 SQLite 不同.不确定其他数据库引擎.

This works with PostgreSQL, but breaks with SQLite. Not sure about other database engines.

幸运的是，有一个适用于所有数据库的解决方法.

Fortunately there's a workaround that should work on all databases.

首先用 and_ 表达式映射所有项目:

Start by mapping out all the items with the and_ expression:

conditions = (and_(c1=x, c2=y, c3=z) for (x, y, z) in items)

然后创建一个包含所有条件的 or_ 过滤器:

And then create an or_ filter that encloses all the conditions:

q.filter(or_(*conditions))

这是一个简单的例子:

#/usr/bin/env python
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer
from sqlalchemy.sql import and_, or_
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///')
session = sessionmaker(bind=engine)()
Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)
    a = Column(Integer)
    b = Column(Integer)
    c = Column(Integer)

    def __init__(self, a, b, c):
        self.a = a
        self.b = b
        self.c = c

    def __repr__(self):
        return '(%d %d %d)' % (self.a, self.b, self.c)

Base.metadata.create_all(engine)

session.add_all([Foo(1, 2, 3), Foo(3, 2, 1), Foo(3, 3, 3), Foo(1, 3, 4)])
session.commit()
items = ((1, 2, 3), (3, 3, 3))
conditions = (and_(Foo.a==x, Foo.b==y, Foo.c==z) for (x, y, z) in items)
q = session.query(Foo)
print q.all()
q = q.filter(or_(*conditions))
print q
print q.all()

输出:

$ python test.py 
[(1 2 3), (3 2 1), (3 3 3), (1 3 4)]
SELECT foo.id AS foo_id, foo.a AS foo_a, foo.b AS foo_b, foo.c AS foo_c 
FROM foo 
WHERE foo.a = :a_1 AND foo.b = :b_1 AND foo.c = :c_1 OR foo.a = :a_2 AND foo.b = :b_2 AND foo.c = :c_2
[(1 2 3), (3 3 3)]

这篇关于如何使用 SQLAlchemy 获取与三元组条件列表匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！