sqlalchemy: how to join several tables by one query?

Michael Zippo

👻 Check our latest review to choose the best laptop for Machine Learning engineers and Deep learning tasks!

I have the following SQLAlchemy mapped classes:

class User(Base):
    __tablename__ = "users"
    email = Column(String, primary_key=True)
    name = Column(String)

class Document(Base):
    __tablename__ = "documents"
    name = Column(String, primary_key=True)
    author = Column(String, ForeignKey("users.email"))

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)

    document = Column(String, ForeignKey("documents.name"))

I need to get a table like this for user.email = "[email protected]":

email | name | document_name | document_readAllowed | document_writeAllowed

How can it be made using one query request for SQLAlchemy? The code below does not work for me:

result = session.query(User, Document, DocumentPermission).filter_by(email = "[email protected]").all()

Thanks,

👻 Read also: what is the best laptop for engineering students?

We hope this article has helped you to resolve the problem. Apart from sqlalchemy: how to join several tables by one query?, check other code Python module-related topics.

Want to excel in Python? See our review of the best Python online courses 2023. If you are interested in Data Science, check also how to learn programming in R.

By the way, this material is also available in other languages:

Javier Zelotti

Tallinn | 2023-03-22

Thanks for explaining! I was stuck with sqlalchemy: how to join several tables by one query? for some hours, finally got it done 🤗. Will get back tomorrow with feedback

Boris Emmerson

London | 2023-03-22

Thanks for explaining! I was stuck with sqlalchemy: how to join several tables by one query? for some hours, finally got it done 🤗. Will get back tomorrow with feedback

Marie Nickolson

Warsaw | 2023-03-22

I was preparing for my coding interview, thanks for clarifying this - sqlalchemy: how to join several tables by one query? in Python is not the simplest one. Checked yesterday, it works!