SQLAlchemy配置
安装flask-sqlalchemy,它是对SQLAlchemy基于flask的封装,SQLAlchemy是python的orm框架并不是针对特定web的框架。
配置数据库
这db初始化APP使用的db=SQLAlchemy(app)这种方式,这样初始化的时候就会加载上下文环境,比如在交互式环境下,就可以直接User.query.get(1)查询,还有一种初始化方式,db.init_app(app)这种方式在初始化的时候不会加载上下文环境,在交互式模式下操作数据库时需要手动加载,例如
with app.app_context():
User.query.get(1)
但是无论哪种方式,在视图中进行数据查询时,都不要手动加载上下文,因为在视图中执行数据库操作时,服务已经启动,在视图中上下文环境已经加载好了。
SQLALCHEMY_ECHO = True
SQLALCHEMY_ECHO = True输出效果
在进行数据库操作时输出操作数据库的日志,包括sql语句,方便调试,可以在测试环境打开线上关闭。
新增数据
添加单个数据
db.session.add(user)
添加多个数据,需传递一个列表
db.session.add_all(users)
基础查询
User.query.get() 使用主键查询,get()方法只能传入主键
User.query.get(1)
Out[3]: <User 1>
User.query.filter_by() 做一些简单的查询,只能使用单个条件或者and,稍微复杂的一点的类似!=者or则不支持
User.query.filter_by(name='itcast').first()
Out[5]: <User 1>
User.query.filter() 相比filter_by则能做的更多,大部分的复杂查询也都是用filter()来做
db.session.query(User).all(),上面的查询都是基于flask-sqlalchemy来进行的,这个是直接使用SQLAlchemy来操作数据库
offset、limit查询
offset从指定位置开始查询,limit从开始位置返回指定个数数据,例如现在有个需求从第二个位置开始返回两个用户,
User.query.offset(2).limit(2).all()
Out[10]: [<User 4>, <User 5>]
对应的sql就是
select * from user_basic limit 2,2
模糊查询
User.query.filter(User.name.startwith('itcast')).all()
查询用户名是已itcast开头的用户,对应sql语法,name like 'itcast%' 还可以是User.name.endwith(''),对应sql中的语法是name like '%itcast'
指定字段查询
在查询数据库是,通常不会直接select *进行查询,需要指定字段,在flask-sqlalchemy中需要指定字段需要导入额外的方法来支持。
from sqlalchemy.orm import load_only
User.query.options(load_only(User.name,User.mobile))
.filter(User.id == 1).first()
Out[12]: <User 1>
对应sql语句就就是
select name, mobile from user where id=1 limit 1;
使用聚合函数
在flask-sqlalchemy使用聚合需要导入新函数,from sqlalchemy import func
db.session.query(Relation.user_id, func.count(Relation.target_user_id))
.group_by(Relation.user_id).all()
Out[26]: [(1, 3)]
列表中每个元组的第一位表示user_id,第二位表示聚合结果,上面查询语句对应sql
select user_id, target_user_id from user_relation group_by(user_id)
关联查询
1、使用外键关联查询
建表的时候,我们将用户的基本常用信息存储在user_basic表中,另外的一些不常用的存储在user_profile表中,在查询的时候,如何通过user.profile就能查询出profile表中存储的信息呢?
现在从表中指定外键,然后在主表指定额外的字段,进行关联查询,注意在从表设置外键是,里面传的一定要是数据库中真实的表名和字段名。
id = db.Column('relation_id', db.Integer, db.ForeignKey('user_basic.user_id'),
primary_key=True, doc='主键ID')
在主表指定关联字段
profile = db.relationship("UserProfile", uselist=False)
直接使用user.profile调用,以下是输出结果
2、使用primaryjoin关联查询
在user_basic表中修改关联字段属性,db.relationship这种额外声明的字段属性,不对应数据库表的字段,不是映射字段,只是为了方便进行关联查询的时候使用
profile = db.relationship('UserProfile',
primaryjoin='User.id==foreign(UserProfile.id)', uselist=False)
user.profile调用和上面的输出结果一样。
3、指定字段关联查询
上面的两种方式都需要两步才能查询出结果,那么如何一次性查出呢。比如现在有一个需求需要查询user_id=1用户所关注用户的信息,sql语句实现如下
select a.user_id,b.user_name,b.mobile from user_relation as a
inner join user_basic as b on a.target_user_id=b.user_id
where a.user_id=1;
现在Relation表中增加关联字段,
user = db.relationship('User',
primaryjoin="Relation.target_user_id==foreign(User.id)")
具体查询语句如下
执行效果
从debug日志中,拿出SQLAlchemy生成的查询语句和我们自己写的看看是否一样
SELECT user_relation.relation_id AS user_relation_relation_id, user_basic.user_id AS user_basic_user_id, user_basic.user_name AS user_basic_user_name, user_relation.user_id AS user_relation_user_id, user_relation.target_user_id AS user_relation_target_user_id
FROM user_relation INNER JOIN user_basic ON user_relation.target_user_id = user_basic.user_id
WHERE user_relation.user_id = %(user_id_1)s,可以看出和之前写的sql语句是一样。
私信我,可获取此文章的python代码包括model以及建表语句和测试数据,你们的关注是我创作的最大动力
推荐阅读:苹果xr和x