Sqlalchemy query over multiple tables

0
votes

I am using SQLAlchemy with FastAPI to build an API. So my current problem is, querying the data from the database. I have multiple tables e.g. Table Header, Table Foo and Table Bar.

class Header(Base):
idno = Column(NUMBER, primary_key=True)
id_db = Column(NUMBER, primary_key=True)
authorization = Column(NUMBER)
foo = relationship('Foo')


class Foo(Base):
idno = Column(NUMBER, primary_key=True)
id_db = Column(NUMBER, primary_key=True)
idno_header = Column(NUMBER, Foreign_key(Header.idno))
id_db_header = Column(NUMBER, Foreign_key(Header.id_db))
nr = Column(NUMBER)
header = relationship('Header')
bar = relationship('Bar')
another_bar = relationship('AnotherBar')


class Bar(Base):
idno = Column(NUMBER, primary_key=True)
id_db = Column(NUMBER, primary_key=True)
idno_foo = Column(NUMBER, Foreign_key(Bar.idno)
id_db_foo = Column(NUMBER, Foreign_key(Bar.id_db)
data = Column(NUMBER)
foo = relationship('Foo')

class AnotherBar(Base):
idno = Column(NUMBER, primary_key=True)
id_db = Column(NUMBER, primary_key=True)
idno_foo = Column(NUMBER, Foreign_key(AnotherBar.idno)
id_db_foo = Column(NUMBER, Foreign_key(AnotherBar.id_db)
anoter_bar = relationship('Foo')

In my actual code, the Foreign_key is replaced by a ForeignKeyConstraint. This example is only for demonstration.

What I want to do is to get the results from the following queryies:

1. query = db.query(Header, Foo, Bar).join(Header.foo).join(Foo.bar).filter(Bar.data == 2)

2. query = db.query(Header, Foo, AnotherBar).join(Header.foo).join(Foo.another_bar).filter(AnotherBar.data == 3)

The above example works, but when i run e.g. the first query it returns the data from Header, Foo, Bar and AnotherBar. How can I get only the requested data from the query?

Thanks for any help :)

pythonmysqlsqldatabasesqlalchemy