Implicit Joining

If mapped classes of related PostgreSQL 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: