flask之 sqlalchemy 原生操作和增删改查

一、sqlalchemy简介

SQLAlchemy 是一个强大的 Python 数据库工具集，它提供了一套完整的数据库访问方案。它通过多个层次来实现其功能，这些层次提供了灵活且强大的数据库操作能力。

1、SQLAlchemy底层组成

Engine（引擎）

• 角色：Engine 是 SQLAlchemy 的核心，通过它可以连接到数据库并执行 SQL 语句。
• 功能：它是一个用于管理数据库连接的接口，并负责生成与数据库交互的Connection对象。
• 创建：通常通过 create_engine() 函数来创建，使程序能够接入相应的数据库。

Connection Pooling（连接池）

• 角色：连接池用于管理一组数据库连接以供重复使用。
• 功能：通过复用已有的数据库连接，减少连接开销，提高应用的性能和效率。
• 实现：SQLAlchemy 提供了一些连接池实现，如 QueuePool（默认）、SingletonThreadPool 等，用户可以通过配置参数进行定制。

Dialect（方言）

• 角色：Dialect 用于处理不同数据库之间的特定差异。

• 功能：它管理与特定数据库的通信，决定如何执行 SQL、翻译 SQL 为数据库特定的语言、处理特定的数据类型和连接设置。

• 支持：SQLAlchemy 支持多种数据库方言，包括 SQLite、MySQL、PostgreSQL、Oracle、SQL Server 等。选择方言通过在连接字符串中指定合适的数据库驱动：

• 如 "mysql+pymysql://user:password@host/db"

Schema/Types（架构和类型）

• 角色：Schema/Types 层次定义数据库表的结构和列的数据类型。
• 功能：它用于映射 Python 对象到数据库表，并支持定义和操作数据库的结构。
• 组件：包括 Table、Column 等元素，以及丰富的内置数据类型如 Integer、String、DateTime，也允许自定义数据类型。

SQL Expression Language（SQL 表达式语言）

• 角色：提供了一个灵活的 SQL 构建器，使得可以用 Python 对象来构建 SQL 语句。

• 功能：它是一个抽象层，允许开发者通过 Python 的语法来构建复杂的 SQL 查询，而不需要手写 SQL。

• 使用：用户可以通过表达式构建器来拼凑 SQL 语句，然后交给引擎执行。例如：

• from sqlalchemy import select
• stmt = select([users_table]).where(users_table.c.id == 42)
• result = connection.execute(stmt)

SQLAlchemy 的分层结构通过 Engine 连接数据库、使用 Connection Pooling 管理连接、依赖 Dialect 处理数据库差异、通过 Schema/Types 进行数据库建模，并使用 SQL Expression Language 构建和执行表达式。这种设计使 SQLAlchemy 成为功能丰富且灵活的数据库访问工具，适用于广泛的开发需求。

2、官网

https://docs.sqlalchemy.org/en/20/

3、orm操作表的能力

SQLAlchemy

• 不支持创建数据库：SQLAlchemy 本身不能创建数据库，库在使用 SQLAlchemy 之前需要存在。
• 创建和删除表：可以通过 ORM 的 Base.metadata.create_all() 和 Base.metadata.drop_all() 方法来创建和删除数据库表。
• 修改表结构：SQLAlchemy 不原生支持数据库表结构的变更（如添加或删除列）。需要使用 Alembic，这是一个用于处理 SQLAlchemy 数据库迁移的独立工具。在 Flask 中，通常通过 Flask-Migrate 插件来实现，它是 Alembic 的一个集成。

Django ORM

• 创建数据库：Django 本身不支持直接创建数据库，通常需要手动创建，但可配置数据库参数以便进行其他操作。
• 创建、删除表和修改结构：Django 管理表结构的改变是通过迁移来完成的，包括添加和删除列。makemigrations 和 migrate 命令实现了这部分功能。因此，Django 实际上是可以添加和删除字段的。

完整性和集成性

• 独立使用和集成：SQLAlchemy 和 Django ORM 都可以作为独立模块使用，也可以集成到 Web 项目中。
  • Flask：SQLAlchemy 常与 Flask 结合使用，通过 Flask-SQLAlchemy 扩展，使其更便于在 Flask 应用中使用。
  • FastAPI：FastAPI 通常结合 SQLAlchemy 使用，利用其异步支持来实现高效的数据库访问。

二、sqlalchemy原生操作

1、方式一

# 1  导入
from sqlalchemy import create_engine
import pymysql

# 2 创建engine对象
engine = create_engine(
    "mysql+pymysql://root:bigdata@192.168.1.241:3306/monitor",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

# 3 通过engine获得conn,cursor
conn = engine.raw_connection()  # 拿到连接对象
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 4 具体操作
cursor.execute('select * from alarm_record limit 10')
print(cursor.fetchall())
cursor.close()
conn.close()

方式一的补充：

1. 连接驱动

url = f'mysql+mysqldb://{self._user}:{self._pwd}@{self._host}:{self._port}/{self._db_name}?charset=utf8'

url = f'mysql+pymysql://{self._user}:{self._pwd}@{self._host}:{self._port}/{self._db_name}?charset=utf8'

在 SQLAlchemy 中，创建数据库连接时，连接字符串的格式和使用的数据库 API 驱动程序是非常重要的。

`mysql+mysqldb` (MySQLdb) 使用 `MySQLdb` 作为数据库驱动。这是一个基于 C 的 Python 驱动程序。

需要安装 `mysqlclient`，这是 `MySQLdb` 的一个分支并且是目前维护的。

pip install mysqlclient

性能: 通常由于使用 C 编写，性能可能较好。
兼容性: 可能会遇到更多的兼容性问题，特别是在 Windows 平台下，因为编译和依赖性问题。

 `mysql+pymysql` (PyMySQL) 使用 `PyMySQL` 作为数据库驱动。`PyMySQL` 是一个纯 Python 实现的驱动。

安装更为简单，因为它不需要编译过程。

pip install pymysql

性能： 由于是纯 Python 实现，可能比基于 C 的驱动稍慢，但现代硬件通常可以忽略这一点。
兼容性: 更好地兼容各种平台（Windows、Linux、macOS），特别适合在无法轻松安装 C 扩展的环境中使用。

2. engine.raw_connection() 和 engine.connect()

engine.raw_connection()

• 直接获取底层连接：raw_connection() 方法直接返回底层数据库驱动的原生连接（在这个例子中是 PyMySQL 的连接对象），这意味着您将绕过 SQLAlchemy 的连接池和管理机制。
• 手动管理连接：使用原生连接时，您需要手动管理连接的生命周期，包括打开、关闭和清理。当使用完连接后，您必须显式地关闭它，这样可以避免连接泄漏。
• 性能：因为直接使用原生连接，它可能会略快一些，但这也导致了没有连接池的好处，连接的复用性大大降低。

engine.connect()

• 获取 SQLAlchemy 的连接对象：connect() 方法返回的是 SQLAlchemy 的连接对象，这个对象是经过连接池管理的，并为您提供了更高级别的抽象。

• 自动管理连接：使用 SQLAlchemy 管理的连接可以更好地管理连接，包括连接池的复用、自动关闭闲置连接等，简化了代码。

• 上下文管理器支持：使用 connect() 方法时，通常建议结合上下文管理器（with 语句）来实现连接的自动管理。例如：

• with engine.connect() as conn:

3. conn.cursor(pymysql.cursors.DictCursor)

DictCursor 是 PyMySQL 库中的游标类型，用于从数据库中以字典的形式返回查询结果。每一行结果都以字典的形式返回，其中字段名作为字典的键

.mappings() 是 SQLAlchemy ORM 中的一种方法，可以将查询结果以映射的形式返回，其中[ORM映射]的每个对象都可以通过属性名来访问。这提供了更高层次的抽象，允许您使用对象而不是原始的 SQL 结果。

2、方式二

from sqlalchemy.orm import sessionmaker,scoped_session
from sqlalchemy.sql import text
Session = sessionmaker(bind=engine)
session = scoped_session(Session) # 线程安全的session
# cursor=session.execute(text('select * from article'))  # 需要用text包一下
cursor=session.execute(text('select * from article where id = :value'),params={"value":218})  # 需要用text包一下 ,用 :变量名占位
print(cursor.mapping().all())
cursor.close()
session.close()

补充：.fetchall() 和 .mappings().all() 两种返回方式：

推荐使用后者, 返回一个列表套字典的形式

方法	数据结构	数据访问方式	可读性
.fetchall()	元组列表	列索引（如 row[0]）	低（依赖列顺序）
.mappings().all()	字典列表	列名（如 row['id']）	高（明确列名）

3、方式三

# res = session.query(User).from_statement(text("SELECT * FROM boy where name=:name")).params(name='lqz').all()

4、text 介绍

1. 什么是text

sqlalchemy.text()（简称 text()） 是 SQLAlchemy 中用于编写原生 SQL 语句的工具，它的作用不止是把 SQL 写成字符串，它带来了更高的安全性、兼容性与灵活性。下面是它的核心好处

✅ 1. 防止 SQL 注入（绑定参数机制）

为什么安全？

• text() 会把 :name 当作占位符。

• conn.execute() 会自动转义输入的值，防止注入攻击。

✅ 2. 语义清晰、结构分明

✅ 3. 支持复杂查询 + 保留 SQL 特性

 

✅ 4. 自动类型绑定 & 执行优化

✅ 5. 事务管理支持好

配合 engine.begin() 或 session.begin()，你可以安全地管理事务：

with engine.begin() as conn:
    conn.execute(text("UPDATE ..."), params)

✅ 6. 兼容 ORM 和原生 SQL 混合开发

即使你的项目用了 SQLAlchemy ORM（如 User.query.filter(...)），你也可以随时切到原生 SQL：

from sqlalchemy import text
result = db.session.execute(text("SELECT * FROM users WHERE id = :id"), {"id": 1})

2. 写法一：绑定参数 + text() + 占位符

sql = text('''
    UPDATE alarm_record 
    SET status = "resolved", update_time = CURRENT_TIMESTAMP 
    WHERE status = "firing" 
    AND alertname = :alertname 
    AND code = :code 
    AND level = :level
''')
params = {
    'alertname': alertname,
    'code': code,
    'level': level
}

✅ 优点：

• 安全性高：自动防止 SQL 注入（参数自动转义）

• 可读性好：结构清晰，参数与 SQL 逻辑分离

• 易维护：后续加条件只需改参数

• 适合结构化查询，尤其在用户输入参与查询时（如 Web 接口）

⚠️ 缺点：

• 动态构造复杂 WHERE 条件不太方便（需拼接 text() + params）

写法二：拼接字符串 + f-string + text()

sql = text(f'''
    SELECT * FROM {self._db_name}.mysql_slow_log 
    {where_clause}
    ORDER BY create_time DESC 
    LIMIT :size
''')

✅ 优点：

• 动态性强：更灵活，适合复杂条件（where_clause 可拼多个字段）

• 可以组合复杂表名、字段名（如数据库名、日志表等）

⚠️ 缺点：

• 存在 SQL 注入风险，如果 where_clause 来自用户输入，必须手动转义

• 可读性较差：SQL 语句不易一眼看清哪些是数据，哪些是逻辑

• 结构不规范：调试/审计时不如参数化清晰

三、 sqlalchemy ORM 增删改查

1、sqlalchemy 创建表结构

# 1 导入一些模块
import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import \
    declarative_base  # 新的变成它，老的会有  from sqlalchemy.ext.declarative import declarative_base  Warning
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index

# django orm，所有类，都要继承一个父类，sqlalchemy中这个父类就是Base，有点特殊，正常导入一个类，继承即可，但是它不是

# 2 得到所有表模型需要继承 基类
Base = declarative_base()

# 3 写表模型
class Users(Base):
    # 定义表名 数据库表名称，如果不写，默认以类名小写作为表名
    __tablename__ = 'users'
    # 定义字段
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)  # index=True 创建索引，不可为空
    email = Column(String(32), unique=True)
    # datetime.datetime.now不能加括号，加了括号，以后永远是当前时间
    ctime = Column(DateTime, default=datetime.datetime.now)
    extra = Column(Text, nullable=True)  # 可以为空

# 4 创建 engine,需要手动创建数据库
engine = create_engine(
    "mysql+pymysql://root:pasword@ip:3306/databas_name?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 5 通过 engine 把上面的表模型，创建到数据库中
Base.metadata.create_all(engine)

# 6 删除被Base管理的所有表
# Base.metadata.drop_all(engine)

补充：

数据库得手动创建好，sqlalchemy 修改表字段需要借助其他迁移工具，如Alembic

2、ORM 增 session.add session.add_all

1、session.add(user) 

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Users, Person, Hobby, Boy, Girl, Boy2Girl

# 1 生成 engine
engine = create_engine(
    "mysql+pymysql://root:bigdata@192.168.1.238:3306/users_test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

# 2 orm操作，拿到一个 session 对象
Session = sessionmaker(bind=engine)  # 得到Session类
session = Session()  # 类➕()得到对象

# 3 以后都通过session操作
# 3.1 创建一个User对象,新增到数据库中
user = Users(name='lll', email='305@qq.com')
session.add(user)
session.commit()
session.close()

2、session.add_all([user1, user2])

user1 = Users(name='test1', email='31@qq.com')
user2 = Users(name='test2', email='32@qq.com')
session.add_all([user1, user2])　　

3、ORM 删

1、批量删除.delete()

res=session.query(Users).filter_by(name='lqz').delete()  # 影响的行数
print(res)
session.commit()

2、session.delete(user) 删除单个对象

user=session.query(Users).filter_by(name='lqz').first()
# # user.delete()  # 单个对象，不能这么删：'Users' object has no attribute 'delete'
session.delete(user) #需要使用session删
session.commit()　　

4、ORM 改（更新）

1、方式一

session.query(Users).filter_by(name='lqz').update({'email':'333@qq.com'})

2、方式二 类名.属性名，作为要修改的key

session.query(Users).filter_by(name='test2').update({Users.name: 'lqz', Users.email: '55@qq.com'})

# id为4的人的名字后+  _nb   类似于django的 F 查询
session.query(Users).filter_by(id=3).update({'name': Users.name + '_nb'}, synchronize_session=False)
# session.query(Users).filter_by(id=4).update({'id':Users.id+6}, synchronize_session="evaluate")

3、方式三

user = session.query(Users).filter_by(id=4).first()
user.name = 'zhangsan'
session.add(user)

5、ORM 查

1、 查表全部数据

要查询 Users 表中的所有记录，可以使用 session.query() 结合 all() 方法：

all_users = session.query(Users).all()  # 获取所有用户
for user in all_users:
    print(user.name, user.email)  # 输出所有用户的姓名和邮箱

2、filter_by() 和 filter() 方法

1. 语法和用法

• filter_by():

  • 用法更简单，适合进行简单的条件查询。
  • 接受关键字参数，其字段名直接对应数据库模型的列名。
  • 适用于直接比较字段与常量的情况。

user = session.query(Users).filter_by(name='jing').first()  # 查找名字为 'jing' 的第一个用户　

• filter():
  • 更加灵活，可以用于复杂的条件查询。
  • 接受 SQLAlchemy 的表达式和条件，这意味着可以使用更复杂的比较逻辑（例如 >, <, LIKE, IN 等）。
  • 更加适合使用多个条件组合。

from sqlalchemy import and_, or_

# 查询名字为 'jing' 或者邮箱包含 '@example.com' 的用户
users = session.query(Users).filter(
    or_(Users.name == 'jing', Users.email.like('%@example.com%'))
).all()

查询条件可以使用text自己拼凑

# select * from users where id< 224 and name=lqz order by id
res = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='zhangsan').order_by(
    Users.id).all()

test中直接使用原生的sql

# SELECT * FROM users where name=zhangsan

res = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='zhangsan').all()

# 查看原生的sql
# res = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='zhangsan')
print(res)

2. 适用场景

• 使用 filter_by():

  • 对于简单查询或者直接的字段值比较，filter_by() 更加方便快捷。

• 使用 filter():

  • 当需要构建复杂查询逻辑（例如，包含多个运算符、联合条件等）时，使用 filter() 更加合适。

3、查询指定的列

select name as xx, email from user;

res = session.query(Users.name.label('xx'), Users.email).all()
res = session.query(Users.name, Users.email).all()　　

4、计数

如果想知道某个条件下有多少条记录，可以使用 count() 方法：

jing_count = session.query(Users).filter_by(name='jing').count()
print(f"Number of users named 'jing': {jing_count}")

5、排序

可以使用 order_by() 方法对查询结果进行排序, 这里name 为排序依据

sorted_users = session.query(Users).order_by(Users.name).all()
for user in sorted_users:
    print(user.name, user.email)

6、分页查询

如果记录很多，您可能需要进行分页操作，可以结合 limit() 和 offset() 方法：

# 获取前 10 条用户记录
first_ten_users = session.query(Users).limit(10).all()
for user in first_ten_users:
    print(user.name, user.email)

# 获取第 11 到 20 条用户记录
next_ten_users = session.query(Users).offset(10).limit(10).all()
for user in next_ten_users:
    print(user.name, user.email)

7、使用selectinload()、join() 进行联表查询

如果需要跨表查询，可以通过 join() 来完成，例如如果您的 Users 表与其他表（如 Hobby）有关联：

 # Or you can use join directly if you expect to return a flat structure
from sqlalchemy.orm import aliased

# 使用 join() 进行联表查询
results = session.query(Users, Hobbies).join(Hobbies).all()  # 默认内连接
for user, hobby in results:
    print(user.name, hobby.name)  # 输出用户及其相应的爱好

selectinload()

# 假设每个用户有若干爱好，示例联表查询
from sqlalchemy.orm import selectinload

users_with_hobbies = session.query(Users).options(selectinload(Users.hobbies)).all()
for user in users_with_hobbies:
    print(user.name, [hobby.name for hobby in user.hobbies])  # 输出用户及其所有爱好