CODEWITHSUNDEEP
pythonsqlalchemy
Sledge
Sledge

Reputation: 1345

sqlalchemy: Select from table where column in QUERY

I have a situation where I am trying to count up the number of rows in a table when the column value is in a subquery. For example, lets say that I have some sql like so:

select count(*) from table1
where column1 in (select column2 from table2);

I have my tables defined like so:

class table1(Base):
    __tablename__ = "table1"
    __table_args__ = {'schema': 'myschema'}
    acct_id = Column(DECIMAL(precision=15), primary_key=True)


class table2(Base):
    __tablename__ = "table2"
    __table_args__ = {'schema': 'myschema'}
    ban = Column(String(length=128), primary_key=True)

The tables are reflected from the database so there are other attributes present that aren't explicitly specified in the class definition.

I can try to write my query but here is where I am getting stuck...

qry=self.session.query(func.?(...)) # what to put here?
res = qry.one()

I tried looking through the documentation here but I don't see any comparable implementation to the 'in' keyword which is a feature of many SQL dialects.

I am using Teradata as my backend if that matters.

Upvotes: 1

Views: 1316

Answers (1)

nosklo
nosklo

Reputation: 222822

sub_stmt = session.query(table2.some_id)
stmt = session.query(table1).filter(table1.id.in_(sub_stmt))
data = stmt.all()

Upvotes: 1

Related Questions