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

合集 - flask 框架(14)
1.flask 之创建和运行2.flask之配置文件3.flask 路由、钩子函数4.flask之蓝图和前后端变量传递5.python之任务调度（APScheduler和schedule）6.flask之cbv、模版7.flask之请求对象、响应对象、cookies和session8.falsk之闪现、wtforms表单9.django、flask自定义命令10.flask 之信号(事件)、信号量(计数器、锁)
11.flask之 sqlalchemy 原生操作和增删改查
12.flask之sqlalchemy ORM 一对多、多对多13.flask之 scoped实现线程安全.py14.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、官网

?
1
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 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
2
3
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 驱动程序是非常重要的。

1. `mysql+mysqldb` (MySQLdb)

- **驱动程序**: 使用 `MySQLdb` 作为数据库驱动。这是一个基于 C 的 Python 驱动程序。
- **安装方式**: 需要安装 `mysqlclient`，这是 `MySQLdb` 的一个分支并且是目前维护的。

?
1
pip install mysqlclient

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

2. `mysql+pymysql` (PyMySQL)

- **驱动程序**: 使用 `PyMySQL` 作为数据库驱动。`PyMySQL` 是一个纯 Python 实现的驱动。
- **安装方式**: 安装更为简单，因为它不需要编译过程。

?
1
pip install pymysql

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

2、方式二

?
1
2
3
4
5
6
7
8
9
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.fetchall())
cursor.close()
session.close()

3、方式三

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

三、 sqlalchemy ORM 增删改查

1、sqlalchemy 创建表结构

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 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) 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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])

?
1
2
3
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()

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

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

?
1
2
3
4
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、方式一

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

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

?
1
2
3
4
session.query(Users).filter_by(name='test2').update({Users.name: 'lqz', Users.email: '55@qq.com'})
<br># 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、方式三

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

5、ORM 查

1、 查表全部数据

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

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

2、filter_by() 和 filter() 方法

1. 语法和用法

  • filter_by():

    • 用法更简单，适合进行简单的条件查询。
    • 接受关键字参数，其字段名直接对应数据库模型的列名。
    • 适用于直接比较字段与常量的情况。
?
1
user = session.query(Users).filter_by(name='jing').first()  # 查找名字为 'jing' 的第一个用户　
  • filter():
    • 更加灵活，可以用于复杂的条件查询。
    • 接受 SQLAlchemy 的表达式和条件，这意味着可以使用更复杂的比较逻辑（例如 >, <, LIKE, IN 等）。
    • 更加适合使用多个条件组合。
?
1
2
3
4
5
6
from sqlalchemy import and_, or_
 
# 查询名字为 'jing' 或者邮箱包含 '@example.com' 的用户
users = session.query(Users).filter(
    or_(Users.name == 'jing', Users.email.like('%@example.com%'))
).all()

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

?
1
2
3
# 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

?
1
2
3
4
5
6
7
# 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;

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

4、计数

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

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

5、排序

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

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

6、分页查询

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

?
1
2
3
4
5
6
7
8
9
# 获取前 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）有关联：

?
1
2
3
4
5
6
7
# 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()

?
1
2
3
4
5
6
# 假设每个用户有若干爱好，示例联表查询
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])  # 输出用户及其所有爱好

　　

 

posted @   凡人半睁眼  阅读(80)  评论(0)  编辑  收藏  举报
相关博文：
· flask之 sqlalchemy 高级查询
· flask之sqlalchemy ORM 一对多、多对多
· sqlalchemy模块介绍、单表操作、一对多表操作、多对多表操作、flask集成.
· flask sqlalchemy介绍和使用 表操作增删查改 flask集成
· flask六
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· .NET10 - 预览版1新功能体验（一）

阅读目录(Content)

此页目录为空

点击右上角即可分享
微信分享提示
AI FOR CODE 大赛