sqlalchemy常用查询

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
web = Flask(__name__)
db = SQLAlchemy(web)

db_user_name = 'test'
db_password = '123456'
db_name = 'school'
SQLALCHEMY_DATABASE_URI = "mysql+pymysql://%s:%s@127.0.0.1:3306/%s?charset=utf8mb4"%(db_user_name, db_password, db_name)
web.config.from_object('sqlalchermy')

# 学生表
class Student(db.Model):
    __tablename__ = 'students'
    student_id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 自增id
    class_id = db.Column(db.Integer, index=True, nullable=False)  # 班级id
    no = db.Column(db.Integer, nullable=False)  # 学号
    name = db.Column(db.String(100))   # 姓名
    age = db.Column(db.Integer, nullable=False, default=18, server_default='18')  # 年龄

# 班级表
class Class(db.Model):
    __tablename__ = 'classes'
    class_id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 班级id
    name = db.Column(db.String(100))  # 名称

# 查询学号=1的学生
student = Student.query.filter(Student.no==1).first()

# 查询学号介于10到20间的学生
from sqlalchemy import and_
arr = Student.query.filter(and_(Student.no>=10,Student.no<20)).all()

# 查询姓名包含“强”字的学生
arr = Student.query.filter(Student.name.like('%强%')).all()

# 查询年龄20岁的学生，按学号降序排序
arr = Student.query.filter(Student.age==20).order_by(Student.no.desc()).all()

# 查询年龄20岁的学生，按学号降序排序，分页第100条到120条
arr = Student.query.filter(Student.age==20).order_by(Student.no.desc()).offset(100).limit(20).all()

# 联表查出学生的班级（班级为空的学生不要）
arr = db.session.query(Student.no, Student.name, Class.name).join(Class, Class.class_id==Student.class_id).all()

# 联表查出学生的班级（班级为空的学生也要）
arr = db.session.query(Student.no, Student.name, Class.name).outerjoin(Class, Class.class_id==Student.class_id).all()

# 对每个班级进行分组计算每个班级学生数量
from sqlalchemy import func
arr = db.session.query(Student.class_id, func.count(Student.student_id)).group_by(Student.class_id).all()

# 对每个班级进行分组计算每个班级学生数量,只保留人数大于30的班级
from sqlalchemy import func
arr = db.session.query(Student.class_id, func.count(Student.student_id)).group_by(Student.class_id).having(func.count(Student.student_id)>30).all()