SQL-Alchemy 的使用

SQL-Alchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果.
参考文献：https://www.cnblogs.com/wupeiqi/articles/8259356.html

1.安装介绍

# 安装
pip install sqlalchemy
# 操作mysql 的时候依赖pymysql
pip install pymysql

组成部分：

• Engine:框架的引擎。
• Connection Pooling:数据库连接池。
• Dialect:选择连接数据库的DB API 种类。
• Schema/Types:架构和类型。
• SQL Exprression Language:SQL表达式语言。

SQLAlchemy 本身无法操作数据库，其必须以pymysql等第三方插件，Dialect用于和数据API进行交流, 根据配置文件的不同调用不同的数据库 API,从而实现对数据库的操作，

pymysql
   mysql+pymysql://<username>:<``password``>@<host>/<dbname>[?<options>]
 
 
cx_Oracle
	oracle+cx_oracle://``user``:pass@host:port/dbname[key``=value&``key``=value.]

2.简单使用

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine 使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

2.1 创建引擎

from sqlalchemy import create_engine

# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=30,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收
)

create_engine()函数创建引擎的更多参数，可以在Pycharm中点击查看源码，观看注释查看更多参数的用法。

2.2 执行原生sql

获取连接对象执行 sql 语句。

from sqlalchemy import create_engine

# 创建引擎
engine = create_engine(
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=30,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收（重置）
)

def task():
    conn = engine.raw_connection()# 返回数据库连接对象，后续操作与pymysql操作相同
    cur = conn.cursor()
    cur.execute("select * from t1")
    res = cur.fetchall() # 元组嵌套元组
    print(res)
    cur.close()
    conn.close()
task() 

直接使用引擎执行 SQL 语句。

from sqlalchemy import create_engine

# 创建引擎
engine = create_engine(
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=30,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收
)

def task():
    cur = engine.execute("select * from t1") # 直接使用引擎执行SQL语句。
    res = cur.fetchall()
    print(res)# 返回的是列表嵌套元组
    cur.close()
task()

3.ORM 使用

ORM:是对象关系映射数据库模型。将 Python 的类映射到数据库中进行操作。

3.1 创建表

3.1.1 创建单表

创建单个表，或者无关联的多表

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

# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=30,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收
)

Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。

class User(Base):# 继承类

    __tablename__ = 'users' # 表名

    id = Column(Integer,primary_key=True) # 主键
    name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
    email = Column(String(32),unique=True) # 限制唯一约束

class Books(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)  # 主键
    book = Column(String(32),nullable=False) # 创建
    author = Column(String(32),nullable=False) 

    __table_args__ = (
        # 相当于 Django ORM 中的class Meta。
        UniqueConstraint('book', 'author', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'email'),
    )


def init_db():
    Base.metadata.create_all(engine) # 创建所有表
init_db()

3.1.2 删除所有表

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

# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=30,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收
)

Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。

class User(Base):

    __tablename__ = 'users' # 表名

    id = Column(Integer,primary_key=True) # 主键
    name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
    email = Column(String(32),unique=True) # 限制唯一约束

class Books(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)  # 主键
    book = Column(String(32),nullable=False)
    author = Column(String(32),nullable=False)

    __table_args__ = (
        UniqueConstraint('book', 'author', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'email'),
    )


def init_db():
    Base.metadata.create_all(engine)

def drop_db(): # 删除表的所有函数。执行
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    drop_db()

SQLAlchemy 不使用 flask-script 作为 flask 的组件的时候，无法检测到相关的单表的变化。

补充：删除所有表的时候，有时存在外键关联的时候可能会导致删除所有的失败，需要首先注释对应的关联的第三张表，或者在 mysql 中手动删除第三张关联表。

3.1.3 创建多表

创建多表之间的关联，根据表之间的关联进行表的创建。

• 一对多 or 一对一

  from sqlalchemy import create_engine
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy import Column,Integer,String,UniqueConstraint,ForeignKey
  from sqlalchemy.orm import relationship
  # 创建引擎
  engine = create_engine(
      "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
      # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
      max_overflow=0,  # 超过连接池大小外最多创建的连接
      pool_size=5,  # 连接池中数量的大小
      pool_timeout=30,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
      pool_recycle= -1# 默认值是 -1,不回收,多久之后对线程池中的线程进行一次连接的回收
  )
  
  Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。
  
  class User(Base):
  
      __tablename__ = 'users' # 表名
  
      id = Column(Integer,primary_key=True) # 主键
      name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
      email = Column(String(32),unique=True) # 限制唯一约束
  
  class Student(Base):
  
      __tablename__ = 'student'
  
  
      nid = Column(Integer,primary_key=True)
      id = Column(Integer,ForeignKey("users.id"))
      # 使用外键的时候，ForignKey()中的参数是表名(__tablename__).列名，不是类名
      stu_no = Column(String(32),nullable=False)
  
      # 与生成的表结构无关,仅用于方便查询,第一个参数一般是类名。
      hobby = relationship("User", backref='stu')
  
  def init_db():
      Base.metadata.create_all(engine)
  
  def drop_db():
      Base.metadata.drop_all(engine)
  
  if __name__ == '__main__':
      # drop_db()
      init_db()
  

  

  relationship 用于数据的跨表查询，类似django中的跨表时使用的.和双下划线__.

• 多对多

  from sqlalchemy import create_engine
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy import Column,Integer,String,UniqueConstraint,ForeignKey
  from sqlalchemy.orm import relationship
  # 创建引擎
  engine = create_engine(
      "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
      # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
      max_overflow=0,  # 超过连接池大小外最多创建的连接
      pool_size=5,  # 连接池中数量的大小
      pool_timeout=30,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
      pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收（重置）
  )
  
  Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。
  
  class Group(Base):
      __tablename__ = 'group'
  
      id = Column(Integer,primary_key=True)
      name = Column(String(64),unique=True,nullable=False)
  
      servers = relationship('Server',secondary='servergroup',backref='groups')
  
  class Server(Base):
      __tablename__ = 'server'
  
      id = Column(Integer,primary_key=True,autoincrement=True)# 设置主键自增
      hostname = Column(String(64),unique=True,nullable=False)# 非空约束,唯一约束
  
  class ServerGroup(Base):
      __tablename__ = 'servergroup'
      id = Column(Integer,primary_key=True,autoincrement=True)
      # 创建两个外键约束，
      server_id = Column(Integer,ForeignKey("server.id"))
      group_id = Column(Integer,ForeignKey("group.id"))
  
  def init_db():
      Base.metadata.create_all(engine)
  
  def drop_db():
      Base.metadata.drop_all(engine)
  
  if __name__ == '__main__':
      drop_db() # 删除之前的表啊
      init_db() # 创建现有的模型表
  

  

  多对多之间的约束，在 SQLAlchemy 中多对多的第三张表只能自己手动建立，不能像在Django中的 ORM 一样程序中自动创建第三张表，只能手动创建。

3.2 增删改查

3.2.1 增加数据

单表插入，创建session使用，创建 orm 对象，使用session提交事务，关闭session.

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,UniqueConstraint,ForeignKey
from sqlalchemy.orm import relationship,sessionmaker
# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=30,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收
)

Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。

class User(Base):

    __tablename__ = 'users' # 表名

    id = Column(Integer,primary_key=True) # 主键
    name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
    email = Column(String(32),unique=True) # 限制唯一约束

class Books(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)  # 主键
    book = Column(String(32),nullable=False)
    author = Column(String(32),nullable=False)

    __table_args__ = (
        UniqueConstraint('book', 'author', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'email'),
    )
    
# 创建所有表
def init_db():
    Base.metadata.create_all(engine)

# 删除所有表
def drop_db():
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    # drop_db()
    # init_db()

    Session = sessionmaker(bind=engine)
    # 每次执行数据库操作的时候都需要创建一个session
    session = Session()
    # 执行 orm 操作
    obj_user = User(name='123',email='123@qq.com')
    session.add(obj_user)
    # 提交事务
    session.commit()
    # 关闭session
    session.close()

增加多条数据

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,UniqueConstraint,ForeignKey
from sqlalchemy.orm import relationship,sessionmaker,scoped_session

# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=10,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收
)

Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。

class User(Base):

    __tablename__ = 'users' # 表名

    id = Column(Integer,primary_key=True) # 主键
    name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
    # email = Column(String(32),unique=True) # 限制唯一约束
    email = Column(String(32)) # 限制唯一约束

Session = sessionmaker(bind=engine)
session = scoped_session(Session) # 创建线程安全的session 对象

def orm_func():
    # 添加一条
    obj = User(name='1234', email='123@qq.com')
    session.add(obj)
    # 添加多条,参数是列表嵌套,表的对象。
    session.add_all([
        User(name='123456', email='123@qq.com'),
        User(name='123456', email='123@qq.com'),
        User(name='123456', email='123@qq.com'),
    ])
    # 提交事务
    session.commit()

if __name__ == '__main__':
    orm_func()

3.2.2 查询数据

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,UniqueConstraint,ForeignKey
from sqlalchemy.orm import relationship,sessionmaker,scoped_session

# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=10,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回
)

Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。

class User(Base):

    __tablename__ = 'users' # 表名

    id = Column(Integer,primary_key=True) # 主键
    name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
    # email = Column(String(32),unique=True) # 限制唯一约束
    email = Column(String(32)) # 限制唯一约束

Session = sessionmaker(bind=engine)
session = scoped_session(Session) # 创建线程安全的session 对象

def query_task():
    rsp1 = session.query(User).all() # 输入表的类，查询所有
    # label 是相当与 SQL 语句中的 AS 列名。
    rsp2 = session.query(User.name.label('asname'),User.email).all()
    # 条件筛选
    rsp3 = session.query(User).filter(User.name == '1234').all()
    # 不需要出入表名的类名
    rsp4 = session.query(User).filter_by(name='123').all()
    # 获取第一条数据。
    rsp5 = session.query(User).filter_by(name='123').first()
    print("rsp1",rsp1,'\n',[i.name for i in rsp1])
    print("rsp2",rsp2,'\n',[i.asname for i in rsp2])
    print("rsp3",rsp3,'\n',[i.name for i in rsp3])
    print("rsp4",rsp4,'\n',[i.name for i in rsp4])
    print("rsp5",rsp5,rsp5.name)

条件查询，升序排序。

from sqlalchemy.sql import text
rsp6 = session.query(User).filter(text("id<:value and name=:name")).params(value=5,name='123').order_by(User.id).all()
print("rsp6",rsp6,'\n',[i.id for i in rsp6])

降序排序。

from sqlalchemy.sql import text
rsp6 = session.query(User).filter(text("id<:value and name=:name")).params(value=5,name='123').order_by(-User.id).all()
print("rsp6",rsp6,'\n',[i.id for i in rsp6])

执行原生 SQL 语句

from sqlalchemy.sql import text
r7 = session.query(User).from_statement(text("select * from users where id=:id")).params(id='1').all()
print("r7",r7,'\n',[i.id for i in r7])

3.2.3 删除数据

# ******* 删除数据 *********
session.query(User).filter(User.id > 5).delete() # 删除id> 5的数据
session.commit() #提交事务
session.close()

删除数据的时候filter()内部的参数是筛选的条件,之后就是使用delete()进行删除，删除后进行提交事务，最后关闭session.close().

3.2.4 修改数据

# 修改数据
session.query(User).filter(User.id>0).update({"name":"678"})
session.query(User).filter(User.id>0).update({User.name:User.name+"099"},synchronize_session=False)
session.query(User).filter(User.id>0).update({"email":User.email+'666'})
session.commit()
session.close()

3.3 常用功能

3.3.1 条件筛选补充

# filter_by 直接用属性名,filter()类名.属性名
ret = session.query(User).filter_by(name='678099').all()
print(ret)

参考文献：https://www.jianshu.com/p/f40a4987891b

ret = session.query(User).filter(User.id>2,User.name=='678099').all()
print(ret,'\n',[i.id for i in ret])

# 使用between,确定范围。
ret = session.query(User).filter(User.id.between(1,3),User.name == '678099').all()
print([i.id for i in ret])
>>> [1,2,3]

使用in关键字，和not in这两个关键字

# in 关键字
ret = session.query(User).filter(User.id.in_([1, 3, 4])).all()
print([i.id for i in ret])
# not in 的使用
ret = session.query(User).filter(~User.id.in_([1, 3, 4])).all()
print([i.id for i in ret])

使用查询结果当做临时表.

ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='678099'))).all()
print([i.id for i in ret])
>>> [1, 2, 3, 4, 5]

分组，排序，跨表查询；

from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all()


# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()

3.3.2 relationship的使用

外键操作。

import time
import threading

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.engine.result import ResultProxy
from db import Users, Hosts, Hobby, Person

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 添加
"""
session.add_all([
    Hobby(caption='乒乓球'),
    Hobby(caption='羽毛球'),
    Person(name='张三', hobby_id=3),
    Person(name='李四', hobby_id=4),
])

person = Person(name='张九', hobby=Hobby(caption='姑娘'))
session.add(person)

hb = Hobby(caption='人妖')
hb.pers = [Person(name='文飞'), Person(name='博雅')]
session.add(hb)

session.commit()
"""

# 使用relationship正向查询
"""
v = session.query(Person).first()
print(v.name)
print(v.hobby.caption)
"""

# 使用relationship反向查询
"""
v = session.query(Hobby).first()
print(v.caption)
print(v.pers)
"""

session.close()

操作多对多的表

import time
import threading

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.engine.result import ResultProxy
from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 添加
"""
session.add_all([
    Server(hostname='c1.com'),
    Server(hostname='c2.com'),
    Group(name='A组'),
    Group(name='B组'),
])
session.commit()

s2g = Server2Group(server_id=1, group_id=1)
session.add(s2g)
session.commit()


gp = Group(name='C组')
gp.servers = [Server(hostname='c3.com'),Server(hostname='c4.com')]
session.add(gp)
session.commit()


ser = Server(hostname='c6.com')
ser.groups = [Group(name='F组'),Group(name='G组')]
session.add(ser)
session.commit()
"""


# 使用relationship正向查询
"""
v = session.query(Group).first()
print(v.name)
print(v.servers)
"""

# 使用relationship反向查询
"""
v = session.query(Server).first()
print(v.hostname)
print(v.groups)
"""


session.close()

3.4 线程安全

基于scoped_session实现线程安全；

3.4.1 问题引出

import threading

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

# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=10,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收
)

Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。

class User(Base):

    __tablename__ = 'users' # 表名

    id = Column(Integer,primary_key=True) # 主键
    name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
    # email = Column(String(32),unique=True) # 限制唯一约束
    email = Column(String(32)) # 限制唯一约束

class Books(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)  # 主键
    book = Column(String(32),nullable=False)
    author = Column(String(32),nullable=False)

    __table_args__ = (
        UniqueConstraint('book', 'author', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'email'),
    )

def init_db():
    Base.metadata.create_all(engine)

def drop_db():
    Base.metadata.drop_all(engine)

Session = sessionmaker(bind=engine)
def insert_task():
	# 不出错,但是每个线程函数中都需要手动创建一个session
    
    
    # 每次执行数据库操作的时候都需要创建一个session
    session = Session() # 每一个session 中都创建一个session
    # 执行 orm 操作
    obj_user = User(name='123', email='123@qq.com')
    session.add(obj_user)
    # 提交事务
    session.commit()
    # 关闭session
    session.close()

if __name__ == '__main__':
    drop_db()
    init_db()
    for i in range(10):
        t = threading.Thread(target=insert_task)
        t.start()

引出：当把session创建在线程函数之外，代码和结果如下。

import threading

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

# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=10,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收（重置）
)

Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。



class User(Base):

    __tablename__ = 'users' # 表名

    id = Column(Integer,primary_key=True) # 主键
    name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
    # email = Column(String(32),unique=True) # 限制唯一约束
    email = Column(String(32)) # 限制唯一约束

class Books(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)  # 主键
    book = Column(String(32),nullable=False)
    author = Column(String(32),nullable=False)

    __table_args__ = (
        UniqueConstraint('book', 'author', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'email'),
    )

def init_db():
    Base.metadata.create_all(engine)

def drop_db():
    Base.metadata.drop_all(engine)

Session = sessionmaker(bind=engine)
session = Session()
def insert_task():

    # 每次执行数据库操作的时候都需要创建一个session
    # session = Session() # 每一个session 中都创建一个session
    # 执行 orm 操作
    obj_user = User(name='123', email='123@qq.com')
    session.add(obj_user)
    # 提交事务
    session.commit()
    # 关闭session
    session.close()

if __name__ == '__main__':
    drop_db()
    init_db()
    for i in range(10):
        t = threading.Thread(target=insert_task)
        t.start()

报错，因为当异步线程请求session的时候可能在关闭的状态。解决方法可以直接按照最开始的状态在每一个线程中设置自己的session进行处理，也可以按照下方的方法进行处理。

3.4.2 线程安全

from sqlalchemy.orm import scpoed_session

Session = sessionmaker(bind=engine)
session = scoped_session(Session) # 创建线程安全的session 对象
# 接下来的操作与普通session完全相同。

示例

import threading

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,UniqueConstraint,ForeignKey
from sqlalchemy.orm import relationship,sessionmaker,scoped_session

# 创建引擎
engine = create_engine(
    "mysql+pymysql://root:1234567@127.0.0.1:3306/testflask?charset=utf8",
    # "mysql+pymysql://root:1234567@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池中数量的大小
    pool_timeout=10,  # 连接池中没有连接的时候的最长的等待秒数，超时则报错
    pool_recycle= -1  # 默认值是 -1，不回收,多久之后对线程池中的线程进行一次连接的回收（重置）
)

Base = declarative_base() # 实例化映射关系的类对象,后期的数据库表类直接继承该类。



class User(Base):

    __tablename__ = 'users' # 表名

    id = Column(Integer,primary_key=True) # 主键
    name = Column(String(32),index=True,nullable=False) # 普通约束,非空约束。
    # email = Column(String(32),unique=True) # 限制唯一约束
    email = Column(String(32)) # 限制唯一约束

class Books(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)  # 主键
    book = Column(String(32),nullable=False)
    author = Column(String(32),nullable=False)

    __table_args__ = (
        UniqueConstraint('book', 'author', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'email'),
    )

def init_db():
    Base.metadata.create_all(engine)

def drop_db():
    Base.metadata.drop_all(engine)

Session = sessionmaker(bind=engine)
session = scoped_session(Session) # 创建线程安全的session 对象
def insert_task():

    # 每次执行数据库操作的时候都需要创建一个session
    # session = Session() # 每一个session 中都创建一个session
    # 执行 orm 操作
    obj_user = User(name='123', email='123@qq.com')
    session.add(obj_user)
    # 提交事务
    session.commit()
    # 关闭session
    session.close()

if __name__ == '__main__':
    drop_db()
    init_db()
    for i in range(10):
        t = threading.Thread(target=insert_task)
        t.start()

插入到对应的数据表中。

3.4.3 原理剖析

为每一个线程创建一个session之间的数据却有不相互影响，应该就是therading.local()函数。

from sqlalchemy.orm import scpoed_session

Session = sessionmaker(bind=engine)
session = scoped_session(Session) # 创建线程安全的session 对象
# 接下来的操作与普通session完全相同。

点击查看源码。

# 行线程安全的类
class scoped_session(ScopedSessionMixin):
	"""Construct a new :class:`.scoped_session`.

        :param session_factory: a factory to create new :class:`.Session`
         instances. This is usually, but not necessarily, an instance
         of :class:`.sessionmaker`.
        :param scopefunc: optional function which defines
         the current scope.   If not passed, the :class:`.scoped_session`
         object assumes "thread-local" scope, and will use
         a Python ``threading.local()`` in order to maintain the current
         :class:`.Session`.  If passed, the function should return
         a hashable token; this token will be used as the key in a
         dictionary in order to store and retrieve the current
         :class:`.Session`.

        """
    _support_async = False

    session_factory = None
	
    # 实例化对象,就执行改方法，`session_factory`猜想为session的工厂,工厂模式，产生session
    def __init__(self, session_factory, scopefunc=None):
		# 参数的介绍参考解析，大致意思是没有自定义的线程范围函数，则通过			
        # theading.local()函数设置键值区分 session 
        self.session_factory = session_factory # 传入创建Session的对象

        if scopefunc:
            # 第二个参数有传入值且不为空的时候执行
            self.registry = ScopedRegistry(session_factory, scopefunc)
        else:
            # 默认情况，执行后面的类。
            self.registry = ThreadLocalRegistry(session_factory)

    def remove(self):
		# 
        if self.registry.has():
            self.registry().close()
        self.registry.clear()

    def query_property(self, query_cls=None):

        class query(object):
            def __get__(s, instance, owner):
                try:
                    mapper = class_mapper(owner)
                    if mapper:
                        if query_cls:
                            # custom query class
                            return query_cls(mapper, session=self.registry())
                        else:
                            # session's configured query class
                            return self.registry().query(mapper)
                except orm_exc.UnmappedClassError:
                    return None

        return query()

ThreadLocalRegistry()类，

class ThreadLocalRegistry(ScopedRegistry):
    """A :class:`.ScopedRegistry` that uses a ``threading.local()``
    variable for storage.

    """
	# 执行`__init__()`方法
    def __init__(self, createfunc):
        self.createfunc = createfunc # 传入的是创建`session`的函数。
        self.registry = threading.local() # 实例化 threading.local()对象。

    def __call__(self):
        try:
            # 不是第一次，直接返回session值
            return self.registry.value
        except AttributeError:
            # 第一次创建session()
            # self.createfunc 就是 传入的 Session,加上括号代表实例化，并写入
            # threading.local()中
            val = self.registry.value = self.createfunc()
            return val # 返回创建好的值

    def has(self):
        # 通过反射查看值是否存在
        return hasattr(self.registry, "value")

    def set(self, obj):
        # 设置相关的值
        self.registry.value = obj

    def clear(self):
        try:
            # 清空值
            del self.registry.value
        except AttributeError:
            pass

继续努力，终成大器。