Python ORM框架SQLAlchemy学习笔记之关系映射实例

什么是ORM框架

ORM (Object Relational Mapping) 即对象关系映射，是通过使用描述对象和数据库之间映射的元数据，将面向对象语言程序中的对象自动持久化到关系型数据库中。

ORM框架的优点：

ORM框架能够消除常见的 SQL 注入问题，提高代码的安全性。
ORM框架主要支持多种非常流行的数据库，包括MySQL, PostgreSQL等，具有很强的兼容性。
ORM框架能够使程序的可维护性和可重用性更高，程序员更注重开发业务逻辑而非SQL查询。

SQLAlchemy概述

SQLAlchemy是Python语言的开源ORM框架之一，使用Apache License 2.0发行。它实现了大部分的SQL92标准，包括如何声明关系、联结、执行原始SQL语句等。

SQLAlchemy提供两个主要的工具：SQLAlchemy Core和SQLAlchemy ORM，其中SQLAlchemy Core为SQLAlchemy提供了一些底层的SQL执行工具，而SQLAlchemy ORM则提供了一个通过关系映射为面向对象程序设计的QLAPI。

至于为什么要使用SQLAlchemy ORM，以下几点原因：

抽象数据库操作以及 Model 层，风格类似 Django ORM 但不限于 django 的使用范围；
持有一个 SQL 核心，提供了一个直接使用原生 SQL 语句的实现方式；
提供更加可控的事务会话，避免除了自己业务意愿之外引入多余的事务阻塞等。

SQLALchemy与ORM花式查询

SQLAlchemy提供了两种主要方式来进行查询：

通过SQLAlchemy Core；
通过SQLAlchemy ORM。

这里我们选择通过SQLAlchemy ORM的方式来进行查询。SQLAlchemy ORM的查询方式可以分为基础查询和高级查询两种方式。

基础查询

基础查询通过select()函数来构建查询语句，常用的过滤器方法包括：

filter()：通过指定条件来选择性返回结果；
filter_by()：通过指定字段和值来选择性返回结果；
first()：返回第一条记录；
one()：返回第一条且保证只有一条记录，否则抛出异常。

示例：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql+pymysql://root:123456@localhost/test?charset=utf8', 
                       echo=True, encoding='utf-8')

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    age = Column(Integer)

from sqlalchemy.orm import sessionmaker

DBSession = sessionmaker(bind=engine)

session = DBSession()

# 基础查询示例
result = session.query(User).first()
print(type(result))
print(result.name)

高级查询

高级查询可以通过filter()、filter_by()等方法和运算符操作来实现复杂的查询，SQLAlchemy ORM还提供了类似原生SQL语句的查询方式。常用的方法包括：

group_by()：对查询结果进行分组；
having()：对查询结果进行过滤；
order_by()：对查询结果进行排序；
limit()和offset()：用于分页。

示例：

# 高级查询示例1
result = session.query(User).filter(User.name == 'aaron').filter(User.age == 20).all()

# 高级查询示例2
from sqlalchemy import func
result = session.query(User.name, func.count('*').label('count')).group_by(User.name).all()

# 高级查询示例3
from sqlalchemy.orm import aliased
a1 = aliased(User)
a2 = aliased(User)
result = session.query(a1.name, a2.name).filter(a1.age > a2.age).all()

示例1表示查询名字为aaron，并且年龄为20岁的所有用户；示例2表示查询用户按名字分组，并统计每组用户数量；示例3表示查询年龄比自己小的用户，并将这些结果按照名字进行聚合。在示例3中，我们使用了一个新的技术：Aliased对象来生成一个User表的副本，并用副本来实现查询比当前用户年龄小的用户。