flask — sql query聚合函式和filter篩選

---

先創建數據庫

from sqlalchemy import create_engine,Column,Integer,String,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import setting
import random

DB_URL = setting.conn()
engine = create_engine(DB_URL)

Base = declarative_base(engine)

session = sessionmaker(engine)()

class Sql(Base):
    __tablename__ = 'sql_test'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    price = Column(String(50),nullable=False)

    def __repr__(self):
        return "<data(id:%s,title:%s,price:%s)>" % (self.id,self.title,self.price)

Base.metadata.drop_all()  #先刪除原來的數據庫再重新創建
Base.metadata.create_all()

for x in range(6):
    article = Sql(title='title%s'%x,price = random.randint(50,100))
    session.add(article)
session.commit()

模型對象

articles = session.query(Sql).all()
print(articles)

模型中的屬性

articles = session.query(Sql.title,Sql.price).all()
print(articles)

query聚合函式

聚合函式

#以下效果和sql查詢一致

#count統計個數
result = session.query(func.count(Sql.id)).first()

#avg求平均數
result = session.query(func.avg(Sql.price)).first()

#max求最大值
result = session.query(func.max(Sql.price)).first()

#min求最小值
result = session.query(func.min(Sql.price)).first()

#sum求和
result = session.query(func.sum(Sql.price)).first()

filter篩選

#以下效果和sql查詢一致

#equals
result = session.query(Sql).filter(Sql.id == 1).first()

#not equals
result = session.query(Sql).filter(Sql.id != 1).all()

#like & ilike(不區分大小寫)
result = session.query(Sql).filter(Sql.title.like('title%')).all()

#in_ & notin_
result = session.query(Sql).filter(Sql.title.in_(['title1','title2'])).all()
result = session.query(Sql).filter(~Sql.title.in_(['title1','title2'])).all()
#或者可以用 result = session.query(Sql).filter(Sql.title.notin_(['title1','title2'])).all()

#is null & is not null
result = session.query(Sql).filter(Sql.title!=None).all()

#or
#使用或的話需要導入or_方法
result = session.query(Sql).filter(or_(Sql.title=='title0',Sql.id==2)).all()

#and
#使用and可以直接用','分割，或者類似or_方法導入and_
result = session.query(Sql).filter(Sql.title=='title0',Sql.id==1).all()
result = session.query(Sql).filter(and_(Sql.title=='title0',Sql.id==1)).all()