5. SQLAlchemy

SQLAlchemy

ORM与Flask-SQLAlchemy扩展

• 什么是 orm ?

  ORM的全称是：Object Relational Mapping （对象 关系 映射）
  简单的说，orm是通过使用描述对象和数据之间映射的元数据，将程序中的对象自动持久化到关系数据库中。

  ORM需要解决的问题是，能否把对象的数据直接保存到数据库中，又能否直接从数据库中拿到一个对象？要想做到上面两点，则必须要有映射关系。

  模型类 《----》 表

  属性 《----》 字段

  对象 《----》 记录

• orm的优势与劣势

  • 优势

    orm的技术特点，提高了开发效率。可以自动对实体Entity对象与数据库中的Table进行字段与属性的映射；不用直接SQL编码，能够像操作对象一样从数据库中获取数据

  • 劣势

    orm会牺牲程序的执行效率和会固定思维模式，在从系统结构上来看，采用orm的系统多是多层系统的，系统的层次太多，效率就会降低，orm是一种完全面向对象的做法，所以面向对象的做法也会对性能产生一定的影响。

• flask 如何使用 orm ?

  flask-sqlalchemy
  

• 如何配置数据库？

  SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123456@127.0.0.1:3306/db0406?charset=utf8"
  SQLALCHEMY_TRACK_MODIFICATIONS = False
  

• 如何创建数据模型(常用的字段)

• 如何创建一张表

  class User(db.Model):
    # 指定表面
    __tablename__ = 'user'
    # id
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(32), unique=True)
    password = Column(String(255), default='')
    # 状态 0 无效 1有效
    status = Column(Integer, default=0)
    # 时间
    create_time = Column(DateTime, default=datetime.now())
  

• 如何完成数据迁移

单表操作

• 数据库事务

• 新增数据

 # 添加单个数据
    u1 = UserModel(username="sxr")
    db.session.add(u1)
    db.session.commit()
 # 批量添加
    u1 = UserModel(username="bikuicheng")
    u2 = UserModel(username="waghaiyuan")
    u3 = UserModel(username="renyu")
    u4 = UserModel(username="dongdeqian")
    u5 = UserModel(username="shixiaoru")
    #
    db.session.add_all([u1,u2,u3,u4,u5])
    db.session.commit() 

• 查询数据

 # 读取所有数据
    u1 = UserModel.query.all()
    data_list = [{
        "id": i.id,
        "username": i.username,
    } for i in u1]
    
    # 返回值
    ret = {
        "data": data_list
    }

    # 只显示一条数据
    u1 = UserModel.query.first()
    print(u1.username)

    # 排序 order by id desc  asc升序/desc降序
    u1 = UserModel.query.order_by(UserModel.id.desc()).first()
    u1 = UserModel.query.order_by(UserModel.id.asc()).all()

    # 精确检索条件
    # select * from user where username = "shixiaoru";
    u1 = UserModel.query.filter_by(username="shixiaoru").first()
    u1 = UserModel.query.filter(UserModel.username=="shixiaoru").first()

    # 模糊搜索 select * from user where username like "%li%";
    u1 = UserModel.query.filter(UserModel.username.like("%{}%".format("li"))).all()
    u1 = UserModel.query.filter(UserModel.username.contains("li")).all()
    data_list = [{"username": i.username} for i in u1]

    # orm执行sql语句  group by xxx having ss
    sql = "select id,username from user where id > 14;"
    data = db.session.execute(sql)
    data_list = []
    for i in data:
        data_list.append({
            "id": i[0],
            "username": i[1]
        })

• 编辑数据

# 获取前段传来的参数id
    id = request.form.get("id")
    username = request.form.get("username")
    # 2 查询数据是否存在
    user_data = UserModel.query.filter_by(id=id).first()
    if not user_data:
        return {"code":1001,"msg":"数据不存在"}
 # 3 编辑
    user_data.username = username
    db.session.add(user_data)
    db.session.commit()
#受影响的行数
    # flag = UserModel.query.filter_by(id=id).update({"username": username})
    # print(flag)    

• 删除数据

 # 获取参数
    id = request.form.get("id")
    # 查询数据是否存在
    user_data = UserModel.query.filter_by(id=id).first()
    if not user_data:
        return {"code":1002,"msg":"数据不存在"}
    # 删除 假删除(逻辑删除,实际上是操作的更新方式) / 真删除(物理删除)
    data = UserModel.query.filter_by(id=id).delete()
    db.session.commit()

单表数据查询补充

• all() 、first() 和 get() 的使用

session.query(Staff).all()
session.query(Staff).first()
session.query(Staff).get(pk=1)

• 过滤器 filter() 和 filter_by() 的使用

session.query(Staff).filter(Staff.name=="aa").all()
session.query(Staff).filter_by(name="aa").all()

• 模糊查询

session.query(Staff).filter(Staff.name.like('%a%'))

• 分页查询

# 分页 select * from user limit 0,10;
  # select * from user limit 参数1,参数2;
  # 参数1 起始的位置   参数2 相当于偏移量
  # total  总条数  data 当前页的数据
# 获取当前页
p = int(request.args.get("page")) if request.args.get("page") else 1
# 页大小
pagesize = int(request.args.get("pagesize")) if request.args.get("pagesize") else 10
# 偏移量
offset = (p-1) * pagesize    
# 获取总数
total = session.query(Staff).filter(Staff.name.like('%a%')).count()
# 当前页的数据  链式调用 惰性查找
data=UserModel.query.order_by(UserModel.id.asc()).limit(pagesize).offset(offset).all()
# 获取数据
session.query(Staff).filter(Staff.name.like('%a%')).limit(pagesize).offset(offset).all()

• 排序查询

session.query(Staff).filter(Staff.name.like('%a%')).order_by(Staff.id.desc()).all()

• or_ , and_ , in_ 查询

from sqlalchemy import or_, and_
task_filter = {
or_(
 and_(
  Task.task_department == current_user.user_department,
  Task.task_commit_status > 0,
  Task.task_complete_time >= prev,
 ),
 and_(
  Task.task_complete_time >= prev,
  Task.task_members.like('%,'+str(current_user.user_id)+',%'),
 )
)
}
Task.query.filter(*task_filter).all()

• 比较查询 < >

session.query(Task).filter(Task.task_complete_time >= prev).all()

• 聚合查询 func
  • 求总数

  	User.query.filter_by(role_id=3,username='susan').count()
  

  • 求和(sum)

  User.query.with_entities(func.sum(User.id)).all()
  
  session.query(func.sum(Staff.id)).scalar()
  

  • 平均数

  User.query.with_entities(func.avg(User.role_id)).all()
  

  • 去重

  Staff.query.with_entities(Staff.name).distinct().all()
  

迁移与映射

• flask-script 和 flask-migrate

django :  python manage.py makemigrations /  migrate

from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from main import app
from models import db

# 将脚本注册到app上
manage = Manager(app)
# 实例化迁移

migrate = Migrate(app,db)
# 在脚本上添加迁移命令
manage.add_command("db",MigrateCommand)

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

启动的迁移命令

- python manage.py db init
- python manage.py db migrate
- python manage.py db upgrade