下述範例描述了電影同導演的多對一關係。範例中說明了從用戶定義的Python類建立數據表的方法，雙方關係例項的建立方法，以及最終查詢數據的方法：包括延遲載入和預先載入兩種自動生成的SQL查詢。
結構定義
建立兩個Python類以及DBMS中對應的數據表：
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, sessionmaker
Base = declarative_base()
class Movie(Base):
__tablename__ = 'movies'
id = Column(Integer, primary_key=True)
title = Column(String(255), nullable=False)
year = Column(Integer)
directed_by = Column(Integer, ForeignKey('directors.id'))
director = relation("Director", backref='movies', lazy=False)
def __init__(self, title=None, year=None):
self.title = title
self.year = year
def __repr__(self):
return "Movie(%r, %r, %r)" % (self.title, self.year, self.director)
class Director(Base):
__tablename__ = 'directors'
id = Column(Integer, primary_key=True)
name = Column(String(50), nullable=False, unique=True)
def __init__(self, name=None):
self.name = name
def __repr__(self):
return "Director(%r)" % (self.name)
engine = create_engine('dbms://user:pwd@host/dbname')
Base.metadata.create_all(engine)
插入數據
插入的電影和導演物件可以互相參照：
Session = sessionmaker(bind=engine)
session = Session()
m1 = Movie("Star Trek", 2009)
m1.director = Director("JJ Abrams")
d2 = Director("George Lucas")
d2.movies = [Movie("Star Wars", 1977), Movie("THX 1138", 1971)]
try:
session.add(m1)
session.add(d2)
session.commit()
except:
session.rollback()
查詢
alldata = session.query(Movie).all()
for somedata in alldata:
print somedata
SQLAlchemy將向DBMS（忽略差異）發起如下查詢：
SELECT movies.id, movies.title, movies.year, movies.directed_by, directors.id, directors.name
FROM movies LEFT OUTER JOIN directors ON directors.id = movies.directed_by
並輸出：
Movie('Star Trek', 2009L, Director('JJ Abrams'))
Movie('Star Wars', 1977L, Director('George Lucas'))
Movie('THX 1138', 1971L, Director('George Lucas'))
假如設定lazy=True（預設值），SQLAlchemy將首先發起對電影列表的查詢，並在必要時（延遲載入）逐一查詢導演的名稱：
SELECT movies.id, movies.title, movies.year, movies.directed_by
FROM movies
SELECT directors.id, directors.name
FROM directors
WHERE directors.id = %s
並輸出：
Movie('Star Trek', 2009L, Director('JJ Abrams'))
Movie('Star Wars', 1977L, Director('George Lucas'))
Movie('THX 1138', 1971L, Director('George Lucas'))
假如設定lazy=True
（預設值），SQLAlchemy將首先發起對電影列表的查詢，並在必要時（延遲載入）逐一查詢導演的名稱：
SELECT movies.id, movies.title, movies.year, movies.directed_by FROM moviesSELECT directors.id, directors.nameFROM directors WHERE directors.id = %s
