flask--排序与查询高级运用

排序

• order_by：可以指定根据这个表中的某个字段进行排序，如果在前面加了一个-,代表的是降序排序。
• 在模型定义的时候指定默认排序：有些时候，不想每次查询的时候都指定排序的方式，可以指定模型的时候就指定排序的方式。
• 在模型定义中，添加一下代码

__mapper_args__"{
	"order_by":title
	}

• 即可让文章使用标题来进行排序
• 正向排序和反向排序：默认情况是从小到大，从前到后排序的，如果想要反向排序，可以调用排序的字段的desc方法

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, Text, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship, backref
import random
from sqlalchemy import Table

HOSTNAME = '127.0.0.1'
DATABASE = 'demo0502'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'

DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    # create_time = Column()

    # def __repr__(self):
    #     return "Article(title:%s)" % self.title
    def __str__(self):
        return "Article(title:%s)" % self.title
    __mapper_args__ = {
        "order_by": id
    }
session = sessionmaker(bind=engine)()
# Base.metadata.drop_all()
Base.metadata.create_all()

# for i in range(10):
#     article = Article(title='title%s' % i)
#     session.add(article)
# session.commit()
# order_by 默认升序
# articles = session.query(Article).order_by(Article.id).all()
# print(articles) # 使用repr不需要使用循环
# 倒叙
# articles = session.query(Article).order_by(Article.id.desc()).all()
# articles = session.query(Article).order_by(-Article.id).all()
# for article in articles:
#     print(article)
# mapper_args  指定默认排序
articles = session.query(Article).all()
for article in articles:
    print(article)

limit、offset和切片

• limit：可以限制每次查询的时候只查询几条数据。
• offset：可以限制查找数据的时候过滤掉前面多少条
• 切片：可以对QUERY对象使用切片操作，来获取想要的数据。

# limit
# articles = session.query(Article).limit(3).all()
# 查询某个区间的几条数据 offset从0开始的
# articles = session.query(Article).offset(2).limit(3).all()
# articles = session.query(Article).order_by(Article.id.desc()).offset(3).limit(3).all()
# for article in articles:
#     print(article)

# 切片
articles = session.query(Article).all()[2:5]
for article in articles:
    print(article)

查询高级

group_by

某个字段进行分组。比如想要根据性别进行分组，来统计每个分组分别由多少个人

session.query(User.gender, func.count(User.id).group_by(User.gender).all()

having

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

session.query(User.age, func.count(User.id)).group_by(User.age).having(User.age >=18).all()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    gender = Column(Enum('男','女'))
    age = Column(Integer)

session = sessionmaker(bind=engine)()
# Base.metadata.drop_all()
Base.metadata.create_all()

# for x in range(10):
#     user = User(username='happy%s'%x, gender='女', age=random.randint(15, 26))
#     session.add(user)
#
# session.commit()

# 按照性别来进行排序
# result = session.query(User.gender).group_by(User.gender).all()
# print(result)
# 分组 聚合函数一般会和分组进行使用
# result = session.query(User.gender, func.count(User.id)).group_by(User.gender).all()
# print(result)
# sql语句： SELECT gender,GROUP_CONCAT(username) FROM users GROUP BY users.gender; 显示用户名字

# having
# result = session.query(User.age, func.count(User.id)).group_by(User.age).all()
result = session.query(User.age, func.count(User.id)).group_by(User.age).having(User.age <18).all()
print(result)

join方法

join查询分为两种，一种是inner join，另一种是outer join.默认是inner join。如果指定left join或者是right则为outer join。如果想要查询User及其对应的Address,则可以通过以下方式来实现。

for u, a session.query(User, Address).filter(User.id==Address.user_id).all():
	print(u)
	print(a)
	

这是通过普通方式的实现，也可以通过join的方式来实现，更加简单

for u, a session.query(User, Address).all():
	print(u)
	print(a)

如果采用outerjoin，可以获取所有user，而不用在乎这个user是否有address对象，并且outer join默认为左外查询。

for instance in session.query(User, Address).outerjoin(Address).all():
	print(instance)

子查询

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

HOSTNAME = '127.0.0.1'
DATABASE = 'demo0502'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'

DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)

class User(Base):
    __tablename__ = "userus"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    city = Column(String(50))
    age = Column(Integer)

    def __str__(self):
        return "User(username:%s)" % self.username

session = sessionmaker(bind=engine)()
# Base.metadata.drop_all()
Base.metadata.create_all()

# 查询相同城市和年龄的人
# user = session.query(User).filter(User.username == '张').first()
# print(user.city)
# print(user.age)
#
# result = session.query(User).filter(User.city == user.city,User.age == user.age).all()
# for data in result:
#     print(data)

# 子查询 合并上两句
sub = session.query(User.city.label('city'), User.age.label('age')).filter(User.username == '张').subquery()
result = session.query(User).filter(User.city == sub.c.city, User.age == sub.c.age).all()
for data in result:
    print(data)