Имея список 3-х кортежей:
[(a, b, c), (d, e, f)]
Я хочу получить все строки из таблицы, где 3 столбца соответствуют кортежам. В этом примере предложение WHERE может быть примерно таким:
(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-х наборов будет содержать сотни элементов, и я ищу наилучшее решение.
Спасибо за вашу помощь,
Самый простой способ - использовать функцию tuple_, предоставляемую SQLAlchemy:
from sqlalchemy import tuple_
session.query(Foo).filter(tuple_(Foo.a, Foo.b, Foo.c).in_(items))
Это работает с PostgreSQL, но не работает с SQLite. Не уверен насчет других механизмов баз данных.
К счастью, существует обходной путь, который должен работать на всех базах данных.
Начните с сопоставления всех элементов с выражением and_:
conditions = (and_(c1=x, c2=y, c3=z) for (x, y, z) in items)
А затем создайте фильтр or_, который охватывает все условия:
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)]
Менее условный подход, который, как я подозреваю, будет хорошо масштабироваться, - это создать временную таблицу всех ваших кортежей и затем присоединиться к ней:
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Table
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine = sqlalchemy.create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()
class Triple(Base):
__tablename__ = 'triple'
id = Column(Integer(), primary_key=True)
x = Column(Integer())
y = Column(Integer())
z = Column(Integer())
ws_table = Table('where_sets', Base.metadata,
Column('x', Integer()),
Column('y', Integer()),
Column('z', Integer()),
prefixes = ['temporary']
)
Base.metadata.create_all(engine)
...
where_sets = [(1, 2, 3), (3, 2, 1), (1, 1, 1)]
ws_table.create(engine, checkfirst=True)
session.execute(ws_table.insert(), [dict(zip('xyz', s)) for s in where_sets])
matches = session.query(Triple).join(ws_table, (Triple.x==ws_table.c.x) & (Triple.y==ws_table.c.y) & (Triple.z==ws_table.c.z)).all()
который выполняет SQL следующим образом:
INSERT INTO triple (x, y, z) VALUES (?, ?, ?)
(1, 2, 3)
INSERT INTO triple (x, y, z) VALUES (?, ?, ?)
(3, 1, 2)
INSERT INTO triple (x, y, z) VALUES (?, ?, ?)
(1, 1, 1)
SELECT triple.id AS triple_id, triple.x AS triple_x, triple.y AS triple_y, triple.z AS triple_z
FROM triple JOIN where_sets ON triple.x = where_sets.x AND triple.y = where_sets.y AND triple.z = where_sets.z
Кто-нибудь подумал бы о создании дополнительного ключа в исходной таблице? то есть создайте новый столбец с "1" - "2" - "3" вместо другой таблицы и проверьте уникальность.