Sqlalchemy in_ subquery

Question

I am trying to implement a select with a nested select clause, to find parents without any children. My tables (radically simplified) are as follows:

class Person(Base):
    __tablename__ = "person"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=True, default=None)

class ChildTable(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    data = Column(String)
    person_id = Column(Integer, ForeignKey("person.id"), nullable=True)

In sql my query would be

select id from person where id not in (select person_id from foo);

How do I implement this in Python and sqlalchemy? I found this sqlalchemy, select using reverse-inclusive (not in) list of child column values but it relies on declared relationships. I do not use relationships as I had quite a many problems with them, mostly other threads expiring or changing data cached by other threads, and the fact that foo.person_id can be null and not reference anything.

How do I do this? I have tried a combination of in_ and any operators, with different exceptions thrown. For example this attempt:

empty_persons = config.Session.query(Person).filter(Person.id.notin_(ChildTable.\
    person_id)).all()

This fails: in_() accepts either a list of expressions or a selectable.

This is probably a simple thing to do but I just don't understand how this should be done.

R

Answer 1

Using a subquery:

sub_stmt = config.Session.query(ChildTable.person_id)
stmt = config.Session.query(Person).filter(~Person.id.in_(sub_stmt))
empty_persons = stmt.all()

emits the following sql:

SELECT person.id AS person_id, person.name AS person_name
FROM person
WHERE person.id NOT IN (SELECT foo.person_id AS foo_person_id
FROM foo)

Using a join:

stmt = config.Session.query(Person).outerjoin(ChildTable).filter(ChildTable.person_id.is_(None))
empty_persons = stmt.all()

emits the following sql:

SELECT person.id AS person_id, person.name AS person_name
FROM person LEFT OUTER JOIN foo ON person.id = foo.person_id
WHERE foo.person_id IS NULL

I think both achieve your desired result set.