In SQL, I can use the IN operator with a subquery like so:
SELECT * FROM t1
WHERE (t1.some_int, t1.some_string) IN (
SELECT id, name FROM t2
)
But I am unable to translate this to an SQLAlchemy query. As far as I know, the in_ method only works on one column. Is there any way to replicate this functionality in SQLAlchemy?
You could use JOIN instead of subquery. Something like this:
SELECT * FROM t1 INNER JOIN t2 ON t1.some_int = t2.id AND t1.some_string = t2.name
And in sqlalchemy:
T1:
class T1(DeclarativeBase):
__tablename__ = 't1'
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(u'id', Integer, primary_key=True)
some_int = Column('some_int', Integer)
some_str = Column('some_str', String(45))
def __init__ (self, some_int, some_str):
self.some_int = some_int
self.some_str = some_str
T2:
class T2(DeclarativeBase):
__tablename__ = 't2'
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(u'id', Integer, primary_key=True)
name = Column('name', String(45))
data = Column('data', String(45))
def __init__ (self, name, data):
self.name = name
self.data = data
In source code:
data = session.query(T1).join(T2, and_(T1.some_int == T2.id, T1.some_string == T2.name)).all()
In result engine generates sql:
SELECT t1.some_int AS t1_some_int, t1.id AS t1_id FROM t1 INNER JOIN t2 ON t1.some_int = t2.id AND t1.some_str = t2.name
