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()

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.fetchall())
cursor.close()
session.close()

3、方式三

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

三、 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])  # 输出用户及其所有爱好