详细分析Python中的SQLAlchemy库（附Demo）

目录

前言

了解这篇文章的同时推荐阅读：

1. 详细讲解Python连接Mysql的基本操作
2. java框架 零基础从入门到精通的学习路线 附开源项目面经等（超全）

1. 基本知识

**一、ORM (对象关系映射)**：

• SQLAlchemy 是Python SQL工具包和对象关系映射器(ORM)，允许Python开发者在应用程序中使用SQL来交互，而无需处理数据库的具体细节
• 提供一个高层的抽象层，允许开发者通过Python类和对象来表示数据库中的表和行，从而使得数据库操作更加方便和灵活

二、核心（Core）：
SQLAlchemy 的核心部分提供了一组工具来执行SQL操作，包括

创建和执行SQL语句

、

连接池管理

、

事务管理

等。开发者可以使用核心部分来执行一些高级的数据库操作，如

自定义SQL语句

、

连接到数据库

等。

三、优点：

• 灵活性： 多种不同的方式来与数据库交互，包括使用核心部分执行原始SQL语句、使用ORM进行对象关系映射、以及使用表达式语言构建SQL查询等
• 功能丰富： 许多功能丰富的工具和API，满足各种不同的数据库操作需求
• ORM支持： SQLAlchemy 的ORM工具允许开发者使用Python类来代表数据库中的表和行，从而使得数据库操作更加Pythonic和易于理解 ORM工具提供了一种高级的抽象，隐藏了底层数据库操作的细节，使得开发者可以更专注于业务逻辑的实现
• 跨数据库支持： 支持多种不同的数据库后端，包括MySQL、PostgreSQL、SQLite等
• 活跃的社区： SQLAlchemy 有一个活跃的社区，提供了大量的文档、教程和示例代码，使得开发者可以更容易地学习和使用这个工具包

四、缺点：

性能开销

尽管SQLAlchemy提供了许多便利的功能，但有时候这些功能可能会带来一定的性能开销。特别是在处理大量数据或需要高性能的场景下，可能需要仔细优化代码以减少性能损失

五、与其他工具比较：

与其他ORM工具的比较：
Django ORMPeeweeSQLObject与Django ORM相比，SQLAlchemy提供了更多的灵活性和功能，尤其是在处理复杂数据库操作和跨数据库支持方面

但Django ORM更容易上手，并且与Django框架无缝集成，适合快速开发和小型项目Peewee 是另一个轻量级的Python ORM工具，相比于SQLAlchemy，它的学习曲线更为平缓，适合于简单的数据库操作和小型项目

但Peewee的功能相对较少，不如SQLAlchemy灵活SQLObject 是另一个Python ORM库，它的设计更加接近于Active Record模式，与SQLAlchemy的Data Mapper模式有所不同

但SQLObject的学习曲线较陡，且功能相对较少，通常适用于简单的数据库操作

2. 基本API

1. 安装库：pip install SQLAlchemy
2. 验证是否安装成功：python -c "import sqlalchemy; print(sqlalchemy.__version__)" 或者 pip show sqlalchemy

为了让大家更快上手，先学习下下面这个实战项目：

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

# 创建引擎
engine = create_engine('mysql+pymysql://root:[email protected]:3306/easy-admin')# 创建Session
Session = sessionmaker(bind=engine)
session = Session()# 定义映射类
Base = declarative_base()classUser(Base):
    __tablename__ ='manong'id= Column(Integer, primary_key=True)
    name = Column(String(255))# 在这里指定了 name 列的长度为 255
    age = Column(Integer)# 创建表
Base.metadata.create_all(engine)# 插入数据
new_user = User(name='yanjiuseng', age=25)
session.add(new_user)
session.commit()# 查询数据
query = session.query(User).filter(User.age >18)
result = query.all()for user in result:print(user.name, user.age)

最终截图如下：

通过看完整个代码逻辑，带着一些小疑问，深入探讨下这些API的使用方式

2.1 create_engine（创建引擎）

create_engine

函数用于创建一个与数据库的连接引擎，该引擎可以执行SQL操作

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://username:password@host:port/database')

针对里头的参数解释如下：

• mysql：指定数据库类型，这里是 MySQL 数据库
• username：数据库用户名
• password：数据库密码
• host：数据库主机名或 IP 地址
• port：数据库端口号，默认是 MySQL 的端口号 3306
• database：要连接的数据库名称

MySQL 数据库用户名是 user1，密码是 pass123，主机名是 localhost，端口号是 3306，要连接的数据库名称是 my_database，那么连接字符串就应该是：

'mysql://user1:pass123@localhost:3306/my_database'

对于数据库类型常用的：

mysql+pymysql

，主要区别在于其使用的数据库驱动程序不同。

• mysql+pymysql：（更简单地安装和使用，可以选择使用 pymysql） 指定使用 PyMySQL 作为连接 MySQL 数据库的驱动程序，PyMySQL 是一个纯 Python 实现的 MySQL 客户端库，兼容 Python 数据库 API 规范 2.0，可以在 Python 中直接使用
• mysql：（对性能要求比较高，可以选择使用 mysql 并配合 MySQLdb 或者 mysqlclient） 没有指定具体的数据库驱动程序，使用默认的 MySQL 客户端库，一般情况下会使用 MySQLdb 或者 mysqlclient

再额外补充其他的URL格式：

# MySQL-Python:
mysql+mysqldb://<user>:<password>@<host>:<port>/<dbname># pymysql:
mysql+pymysql://<username>:<password>@<host>:<port>/<dbname>?<options># MySQL-Connector:
mysql+mysqlconnector://<user>:<password>@<host>:<port>/<dbname># cx_Oracle:
oracle+cx_oracle://<user>:<password>@<host>:<port>/<dbname>?key=value&key=value...

2.2 sessionmaker（创建session）

用于创建一个 Session 类，该类用于执行 ORM（对象关系映射）操作

主要作用是创建一个会话工厂，通过工厂可以创建数据库会话对象，用于在代码中执行数据库操作

# 创建Session
Session = sessionmaker(bind=engine)
session = Session()

其中sessionmaker的参数如下：

• bind：要绑定到的数据库引擎，通常是一个 create_engine 函数返回的 Engine 对象
• class_：可选参数，指定要创建的会话类，默认为 Session 类
• autocommit：是否自动提交事务，默认为 False
• autoflush：是否自动刷新会话，默认为 True
• expire_on_commit：在提交事务时是否自动使对象过期，默认为 True
• info：一个字典，用于指定会话的其他配置信息

具体示例如下：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 创建引擎
engine = create_engine('sqlite:///example.db')# 创建会话工厂
Session = sessionmaker(bind=engine, autocommit=False, autoflush=True)# 创建会话对象
session = Session()

需要注意的点如下：

• 在使用会话对象执行数据库操作后，一般需要调用 commit 方法提交事务，或者调用 rollback 方法回滚事务。
• 在会话对象的作用域结束时，通常需要调用 close 方法关闭会话，释放数据库连接资源。

2.3 declarative_base（定义映射类）

使用 ORM 进行数据库操作的核心部分之一，涉及到将数据库中的表映射到 Python 中的类，以及定义类属性来表示表的列

一、映射类的定义：
通过创建Python 类来表示数据库中的表
该类通常继承自 SQLAlchemy 的 Base 类，而 Base 类是使用

declarative_base()

函数创建的

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

二、表的映射：

在映射类中定义

__tablename__

属性，指定该类所映射的数据库表的名称

classUser(Base):
    __tablename__ ='users'

三、列的映射：

在映射类中定义类属性，来表示表中的列

每个类属性通常都会被定义为

Column

对象，并指定其数据类型以及其他属性

from sqlalchemy import Column, Integer, String

classUser(Base):
    __tablename__ ='users'id= Column(Integer, primary_key=True)
    name = Column(String(255))
    age = Column(Integer)

对应的属性如下：

属性的含义：

• Column：表示一个数据库表的列
• Integer、String 等数据类型：表示列的数据类型
• primary_key=True：指定该列为主键

其他参数：例如长度、唯一性等，用于进一步定义列的属性

2.4 SQL与ORM差异

一、基于 SQL 的查询：
特点：

• 原始的 SQL 查询语句，手动编写 SQL 语句来执行数据库操作。
• 于执行复杂的查询、跨表查询或性能要求较高的场景

示例代码：

from sqlalchemy import create_engine, text

# 创建引擎
engine = create_engine('mysql+pymysql://root:[email protected]:3306/easy-admin')# 执行 SQL 查询with engine.connect()as connection:
    result = connection.execute(text("SELECT * FROM manong WHERE age > :age"),{'age':18})for row in result:print(row)

截图如下：

二、基于ORM查询：

• 操作对象来执行数据库操作，而不需要编写原始的 SQL 语句
• 提供了更加 Pythonic 和面向对象的接口，使得代码更加清晰和易于维护

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

# 创建引擎
engine = create_engine('mysql+pymysql://root:[email protected]:3306/easy-admin')# 创建Session
Session = sessionmaker(bind=engine)
session = Session()# 定义映射类
Base = declarative_base()classUser(Base):
    __tablename__ ='manong'id= Column(Integer, primary_key=True)
    name = Column(String(255))# 在这里指定了 name 列的长度为 255
    age = Column(Integer)

result = session.query(User).filter(User.age >18).all()for user in result:print(user.name, user.age)

区别的方式在于：

• 实现方式：基于 SQL 的查询直接使用原始的 SQL 语句，而基于 ORM 的查询则是通过 ORM 工具来执行数据库操作
• 编写方式：基于 SQL 的查询需要开发者手动编写 SQL 语句，而基于 ORM 的查询则是通过操作对象来执行数据库操作，不需要编写原始的 SQL 语句
• 灵活性：基于 SQL 的查询更加灵活，可以执行复杂的原始 SQL 查询，而基于 ORM 的查询提供了更加 Pythonic 和面向对象的接口，使得代码更加清晰和易于维护

3. ORM CRUD

对于基本的SQL查询，需要编写SQL语句，此处偏向实战类，所以详细补充ORM CRUD的的基本知识

前半部分代码如下：

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

# 创建引擎
engine = create_engine('mysql+pymysql://root:[email protected]:3306/easy-admin')# 创建Session
Session = sessionmaker(bind=engine)
session = Session()# 定义映射类
Base = declarative_base()classUser(Base):
    __tablename__ ='manong'id= Column(Integer, primary_key=True)
    name = Column(String(255))# 在这里指定了 name 列的长度为 255
    age = Column(Integer)# 创建表
Base.metadata.create_all(engine)

3.1 增加（C）

• 添加单个对象：将新对象添加到数据库中

user = User(name='AA', age=30)
session.add(user)
session.commit()

• 添加多个对象：将多个新对象批量添加到数据库中

users =[User(name='BB', age=30), User(name='CC', age=25)]
session.add_all(users)
session.commit()

3.2 查找（R）

• 查询所有对象：从数据库中检索所有对象

all_users = session.query(User).all()

• 根据条件查询：根据指定条件过滤对象

# # 查询数据
result = session.query(User).filter(User.age >18).all()for user in result:print(user.name, user.age)

• 查询单个对象：从数据库中检索满足条件的单个对象

user = session.query(User).filter_by(name='Alice').first()

3.3 更新（U）

• 更新单个对象：修改数据库中的现有对象

user = session.query(User).filter_by(name='Alice').first()
user.age =35
session.commit()

• 批量更新：使用 update() 方法批量更新满足条件的对象

session.query(User).filter(User.age <30).update({'age':30})
session.commit()

3.4 删除（D）

• 删除单个对象：从数据库中删除指定的对象

user = session.query(User).filter_by(name='Alice').first()
session.delete(user)
session.commit()

• 批量删除：使用 delete() 方法批量删除满足条件的对象

session.query(User).filter(User.age >30).delete()
session.commit()

4. 彩蛋

4.1 建表Bug

建表的过程中如果语句如下：

classManong(Base):
    __tablename__ ='manong'id= Column(Integer)
    name = Column(String)

报错信息如下：

sqlalchemy.exc.CompileError: (in table 'manong', column 'name'): VARCHAR requires a length on dialect mysql

主要问题如下：
在 MySQL 中，VARCHAR 类型的列必须指定长度，即字符的最大数量。
需要为表中的 VARCHAR 类型的列指定长度

将其代码修改为：

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

Base = declarative_base()classManong(Base):
    __tablename__ ='manong'id= Column(Integer, primary_key=True)
    name = Column(String(255))# 在这里指定了 name 列的长度为 255# 继续定义其他列和表结构

如果不是建表，可以省略字段长度

4.2 filter 和 filter_by

• filter 方法使用类名和属性名来构建查询条件，比较通常使用 ==，也可以使用其他比较操作符如 >, <, >=, <= 等
• filter_by 方法直接使用属性名和相应的值来构建查询条件，比较通常使用 =

以下为简易Demo，方便理解：

# 使用 filter 方法from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()# 查询名字为 Alice 的记录
alice_records = session.query(User).filter(User.name =='Alice').all()# 查询年龄大于等于 25 岁的记录
older_users = session.query(User).filter(User.age >=25).all()# 使用 filter_by 方法# 查询名字为 Alice 的记录
alice_records = session.query(User).filter_by(name='Alice').all()

filter 的组合查询： （这个在实战中比较常用！！！）

通过连续调用来实现多个条件的组合查询，或者使用AND 条件连接多个条件

# 使用 filter 连续添加条件查询# 查询名字为 Alice 且年龄大于等于 25 岁的记录
alice_older_records = session.query(User).filter(User.name =='Alice').filter(User.age >=25).all()

或者如下：

from sqlalchemy import and_

# 使用 and_ 函数连接两个条件
alice_older_records = session.query(User).filter(and_(User.name =='Alice', User.age >=25)).all()