SQLAlchemy Error Query Join Across Database

I have multiple sqlite databases. I tried this below code to get a Construction data from 'Owner1': sess.query(Construction).select_from(join_(Owner)).filter(Owner.name == 'Owner 1

Solution 1:

The problem is that the resulting query is not "cross-database", it executes using engine for Construction, and its database indeed does not have a table for Owner.

You can do that in two steps: get id of the Owner(s), and then search Construction:

In case of just one owner:

owner = sess.query(Owner).filter(Owner.name = 'Owner 1').one()
q = sess.query(Construction).filter(Construction.owner_id == owner.id)

In case of multiple owners:

owners = sess.query(Owner).filter(Owner.name.like('Owner 1%')).all()
ids = (_.id for _ in owners)
q = sess.query(Construction).filter(Construction.owner_id.in_(ids))