flask_filter_oder

class Title(Base):
    __tablename__ = "title"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(40))
    def __str__(self):
        return "Title(name:%s)" %self.title
    # 在模型中如何排序
    # __mapper_args__ = {
    #     # "order_by":-id
    #     "order_by":id.desc()
    # }

# order_by()
# titl = session.query(Title).order_by(Title.id).all()

# titl = session.query(Title).order_by(Title.id.desc()).all()

# .limit(3) 获取前三条数据
# offset(0)从0开始的偏移量
# titl = session.query(Title).limit(3).all()
# titl = session.query(Title).offset(0).limit(3).all()
# titl = session.query(Title).order_by(Title.id.desc()).offset(2).limit(3).all()

# 通过切片取数据
titl = session.query(Title).all()[2:4]

for i in titl:
    print(i)
多对多的关系

"""

# 中间表的定义

mytable = Table("mytable", metadata,
            Column('mytable_id', Integer, primary_key=True),
            Column('value', String(50))
                   )
"""
teacher_classes = Table(
    "teacher_classes",
    Base.metadata,
    Column('teacher_id', Integer, ForeignKey('teacher.id')),
    Column('classes_id', Integer, ForeignKey('classes.id'))
)

class Teacher(Base):
    __tablename__ = "teacher"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(40))

    classes = relationship('Classes', backref='teachers', secondary=teacher_classes)
    def __str__(self):
        return "Teacher(name:%s)" %self.name


class Classes(Base):
    __tablename__ = "classes"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(40))

    def __str__(self):
        return "Class(name:%s)" %self.name

# 按照性别分组
# re = session.query(User.gender).group_by(User.gender).all()
# 聚合函数
# 根据性别来分组，
# re = session.query(User.gender, func.count(User.id)).group_by(User.gender).all()

# utf8mb4 用来存放表情
re = session.query(User.age, func.count(User.id)).group_by(User.age).having(User.age<18).all()

# 查询和李 相同的城市和年龄的人
user = session.query(User).filter(User.username.like('李%')).first()
print(user.username)
print(user.city)
print(user.age)

一对一关联关系

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(40), nullable=False)
    extent = relationship("UserExtend", uselist=False)

class UserExtend(Base):
    __tablename__ = "user_extend"
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer, ForeignKey("user.id"))
    # , backref=backref("UserExtend", uselist=False)
    user = relationship("User")