sqlalchemy 的 ORM 方式使用示例

知乎：使用 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

巴特西

sqlalchemy 的 ORM 方式使用示例

1. 建立连接

2. 建立会话

3. 声明基类

4. 定义表（继承基类）

5. 生成表

6. 添加记录

7. 查询

8. 完整代码

最新文章

热门文章