首页 > 代码库 > SQLAlchemy 一对多

SQLAlchemy 一对多

2024-08-14 11:15:11   221人阅读

下述範例描述了電影同導演的多對一關係。範例中說明了從用戶定義的Python類建立數據表的方法，雙方關係例項的建立方法，以及最終查詢數據的方法：包括延遲載入和預先載入兩種自動生成的SQL查詢。

結構定義

建立兩個Python類以及DBMS中對應的數據表：

from sqlalchemy import *from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import relation, sessionmakerBase = 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 moviesSELECT directors.id, directors.nameFROM directors WHERE directors.id = %s

SQLAlchemy 一对多


联系
我们