python SQLAlchemy中子查询subquery的使用

将sql拆解：

1 1.
2 select * from TableName where 1=1 and datetime >= "2020-1-9 11:00:00" 
3 and datetime <= "2020-1-9 20:00:00" ORDER BY id DESC LIMIT 100000
4 
5 2.
6 select a.name,count(*),a.datetime from 
7 (1中sql) a 
8 GROUP BY a.name ;

SQLAlchemy的写法：

1 sql_text = ‘ 1=1 and datetime >= "2020-1-9 11:00:00" and datetime <= "2020-1-9 20:00:00"‘
2 
3 #相当于 sql拆解中的1
4 resSub = db.session.query(TableName).filter(text(sql_text)).order_by(TableName.id.desc()).limit(10000).subquery()
5 
6 #相当于 sql拆解中的2
7 res = db.session.query(resSub.c.name, func.count(resSub.c.name), resSub.c.datetime).group_by(resSub.c.name).all()

python SQLAlchemy中子查询subquery的使用

标签：date   bsp   des   subquery   com   展示   limit   最新   tps