SQLAlchemy和Flask-SQLAlchemy

一、ORM 与 SQLAlchemy 简介

ORM 全称 Object Relational Mapping, 翻译过来叫对象关系映射。简单的说，ORM 将数据库中的表与面向对象语言中的类建立了一种对应关系。这样，我们要操作数据库，数据库中的表或者表中的一条记录就可以直接通过操作类或者类实例来完成。

SQLAlchemy 是Python 社区最知名的 ORM 工具之一，为高效和高性能的数据库访问设计，实现了完整的企业级持久模型。

二、SQLAlchemy的使用

1、创建单表结构：

from sqlalchemy.ext.declarative import declarative_base  # 导入基类
from sqlalchemy import Column, Integer, String  # 数据类型

# Base = ORM基类 - 要按照ORM的规则定义你的类
Base = declarative_base()

class Users(Base):
    __tablename__ = "user"
    # 创建ID数据字段 , 那么ID是不是一个数据列呢? 也就是说创建ID字段 == 创建ID数据列
    # id = Column(数据类型,索引,主键,外键,等等)
    id = Column(Integer, primary_key=True, autoincrement=True,index=True)
    name = Column(String(32), nullable=False)  # nullable=False 不能为空


# 打开数据库的连接 -- 创建数据库引擎
from sqlalchemy import create_engine
# 创建数据库引擎
# engine = create_engine("mysql://scott:tiger@hostname/dbname",encoding='latin1', echo=True)
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/SQLAlchemy_Pro?charset=utf8")

Base.metadata.create_all(engine)  # Base自动检索所有继承Base的ORM 对象 并且创建所有的数据表

2.单表的增删改查：

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from creatTable import Users

# 创建引擎
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/SQLAlchemy_Pro?charset=utf8")

Session = sessionmaker(engine)
db_session = Session()

# 1. 增加数据add(创建表结构的类名(字段名=添加的数据))
db_session.add(Users(name="ZWQ"))  # 相当于建立一条添加数据的sql语句
db_session.commit()  # 执行
db_session.close()   # 结束关闭

# 批量添加
db_session.add_all([Users(name="清风徐来"), Users(name="水波不兴")])
db_session.commit()
db_session.close()

# 2.查询 query(表结构的类名)
sqlres = db_session.query(Users)
print(sqlres)  # 直接翻译输出对应的SQL查询语句

res = db_session.query(Users).all()  # 返回表中所有数据对象
print(res)# [<creatTable.Users object at 0x00000000038A1B00>,<creatTable.Users object at 0x00000000038A1B70>]

for u in res:
    print(u.id, u.name)

res = db_session.query(Users).first()  # 取第一个，返回是对象
print(res.id, res.name)

res = db_session.query(Users).filter(Users.id == 3).first()  # 返回符合条件查询结果
print(res.name)

res = db_session.query(Users).filter(Users.id <= 2, Users.name == "ZWQ").all() # filter中的条件可以是模糊条件,多个条件
for u in res:
    print(u.id,u.name)

# 3.更改数据 update({k:v})
res = db_session.query(Users).filter(Users.id == 1).update({"name":"DragonFire"})
print(res)
db_session.commit()

res = db_session.query(Users).update({"name":"ZWQ"})  # 全部修改，返回修改的数据个数
print(res)
db_session.commit()

# 4.删除 delete()结合查询条件删除
res = db_session.query(Users).filter(Users.id == 1).delete()  # 删除否合条件的数据，返回删除数量
print(res)
db_session.commit()

res = db_session.query(Users).delete()  # 删除表中所有数据，返回删除数量
print(res)
db_session.commit()

3.创建外键关联的表结构：

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship

class Student(Base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    sch_id = Column(Integer,ForeignKey("school.id"))  # 关联的表的字段,表间的关系

    stu2sch = relationship("School",backref="sch2stu")  # 写在哪边那边就是正向查询，对象间的关系，backref(反向查询)


class School(Base):
    __tablename__ = "school"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)


from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/SQLAlchemy_Pro?charset=utf8")

Base.metadata.create_all(engine)

 4、外键关联的表添加与查询操作：

from sqlalchemy.orm import sessionmaker

from creatTableFk import engine   
from creatTableFk import Student,School  # 导入创建表结构的类

Session = sessionmaker(engine)

db_session = Session()

# 1.添加数据
db_session.add(School(name="NCU"))
db_session.commit()

# relationship  正向添加
stu = Student(name="清风徐来",stu2sch=School(name="NCU"))
db_session.add(stu)
db_session.commit()

# relationship  反向添加
sch = School(name="NCU")
sch.sch2stu = [Student(name="YWB"),Student(name="CT")]
db_session.add(sch)
db_session.commit()

# 2.查询
res = db_session.query(Student).all()
for stu in res:
    print(stu.name,stu.stu2sch.name)  # 正向跨表

res = db_session.query(School).all()
for sch in res:
    for stu in sch.sch2stu:
        print(sch.name,stu.name)  # 反向跨表

5.多对多的表的创建

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

Base = declarative_base()


# 多对多关联通过第三张表关联，sqlalchemy要自己创建第三张表
class Girl(Base):
    __tablename__ = "girls"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)

    g2b = relationship("Boy", backref="b2g", secondary="hotels")


class Boy(Base):
    __tablename__ = "boys"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)


class Hotel(Base):
    __tablename__ = "hotels"
    id = Column(Integer, primary_key=True)
    boy_id = Column(Integer, ForeignKey("boys.id"), nullable=False)
    girl_id = Column(Integer, ForeignKey("girls.id"), nullable=False)


engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/SQLAlchemy_Pro?charset=utf8")
Base.metadata.create_all(engine)

6.多对多表数据的添加与查询：

from sqlalchemy.orm import sessionmaker

from CreateTableM2M import engine

Session = sessionmaker(engine)
db_session = Session()

from CreateTableM2M import Girl,Boy

# 1.增加数据
# relationship 正向添加
g = Girl(name="ZLY",g2b=[Boy(name="ZWQ"),Boy(name="FSF")])
db_session.add(g)
db_session.commit()

# relationship 反向添加
b = Boy(name="ZS")
b.b2g = [Girl(name="罗玉凤"),Girl(name="娟儿"),Girl(name="芙蓉姐姐")]
db_session.add(b)
db_session.commit()

# 2.查询
# relationship 正向
res = db_session.query(Girl).all()
for g in res:
    for b in g.g2b:
        print(g.name,b.name)

# relationship 反向
res = db_session.query(Boy).all()
for b in res:
    for g in b.b2g:
        print(b.name,g.name)

7. 高级ORM操作

from CreateTable import Users,engine
from sqlalchemy.orm import sessionmaker
from CreateTableForeignKey import Student

Session = sessionmaker(engine)
db_session = Session()

# 查询数据表操作
# and or
from sqlalchemy.sql import and_ , or_,desc
ret = db_session.query(Users).filter(and_(Users.id > 3, Users.name == 'DragonFire')).all()
ret = db_session.query(Users).filter(or_(Users.id < 2, Users.name == 'DragonFire')).all()

ret = db_session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(
            Users.name == 'eric',
            Users.id > 3
        ),
        Users.name != ""
    )
)
print(ret)
select * from User where id<2 or (name="eric" and id>3) or extra != ""


# 查询所有数据
r1 = db_session.query(User).all()

查询数据 指定查询数据列 加入别名
r2 = db_session.query(Student.name.label('username'), Student.id).first()
print(r2.id,r2.username) # 15 NBDragon

# 表达式筛选条件
r3 = db_session.query(User).filter(User.name == "DragonFire").all()

# 原生SQL筛选条件
r4 = db_session.query(User).filter_by(name='DragonFire').all()
r5 = db_session.query(Users).filter_by(name='DragonFire').first()

字符串匹配方式筛选条件 并使用 order_by进行排序
r6 = db_session.query(Student).order_by(Student.name.desc()).all()
for i in r6:
    print(i.id,i.name)

原生SQL查询
from sqlalchemy.sql import text
r7 = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).params(name='DragonFire').all()

# 筛选查询列
# query的时候我们不在使用User ORM对象,而是使用User.name来对内容进行选取
user_list = db_session.query(User.name).all()
print(user_list)
for row in user_list:
    print(row.name)

# 别名映射  name as nick
user_list = db_session.query(User.name.label("nick")).all()
print(user_list)
for row in user_list:
    print(row.nick) # 这里要写别名了

# 筛选条件格式
user_list = db_session.query(User).filter(User.name == "DragonFire").all()
user_list = db_session.query(User).filter(User.name == "DragonFire").first()
user_list = db_session.query(User).filter_by(name="DragonFire").first()
for row in user_list:
    print(row.nick)

# 复杂查询
from sqlalchemy.sql import text
user_list = db_session.query(User).filter(text("id<:value and name=:name")).params(value=3,name="DragonFire")

# 查询语句
from sqlalchemy.sql import text
user_list = db_session.query(User).filter(text("select * from User id<:value and name=:name")).params(value=3,name="DragonFire")

# 排序 :
user_list = db_session.query(User).order_by(User.id).all()
user_list = db_session.query(User).order_by(User.id.desc()).all()
for row in user_list:
    print(row.name,row.id)

#其他查询条件
"""
ret = session.query(User).filter_by(name='DragonFire').all()
ret = session.query(User).filter(User.id > 1, User.name == 'DragonFire').all()
ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='DragonFire'))).all() 子查询
from sqlalchemy import and_, or_
ret = session.query(User).filter(and_(User.id > 3, User.name == 'DragonFire')).all()
ret = session.query(User).filter(or_(User.id < 2, User.name == 'DragonFire')).all()




# 限制
ret = db_session.query(User)[1:2]

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

# 分组
from sqlalchemy.sql import func

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

ret = db_session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()
"""

# 关闭连接
db_session.close()


ret = db_session.query(Student).filter(Student.id.between(1, 3)).all() # between 大于1小于3的
for i in ret:
    print(i.id,i.name)
ret = db_session.query(Student).filter(~Student.id.in_([1,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的
for i in ret:
    print(i.id,i.name)
ret = session.query(User).filter(~User.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
# 通配符
ret = db_session.query(Student).filter(Student.name.like('%e%')).all()


ret = db_session.query(Student).filter(~Student.name.like('Z%')).all()
for i in ret:
    print(i.id,i.name)



高级版更新操作
from my_create_table import User,engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

#直接修改
db_session.query(Student).filter(Student.id > 3).update({Student.name: Student.name + "099"}, synchronize_session=False)
db_session.commit()

db_session.query(Student).filter(Student.id > 3).update({"name": Student.name + "123"}, synchronize_session=False)
db_session.commit()


#在原有值基础上添加 - 1
db_session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False)

#在原有值基础上添加 - 2
db_session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate")
db_session.commit()

三、Flask_SQLAlchemy

1. 安装 Flask-SQLAlchemy模块，pip安装 

　　pip install Flask-SQLAlchemy

2.先创建一个Flask项目

3.基于这个Flask项目,加入Flask-SQLAlchemy让项目变得生动起来

from flask import Flask

# 导入Flask-SQLAlchemy中的SQLAlchemy
from flask_sqlalchemy import SQLAlchemy

# 实例化SQLAlchemy
db = SQLAlchemy()
# PS : 实例化SQLAlchemy的代码必须要在引入蓝图之前

from .views.users import user


def create_app():
    app = Flask(__name__)

    # 初始化App配置 这个app配置就厉害了,专门针对 SQLAlchemy 进行配置
    # SQLALCHEMY_DATABASE_URI 配置 SQLAlchemy 的链接字符串儿
    app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:DragonFire@127.0.0.1:3306/dragon?charset=utf8"
    # SQLALCHEMY_POOL_SIZE 配置 SQLAlchemy 的连接池大小
    app.config["SQLALCHEMY_POOL_SIZE"] = 5
    # SQLALCHEMY_POOL_TIMEOUT 配置 SQLAlchemy 的连接超时时间
    app.config["SQLALCHEMY_POOL_TIMEOUT"] = 15
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    # 初始化SQLAlchemy , 本质就是将以上的配置读取出来
    db.init_app(app)

    app.register_blueprint(user)

    return app

MyApp/__init__.py

4.建立models.py ORM模型文件

from MyApp import db

Base = db.Model # 这句话你是否还记的?
# from sqlalchemy.ext.declarative import declarative_base
# Base = declarative_base()
# 每一次我们在创建数据表的时候都要做这样一件事
# 然而Flask-SQLAlchemy已经为我们把 Base 封装好了

# 建立User数据表
class Users(Base): # Base实际上就是 db.Model
    __tablename__ = "users"
    __table_args__ = {"useexisting": True}
    # 在SQLAlchemy 中我们是导入了Column和数据类型 Integer 在这里
    # 就和db.Model一样,已经封装好了
    id = db.Column(db.Integer,primary_key=True)
    username = db.Column(db.String(32))
    password = db.Column(db.String(32))


if __name__ == '__main__':
    from MyApp import create_app
    app = create_app()
    # 这里你要回顾一下Flask应用上下文管理了
    # 离线脚本:
    with app.app_context():
        db.drop_all()
        db.create_all()

MyApp/models.py

5.实现一个简单的用户管理，登录视图

from flask import Blueprint, request, render_template, redirect
from MyApp.models import User,db

users = Blueprint("user",__name__)

@users.route("/add_user",methods=["POST","GET"])
def add_user():
    if request.method == "GET":
        return render_template("add_user.html")
    else:
        username = request.form.get("username")
        db.session.add(User(name=username))
        db.session.commit()
        return redirect("/user_list")

@users.route("/user_list",methods=["POST","GET"])
def user_list():
    res = User.query.all()
    print(res)
    return render_template("user_list.html",user_list=res)


@users.route("/del/<uid>",methods=["POST","GET"])
def delete_user(uid):
    res = User.query.filter(User.id == int(uid)).delete()
    db.session.commit()
    print(res)
    return redirect("/user_list")



@user.route("/login",methods=["POST","GET"])
def user_login():
    if request.method == "POST":
        username = request.form.get("username")
        password = request.form.get("password")

        # SQlalchemy需要这样做
        # from sqlalchemy.orm import sessionmaker
        # Session = sessionmaker(engine)
        # db_sesson = Session()
        # 现在不用了,因为 Flask-SQLAlchemy 也已经为我们做好会话打开的工作
        # 我们在这里做个弊:
        db.session.add(Users(username=username,password=password))
        db.session.commit()

        # 然后再查询
        user_info = Users.query.filter(Users.username == username and User.password == password).first()
        print(user_info.username)
        if user_info:
            return f"登录成功{user_info.username}"

    return render_template("login.html")

其实Flask-SQLAlchemy比起SQLAlchemy更加的简单自如,用法几乎一模一样,就是在配置和启动上需要注意与Flask的配合就好啦