orm 框架 之 sqlalchemy

一. sqlalchemy 简介

1 下载

pip3 install sqlalchemy

更多: http://docs.sqlalchemy.org/en/latest/dialects/index.html

2 组成部分

Engine --->>> 框架的引擎

Connection Pooling --->>> 数据库连接池

Dialect --->>> 选择连接数据库的DB API种类：mysql，sqllite。。。

Schema/Types --->>> 架构和类型

SQL Exprression Language --->>> SQL表达式语言

3 能够操作的关系型数据库

1. pymysql
   # username： 数据库用户名， password： 数据库密码， host：地址（127.0.0.1：3306）， dbname： 数据库名称
   mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

2. MySQL-Connector
   mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

3. cx_Oracle
   oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

4 原生操作使用

1. 导入包
from threading import Thread
from sqlalchemy import create_engine

2. 实例化得到一个 engine 对象， create_engine 是一个类。
engine = create_engine(
    # 连接数据库
    'mysql+pymysql://root:455525@127.0.0.1:3306/cnblogs?charset=utf8',
    # 超过连接池大小外最多创建的连接
    max_overflow=0,
    # 连接池大小
    pool_size=5,
    # 池中没有，线程最多等待的时间，否则报错
    pool_timeout=30,
    # 多久之后对线程池中的线程进行一次连接的回收（重置）
    pool_recycle=-1
)

3. 通过engine拿到一个链接， 拿到一个conn对象，从连接池中取出一个链接
def task():
    conn = engine.raw_connection()
    cursor = conn.cursor()
    cursor.execute("select id from article")
    res = cursor.fetchall()
    print(res)

4. 多线程测试
for i in range(5):
    t = Thread(target=task)
    t.start()

二. 创建操作表 >>> models.py

1. 可以以脚本的形式运行 >>> 右键点击启动即可

2. 可以以命令的形式运行 >>> python models.py init_db

3. ★★不要忘记修改 create_all( ) 和 drop_all( )

4. 只能全部创建 或 全部删除， 不能操作几个字段 或 表

1 新版本

建立字段如下，连接数据库等操作可参考老版本。

from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    user: Mapped["User"] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

2 老版本

# 导入相应的依赖
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, Text, UniqueConstraint, Index, create_engine
from flask_script import Manager
from flask import Flask
import datetime

app = Flask(__name__)

# 创建成一个Base
Base = declarative_base()
# 利用自定义命令的形式执行文件
manage = Manager(app)


class User(Base):
    id = Column(Integer, primary_key=True)  # id 主键
    name = Column(String(32), index=True, nullable=False)
    email = Column(String(32), unique=True)  # 唯一
    ctime = Column(DateTime, default=datetime.datetime.now)
    extra = Column(Text, nullable=True)

    # 自定义数据库表名称
    __tablename__ = 'users'

    __table_args__ = (
        # id 和 name 联合唯一
        UniqueConstraint('id', 'name', name='uix_id_name'),
        # 联合索引
        Index('ix_id_name', 'name', 'email'),
    )


class Book(Base):
    __tablename__ = 'books'  # 数据库表名称
    id = Column(Integer, primary_key=True)  # id 主键
    name = Column(String(32), index=True, nullable=False)  # varchar32  name列，索引，不可为空
    price = Column(Integer)


class Publish(Base):
    __tablename__ = 'publish'  # 数据库表名称
    id = Column(Integer, primary_key=True)  # id 主键
    name = Column(String(32), nullable=True)


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

    # 创建出所有被 Base 管理的表
    Base.metadata.create_all(engine)

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


if __name__ == '__main__':
    manage.run()

三. 快速插入数据 >>> 没有做到线程安全

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import User, Book

# 第一步：创建engine
engine = create_engine("mysql+pymysql://root:455525@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5)

# 第二步：通过engine，获得session对象：跟之前学的cookie，session不是一个东西。此 session: 会话。
Session = sessionmaker(bind=engine)
# 每次执行数据库操作时，都需要创建一个Connection
session = Session()

# 第三步，通过session操作插入数据
# book=Book(name='不良人',price=33)
user = User(name='pyy', email='123@qq.com', extra='有品')
session.add(user)
session.commit()
session.close()

四. scoped_session >>> 做到了线程安全

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import User, Book

# 第一步：创建engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5)

# 第二步：通过engine，获得session对象：跟之前学的cookie，session不是一个东西
Session = sessionmaker(bind=engine)


# session是链接对象，如果集成到flask中，我们是吧session定义成全局，还是每个视图函数一个session呢？正常来讲要每个视图函数定义一个session，有些麻烦

# sqlalchemy 帮咱提供了一个只要定义一次的session，能够做到在不同线程中，使用的是自己的session，底层基于local

from sqlalchemy.orm import scoped_session
from threading import Thread

# session=Session() #没有线程安全

# 以后咱们使用这个它做到了线程安全
session = scoped_session(Session)
def task(i):
    user = User(name='pyy%s' % i, email='%s@qq.com' % i, extra='有品')
    session.add(user)
    session.commit()
    session.close()

for i in range(50):
    t = Thread(target=task, args=[i, ])
    t.start()

五. 增删改查

1 基本操作

1.1. 初始化

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Boy, Girl, Boy2Girl, Hobby, Book, User, Person
from sqlalchemy.orm import scoped_session
from sqlalchemy.sql import text

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

"""
   这里做增删改查的操作
"""

# 提交数据
session.commit()
# 并关闭此次连接。 相当于数据操作完后将连接放回连接池中， 不是真正的关闭。
session.close()

1.2. 增加数据

1. 增加单条数据 >>> add()

    user01 = User(name='lyf', email='11111@qq.com', extra='有品')
    session.add(user01)

2. 增加多条数据 >>> add_all()

    book01 = Book(name='斗破苍穹', price=66)
    book02 = Book(name='不良人', price=88)
    publish01 = Publish(name='上海出版社')
    session.add_all([book01, book02, publish01])

1.3. 删除数据

# 返回值是删除的个数
    res = session.query(Book).filter_by(price=11).delete()

1.4. 修改数据

1. 普通修改
    res = session.query(Book).filter(Book.id < 4).update({"name": "修仙"})

2. 字符串相加
    # update({Book.name: Book.name + "番外"}  == update({"name": Book.name + "番外"}
    res = session.query(Book).filter(Book.id > 0).update({Book.name: Book.name + "番外"}, synchronize_session=False)

3. 数字相加
    res = session.query(Book).filter(Book.price > 20).update({"price": Book.price - 1}, synchronize_session="evaluate")

1.5. 查询数据

查询之后是一个 list 对象， 可以直接对其遍历

基本操作

1. 查询所有
    res = session.query(User).all()

2. 查询某些字段 >>> label: 别名
    res = session.query(Book.name.label('xx'), Book.id)

3. filter传的是表达式，filter_by传的是参数
    res = session.query(User).filter(User.name == "lqz").all()
    res = session.query(User).filter(User.name != "lqz").all()
    res = session.query(User).filter(User.name != "lqz", User.email == '47@qq.com').all()
    res = session.query(User).filter_by(name='lqz099').all()
    res = session.query(User).filter_by(name='lqz099',email='47@qq.com').all()

4. 使用占位符(了解)  :value  :name
    res = session.query(User).filter(text("id<:value and name=:name")).params(value=10, name='lqz099').order_by(User.id).all()

5. 自定义查询（了解）
    res=session.query(User).from_statement(text("SELECT * FROM users where email=:email")).params(email='7@qq.com').all()

高级查询

'''高级查询'''

1. and条件连接
    res = session.query(User).filter(User.id > 1, User.name == 'lqz099').all()

2. between （两者之间）
    res = session.query(User).filter(User.id.between(1, 3), User.name == 'lqz099').all()

3. in
    res = session.query(User).filter(User.id.in_([1,3,4])).all()
    res = session.query(User).filter(User.email.in_(['y@qq.com','r@qq.com'])).all()

4. ~非 >>> 除...外
    res = session.query(User).filter(~User.id.in_([1,3,4])).all()

5. 二次筛选
    res = session.query(User).filter(~User.id.in_(session.query(User.id).filter_by(name='lqz099'))).all()

6. and or条件
    # 导入
    from sqlalchemy import and_, or_

    res = session.query(User).filter(and_(User.id >= 3, User.name == 'lqz099')).all()
    res = session.query(User).filter(or_(User.id < 2, User.name == 'eric')).all()
    res = session.query(User).filter(
        or_(
            User.id < 2,
            and_(User.name == 'lqz099', User.id > 3),
            User.extra != ""
        )).all()

7. 通配符，以e开头，不以e开头（模糊查询）
    res = session.query(User).filter(User.email.like('%@%')).all()
    res = session.query(User.id).filter(~User.name.like('e%'))

8.  分页  eg: 一页2条，查第5页
    res = session.query(User)[2*5:2*5+2]

9. 排序
    res = session.query(User).order_by(User.email.desc()).all()
    res = session.query(Book).order_by(Book.price.asc()).all()

    # 第一个条件重复后，再按第二个条件升序排
    res = session.query(User).order_by(User.name.desc(), User.id.asc()).all()

10. 分组查询
    from sqlalchemy.sql import func

    # 1. 按 price 排序
      res = session.query(Book).group_by(Book.price).all()

    # 2. 分组之后取最大 price， price 之和，最小 price
      res = session.query(
          func.max(Book.price),
          func.sum(Book.price),
          func.min(Book.price)).group_by(Book.price).all()
      print(res)
      for item in res:
          print(item)

    # 3. having
      res = session.query(
      func.max(Book.id),
      func.sum(Book.id),
      func.min(Book.id)).group_by(Book.price).having(func.max(Book.id) > 2).all()

联表操作

1. 联表操作
    select * from person,hobby where person.hobby_id=hobby.id;
    res = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id).all()

2. join表，默认是inner join,自动按外键关联
    select * from Person inner Hobby on Person.hobby_id=Hobby.id;
    res = session.query(Person).join(Hobby).all()

3. isouter=True 外连，表示Person left join Favor，没有右连接，反过来即可
    select * from Person left Hobby on Person.hobby_id=Hobby.id;
    res = session.query(Person).join(Hobby, isouter=True).all()

4. 自己指定on条件（连表条件）,第二个参数，支持on多个条件，用and_,同上
    res = session.query(Person).join(Hobby, Person.id == Hobby.id, isouter=True)

组合（了解）

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

'''union 和 union_all'''

    q1 = session.query(User).filter(User.id > 40)
    q2 = session.query(User).filter(User.id > 38)
    res = q1.union(q2).all()

    q1 = session.query(User.email).filter(User.id > 40)
    q2 = session.query(User.email).filter(User.id > 38)
    res = q1.union_all(q2).all()

基于链表跨表查

1. 一对多
    # 方式一：直接连
      res = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id,Hobby.id>=2).all()
    # 方式二：join连
      res = session.query(Person).join(Hobby).filter(Person.id>=2).all()

2. 多对多
    # 方式一：直接连
      res = session.query(Boy, Girl,Boy2Girl).filter(Boy.id == Boy2Girl.boy_id,Girl.id == Boy2Girl.girl_id).all()
    # 方式二：join连
      res = session.query(Boy).join(Boy2Girl).join(Girl).filter(Person.id>=2).all()

2 原生sql

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Boy, Girl, Boy2Girl, Hobby, Book, User, Person
from sqlalchemy.orm import scoped_session
from sqlalchemy.sql import text

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

# 查询
cursor = session.execute(text('select * from books'))
res = cursor.fetchall()

# 添加
cursor = session.execute(text('insert into users(name, email) values(:value1, :value2)'), params={"value1": 'lqz', "value2": "999@qq.com"})


session.commit()
session.close()

3 一对多， 多对多

3.1. 一对多

表模型

class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    hobby_id = Column(Integer, ForeignKey("hobby.id"))

    # 跟数据库无关，不会新增字段，只用于快速链表操作
    # 基于对象的跨表查询:就要加这个字段
    # backref用于反向查询
    hobby = relationship('Hobby', backref='pers')

    def __repr__(self):
        return self.name

基本操作

'''新增'''
   person=Person(name='张三',hobby=Hobby(caption='乒乓球'))
   session.add(person)

'''正向查询'''
   person = session.query(Person).filter_by(id=1).first()
   print(person.hobby_id)
   print(person.hobby.caption)


'''反向查询'''
    hobby = session.query(Hobby).filter_by(id=2).first()
    print(hobby.pers)

3.2. 多对多

表模型

class Boy2Girl(Base):
    __tablename__ = 'boy2girl'
    id = Column(Integer, primary_key=True, autoincrement=True)
    girl_id = Column(Integer, ForeignKey('girl.id'))
    boy_id = Column(Integer, ForeignKey('boy.id'))


class Girl(Base):
    __tablename__ = 'girl'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name


class Boy(Base):
    __tablename__ = 'boy'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)

    # 与生成表结构无关，仅用于查询方便,放在哪个单表中都可以
    # 方便快速查询，写了这个字段，相当于django 的manytomany，快速使用基于对象的跨表查询
    girls = relationship('Girl', secondary='boy2girl', backref='boys')

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name

基本操作

'''新增'''
   session.add(Boy(name='李清照', girls=[Girl(name='小红'), Girl(name='小黄')]))
    ||
    ||
   girl1 = Girl(name='小红1')
   girl2 = Girl(name='小黄1')
   boy = Boy(name='李清照1', girls=[girl2, girl1])
   session.add(boy)

'''正向查询'''
   boy = session.query(Boy).filter_by(id=2).first()
   print(boy.girls)

'''反向查询'''
   girl = session.query(Girl).filter_by(id=2).first()
   print(girl.boys)

六. flask-sqlalchemy 和 flask-migrate

1 flask-sqlalchemy

本质上是将上面的一些初始化操作封装到了一个类里面， 使操作更加便捷

1.1. 下载

pip install Flask-SQLAlchemy

1.2. 基本使用

1 导入
    from flask_sqlalchemy import SQLAlchemy

2 实例化得到对象
    db = SQLAlchemy()

3  将db注册到app中
    db.init_app(app)

4 视图函数中使用session  >>>  # 线程安全的
    全局的db.session

5 models.py 中   >>>   之前是继承Base
    现在继承 db.Base

6 写字段
    username = db.Column(db.String(80), unique=True, nullable=False)

2 flask-migrate

和 django 的数据库迁移一样

2.1. 下载

pip install flask-migrate

2.2. 基本使用

1 导入
    from flask_script import Manager
    from flask_migrate import Migrate, MigrateCommand

2 注册
    manager = Manager(app)
    # 使用flask_migrate的Migrate  包裹一下app和db（sqlalchemy对象）
    Migrate(app, db)

3 给flask_script增加一个db命令
    # 把命令增加到flask-script中去
    manager.add_command('db', MigrateCommand)

4 出现3条命令
    python manage.py db init     # 只执行一次，做初始化操作，以后再也不执行了，多出一个migrations文件夹
    python manage.py db migrate  # 等同于django 的makemigrations
    python manage.py db upgrade  # 等同于django 的migrate

3 小案例

完成下面操作后， 执行迁移命令即可

3.1. 目录结构

-flask_project     # 项目名称
    -app01         # app名称
        - static
        - views
        - __ init __.py
        - models.py
    -manage.py
    -settings.py

3.2. 代码

init.py

1. 做初始化操作

2. 这里一定要导入 models 模块

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# 第一步：类实例化得到对象
db = SQLAlchemy()

'''
   ** 这里一定要导入 models 模块， 不然 models模块 不会被执行。
'''
from .models import *

def create_app():
    app = Flask(__name__)

    app.config.from_object('settings.DevelopmentConfig')

    #第二步： 将db注册到app中
    db.init_app(app)

    '''
       注册蓝图
    '''

    return app

models.py

from . import db

# 第三步：把db导入，直接继承db.Model
class Users(db.Model):
    """
    用户表
    """
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username

manage.py

from app01 import create_app
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from app01 import db

app = create_app()

# 第一步：初始化出flask_script的manage
manager = Manager(app)

# 第二步：使用flask_migrate的Migrate  包裹一下app和db（sqlalchemy对象）
Migrate(app, db)

# 第三步：把命令增加到flask-script中去
manager.add_command('db', MigrateCommand)

if __name__ == '__main__':
    # app.run()
    manager.run()

setting.py

class BaseConfig(object):
    SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:455525@127.0.0.1:3306/abc?charset=utf8"
    SQLALCHEMY_POOL_SIZE = 5
    SQLALCHEMY_POOL_TIMEOUT = 30
    SQLALCHEMY_POOL_RECYCLE = -1
    # 追踪对象的修改并且发送信号
    SQLALCHEMY_TRACK_MODIFICATIONS = False