SQLALchemy框架

SQLALchemy的介绍

SQLALchemy是一个基于Python实现的ORM框架，该框架建立在DB API之上，使用关系对象映射进行数据库操作，简而言之就是：将类和对象转换成SQL,然后使用数据API执行SQL并获取执行结果

pip install sqlalchemy

组成成分

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

能够操作的关系型数据库

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

pymysql
       	mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

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

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

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

django中如何反向生成models

python manage.py inspectdb > app/models.py

简单使用

sqlalchemy没有迁移一说，只能创建出被Base管理的所有表，和删除被Base管理的所有表

import time
import threading
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine

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


def task(arg):
    conn = engine.raw_connection()
    cursor = conn.cursor()
    cursor.execute(
        "select * from luffy_banner"
    )
    result = cursor.fetchall()
    print(result)
    cursor.close()
    conn.close()


for i in range(20):
    t = threading.Thread(target=task, args=(i,))
    t.start()

orm使用

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
Base = declarative_base()

class User(Base):
    # 定义表名
    __tablename__ = 'user'  # 数据库表名称
    id = Column(Integer, primary_key=True)  # id 主键
    # 所有的字段都是Column的对象，在里面通过参数控制类型
    name = Column(String(32), index=True, nullable=False)  # name列，索引，不可为空
    email = Column(String(32), unique=True)
    ctime = Column(DateTime, default=datetime.datetime.now)
    extra = Column(Text, nullable=True)

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

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

    Base.metadata.create_all(engine)

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

    Base.metadata.drop_all(engine)

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

app.py

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

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

# 每次执行数据库操作时，都需要创建一个Connection
con = Connection()

# 执行ORM操作
obj1 = User(name="张冉", email="zxr@qq.com")
con.add(obj1)
# 提交事务
con.commit()

# 关闭session，其实是将连接放回连接池
con.close()

sqlalchemy快速插入数据

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

# 第一步：创建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)
# 每次执行数据库操作时，都需要创建一个Connection
session = Session()

# 第三步，通过session操作插入数据
# book=Book(name='三只小孙淑',price=33)
user = User(name='刘清政', email='lqz@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)
# scoped_session类的对象，正常来讲是没有add,close,commit...方法和属性，但是实际上是有的，是通过create_proxy_methods装饰器，设置进去的（通过反射setattr写进去的）
def task(i):
    user = User(name='彭于晏%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()

一对多

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


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    hobby_id = Column(Integer, ForeignKey("hobby.id"))
    # 跟数据无关，不会新增字段，只用于快速链表操作
    # 类名，backref用于反向查询
    # hobby不会显示在表中
    hobby = relationship('Hobby', backref='pers')

查询练习

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Person, Hobby

# 第一步：创建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)
# 每次执行数据库操作时，都需要创建一个Connection
session = Session()

# 1 一对多增加
# 方式一：
# session.add(Hobby(caption='唱歌'))
# session.add(Person(name='杨紫',hobby_id=1))
# 方式二：
# session.add(Person(name='刘亦菲', hobby=Hobby(caption='拍戏')))

# 2 一对多查询
# 基于对象的跨表查询
# 正向查询
# res = session.query(Person).filter(Person.name=='刘亦菲').first()
# print(res)
# print(res.hobby.caption)
# 反向查询
# res = session.query(Hobby).filter_by(caption='唱歌').first()
# print(res.pers)  # [杨紫]
# print(res.pers[0])  # 杨紫

# 基于链表的跨表查询
# res = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id, Person.name == '刘亦菲').all()
# print(res)  # [(刘亦菲, 拍戏)]
res = session.query(Person).join(Hobby).filter(Person.name=='杨紫').all()
print(res)
session.commit()
session.close()

多对多

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)


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

查询练习

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

# 第一步：创建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)
# 每次执行数据库操作时，都需要创建一个Connection
session = Session()

# 1 多对多增加
# 方式一：所有表都有，一个个增加
# session.add(Boy(name='鹿晗'))
# session.add(Girl(name='关晓彤'))
# session.add_all([Boy(name='李易峰'), Girl(name='杨超越')])
# session.add_all([Boy2Girl(boy_id=1,girl_id=2),Boy2Girl(boy_id=1,girl_id=1)])
# 方式二：
# session.add(Boy(name='刘沁知', girls=[Girl(name='张晓蓉'), Girl(name='胡三汉')]))
# 2 一对多查询
# 基于对象的跨表查询
# 正向查询
# res = session.query(Boy).filter_by(name='王源').first()
# print(res.girls)
# 反向查询
# res = session.query(Girl).filter_by(name='胡三汉').first()
# print(res.boys)
session.commit()
session.close()

基本增删改查

基本增删改查和高级查询

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Boy, Girl, Boy2Girl, Hobby, User, Person, Book
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)

# 1 基本增
# add add_all
# Hobby表增加记录
# hobby = Hobby(caption='运动')
# user = User(name='鞠疯味', email='jfw@qq.com', extra='温柔')
# session.add(hobby)
# session.add_all([hobby, user])

# 2 删除
# hobby = session.query(Hobby).filter_by(caption='运动').delete()
# print(hobby)

# 3 修改更新
# res = session.query(User).filter(User.id > 1).update({"name": "天兵小将"})
# res = session.query(User).filter(User.id > 0).update({User.name: User.name + '特级'}, synchronize_session=False)
'''
synchronize_session参数
    False 不对session进行同步，直接进行delete or update操作。
    fetch 在delete or update操作之前，先发一条sql到数据库获取符合条件的记录。
    evaluate 在delete or update操作之前，用query中的条件直接对session的identity_map中的objects进行eval操作，将符合条件的记录下来。
'''
# res = session.query(Book).filter(Book.price > 20).update({"price": Book.price + 1}, synchronize_session="evaluate")

# 4 查询  filer:写条件     filter_by：等于的值，filter传的是表达式，filter_by传的是参数
# 查询所有 是list对象
# res = session.query(User).all()
# print(res)

# 查询某几个字段
# res = session.query(User.name.label('XX'), User.email)
# print(res)  # SELECT user.name AS `XX`, user.email AS user_email FROM user
# res = session.query(User).filter(User.name == "张冉").all()
# print(res)
# res = session.query(User).filter_by(name='张冉').all()
# print(res)

# 取一个 all了后是list，list 没有first方法
# res = session.query(User).first()
# print(res)  # 张冉

# 查询所有，使用占位符(了解)  :value     :name
# res = session.query(User).filter(text("id<:value and name=:name")).params(value=3, name='天兵小将特级').order_by(User.id).all()
# print(res)  # [天兵小将特级]

# 自定义查询
# res = session.query(User).from_statement(text("SELECT * FROM user where email=:email")).params(email='zxr@qq.com').all()
# print(res)

# 高级查询
# 表达式：and条件连接
# res = session.query(User).filter(User.id > 1, User.name == '天兵小将特级').all()
# print(res)  # [天兵小将特级, 天兵小将特级, 天兵小将特级, 天兵小将特级]

# between
# res = session.query(User).filter(User.id.between(1, 3), User.name == '天兵小将特级').all()
# print(res)  # [天兵小将特级, 天兵小将特级]

# in
# res = session.query(User).filter(User.id.in_([1, 3, 4])).all()
# print(res)  # [张冉, 天兵小将特级, 天兵小将特级]

# ~非，除外
# res = session.query(User).filter(~User.id.in_([1, 3, 4])).all()
# print(res)  # [天兵小将特级, 天兵小将特级]

# 二次筛选
# res = session.query(User).filter(~User.id.in_(session.query(User.id).filter_by(name='张冉'))).all()
# print(res)  # [天兵小将特级, 天兵小将特级, 天兵小将特级, 天兵小将特级]

# and or 条件
from sqlalchemy import and_, or_
# res = session.query(User).filter(and_(User.id >= 3, User.name == '天兵小将特级')).all()
# print(res)  # [天兵小将特级, 天兵小将特级, 天兵小将特级]

# res = session.query(User).filter(or_(User.id >= 2, User.name == '张x冉')).all()
# print(res)  # [天兵小将特级, 天兵小将特级, 天兵小将特级, 天兵小将特级]
# 通配符，以e开头，不以e开头
# res = session.query(User).filter(User.email.like('%@%')).all()
# print(res)  # [张冉, 天兵小将特级, 天兵小将特级, 天兵小将特级, 天兵小将特级]

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

# 分组查询
from sqlalchemy.sql import func

# res = session.query(User).group_by(User.extra).all()
# print(res) # [天兵1小将, 张冉, 天兵4小将]

# 分组之后取最大值ID，ID之和，最小id
# res = session.query(func.max(User.id), func.min(User.id), func.sum(User.id)).group_by(User.extra).all()
# print(res)  # [(7, 2, Decimal('13')), (3, 1, Decimal('4')), (6, 6, Decimal('6'))]

# having
# res = session.query(func.max(User.id), func.min(User.id), func.sum(User.id)).group_by(User.extra).having(func.max(User.id) > 2).all()
# print(res)

# 链表操作
# res = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id).all()
# print(res)  # [(杨紫, 唱歌), (刘亦菲, 拍戏)]

# # join表，默认是inner join,自动按外键关联
# res = session.query(Person).join(Hobby).all()
# print(res)  # [刘亦菲, 杨紫]

# isouter=True 外连，表示Person left join Favor，没有右连接，反过来即可
# res = session.query(Person).join(Hobby, isouter=True).all()
# print(res)  # [刘亦菲, 杨紫]

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

# 右链接
# res = session.query(Hobby).join(Person, isouter=True)
# print(res)

# 组合（了解）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()

# 一对多，基于链表跨表查（__链表）
#方式一：直接连
# 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()

# 多对多关系，基于链表的跨表查
# 方式一：直接连
# 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()


session.commit()
session.close()

flask-sqlalchemy使用和和flask-migrate使用

# flask中使用sqlalchemy，直接使用

#  使用flask-sqlalchemy集成
	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)

flask-migrate

# python manage.py makemigrations  # 记录变化
# python manage.py migrate         #把变化同步到数据库

# 使用步骤：
	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