SQLAlchemy工具(ORM工具)
什么是ORM!?
答:object relation mapping;对象关系映射;
大白话解释:通过模型类对象,操作数据库增删改查!!而不是直接使用sql语句!
ORM语法操作的本质就是一种翻译,把基于orm语法编写的代码(如BookInfo.objects.all())翻译成真正用于和数据库通信的sql语句(select * from tb_books);
作用:
- 省去自己拼写SQL,保证SQL语法的正确性
- 一次编写可以适配多个数据库
- 防止注入攻击
- 在数据库表名或字段名发生变化时,只需修改模型类的映射,无需修改数据库操作的代码
ORM使用方式分为两种:
1、定义模型类,依据模型类迁移建表
2、迁移建表使用原生sql语句,操作数据库增删改查使用orm
SQLAlchemy和Flask-SQLAlchemy关系?!
Flask-SQLAlchemy是在SQLAlchemy基础上做了一层封装,方便在Flask工程中使用SQLAlchemy;
Flask-SQLAlchemy使用
1、安装
pip install flask-sqlalchemy==2.3.2
翻译出sql语句;
pip install mysqlclient==1.3.13
安装mysqlclient作用是和数据库通信!
2、在flask工程中配置flask-sqlalchemy流程
- 类属性和表字段名称一致,那么就可以省略Column中的第一参数;
# 第一步、创建数据库对象
# 注意:这两种呢方式在工程中使用,完全没有任何区别;区别在调试的时候,第二种方式需要我们自己加载上下文环境才能够使用!
# 第一种方式
data = SQLAlchemy(app) # 参数app指的是flask应用程序对象
# 第二种方式
# data = SQLAlchemy() # 不传入app,不自动加载应用程序上下文
# data.init_app(app) # 调用函数手动加载应用程序上下文呢
# from . import data
#
# # 第二步:映射数据库字段即构建模型类
# # 这里需要继承data.Model
# class User(data.Model):
# """
# 只要是继承自data.Model的类,就是一个模型类
# """
# # __tablename__:需要声明操作的对象是哪个表
# __tablename__='user_basic'
# class STATUS:
# ENABLE = 1
# DISABLE = 0
# # 指定映射的表字段
# id = data.Column('user_id', # 第一个参数:表里面的user_id字段
# data.BIGINT, # 第二个参数:字段类型
# nullable=False, # 是否为空
# primary_key=True, # 是否是主键
# doc='用户ID') # 字段的描述信息
# # 当映射的字段名和表里面的字段名一致时,可以不写第一个参数
# account = data.Column(data.String,doc='账号')
# status = data.Column(data.INT,doc='状态')
# 3、通过模型类语法实现增删该查
print(User.query.all()) # select * from user_basic;
3、封装数据库模块/包
# 1、在工作目录中创建common目录,标示为导包路径
# 2、在common目录的初始化文件中,实例化db对象
# 3、在common目录下创建users.py模块,在该模块中茶 u 你更加爱你模型类对象
# 4、把common目录添加到导包的路径中
# 以下这部分代码在flask工程的主文件main.py中添加
# 把common目录设置成导包路径
import sys,os
# 获得main.py文件的绝对路径
# /Users/weiwei/Desktop/flask_sqlalchemy_demo/toutiao/main.py
MAIN_DIR = os.path.abspath(__file__)
# 获取工程的工作目录,作为配置的基础目录
# /Users/weiwei/Desktop/flask_sqlalchemy_demo/
BASE_DIR = os.path.dirname(os.path.dirname(MAIN_DIR))
# 把/Users/weiwei/Desktop/flask_sqlalchemy_demo/common添加到导包的路径中
# 添加进导包路径之后,我们就可以 from models import xxxx
sys.path.insert(0, os.path.join(BASE_DIR, 'common'))
SQLAlchemy操作(核心重点,核心解决方案)
解决了使用orm语法,操作数据库增删该查;
1、插入数据
- 构建模型类的时候,约束条件要以数据库表为准,而不是模型类,模型类中并无约束条件声明!
- 插入数据的时候,注意诸如唯一约束索引等可能引发的插入失败的操作!
# 2.新增用户(单一,多个)
# 2.1 创建模型类对象
# 在插入字段时,需要注意字段的约束条件,比如:唯一索引的字段(mobile),还需要注意字段名和模型类里面对应
# user1 = User(name='ssj',mobile='15518272035')
# user2 = User(name='chenyong',mobile='15518272564')
# user3 = User(name='peter',mobile='15518272531')
# 2.2 保存模型类对象即在表里面插入数据
# 插入单一数据
# db.session.add(user1)
# 插入多条数据
# db.session.add_all([user2,user3])
# 必须要提交事务,否则无效
db.session.commit()
2、更新数据
# 3、更新用户对象
# 3.1 获得用户单一对象
user = User.query.get(1211573311889211396) # 参数是用户的id值
user.password = "12345678" # 修改密码
db.session.add(user)
db.session.commit()
# 3.2 批量更新
User.query.filter_by(id=1211573311889211396).update({"password":"987654321"})
db.session.commit()
3、删除操作
# 4、删除对象
# 4.1 获得单一对象
user = User.query.get(1211573311889211396) # 参数是用户的id值
db.session.delete(user)
db.session.commit()
# 4.2 批量删除
User.query.filter_by(id=1211573311889211395).delete()
db.session.commit()
4、查询(重点中的重点)
查询方式有2种:
- 直接使用模型类中的query管理器取查询,如:User.query.all()
- 使用session去查询,如:db.session.query(User).all()
注意事项,函数链式调用顺序:
# 这个三个函数是最基本的,会触发"sql语句"的执行;
# 该三个函数写在最后;在一个模型语句中只需出现某一个!
all()
first()
get() # get函数不能够拼接在复合查询的后面,get函数只能够用来根据id去查询单一数据:User.query.get(10),查询单一制定本id的对象
# sql语句中关键字的书写顺序(参考着去记忆orm中函数调用顺序):
# select -> from -> where -> group by -> order by -> limit
# 如果offset存在,limit必须写在offset后面
filter/filter_by > order_by > offset > limit > all/first
# 查询所有
User.query.all()
# 查询返回的数据集中的第一条数据
User.query.first()
# 按照主键过滤查询单一对象
User.query.get(1) # 参数是主键值, 位置参数,而不是命名参数
# User.query.get(id=1),这种写法是错误!
# filter和filter_by查询:都是查询过滤器,要使用查询执行器;
User.query.filter(User.name=='wang').all() # 参数:必须使用类名.字段,可以使用运算符>= == <= !=
User.query.filter_by(name='wang').all() # 参数:只需要使用字段名,不能使用运算符。
# or_函数,拼接sql语句中的or关键字;代表多个条件之间的“或”关系
# 默认filter中多个表达式是and关系;把多个表达式传入or_函数,该函数返回的表达式就是传入的多个表达式之间的“或”关系
User.query.filter( or_(User.name=='张三', User.name=='python') ).all()
# and_函数构建多个表达式之间是并且关系
# 等价于User.query.filter(User.name='张三', User.id=10).all()
User.query.filter(and_(User.name='张三', User.id=10)).all()
# not_构建多个表达式取反(注意:not_函数参数传入只能够传入一个表达式参数)
User.query.filter(
not_(
# 对mobile=='13911111111' and id>=10进行取反
# and_(User.mobile== '13911111111', User.id>=10)
# 对mobile=='13911111111' or id>=10进行取反
or_(User.mobile== '13911111111', User.id>=10)
)
).all()
# 6 偏移查询
# print(User.query.offset(2).all())
# 7 限制查询
# select * from user_basic limit 3----查3条数据
# print(User.query.limit(3).all())
# select * from user_basic limit 4,3;----先偏移4个数据,再查询3条数据
# print(User.query.offset(4).limit(3).all())
# 8 排序查询
# select * from user_basic order_by user_id;
# print(User.query.order_by(User.id).all())
# select * from user_basic order_by user_id desc(asc);
# 降序:desc 升序:asc
print(User.query.order_by(User.id.desc()).all())
# print(User.query.order_by(User.id.asc()).all())
# 模糊查询
# select * from user_basic where user_name like '张%';
User.query.filter(User.name.startswith('张')).all() # 张开头
User.query.filter(User.name.endswith('张')).all() # 张结尾
优化查询
User.query.all() # select * from user_basic;
# 1.尽量不要使用select *这种方式查询,*号代表获取所有字段,会导致此处mysql查询数据量庞大;原则,需要查询什么字段select后面就写什么字段
# 指定查询字段
# select user_name,mobile from user_basic;
# options函数和load_only函数配置使用,作用是用来拼接select关键字后面的指定字段!
from sqlalchemy.orm import load_only
User.query.options(
load_only(User.name, User.mobile)
).all()
聚合查询
-- 聚合查询: 查询出所有用户关注的用户数量
-- 用户id 关注用户的数量
-- 1 3
-- 2 1
-- 5 1
select user_id,count(target_user_id) from user_relation where relation=1 group by user_id;
# orm语法
from models.users import *
from sqlalchemy import func
# 1、query函数中可以传入执行模型类字段,用来构建select后面的指定字段;from关键字后面的表,会自动推导
# func.count函数是聚合查询用于构造sql中关键字count
# group_by函数是分组查询函数用于构造sql中关键字group by
db.session.query(Relation.user_id, func.count(Relation.target_user_id)).filter(Relation.relation==Relation.RELATION.FOLLOW).group_by(Relation.user_id).all()
# sqlalchemy中聚合查询返回结果
# 是一个列表嵌套元组的形势,元组中的值的先后顺序和query函数中指定字段的顺序是一致的
[(1, 3), (2, 1), (5, 1), (33, 1)]
关联查询
1.在mysql中知道主表单一对象,查找与之关联的从表对应的数据
select b.user_name,r.user_id from user_relation r inner join user_basic b on b.user_id=r.user_id;
2.在sqlalchemy中知道主表单一对象,查找与之关联的从表对应的数据
第一种方法:
# 目的,根据主表单一对象,查询与之关联的从表多条数据
# 第一步:在主表中通过db.relationship(与之关联的从表名称)函数构建一个外键关联字段
# 主表模型类
class User(db.Model):
# relations字段,代表的含义是,根据对等条件user_basic.user_id=user_relation.user_id,
# 从表user_relation表中的
# 多条数据
# db.relationship函数设置的就是关联字段
relations = db.relationship("Relation")
# 第二步:外键关联的对等条件需要在从表当中用db.Foreignkey()进行设置
# ForeignKey函数指定的关联字段,必须是真正的数据库表的字段名,而不是模型类属性
# 从表模型类
class Relation(db.Model):
# 用户的id: 1
# ForeignKey函数指定的关联字段,必须是真正的数据库表的字段名,而不是模型类属性
user_id = db.Column(db.Integer, db.ForeignKey("user_basic.user_id"), doc='用户ID')
# 以上模型类设置的模型类语法
user = User.query.all()[0] # user单一对象
user.relations # 得出的结果是根据关联条件,查处从表中对应的多条数据
# 对应的sql语句
# -- 关联查询:查询指定id用户关注的信息
# 使用join on语法的关联查询
# select user_basic.user_name,user_relation.user_id,user_relation.target_user_id
# from user_relation
# join user_basic
# on user_relation.user_id=user_basic.user_id;
# 使用where的关联查询
# select * from user_relation,user_basic
# where user_relation.user_id=user_basic.user_id
# and user_basic.user_id=1;
第二种方法:主表设置relationship,通过primaryjoin参数指定关联相等条件
# 主表User模型类
class User(db.Model):
# 设置外间关联字段,代表从表多条数据
relations = db.relationship("Relation", primaryjoin="User.id==foreign(Relation.user_id)")
# 从表Relation模型类
class Relation(db.Model):
# 关联从表字段只要有就行,无需设置ForeignKey
user_id = db.Column(db.Integer, doc='用户ID')
# 以上模型类设置的模型类语法
user = User.query.all()[0] # user单一对象
user.relations # 得出的结果是根据关联条件,查处从表中对应的多条数据
三、使用uselist参数来指定一对多、一对一关联字段含义(根据主表查询从表)
# user_basic设置关联字段,代表user_profile表对应的数据
# 咱们在业务中已经明确是一对一的关系,这个profile代表的绝对是与当前主表User对象关联的从表UserProfile单一对象
# 指定uselist=True返回结果 [<UserProfile 1>];通过结果得知:执行的操作把User作为主表,UserProfile作为从表,认为两者之间的关系是一对多
# 但是user_basic和user_profile的关系是一对一关系,因此需要添加uselist = False进行声明
# 指定uselist=False返回结果单一对象:<UserProfile 1>
profile = db.relationship("UserProfile",primaryjoin="User.id==foreign(UserProfile.id)",uselist=False # 只会提取保存单一数据;换句话说,我们使用uselist=False来表达出一对一
)
四、使用从表对象查询主表对象(主表对象一定是单一的)
class Relation(db.Model):
# 用户的id: 1
# ForeignKey函数指定的关联字段,必须是真正的数据库表的字段名,而不是模型类属性
# user_id = db.Column(db.Integer, db.ForeignKey("user_basic.user_id"), doc='用户ID')
# 如果主表的关联字段中使用primaryjoin指定了关联的相等条件,那么此处就无需设置
user_id = db.Column(db.Integer, doc='用户ID')
# A关注了B;
# A就是关注者
# B就是被关注者
# user字典,代表的是主表对象(关注者)
# user数据对象查询的本质,就是根据user_id作为对等条件,查询出来的主表单一对象
user = db.relationship("User", primaryjoin="Relation.user_id==foreign(User.id)", uselist=False)
# 被关注用户的id:2
target_user_id = db.Column(db.Integer, doc='目标用户ID')
# target_user代表的是主表对象(被关注者)
# target_user数据对象查询的本质,就是根据target_user_id作为对等条件,查询出来的主表单一对象
target_user = db.relationship("User", primaryjoin="Relation.target_user_id==foreign(User.id)", uselist=False)
五、django的orm映射和sqlalchemy的区别,简单用下面一句话说明.
sqlalchemy中的从表有主表对象的主键字段值user_id,而主表对象user是需要我们手动定义出来的!django中从表有主表对象user(是一个ForeignKey类型),而该对象的主键隐藏字段user_id是自动构建的!
六、指定字段关联查询
# join传入关联对象会根据关联对象自动推导关联查询的对等条件
# 由于我们是从Relation入手查询的,options内部只能设置Relation查询的字段,如果想要设置关联表字段;需要使用contains_eager传入关联对象,进行推导,在使用load_only函数查询关联对象的字段
from sqlalchemy.orm import load_only,contains_eager
Relation.query.join(Relation.target_user) # join(传入从表关联的主表对象)
.options(
# 需要查询的字段信息
load_only(Relation.target_user_id),
# contains_eager(传入关联的对象)
contains_eager(Relation.target_user)
.load_only(User.name))
.all()
# 对应的sql语句
# select user_relation.target_user_id, user_basic.user_name from user_relation join user_basic on user_relation.target_user_id=user_basic.user_id;
七、sqlalchemy中的事务
注意事项(什么情况下不需要手动构建上下文):
- 在工程中运行的代码不需要手动构建上下文(比如视图中的代码);
- 使用flask shell命令进入的调试交互终端环境。也不需要手动构建上下文;
sqlalchemy中的代码,默认都是开启事务的!
# 事务,
# 为什么我在视图函数中没有构建上下文去捕获异常呢?!
# 答:在视图函数中的一切代码,都是在整个工程的上下文环境下执行的,所以不需要再构建上下文!
try:
user = User(mobile='18588369038', name='wiewie')
db.session.add(user)
db.session.commit()
except:
db.session.rollback() # 事务回滚