sqlalchemy.sql包常用方法

Sqlalchemy

Sqlalchemy.sql

• 通过这个包可以帮助我们用python代码实现sql

from sqlalchemy import func
from sqlalchemy.sql import case, select, label, union_all, expression, column, or_, and_


db.session.query(
	# sql相当于 case when level_id =="" then 0 else level_id as level_id
	label("level_id", case([(model.level_id == "", "0")],
                                         else_=model.level_id)),
	# 相当于 0 as total
				
				expression.bindparam("total", 0).label("total")
).filter()


try:
	# select 与 sql中select用法一致 注意里面字段放在列表里
    query_res = select(["id", "name",
                func.sum(column("total")), # 聚合函数
                func.sum(column("count")),
                ]). \
    # 相当于from union_all 同 union_all 里面可以放多个查询的组合
    # where 条件查询 多个条件用and_和or_组合
        select_from(union_all(*qy_list).alias('ret_1')).where(and_(*cond_list)). \
        group_by("id", "name")
    all_data = db.session.execute(query_res).fetchall()
except Exception as e:
    
    print(e)