flask_sqlalchemy相关查询语句总结：
班级表：
学生表：
返回student表中所有数据并限制返回条数：select……from……limit()
result = (,,,,).limit(10).all()对应的SQL和结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes
FROM student
LIMIT ? OFFSET ?
[(1, '张三', 'male', '18', 'java'), (2, '李四', 'female', '19', 'c++'), (3, '王五', 'male', '22', 'php'), (4, '赵六', 'female', '25', 'matalab'), (5, 'lee', 'man', '18', 'python'), (6, '张三', 'male', '18', 'java'), (7, '李四', 'female', '19', 'c++'), (8, '王五', 'male', '22', 'php'), (9, '赵六', 'female', '25', 'matalab'), (10, 'lee', 'man', '18', 'python')]
过滤条件查询： select……from……where ……and……
restult = (,,,,).filter(=='java').filter(=='female')
print(restult)
print(result..all())返回的SQL和对应的结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes
FROM student
WHERE = ? AND = ?
结果：[(11, '胡和', 'female', '18', 'java')]
过滤条件or_,and_ 的使用，需要先导入from sqlalchemy import or_,and_
task_filter = {
or_(
and_(=='female',
=='java'
),
and_(
== 18,
== 'python'
)
)
}
restult2 = (, , , , ).filter(*task_filter).all()
print(restult2)返回的SQL和对应的结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes
FROM student
WHERE = ? AND = ? OR = ? AND = ?
[(5, 'lee', 'male', '18', 'python'),
(10, '乐奋', 'male', '18', 'python'),
(11, '胡和', 'female', '18', 'java')]上面这个filter中有两个条件组，关系为or，每个条件组里有一些and关系的条件。
两表联合查询：student表与grades表联合查询
restult3 = (, , , , , ).filter(
== 'java').filter( == 'male').filter(Student.cls_id == ).all()返回的SQL和对应的结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes, AS grades_grade
FROM student, grades
WHERE = ? AND = ? AND student.cls_id =
[(6, '张三', 'male', '18', 'java', '一年级(3)班')]
Count函数使用：
task_filter={
and_( == 'male',
== 'python')
}
restult4 = (, , , , ).filter(*task_filter).count()
print(restult4)返回的SQL和对应的结果：
SELECT
count(*) AS count_1
FROM
(
select……from……where ……and……
)
AS anon_1
结果为：4
进行优化后的()函数： 无子查询，效率高
restult5 = (()).filter(*task_filter).scalar()
print((()).filter(*task_filter))
print(restult5)返回的SQL和对应的结果：
select count() as count_1
FROM
student
WHERE
= ? AND = ?
结果为：4
Join查询：
query = (, , , , , ).join(Grades,Student.cls_id==).filter(*task_filter).order_by().limit(2)
print(query)
print(())返回的SQL和结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes, AS grades_grade
FROM student JOIN grades ON student.cls_id =
WHERE = ? AND = ? ORDER BY
LIMIT ? OFFSET ?
[(6, '张三', 'male', '18', 'java', '一年级(3)班')]
with_entities()方法 指定某列并去重
#返回指定的一列
query1 = (, , , , ).with_entities().distinct().all()
print((, , , , ).with_entities().distinct())
print(query1)
#返回指定的两列
query = (, , , , ).with_entities(,Student.cls_id).distinct().all()返回对应的sql和查询结果：
SELECT DISTINCT AS student_classes
FROM student
[('java',), ('c++',), ('php',), ('matalab',), ('python',), ('C',)]
with_entities()方法筛选字段：
query = (Grades,and_(Student.cls_id==)).filter(=='python').with_entities(,)
print(query) #打印SQL
results = ()
print(results) #打印结果
data = [dict(zip((), result)) for result in results]
print(data) #将结果转为dict对应结果如下：
SELECT AS student_name, AS grades_grade
FROM student JOIN grades ON student.cls_id =
WHERE = 'python'
[('乐奋', '一年级(5)班'), ('石雨', '一年级(5)班'), ('马庆', '一年级(1)班'), ('刘胜', '一年级(4)班')]
[{'name': '乐奋', 'grade': '一年级(5)班'}, {'name': '石雨', 'grade': '一年级(5)班'}, {'name': '马庆', 'grade': '一年级(1)班'}, {'name': '刘胜', 'grade': '一年级(4)班'}]
获取多个Model的记录：
除了筛选字段外，还可以用另一个方法获取多个 Model 的记录。那就是，返回两个 Model 的所有字段：
query = (Student,Grades).join(Grades,and_(Student.cls_id==)).filter(=='python')
print(query)
restult = ()
print(restult)返回的SQL和结果：
SELECT AS student_id, AS student_name, AS student_age, AS student_gender, AS student_classes, student.cls_id AS student_cls_id, AS grades_id, AS grades_name, AS grades_grade
FROM student JOIN grades ON student.cls_id =
WHERE = 'python'
[(<Student 10>, <Grades 5>), (<Student 15>, <Grades 5>), (<Student 16>, <Grades 8>), (<Student 17>, <Grades 9>)]使用上面的语法直接返回 Account 和 Bind 对象，可以进行更加灵活的操作。
group_by函数:
student = (, , , , ).group_by("classes").all() # 按照组
print((, , , , ).group_by("classes"))
print(student)对应的SQL和group_by查询结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes
FROM student GROUP BY
[(18, '贾华', 'female', '19', 'C'), (12, '李天', 'male', '19', 'c++'), (11, '胡和', 'female', '18', 'java'), (14, '李广', 'male', '24', 'matalab'), (13, '陈安', 'male', '26', 'php'), (17, '刘胜', 'male', '25', 'python')]
倒序排序 order_by……desc：
query = (, , , , ).order_by(())
print(query)
print(())对应的SQL和返回结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes
FROM student ORDER BY DESC
[(13, '陈安', 'male', '26', 'php'), (4, '赵六', 'female', '25', 'matalab'), (9, '赵六', 'female', '25', 'matalab'), (17, '刘胜', 'male', '25', 'python'), (14, '李广', 'male', '24', 'matalab'), (3, '王五', 'male', '22', 'php'), (8, '王五', 'male', '22', 'php'), (16, '马庆', 'male', '22', 'python'), (1, '张三', 'male', '20', 'java'), (2, '李四', 'female', '19', 'c++'), (7, '李四', 'female', '19', 'c++'), (12, '李天', 'male', '19', 'c++'), (18, '贾华', 'female', '19', 'C'), (5, 'lee', 'male', '18', 'python'), (6, '张三', 'male', '18', 'java'), (10, '乐奋', 'male', '18', 'python'), (11, '胡和', 'female', '18', 'java'), (15, '石雨', 'female', '17', 'python')]
按用户名模糊查询（两表联合查询名称 .like('%李%')）：
query = (, , , , ,).join(Grades,Student.cls_id==).filter(('%李%'))
print(query)
print(())非外键连接，表student与表grades内连接inner join
对应的SQL和返回结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes, AS grades_grade
FROM student JOIN grades ON student.cls_id =
WHERE LIKE "%李%"
[(7, '李四', 'female', '19', 'c++', '一年级(2)班'), (12, '李天', 'male', '19', 'c++', '一年级(2)班'), (14, '李广', 'male', '24', 'matalab', '一年级(3)班')]
还可以在 filter 得到结果后继续加 join 进行多表联查
outerjoin左外连接：
query = (, , , , ,).outerjoin(Grades,Student.cls_id==).filter(('%李%'))SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes, AS grades_grade
FROM student LEFT OUTER JOIN grades ON student.cls_id =
WHERE LIKE ?
[(2, '李四', 'female', '19', 'c++', None), (7, '李四', 'female', '19', 'c++', '一年级(2)班'), (12, '李天', 'male', '19', 'c++', '一年级(5)班'), (14, '李广', 'male', '24', 'matalab', '一年级(3)班')]
outerjoin相当于LEFT OUTER JOIN 左外连接；outerjoin( ) 返回结果有null
多表联合查询(没有外键)：
query = (, , , , ,).join(Grades,and_(Student.cls_id==,=='python',=='一年级(5)班'))
print(query)
print(())对应的SQL和返回结果：
SELECT AS student_id, AS student_name, AS student_gender, AS student_age, AS student_classes, AS grades_grade
FROM student JOIN grades ON student.cls_id =
AND = "python"
AND = "一年级(5)班"
[(10, '乐奋', 'male', '18', 'python', '一年级(5)班'), (15, '石雨', 'female', '17', 'python', '一年级(5)班')]
这里只有两张表，如果是三张表继续在后面join()即可。
要联结超过 2 张以上的表，可以直接在 join 得到的结果之后链式调用 join 。也可以在 filter 的结果后面链式调用 join 。join 和 filter 返回的都是 query 对象，因此可以无限链式调用下去。
外键是否设置中Join()函数的区别：
没有设置外键：
query = (, , ,
, ,)
.join(Grades,Student.cls_id==)
.filter(('%李%'))已经设置外键：
query = (, , , , ,
)
.join(Grades)
.filter(('%李%'))
paginate()函数实现分页功能：
query = (Student).order_by(()).paginate(1,5)
print((Student).order_by(()))
print()
print()
print()对应SQL和结果：
SELECT AS student_id, AS student_name, AS student_age, AS student_gender, AS student_classes, student.cls_id AS student_cls_id
FROM student ORDER BY DESC
[<Student 13>, <Student 4>, <Student 9>, <Student 17>, <Student 14>]这里的结果返回的是model对象，需要在query()括号里明确字段：
query = (, , , , ).order_by(()).paginate(1,5)
print((, , , , ).order_by(()))
print()返回的是第一页前5个的结果：
[(13, '陈安', 'male', '26', 'php'), (4, '赵六', 'female', '25', 'matalab'), (9, '赵六', 'female', '25', 'matalab'), (17, '刘胜', 'male', '25', 'python'), (14, '李广', 'male', '24', 'matalab')]
in_、notin_函数：
query = (, ).filter(.in_([1,3,4]))
print(())
query = (, ,).filter(.notin_([18, 19, 20,22]))
print(())返回结果：
[(1, '张三'), (3, '王五'), (4, '赵六')]
[(4, '赵六', '25'), (9, '赵六', '25'), (13, '陈安', '26'), (14, '李广', '24'), (15, '石雨', '17'), (17, '刘胜', '25')]
组合 union与union_all函数：
#组合 union与union_all函数 组合的字段数量应一致
query1 = (, ).filter(>22)
query2 = (,).filter(>1).distinct()
print((query2))
print((query2).all()) #union默认会去重
res = query1.union_all(query2).all() #union_all默认不去重
print(res)对应SQL和结果：
SELECT anon_1.student_id AS anon_1_student_id, anon_1.student_name AS anon_1_student_name
FROM (SELECT AS student_id, AS student_name
FROM student
WHERE > ? UNION SELECT AS grades_id, AS grades_name
FROM grades
WHERE > ?) AS anon_1
[(2, '李四'), (3, '王五'), (4, '赵六'), (5, 'lee'), (6, '张三'), (7, '李四'), (8, '王五'), (9, '赵六'), (10, 'lee'), (11, '张三'), (12, '李四'), (13, '王五'), (13, '陈安'), (14, '李广'), (14, '赵六'), (15, 'lee'), (16, '张三'), (17, '刘胜'), (17, '李四'), (18, '王五'), (19, '赵六'), (20, 'lee')]
[(4, '赵六'), (9, '赵六'), (13, '陈安'), (14, '李广'), (17, '刘胜'), (2, '李四'), (3, '王五'), (4, '赵六'), (5, 'lee'), (6, '张三'), (7, '李四'), (8, '王五'), (9, '赵六'), (10, 'lee'), (11, '张三'), (12, '李四'), (13, '王五'), (14, '赵六'), (15, 'lee'), (16, '张三'), (17, '李四'), (18, '王五'), (19, '赵六'), (20, 'lee')]
Group_by分组统计并排序
query = (,().label("cnt")).group_by('classes').order_by(desc('cnt'))
print(query)
print(())返回的SQL和结果：
SELECT AS student_classes, count() AS cnt
FROM student GROUP BY ORDER BY cnt DESC
[('python', 5), ('c++', 3), ('java', 3), ('matalab', 3), ('php', 3), ('C', 1)]
子查询：
query1 = (Student,Grades).filter(Student.cls_id.in_(().filter(=='一年级(5)班'))).with_entities(,,,,).distinct()
print(query1)
print(())返回的SQL和结果：
SELECT DISTINCT AS student_id, AS student_name, AS student_age, AS student_gender, AS student_classes
FROM student
WHERE student.cls_id IN (SELECT AS grades_id
FROM grades
WHERE = '一年级(5)班')
[(10, '乐奋', '18', 'male', 'python'), (15, '石雨', '17', 'female', 'python')]
subquery = (().label("sid")).filter(Student.cls_id==).correlate(Grades).as_scalar()
#第一步：(().label("sid")).filter(Student.cls_id==)
#这句话SQL为：SELECT count() AS sid FROM student WHERE student.cls_id = #如果直接运行，则会报错
#第二步：.correlate(Grades).as_scalar() ==> 代表此时不执行查询操作，将其当作条件，在Grades表中查询时，才执行查询
restult = (, subquery)
#sql语句为：select subquery from Grades
print(restult)
# 第三步：将subquery替换为上面的条件，则此句的SQL为：
# SELECT AS grades_name, (SELECT count() AS sid FROM student WHERE student.cls_id = ) AS anon_1 FROM grades
print(())
动态组合条件。针对不同的场景，可能需要不同的查询条件，类似动态的拼接SQL 语句。
if filter_type == 1:
search = and_( ==1,or_(
and_(GameRoom.white_user_id == user_id,
GameRoom.active_player == 1),
and_(GameRoom.black_user_id == user_id,
GameRoom.active_player == 0)))
elif filter_type == 2:
search = and_( ==1,or_(
and_(GameRoom.white_user_id == user_id,
GameRoom.active_player == 0),
and_(GameRoom.black_user_id == user_id,
GameRoom.active_player == 1)))
elif filter_type == 3:
search = GameRoom.create_by == user_id
(GameRoom).filter(search).all()
直接运行SQL语句查询：
如果查询实在太复杂，觉得用SQLAlchemy查询方式很难实现，或者要通过存储过程实现查询，可以让SQLAlchemy直接运行SQL语句返回结果。
sql ="""select b.user_id,b.user_name,,,a.add_score from
(select user_id, sum(score_new - score_old) as add_score from user_score_log
where year(create_date)=year(now()) and month(create_date)=month(now())
group by user_id) a join users b on a.user_id=b.user_id
order by a.add_score desc limit 50"""
list_top = (sql).fetchall()
这些查询语句已经解决了大部分的需求。
注：一般写完查询后，应该打印生成的 SQL 语句查看一下有没有性能问题。
聚合函数：sum、max、min、avg、count
求和：
query = ((Student.cls_id))
求最大值：
query = ((Student.cls_id))
求最小值：
query = ((Student.cls_id))
求平均值：
query = ((Student.cls_id))
进行统计：
query = (())filter常用过滤条件：==、！=、like(区分大小写，模糊查询)、ilike(不区分大小写)、in、not in、字段为空、不为空、and、or
from sqlalchemy import or_,and_,func,desc
query = ().filter( == 6)
query = ().filter( != 6)
query = ().filter(('%王%'))
query = (, ).filter(.in_([1,3,4]))
query = (, ,).filter(.notin_([18, 19, 20,22]))
query = ().filter(Student.cls_id==None)
query = ().filter(Student.cls_id!=None)
query = ().filter(and_(==18,=='python'))
query = ().filter(or_(==18,=='python'))
print(query) #打印SQL
print(()) #打印结果filter与filter_by的区别：
filter －》 column == expression
传入参数的写法，要用：类名.列名 两个等号 去判断
举例：
query().filter(==’Ed Jones’)
且更复杂的查询的语法，比如_and()，or_()等多个条件的查询，只支持filter
举例：
(or_( == ‘ed’, == ‘wendy’))
(and_( == ‘ed’, == ‘Ed Jones’))
filter_by －》keyword = expression
传入参数的写法，只需要用：（不带类名的）列名 单个等号 就可以判断。
－》filter中，语法更加贴近于，类似于，Python的语法。
举例：
query().filter_by(fullname=’Ed Jones’)filter_by() 只接受键值对参数，所以 filter_by() 不支持><（大于和小于）和 and_、or_查询。
在使用多条件匹配的时候，filter需要借助sqlalchemy里的and_ ，or_ ; 而filter_by不需要，直接把多个匹配条件写在一起。
group_by和having子句:
query = (,()).group_by().having( > 20)
print(query)
print(())对应的SQL和结果：
SELECT AS student_age, count() AS count_1
FROM student GROUP BY
HAVING > ?
[('22', 3), ('24', 1), ('25', 3), ('26', 1)]
参考链接：/post/join-in-flash-sqlalchemy/
/huchong/p/#_label3_1_1_0
/zhongyehai/p/