Sqlalchemy 常用方法

class ProductOrm(DataBase):
    __tablename__ = 'data_product'
    __table_args__ = {'comment': '产品'}

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(255), nullable=False, comment='')
    category = Column('category', Integer, nullable=False, comment='')
    price = Column('price', Float, nullable=False, comment='')
    sale= Column('sale', Float, nullable=False, comment='')
    time= Column('time', Datetime, nullable=False, comment='月')
    
 class CategoryOrm(DataBase):
    __tablename__ = 'data_category'
    __table_args__ = {'comment': '类别'}

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(255), nullable=False, comment='')

1. 筛选：

query = db.query(ProductOrm.id.label('id'), ProductOrm.name.label('value')).filter(ProductOrm.category.in_([1,2])).all()

2. 常量使用text

from sqlalchemy.sql import text, func

# DATE_ADD(sales_time, INTERVAL 1 year)
func.DATE_ADD(ProductOrm.time, text('INTERVAL 1 year'))

3. 子查询 subquery， 使用子查询时，字段用.c

Product_query= db.query(ProductOrm.id.label('id'), ProductOrm.name.label('name'),ProductOrm.category.label('category'),ProductOrm.sale.label('sale')).filter(ProductOrm.category.in_([1,2])).subquery('Product')
query = db.query(CategoryOrm.name, func.sum(Product_query.c.sale)).join(CategoryOrm, CategoryOrm.id==Product_query.c.category).group_by(CategoryOrm.name).order_by(func.sum(Product_query.c.sale).desc())

4. 开窗函数：over

func.rank().over(partition_by=ProductOrm.category,
                 order_by=ProductOrm.sale.desc()),
                 
db.query(
    distinct(ProductOrm.name).label('name'),
    func.sum(ProductOrm.sale).over(partition_by=ProductOrm.category).label('csales'), #每类销量
    func.sum(ProductOrm.sale).over(partition_by=text('1')).label('all'))  # 全部的销量
    

5. 

不等于null: isnot(None)

求分位数 Clickhouse : quantile(level)(expr)

case 用法

from sqlalchemy.sql import func, case, quoted_name, and_
# 每月每个类别的利润最大值，最小值，1/4， 1/2， 3/4分位数
base = db.query(ProductOrm.time.label('time'),
                ProductOrm.category.label('category'),
                func.round(case([(ProductOrm.sale == null, 0)], else_= ProductOrm.sale*ProductOrm.price),2).label('value'),
                
                ).filter(and_(ProductOrm.time.between('2020-01-01', '2021-01-01'),
                              ProductOrm.sale.isnot(None))
                        ).subquery('base')
query = db.query(func.round(func.max(base.c.value), 4),
                 func.round(func.__getattr__(quoted_name('quantile(0.75)', False))(base.c.value), 4),
                 func.round(func.median(base.c.value), 4),
                 func.round(func.__getattr__(quoted_name('quantile(0.25)', False))(base.c.value), 4),
                 func.round(func.min(base.c.value), 4),
                 base.c.category,
                 base.c.time).group_by(base.c.time, base.c.category)

6. with 用法 。 使用cte做with 子查询

with_query=db.query(ProductOrm.id.label('id'), ProductOrm.name.label('name'),ProductOrm.category.label('category'),ProductOrm.sale.label('sale')).filter(ProductOrm.category.in_([1,2]))
with_query = with_query.cte('with_query')

7. join时用同一张表，为其取别名 

from sqlalchemy.orm import aliased

p1 = aliased(Product)
p2= aliased(Product)

8. literal 用法，将常量做字段查询

from sqlalchemy import literal
# index = '兴趣'
query= db.query(User.id.label('id'),
                literal(inedx).label('index')
                )