Python学习日记--Flask的数据库操作(三)

目录

• 知识点
• 表关系
• • 多对多
• 排序
• limit、offset、切片
• Group_by
• having
• 子查询

知识点

• 表关系
  • 多对多
• 排序
• 查询高级
  • limit、offset和切片
  • group_by
  • having
  • join
• 子查询

表关系

多对多

多对多需要一个中间表来作为连接。在ORM中，中间表是通过Table模块创建的，因此需要先导入Table。

from sqlalchemy import create_engine, Table  # 导入
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, String, Integer, ForeignKey

# 数据库配置
HOSTNAME = "localhost"
DATANAME = "demo0425"
PORT = 3306
USERNAME = "root"
PASSOWRD = "root"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSOWRD, HOSTNAME, PORT, DATANAME)
engin = create_engine(DB_URL)

Base = declarative_base(engin)


class Teacher(Base):
    __tablename__ = "teacher"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    # secondary绑定中间表
    classes = relationship("Classes", backref="teacher", secondary=teacher_classes)


class Classes(Base):
    __tablename__ = "classes"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))


# Base.metadata.drop_all()  # 删除表
Base.metadata.create_all()  # 创建表

Session = sessionmaker(bind=engin)
session = Session()
           

• 要用secondary绑定中间表
• 其实不管是那种类型的表关系，都是通过relationship关联的

然后就可以正常查询了

teacher = session.query(Teacher).first()
print(teacher)
for i in teacher.classes:
    print(i)


classes = session.query(Classes).first()
for i in classes.teacher:
    print(i)
           

排序

先创建一个表，并插入10条数据

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

# 数据库配置
HOSTNAME = "localhost"
DATANAME = "demo0425"
PORT = 3306
USERNAME = "root"
PASSOWRD = "root"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSOWRD, HOSTNAME, PORT, DATANAME)
engin = create_engine(DB_URL)

Base = declarative_base(engin)


class Article(Base):
    __tablename__ = "article"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    age = Column(Integer, nullable=False)
    gender = Column(Enum("男", "女"), default="男")

    def __str__(self):
        return "User(id:{}, name:{}, age:{}, gender:{})".format(self.id, self.name, self.age, self.gender)


Base.metadata.drop_all()  # 删除表
Base.metadata.create_all()  # 创建表

Session = sessionmaker(bind=engin)
session = Session()

for i in range(10):
    article = Article(name="name%s" % i, age=random.randint(5, 40), gender=random.choice(["男", "女"]))
    session.add(article)

session.commit()
           

然后将添加数据的代码注释了，接下来就开始做排序

articles = session.query(Article).order_by(Article.age).all()  # 正序(默认)
# articles1 = session.query(Article).order_by(Article.age.desc()).all()  # 倒序
articles1 = session.query(Article).order_by(-Article.age).all()  # 前面加个符号"-" 也能实现倒序

for data in articles1:
    print(data)
           

这样的排序是将结果查询之后再进行排序，也可以将排序方法写到模型类中，这样不需要写order_by也能实现排序.

在模型类中加入__mapper_agrs__

class Article(Base):
    __tablename__ = "article"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    age = Column(Integer, nullable=False)

    def __str__(self):
        return "User(id:{}, name:{}, age:{}, gender:{})".format(self.id, self.name, self.age, self.gender)

    # 在模型类中加入排序
    __mapper_agrs__ = {
        "order_by" : age
        # "order_by" : age.desc()  # 倒序
    }
           

• 不能拿字符串排序，字符串排序没有意义。
• 对于像论坛帖子，新闻资讯、网店商品等网页，一般都是将最新发布或者最后修改的内容排在最前面，这类的排序最好都放在模型类中。

limit、offset、切片

# limit 前3条数据
# atricles = session.query(Article).limit(3).all()
# for data in atricles:
#     print(data)

# offset 可以结合limit实现从第几条开始,查询多少条数据
# atricles = session.query(Article).offset(3).limit(3).all()  # 从第4条开始查询3条数据,因为offset的位置是从0开始算的,所以是第四条
# for data in atricles:
#     print(data)

# 切片
atricles = session.query(Article).all()[0:3]
for data in atricles:
    print(data)
           

• 切片的实现效果和limit+offset的效果类似，但不同的是切片是对查询结果做切片；但limit和offset是在查询时就做了处理，因此就性能而言，limit+offset远比切片要好。

Group_by

group_by是用于分组，比如可以通过对性别分组，统计男和女分别有多少人。

# 按照性别进行分组
articles = session.query(Article.gender).group_by(Article.gender).all()
print(articles)
           

• 这里用性别分组，因此query这里只能填Article.gender；如果再加入个username字段，会报错。

接着可以配合聚合函数，统计男女人数分别有多少

# 按照性别进行分组,并统计男女的人数
from sqlalchemy import func
articles = session.query(Article.gender, func.count(Article.id)).group_by(Article.gender).all()
print(articles)
           

having

having是对查找结果进一步过滤。比如只想要看未成年人的数量，那么可以首先对年龄进行分组统计人数，然后再对分组进行having过滤。

# having  分组查询未成年的人数
from sqlalchemy import func
articles = session.query(Article.age, func.count(Article.id)).group_by(Article.age).having(Article.age < 18).all()
print(articles)
           

子查询

# 子查询 查询性别相同,年龄相同的两个人
# 子查询先写内层查询再写外层查询
articles = session.query(Article.age.label("age"), Article.gender.label("gender")).filter(Article.name == "name1").subquery()
result = session.query(Article).filter(Article.gender == articles.c.gender, Article.age == articles.c.age).all()
for data in result:
    print(data)
           

• label是别名的意思