title: Sqlalchemy in_ subquery
body: <p>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:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>In sql my query would be </p>
  
  <pre><code>select id from person where id not in (select person_id from foo);
  </code></pre>
  
  <p>How do I implement this in Python and sqlalchemy? I found this <a href="https://stackoverflow.com/questions/20060485/sqlalchemy-select-using-reverse-inclusive-not-in-list-of-child-column-values">sqlalchemy, select using reverse-inclusive (not in) list of child column values</a> 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.</p>
  
  <p>How do I do this? I have tried a combination of in_ and any operators, with different exceptions thrown. For example this attempt:</p>
  
  <pre><code>empty_persons = config.Session.query(Person).filter(Person.id.notin_(ChildTable.\
      person_id)).all()
  </code></pre>
  
  <p>This fails: in_() accepts either a list of expressions or a selectable. </p>
  
  <p>This is probably a simple thing to do but I just don't understand how this should be done.</p>
  
  <p>R</p>