97.SQLAlchemy中query函数的使用

query函数的使用.py

from audioop import maxpp
from turtle import title
from util_db import Base, Session

from random import randint

from sqlalchemy import Column, Integer, String, func

# 创建数据表
class Item(Base):
    __tablename__ = 't_item'
    id = Column(Integer, primary_key = True, autoincrement = True)
    title = Column(String(32))
    price = Column(Integer)

# 生成数据
class create_data():
    with Session() as ses:
        for i in range(10):
            item = Item(title = f'产品:{i+1}', price = randint(1,100))
            ses.add(item)
        ses.commit()

# query查询数据：
# 类型1：根据模型中的所有属性（全表查询）
def query_model_name():
    # 获取全部字段
    with Session() as ses:
        rs = ses.query(Item).all()
        for r in rs:
            print(r.title)
# 类型2：根据某几个属性查询
def query_model_attr():
    # 根据部分字段获取
    with Session() as ses:
        rs = ses.query(Item.title, Item.price).all()
        print(rs)
        # for r in rs:
        #     print(r.title)
# 类型3：聚合函数
def query_by_func():
    with Session() as ses:
        count = ses.query(func.count(Item.id)).first()
        print(count)
        avg = ses.query(func.avg(Item.price)).first()
        print(avg)
        max = ses.query(func.max(Item.price)).first()
        print(max)
        min = ses.query(func.min(Item.price)).first()
        print(min)
        sum = ses.query(func.sum(Item.price)).first()
        print(sum)

if __name__ == "__main__":
    # Base.metadata.create_all()
    # create_data()
    # query_model_name()
    # query_model_attr()
    query_by_func()

util_db.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
HOST = "localhost"
PORT = 3306
DATA_BASE = 'flask_db'
USER = 'root'
PWD = 'root'
DB_URI = f"mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}"

engine = create_engine(DB_URI)
Base = declarative_base(engine)
Session = sessionmaker(engine)