Executing JOINs
Implicit Joining
If mapped classes of related IMAP objects have a singular foreign key relationship, the classes are implicitly joined. After importing the necessary objects, a relationship is established between your two mapped classes, as in the example below:from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, String, Integer, DateTime, ForeignKey from sqlalchemy.orm import sessionmaker, relationship Base = declarative_base() class Contact(Base): __tablename__ = "Contact" Id = Column(Integer, primary_key=True) Name = Column(String) Email = Column(String) BirthDate = Column(DateTime) AccountId = Column(String, ForeignKey("Account.Id")) Account_Link = relationship("Account", back_populates="Contact_Link") class Account(Base): __tablename__ = "Account" Id = Column(String, primary_key=True) Name = Column(String) BillingCity = Column(String) NumberOfEmployees = Column(Integer) Contact_Link = relationship("Contact", order_by=Contact.Id, back_populates="Account_Link")
Once the relationship is established, the tables are queried simultaneously using the session's query() method.
For example:
rs = session.query(Account, Contact).filter(Account.Id == Contact.AccountId) for Ac, Ct in rs: print("AccountId: ", Ac.Id) print("AccountName: ", Ac.Name) print("ContactId: ", Ct.Id) print("ContactName: ", Ct.Name)
Other Join Forms
In situations where mapped classes have either no foreign keys or multiple foreign keys, you may need different forms of the JOIN query to accommodate them. Using the earlier classes as examples, the following JOIN queries are possible as well:- Explicit condition (necessary if there are no foreign keys in your mapped classes):
rs = session.query(Account, Contact).join(Contact, Account.Id == Contact.AccountId) for Ac, Ct in rs:
- Left-to-right relationship:
rs = session.query(Account, Contact).join(Account.Contact_Link) for Ac, Ct in rs:
- Left-to-right relationship with explicit target:
rs = session.query(Account, Contact).join(Contact, Account.Contact_Link) for Ac, Ct in rs:
- String form of a left-to-right relationship:
rs = session.query(Account, Contact).join("Contact_Link") for Ac, Ct in rs: