Flask-order_by排序/limit、offset/切片

order_by：
可以指定根据这个表中的某个字段进⾏排序，如果在前⾯加了⼀ 个-，代表的是降序排序。 2.在模型定义的时候指定默认排序：有些时候，不想每次在查询的时候都指定排 序的⽅式，可以在定义模型的时候就指定排序的⽅式。

在模型定义中，添加以下代码
mapper_args = {
“order_by”: title
}

即可让⽂章使⽤标题来进⾏排序。 3.正向排序和反向排序：默认情况是从⼩到⼤，从前到后排序的，如果想要反向 排序，可以调⽤排序的字段的desc⽅法

# @ Time : 2020/4/28 
# @ Author : Ellen
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
import random
from sqlalchemy.orm import relationship, backref
from sqlalchemy import Table

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

DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset?'.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))

    def __str__(self):
    # def __repr__(self):
        return "Article(title:%s)" % self.title

    __mapper_args__ = {
        "order_by": id
        # "order_by": id.desc()
        # "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()
# 倒叙 添加desc 或 -(负号)
# articles = session.query(Article).order_by(Article.id.desc()).all()
# articles = session.query(Article).order_by(-Article.id).all()
# print(articles)
# for article in articles:
#     print(article)

articles = session.query(Article).all()
for article in articles:
    print(article)

 排序  order_by 默认排序是升序
# articles = session.query(Article).order_by(Article.id).all()
# 倒叙 添加desc 或 -(负号)
# articles = session.query(Article).order_by(Article.id.desc()).all()
# articles = session.query(Article).order_by(-Article.id).all()
# print(articles)
# for article in articles:
#     print(article)

# articles = session.query(Article).all()
# for article in articles:
#     print(article)

# limit  3 条数据 前三条数据
# articles = session.query(Article).limit(3).all()
# 查询3到5条  offset 从零开始的 偏移量
# articles = session.query(Article).offset(0).limit(3).all()

# articles = session.query(Article).order_by(Article.id.desc()).offset(2).limit(3).all()
# for article in articles:
#     print(article)

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

子查询

# @ Time : 2020/4/28 16:37
# @ Author : Ellen

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, backref
import random
from sqlalchemy.orm import relationship

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

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

engine = create_engine(DB_URL)
Base = declarative_base(engine)

class User(Base):
    __tablename__ = "users"

    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


# Base.metadata.drop_all()
Base.metadata.create_all()

session = sessionmaker(bind=engine)()

# 查询和李 相同的城市和年龄的人  比子查询更清楚简洁好理解
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()
# # c column
# result = session.query(User).filter(User.city == sub.c.city, User.age == sub.c.age).all()
# for data in result:
#     print(data)