知乎： 使用 sqlalchemy 的 orm 方式操作数据库是一种怎样的体验？
答： 酸爽！
本文基于：win10 + python3.4 + sqlAlchemy 1.0.13
先看一个图（来源）：
这是 sqlalchemy 的层级图。不难发现，其中 orm 是最顶级的封装。
ORM 基本操作步骤如下：
1. 建立连接
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)
2. 建立会话
from sqlalchemy.orm import Session
session = Session(engine)
3. 声明基类
from sqlalchemy.ext.declarative
import declarative_base
Base = declarative_base()
4. 定义表（继承基类）
from sqlalchemy
import Column, Integer, ForeignKey
from sqlalchemy.orm
import relationship
class Parent(Base):
__tablename__ =
'parent'
id = Column(Integer, primary_key=
True)
children = relationship(
"Child")
def __repr__(self):
return "<Parent(id='{}', children='{}')>".format(self.id, self.children)
class Child(Base):
__tablename__ =
'child'
id = Column(Integer, primary_key=
True)
parent_id = Column(Integer, ForeignKey(
'parent.id'))
def __repr__(self):
return "<Child(id='{}', parent_id='{}')>".format(self.id, self.parent_id)
5. 生成表
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine) # 生产环境只需运行一次！！！！
6. 添加记录
session.add_all([Parent(children=[Child()
for j
in range(5)])
for i
in range(2
)])
session.commit()
7. 查询
# 懒加载 (lazyload)
# load everything, no eager loading.
for parent
in session.query(Parent):
print(parent.children)
# 联合加载 (joinedload)
# load everything, joined eager loading.
for parent
in session.query(Parent).options(joinedload(
"children")):
parent.children
# 子查询加载 (subqueryload)
# load everything, subquery eager loading.
for parent
in session.query(Parent).options(subqueryload(
"children")):
parent.children
8. 完整代码
from sqlalchemy
import create_engine
from sqlalchemy.orm
import Session
from sqlalchemy.ext.declarative
import declarative_base
from sqlalchemy
import Column, Integer, ForeignKey
from sqlalchemy.orm
import relationship
from sqlalchemy.orm
import joinedload, subqueryload
# 建立连接
engine = create_engine(
'sqlite:///:memory:', echo=
True)
# 建立会话
session =
Session(engine)
# 声明基类
Base =
declarative_base()
# 定义表（继承基类）
class Parent(Base):
__tablename__ =
'parent'
id = Column(Integer, primary_key=
True)
children = relationship(
"Child")
def __repr__(self):
return "<Parent(id='{}', children='{}')>".format(self.id, self.children)
class Child(Base):
__tablename__ =
'child'
id = Column(Integer, primary_key=
True)
parent_id = Column(Integer, ForeignKey(
'parent.id'))
def __repr__(self):
return "<Child(id='{}', parent_id='{}')>".format(self.id, self.parent_id)
# 生成表
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine) # 生产环境只需运行一次！！！！
# 添加记录
session.add_all([Parent(children=[Child()
for j
in range(5)])
for i
in range(2
)])
session.commit()
# 查询
# lazyload
# load everything, no eager loading.
for parent
in session.query(Parent):
print(parent.children)
# joinedload
# load everything, joined eager loading.
for parent
in session.query(Parent).options(joinedload(
"children")):
parent.children
# subqueryload
# load everything, subquery eager loading.
for parent
in session.query(Parent).options(subqueryload(
"children")):
parent.children
转载于:https://www.cnblogs.com/hhh5460/p/5513992.html
相关资源：JAVA上百实例源码以及开源项目
