1、联合查询
join:
A表:
id | group | name | Alogs |
1 | shanghai | AAAA | Acontent here |
2 | beijing | BBBB | Acontent here |
B表:
id | time | Aid | Blogs |
1 | 2022-09-23 09:27:22 | 1 | Bcontent here |
2 | 2022-09-22 16:28:49 | 1 | Bcontent here |
3 | 2022-09-21 11:00:14 | 2 | Bcontent here |
logs = B.query.join(A,A.id==B.Aid).filter(A.group == shanghai).all()
B表联合A表,通过A的id
B.query.join(A,A.id==B.Aid),结果如下表:
id | time | Aid | Blogs | group | name | Alogs |
1 | 2022-09-23 09:27:22 | 1 | Bcontent here | shanghai | AAAA | Acontent here |
2 | 2022-09-22 16:28:49 | 1 | Bcontent here | shanghai | AAAA | Acontent here |
3 | 2022-09-21 11:00:14 | 2 | Bcontent here | beijing | BBBB | Acontent here |
然后再查询所有group为shanghai的数据
.filter(A.group == shanghai).all(),结果为:
id | time | Aid | Blogs | group | name | Alogs |
1 | 2022-09-23 09:27:22 | 1 | Bcontent here | shanghai | AAAA | Acontent here |
2 | 2022-09-22 16:28:49 | 1 | Bcontent here | shanghai | AAAA | Acontent here |
2、时间比较查询
需求:查询某段时间内的数据
库:datetime
当前时间:
now_time = datetime.datetime.utcnow()
需要查询的时间段:
当前时间减去days,可以是一天,表示一天内的数据,七天就表示近七天的数据
info_time = now_time - datetime.timedelta(days=days)
时间比较查询
直接用 '>' 就可以进行时间的比较,而且不需要修改info_time的时间格式,只要mysql里是datetime类型就可以,.order_by表示按时间顺序排序
logs = B.query.join(A,A.id==B.Aid)\
.filter(A.group == shanghai,B.create_time >=info_time)\
.order_by(B.create_time.desc())\
.all()