SQLAlchemy（四)：SQLAlchemy查询高级

 

目录

• SQLAlchemy04 /SQLAlchemy查询高级
  • 1、排序
  • 2、limit、offset和切片操作
  • 3、懒加载
  • 4、group_by
  • 5、having
  • 6、join
  • 7、subquery

1、排序

• 排序概述：

  1. order_by：可以指定根据这个表中的某个字段进行排序，如果在前面加了一个-，代表的是降序排序。

  2. 在模型定义的时候指定默认排序：有些时候，不想每次在查询的时候都指定排序的方式，可以在定义模型的时候就指定排序的方式。有以下两种方式：

     • relationship的order_by参数：在指定relationship的时候，传递order_by参数来指定排序的字段。

     • 在模型定义中，添加以下代码，即可让文章使用标题来进行排序。

        __mapper_args__ = {
            "order_by": title
          }
       

  3. 正序排序与倒序排序：默认是使用正序排序。如果需要使用倒序排序，那么可以使用这个字段的desc()方法，或者是在排序的时候使用这个字段的字符串名字，然后在前面加一个负号。

• 代码示例：

2、limit、offset和切片操作

• limit、offset和切片操作概述：

  1. limit：可以限制每次查询的时候只查询几条数据。
  2. offset：可以限制查找数据的时候过滤掉前面多少条。
  3. 切片：可以对Query对象使用切片操作，来获取想要的数据。可以使用slice(start,stop)方法来做切片操作。也可以使用[start:stop]的方式来进行切片操作。一般在实际开发中，中括号的形式是用得比较多的。希望大家一定要掌握。示例代码如下：

  articles = session.query(Article).order_by(Article.id.desc())[0:10]
  

• 代码示例：

  class Article(Base):
      __tablename__ = 'article'
      id = Column(Integer, primary_key=True, autoincrement=True)
      title = Column(String(50), nullable=False)
      create_time = Column(DateTime,nullable=False,default=datetime.now)
      uid = Column(Integer,ForeignKey("user.id"))
  
      author = relationship("User",backref=backref("articles"))
  
      __mapper_args__ = {
          "order_by": create_time.desc()
      }
  
      def __repr__(self):
          return "<Article(title:%s,create_time:%s)>" % (self.title,self.create_time)
      
      
  # 倒序排序
  articles = session.query(Article).all()
  print(articles)

3、懒加载

• 懒加载概述：

  1、在一对多，或者多对多的时候，如果想要获取多的这一部分的数据的时候，往往能通过一个属性就可以全部获取了。比如有一个作者，想要或者这个作者的所有文章，那么可以通过user.articles就可以获取所有的。但有时候我们不想获取所有的数据，比如只想获取这个作者今天发表的文章，那么这时候我们可以给relationship传递一个lazy='dynamic'，以后通过user.articles获取到的就不是一个列表，而是一个AppenderQuery对象了。这样就可以对这个对象再进行一层过滤和排序等操作。
  2、通过lazy='dynamic'，获取出来的多的那一部分的数据，就是一个AppenderQuery对象了。这种对象既可以添加新数据，也可以跟Query一样，可以再进行一层过滤。
  总而言之一句话：如果你在获取数据的时候，想要对多的那一边的数据再进行一层过滤，那么这时候就可以考虑使用lazy='dynamic'。
  3、lazy可用的选项：

  1. select：这个是默认选项。还是拿user.articles的例子来讲。如果你没有访问user.articles这个属性，那么sqlalchemy就不会从数据库中查找文章。一旦你访问了这个属性，那么sqlalchemy就会立马从数据库中查找所有的文章，并把查找出来的数据组装成一个列表返回。这也是懒加载。
  2. dynamic：这个就是我们刚刚讲的。就是在访问user.articles的时候返回回来的不是一个列表，而是AppenderQuery对象。

• 代码示例：

  class User(Base):
      __tablename__ = 'user'
      id = Column(Integer, primary_key=True, autoincrement=True)
      username = Column(String(50),nullable=False)
  
  
  class Article(Base):
      __tablename__ = 'article'
      id = Column(Integer, primary_key=True, autoincrement=True)
      title = Column(String(50), nullable=False)
      create_time = Column(DateTime,nullable=False,default=datetime.now)
      uid = Column(Integer,ForeignKey("user.id"))
  
      author = relationship("User",backref=backref("articles",lazy="dynamic"))
  
      def __repr__(self):
          return "<Article(title: %s)>" % self.title
      
  """
  Base.metadata.drop_all()
  Base.metadata.create_all()
  
  user = User(username='zhilio')
  for x in range(100):
      article = Article(title="title %s" % x)
      article.author = user
      session.add(article)
  session.commit()
  """
  
  from sqlalchemy.orm.collections import InstrumentedList
  from sqlalchemy.orm.dynamic import AppenderQuery
  from sqlalchemy.orm.query import Query
  user = session.query(User).first()
  # 是一个Query对象
  print(user.articles.filter(Article.id > 50).all())
  # 可以继续追加数据进去
  article =Article(title='title 100')
  user.articles.append(article)
  session.commit()

4、group_by

• 根据某个字段进行分组。比如想要根据性别进行分组，来统计每个分组分别有多少人，那么可以使用以下代码来完成：

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

5、having

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

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

• 代码示例：

  from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey,Table
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker,relationship,backref
  
  HOSTNAME = '127.0.0.1'
  PORT = '3306'
  DATABASE = 'first_sqlalchemy'
  USERNAME = 'root'
  PASSWORD = 'root'
  
  # dialect+driver://username:password@host:port/database
  DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
  
  engine = create_engine(DB_URI)
  
  Base = declarative_base(engine)
  
  session = sessionmaker(engine)()
  
  class User(Base):
      __tablename__ = 'user'
      id = Column(Integer,primary_key=True,autoincrement=True)
      username = Column(String(50),nullable=False)
      age = Column(Integer,default=0)
      gender = Column(Enum("male","female","secret"),default="male")
  
  """
  Base.metadata.drop_all()
  Base.metadata.create_all()
  
  user1 = User(username='王五',age=17,gender='male')
  user2 = User(username='赵四',age=17,gender='male')
  user3 = User(username="张三",age=18,gender='female')
  user4 = User(username="刘二",age=19,gender='female')
  user5 = User(username="彭一",age=20,gender='female')
  
  session.add_all([user1,user2,user3,user4,user5])
  session.commit()
  """
  
  # 每个年龄的人数
  # from sqlalchemy.orm.query import Query
  result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age < 18).all()
  print(result)

6、join

• join概述：

  1. join分为left join（左外连接）和right join（右外连接）以及内连接（等值连接）。
  2. 参考的网页：http://www.jb51.net/article/15386.htm
  3. 在sqlalchemy中，使用join来完成内连接。在写join的时候，如果不写join的条件，那么默认将使用外键来作为条件连接。
  4. query查找出来什么值，不会取决于join后面的东西，而是取决于query方法中传了什么参数。就跟原生sql中的select 后面那一个一样。
     比如现在要实现一个功能，要查找所有用户，按照发表文章的数量来进行排序。示例代码如下：

  result = session.query(User,func.count(Article.id)).join(Article).group_by(User.id).order_by(func.count(Article.id).desc()).all()
  

• 代码示例：

  #encoding: utf-8
  
  from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey,Table
  from sqlalchemy.dialects.mysql import LONGTEXT
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker,relationship,backref
  # 在Python3中才有这个enum模块，在python2中没有
  import enum
  from datetime import datetime
  import random
  
  HOSTNAME = '127.0.0.1'
  PORT = '3306'
  DATABASE = 'first_sqlalchemy'
  USERNAME = 'root'
  PASSWORD = 'root'
  
  # dialect+driver://username:password@host:port/database
  DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
  
  engine = create_engine(DB_URI)
  
  Base = declarative_base(engine)
  
  session = sessionmaker(engine)()
  
  
  class User(Base):
      __tablename__ = 'user'
      id = Column(Integer,primary_key=True,autoincrement=True)
      username = Column(String(50),nullable=False)
  
      def __repr__(self):
          return "<User(username: %s)>" % self.username
  
  class Article(Base):
      __tablename__ = 'article'
      id = Column(Integer, primary_key=True, autoincrement=True)
      title = Column(String(50), nullable=False)
      create_time = Column(DateTime, nullable=False, default=datetime.now)
      uid = Column(Integer,ForeignKey("user.id"))
  
      author = relationship("User",backref="articles")
  
      def __repr__(self):
          return "<Article(title: %s)>" % self.title
  
  """
  Base.metadata.drop_all()
  Base.metadata.create_all()
  
  user1 = User(username='zhiliao')
  user2 = User(username='ketang')
  
  for x in range(1):
      article = Article(title='title %s' % x)
      article.author = user1
      session.add(article)
  session.commit()
  
  
  for x in range(1,3):
      article = Article(title='title %s' % x)
      article.author = user2
      session.add(article)
  session.commit()
  """
  
  # 找到所有的用户，按照发表的文章数量进行排序
  result = session.query(User,func.count(Article.id)).join(Article).group_by(User.id).order_by(func.count(Article.id).desc()).all()
  print(result)
  
  # sql语句：
  # select user.username,count(article.id) from user join article on user.id=article.uid group by user.id order by count(article.id) desc;

7、subquery

• subquery概述：

  子查询可以让多个查询变成一个查询，只要查找一次数据库，性能相对来讲更加高效一点。不用写多个sql语句就可以实现一些复杂的查询。那么在sqlalchemy中，要实现一个子查询，应该使用以下几个步骤：

  1. 将子查询按照传统的方式写好查询代码，然后在query对象后面执行subquery方法，将这个查询变成一个子查询。
  2. 在子查询中，将以后需要用到的字段通过label方法，取个别名。
  3. 在父查询中，如果想要使用子查询的字段，那么可以通过子查询的返回值上的c属性拿到。
     整体的示例代码如下：

  stmt = session.query(User.city.label("city"),User.age.label("age")).filter(User.username=='李A').subquery()
  result = session.query(User).filter(User.city==stmt.c.city,User.age==stmt.c.age).all()
  

• 代码示例：

  from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey,Table
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker,relationship,backref
  
  
  HOSTNAME = '127.0.0.1'
  PORT = '3306'
  DATABASE = 'first_sqlalchemy'
  USERNAME = 'root'
  PASSWORD = 'root'
  
  # dialect+driver://username:password@host:port/database
  DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
  
  engine = create_engine(DB_URI)
  
  Base = declarative_base(engine)
  
  session = sessionmaker(engine)()
  
  class User(Base):
      __tablename__ = 'user'
      id = Column(Integer,primary_key=True,autoincrement=True)
      username = Column(String(50),nullable=False)
      city = Column(String(50),nullable=False)
      age =  Column(Integer,default=0)
  
      def __repr__(self):
          return "<User(username: %s)>" % self.username
  
  """
  Base.metadata.drop_all()
  Base.metadata.create_all()
  
  user1 = User(username='李A',city="长沙",age=18)
  user2 = User(username='王B',city="长沙",age=18)
  user3 = User(username='赵C',city="北京",age=18)
  user4 = User(username='张D',city="长沙",age=20)
  
  session.add_all([user1,user2,user3,user4])
  session.commit()
  """
  
  
  # 寻找和李A这个人在同一个城市，并且是同年龄的人
  user = session.query(User).filter(User.username=='李A').first()
  users = session.query(User).filter(User.city==user.city,User.age==user.age).all()
  print(users)
  
  # 子查询
  stmt = session.query(User.city.label("city"),User.age.label("age")).filter(User.username=='李A').subquery()
  result = session.query(User).filter(User.city==stmt.c.city,User.age==stmt.c.age).all()
  print(result)