python SQLAlchemy中子查询subquery的使用

将sql拆解：

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

SQLAlchemy的写法：

<span style="color: #008080;">1</span> sql_text = <span style="color: #800000;">‘</span><span style="color: #800000;"> 1=1 and datetime >= "2020-1-9 11:00:00" and datetime <= "2020-1-9 20:00:00"</span><span style="color: #800000;">‘</span>
<span style="color: #008080;">2</span> 
<span style="color: #008080;">3</span> <span style="color: #008000;">#</span><span style="color: #008000;">相当于 sql拆解中的1</span>
<span style="color: #008080;">4</span> resSub = db.session.query(TableName).filter(text(sql_text)).order_by(TableName.id.desc()).limit(10000<span style="color: #000000;">).subquery()
</span><span style="color: #008080;">5</span> 
<span style="color: #008080;">6</span> <span style="color: #008000;">#</span><span style="color: #008000;">相当于 sql拆解中的2</span>
<span style="color: #008080;">7</span> 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