How to join unrelated tables with sqlalchemy?

Question

Say I have two table definitions like so:

class Item(Base):
    __tablename__ = "items"
    __table_args__ = {"schema": "my_schema"}
    id = Column(Integer, primary_key=True)
    category= Column(Integer)

class Other(Base):
    __tablename__ = "others"
    __table_args__ = {"schema": "my_schema"}
    id= Column(Integer, primary_key=True)
    some_other_category= Column(Integer)

In my handler, I am creating an engine, like so:

engine = self.connector.get_engine()
        Base.metadata.create_all(engine)

Now my goal is to define a query that performs a join of these two tables:

    def process(self):

        query = select(Item).join_from(Other, Other.id)

        # The actual execution is hidden for the sake of readability.
        # Simple selects on both tables individually work just fine.

        result = some_helper.fetch_connection(query)
        print(result)
        return result

The error I get is the following, on which I can find no explanation:

sqlalchemy.exc.ArgumentError: Join target Other.id does not refer to a mapped entity

What am I doing wrong here?

you use `union` see https://stackoverflow.com/questions/20024744/how-to-union-two-subqueries-in-sqlalchemy-and-postgresql — nbk, Jul 09 '23 at 11:57
I need to perform an inner join. I want a table with both columns (category from items and some_other_category from other) by id. — Ipsider, Jul 09 '23 at 12:12
Sample data and desired output at formatted text table can be helpful. — June7, Jul 10 '23 at 03:42
Perhaps you want `.join_from(Item, Other, Item.id == Other.id)` — snakecharmerb, Aug 10 '23 at 14:53

How to join unrelated tables with sqlalchemy?

0 Answers0