首页 > 代码库 > SQLAlchemy 一对多
SQLAlchemy 一对多
下述範例描述了電影同導演的多對一關係。範例中說明了從用戶定義的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 一对多
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。