sqlalchemy的基本操作大全_sqlalchemy全面指南

sqlalchemy的基本操作大全

SQLAlchemy是一个数据库的ORM框架，安装命令为pip install sqlalchemy

ORM是什么

ORM:Object Relationship Mapping，既对象关系映射，通过ORM我们可以通过类的方式去操作数据库，而不用再写原生的SQL语句。

通过把表映射成类，把行作为实例对象，把字段作为类属性，ORM在执行对象操作的时候最终还是会把对应的操作转化为数据库原生语句。

其主要优点有：

1. 易用
2. 性能损耗小
3. 设计灵活
4. 可移植性强

连接数据库

from sqlalchemy import create_engine

# 配置链接数据库信息
db_config = {
    'host': '127.0.0.1',
    'port': '3306',
    'database': 'flaskdemo',
    'username': 'root',
    'password': 'passwd'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
# 创建数据库链接
with engine.connect() as conn:
    # 测试是否链接成功
    result = conn.execute('select 1')
    print(result.fetchone())
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

其中，数据库连接的地址格式为

dialect+driver://username:password@host:port/database
1

• dialect：数据库类型，比如mysql，sqlite，注意：一定要是小写
• driver：是python对应的驱动，如果不指定，则会使用默认的驱动，比如MySQL默认驱动是MySQLdb
• username：数据库用户名
• password：数据库密码
• host：数据库的域名
• port：数据库监听的端口，通常默认是3306
• database：连接的数据库的名字

连接地址后面还可以加上查询字符串来设定，如charset=utf8

将ORM模型映射到数据库中：

1. 用declarative_base根据engine创建一个ORM基类。

   from sqlalchemy.ext.declarative import declarative_base
   engine = create_engine(DB_URI)
   Base = declarative_base(engine)
   1
   2
   3

2. 用这个Base类作为基类来写自己的ORM类。要定义__tablename__类属性，来指定这个模型映射到数据库中的表名。

   class Person(Base):
       __tablename__ = 'person'
   1
   2

3. 创建属性来映射到表中的字段，所有需要映射到表中的属性都应该为Column类型：

   class Person(Base):
       __tablename__ = 'person'
       # 2. 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射。这些属性必须是sqlalchemy给我们提供好的数据类型。
       id = Column(Integer,primary_key=True,autoincrement=True)
       name = Column(String(50))
       age = Column(Integer)
   1
   2
   3
   4
   5
   6

4. 使用Base.metadata.create_all()来将模型映射到数据库中。

5. 一旦使用Base.metadata.create_all()将模型映射到数据库中后，即使改变了模型的字段，也不会重新映射了。

例如

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

# 配置链接数据库信息
db_config = {
    'host': '127.0.0.1',
    'port': '3306',
    'database': 'flaskdemo',
    'username': 'root',
    'password': 'passwd'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)
# 创建一个基类来继承
Base = declarative_base(engine)
# 1. 创建一个ORM模型，这个ORM模型必须继承自sqlalchemy给我们提供好的基类
class Person(Base):
    # 表名
    __tablename__ = 'person'
    # 2. 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射，这些属性必须是sqlalchemy提供好的数据类型
    # 设定id为Int，主键， 自增长
    id = Column(Integer, primary_key=True, autoincrement=True)
    # String类型需要指定长度
    name = Column(String(50))
    age = Column(Integer)

# 3.将创建好的ORM模型映射到数据库中
Base.metadata.create_all()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

增删改查

在sqlalchemy中，增删改查都是通过**会话(seesion)**进行的，所以我们必须要先创建会话，核心代码如下

from sqlalchemy.orm import sessionmaker
engine = create_engine(db_url)
# 创建一个会话
session = sessionmaker(engine)()
1
2
3
4

增

• 创建对象，也即创建一条数据：

  p1 = Person(name='张三', age=18)
  1

• 将这个对象添加到session会话对象中：

  session.add(p1)
  1

• 将session中的对象做commit操作（提交），注意：增删改必须提交才能生效：

  session.commit()
  1

• 一次性添加多条数据：

  p2 = Person(name='李四', age=19)
  p3 = Person(name='王五', age=20)
  session.add_all([p1,p2])
  session.commit()
  1
  2
  3
  4

删

• 直接删除已提交的数据

  session.delete(p1)
  session.commit()
  1
  2

• 删除从数据库查找出来的数据

  person = session.query(Person).first()
  session.delete(person)
  session.commit()
  1
  2
  3

改

和删除类似，直接对对象进行操作后，提交即可

# 因为p2已经在会话中了，可以直接更改后再次提交
p2.name = '食铁兽'
session.commit()
1
2
3

查

先用query指定查找的类，也就是查找的其对应数据表，获得该表中所有的数据，并生成一个查找对象

query_person = session.query(Person)
1

接下来我们就可以通过一些条件，来进行查找

• all：返回查找对象的所有的数据，组成一个列表

  all_person = query_person.all()
  for person in all_person:
  	print(person)
  1
  2
  3

• first：返回查找对象的第一条数据

  person = query_person.first()
  print(person)
  1
  2

• get：找到表中主键与参数相同的数据，返回一条数据或None

  person = query_person.get(2)
  print(person)
  1
  2

• filter_by：通过参数中的值，来查找符合参数中的值的数据，返回一个查找对象

  all_person = query_person.filter_by(name='食铁兽').all()
  for person in all_person:
  	print(person)
  1
  2
  3

• filter：通过参数中的布尔表达式，来查找使布尔表达式为True的数据，返回一个查找对象，注意：参数必须是类.属性名，功能比filter_by更强

  person = query_person.filter(Person.name == '王五').first()
  print(person)
  1
  2

有关query和filter的还有很多其他的功能，后续介绍

字段常用数据类型

1. Integer：整形，映射到数据库中是int类型。

2. Float：浮点类型，映射到数据库中是float类型。他占据的32位。

3. Double：双精度浮点类型，映射到数据库中是double类型，占据64位。

4. String：可变字符类型，映射到数据库中是varchar类型.

5. Boolean：布尔类型，映射到数据库中的是tinyint类型。

6. DECIMAL：定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位。

7. Enum：枚举类型。指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举，示例代码如下：

   class Article(Base):
       __tablename__ = 'article'
       id = Column(Integer,primary_key=True,autoincrement=True)
       tag = Column(Enum("python",'flask','django'))
   1
   2
   3
   4

   在Python3中，已经内置了enum这个枚举的模块，我们也可以使用这个模块去定义相关的字段。示例代码如下：

   class TagEnum(enum.Enum):
       python = "python"
       flask = "flask"
       django = "django"
   
   class Article(Base):
       __tablename__ = 'article'
       id = Column(Integer,primary_key=True,autoincrement=True)
       tag = Column(Enum(TagEnum))
   
   article = Article(tag=TagEnum.flask)
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11

8. Date：存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，可以使用datetime.date来指定。示例代码如下：

   class Article(Base):
       __tablename__ = 'article'
       id = Column(Integer,primary_key=True,autoincrement=True)
       create_time = Column(Date)
   
   article = Article(create_time=date(2017,10,10))
   1
   2
   3
   4
   5
   6

9. DateTime：存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用datetime.datetime来指定。示例代码如下：

   class Article(Base):
       __tablename__ = 'article'
       id = Column(Integer,primary_key=True,autoincrement=True)
       create_time = Column(DateTime)
   
   article = Article(create_time=datetime(2011,11,11,11,11,11))
   1
   2
   3
   4
   5
   6

10. Time：存储时间，可以存储时分秒。映射到数据库中也是time类型。在Python代码中，可以使用datetime.time来至此那个。示例代码如下：

    class Article(Base):
        __tablename__ = 'article'
        id = Column(Integer,primary_key=True,autoincrement=True)
        create_time = Column(Time)
    
    article = Article(create_time=time(hour=11,minute=11,second=11))
    1
    2
    3
    4
    5
    6

11. Text：存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型。

12. LONGTEXT：长文本类型，映射到数据库中是longtext类型。

字段常用的属性设置

1. primary_key：设置某个字段为主键。
2. autoincrement：设置这个字段为自动增长的。
3. default：设置某个字段的默认值。在发表时间这些字段上面经常用。
4. nullable：指定某个字段是否为空。默认值是True，就是可以为空。
5. unique：指定某个字段的值是否唯一。默认是False。
6. onupdate：在数据更新的时候会调用这个参数指定的值或者函数。在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用的就是update_time（每次更新数据的时候都要更新的值）。
7. name：指定ORM模型中某个属性映射到表中的字段名。如果不指定，那么会使用这个属性的名字来作为字段名。如果指定了，就会使用指定的这个值作为参数。这个参数也可以当作位置参数，在第1个参数来指定。
8. comment：设置该字段的注释

query可用参数与聚合函数

1. 模型对象：指定查找这个模型中所有的对象。

2. 模型中的属性：可以指定只查找某个模型的其中几个属性，会将数据的这几个属性打包成元组

   p1 = Person(name='张三', age=18)
   p2 = Person(name='李四', age=19)
   p3 = Person(name='王五', age=21)
   session.add_all([p1, p2, p3])
   session.commit()
   print(session.query(Person.name, Person.age).all())
   1
   2
   3
   4
   5
   6

   结果为

   [(‘张三’, 18), (‘李四’, 19), (‘王五’, 21)]

3. 聚合函数：需要从sqlalchemy导入func

   • func.count：统计行的数量。注意：结果存放在一个元组中，下同
   • func.avg：求平均值。
   • func.max：求最大值。
   • func.min：求最小值。
   • func.sum：求和。
     func上，其实没有任何聚合函数。但是因为他底层做了一些魔术，只要mysql中有的聚合函数，都可以通过func调用

   print(session.query(func.count(Person.id)).first())
   print(session.query(func.avg(Person.age)).first())
   print(session.query(func.max(Person.age)).first())
   1
   2
   3

   结果为

   (3,)
   (Decimal(‘19.3333’),)
   (21,)

filter过滤条件

过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行解释，并且这些过滤条件都是只能通过filter方法实现的：

1. equals：

   article = session.query(Article).filter(Article.title == "title0").first()
   print(article)
   1
   2

2. not equals:

   query.filter(User.name != 'ed')
   1

3. like：

   query.filter(User.name.like('%ed%'))
   1

4. in：

   query.filter(User.name.in_(['ed','wendy','jack']))
   # 同时，in也可以作用于一个Query
   query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))))
   1
   2
   3

5. not in：

   query.filter(~User.name.in_(['ed','wendy','jack']))
   # 或者是
   query.filter(User.name.notin_(['ed','wendy','jack']))
   1
   2
   3

6. is null：

   query.filter(User.name==None)
   # 或者是
   query.filter(User.name.is_(None))
   1
   2
   3

7. is not null:

   query.filter(User.name != None)
   # 或者是
   query.filter(User.name.isnot(None))
   1
   2
   3

8. and：

   from sqlalchemy import and_
   query.filter(and_(User.name=='ed',User.fullname=='Ed Jones'))
   # 或者是传递多个参数
   query.filter(User.name=='ed',User.fullname=='Ed Jones')
   # 或者是通过多次filter操作
   query.filter(User.name=='ed').filter(User.fullname=='Ed Jones')
   1
   2
   3
   4
   5
   6

9. or：

   from sqlalchemy import or_  
   query.filter(or_(User.name=='ed',User.name=='wendy'))
   1
   2

如果想要查看orm底层转换的sql语句，可以在filter方法后面不要再执行任何方法直接打印就可以看到了。比如：

articles = session.query(Article).filter(or_(Article.title=='abc',Article.content=='abc'))
        print(articles)
1
2

外键和约束

使用SQLAlchemy创建外键非常简单。在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和父表的主键字段类型保持一致。
示例代码如下：

from sqlalchemy import create_engine, Column, Integer, String, func, ForeignKey
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)
    content = Column(Text,nullable=False)

    uid = Column(Integer,ForeignKey("user.id"), ondelete="SET NULL")
1
2
3
4
5
6
7
8
9
10
11
12
13

外键约束，写在Colum中的关键字参数ondelete中，有以下几项：

1. RESTRICT：父表数据被删除，会阻止删除。默认就是这一项。
2. NO ACTION：在MySQL中，同RESTRICT。
3. CASCADE：级联删除。
4. SET NULL：父表数据被删除，子表数据会设置为NULL。

注意：这里设定的约束，仅对于从sql删除有效，若从ORM层面来删除数据，将会无视外键约束，全部视为set null

注意：ForeignKey里的字符串格式不是类名.属性名，而是表名.字段名

ORM关系以及一对多

mysql级别的外键，还不够ORM，必须拿到一个表的外键，然后通过这个外键再去另外一张表中查找，这样太麻烦了。SQLAlchemy提供了一个relationship，这个类可以定义属性，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。示例代码：

导入ralationship：

from sqlalchemy.orm import relationship
1

例如：

class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(10), nullable=False)
	# 关联，默认为一对多，有外键的是多，被引的是一，这个地方写的是类名
    books = relationship('Book', backref='author')
    def __repr__(self):
        return '<Author:(id={}, name={})>'.format(self.id, self.name)

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20), nullable=False)
    # 外键，表名.字段名
    author_id = Column(Integer, ForeignKey('author.id'))
    # 关联，默认为一对多，有外键的是多，被引的是一，这个地方写的是类名
    # author = relationship("Author", backref="books")

    def __repr__(self):
        return '<Book:(id={}, name={}, author_id={})>'.format(self.id, self.name, self.author_id)

# 插入数据
Base.metadata.drop_all()
Base.metadata.create_all()
author1 = Author(name='张三')
author2 = Author(name='李四')
book1 = Book(name='python从入门到入坟', author_id=1)
book2 = Book(name='如何让富婆喜欢你', author_id=1)
session.add_all([author1, author2, book1, book2])
session.commit()

# 查找
book = session.query(Book).get(1)
print(book.author)
author = session.query(Author).get(1)
print(author.books)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

结果为：

<Author:(id=1, name=张三)>
[<Book:(id=1, name=python从入门到入坟, author_id=1)>, <Book:(id=2, name=如何让富婆喜欢你, author_id=1)>]
1
2

另外，可以通过backref来指定反向访问的属性名称。articles是有多个。默认他们之间的关系是一个一对多的关系，有外键的是多，被引的是一。

relationship写在哪一方都可以

对于Book，每个对象都关联一个Author对象，所以其关联类型就是其Author

而对于Author，因为其关联了多个对象，所以关联类型是列表

注意：relationship的第一个参数写的不是表名，而是关联的ORM类名

关联插入

因为我们ORM处理的都是类和对象，所以对于有外键的类，我们都不喜欢直接给外键赋值，而希望可以用对象关联来自动给外键赋值，sqlalchemy确实有实现这样的功能，向数据库增加一条数据后，其关联属性的对象也会跟着加入，例如

# 插入数据
author1 = Author(name='张三')
author2 = Author(name='李四')
book1 = Book(name='python从入门到入坟')
book2 = Book(name='如何让富婆喜欢你')
book3 = Book(name='朝花夕拾')
# 关联插入
author1.books.append(book1)#列表添加
author1.books.append(book2)
book3.author = author2#直接赋值

session.add(author1)
session.add(book3)
session.commit()

# 查找
book = session.query(Book).get(3)
print(book.author)
author = session.query(Author).get(1)
print(author.books)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

结果为：

<Author:(id=2, name=李四)>
[<Book:(id=1, name=python从入门到入坟, author_id=1)>, <Book:(id=2, name=如何让富婆喜欢你, author_id=1)>]
1
2

因为author关联多个对象，所以其books的类型是列表，可以使用append方法添加关联对象

而book关联一个对象，所以其类型就是Author，直接赋值即可

一对一的关系：

在sqlalchemy中，如果想要将两个模型映射成一对一的关系，那么应该在父模型中，指定引用的时候，要传递一个uselist=False这个参数进去。就是告诉父模型，以后引用这个从模型的时候，不再是一个列表了，而是一个对象了。示例代码如下：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

    extend = relationship("UserExtend",uselist=False)

    def __repr__(self):
        return "<User(username:%s)>" % self.username

class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer,ForeignKey("user.id"))

    user = relationship("User",backref="extend")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

当然，也可以借助sqlalchemy.orm.backref来简化代码：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

    # extend = relationship("UserExtend",uselist=False)

    def __repr__(self):
        return "<User(username:%s)>" % self.username

class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer,ForeignKey("user.id"))

    user = relationship("User",backref=backref("extend",uselist=False))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这时，要想使用关联插入，两边的关联属性都直接赋值即可

多对多的关系：

根据数据库的只是，多对多的关系，应该采用加入一张中间表的方式来解决，中间表使用Table类来创建

导入Table类

from sqlalchemy import Table
1

1. 多对多的关系需要通过一张中间表来绑定他们之间的关系。
2. 先把两个需要做多对多的模型定义出来
3. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”。
4. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary=中间表。

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20), nullable=False)
    # 外键，表名.字段名
    author_id = Column(Integer, ForeignKey('author.id'))
    # 关联，默认为一对多，有外键的是多，被引的是一，这个地方写的是类名
    # author = relationship("Author", backref="books")
    # 多对多关联，secondary指定中间表，这样的话，book中有tags，tag中有books，其关联信息的外键将放到book_tag中
    tags = relationship('Tag', backref='books', secondary='book_tag')

    def __repr__(self):
        return '<Book:(id={}, name={}, author_id={})>'.format(self.id, self.name, self.author_id)


class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(10), nullable=False)
# 多对多中间表，第一个参数时表名，第二个参数传Base.metadata
book_tag = Table(
    'book_tag',
    Base.metadata,
    # 组合成复合主键
    Column('book_id', Integer, ForeignKey('book.id'), primary_key=True),
    Column('tag_id', Integer, ForeignKey('tag.id'), primary_key=True)
)

book1 = Book(name='python从入门到入坟')
book2 = Book(name='如何让富婆喜欢你')
tag1 = Tag(name='推理')
tag2 = Tag(name='言情')
book1.tags.append(tag1)
book1.tags.append(tag2)
book2.tags.append(tag1)
book2.tags.append(tag2)
session.add_all([book1, book2])
session.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

ORM层面的删除数据：

ORM层面删除数据，会无视mysql级别的外键约束。先将从表中的那个外键设置为NULL，再将对应的数据删除，。如果想要避免这种行为，应该将从表中的外键的nullable=False。
在SQLAlchemy，只要将一个数据添加到session中，和他相关联的数据都可以一起存入到数据库中了。这些是怎么设置的呢？其实是通过relationship的时候，有一个关键字参数cascade可以设置这些属性，多个属性使用英文逗号,隔开：

1. save-update：默认选项。在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。这种行为就是save-update属性影响的。
2. delete：表示当删除某一个模型中的数据的时候，是否也删掉使用relationship和他关联的数据。
3. delete-orphan：表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型中的relationship中，增加一个single_parent=True的参数。
4. merge：默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作。
5. expunge：移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除。
6. all：是对save-update, merge, refresh-expire, expunge, delete几种的缩写。

排序

1. order_by：调用查询对象的order_by方法，可以指定根据这个表中的某个字段进行排序

   其参数可以是类.属性，也可以是属性名字符串，推荐第一种写法

   对于类.属性形式，如果在前面加了一个-，代表的是降序排序。

   例如：

   class Book(Base):
       __tablename__ = 'book'
       id = Column(Integer, primary_key=True, autoincrement=True)
       name = Column(String(20), nullable=False)
       # 外键，表名.字段名
       author_id = Column(Integer, ForeignKey('author.id'))
       # 注意，datetime.now后不要加括号，加了之后，它就变成一个常数了，所有的Book默认值都是一个常数
       # 而不加括号，则会在每个对象添加进数据库的时候，自动调用datetime.now方法
       release_time = Column(DateTime, default=datetime.now)
   
       def __repr__(self):
           return '<Book:(name={}, release_time={})>'.format(self.name, self.release_time)
   
   # 类属性形式
   # 升序
   all_book = session.query(Book).order_by(Book.release_time).all()
   # 降序
   all_book = session.query(Book).order_by(-Book.release_time).all()
   all_book = session.query(Book).order_by(Book.release_time.desc()).all()
   # 字符串形式
   # 升序
   all_book = session.query(Book).order_by('release_time').all()
   
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23

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

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

     当通过关联获取对象时，会默认排序，降序需要用desc方法，例如：

     author = relationship("Author", backref=backref('books', order_by=release_time.desc()))
     1

     这样当通过Author对象获取其所有Book对象时，就会让Book对象降序排序

   • 在模型定义中，添加以下代码：

      __mapper_args__ = {
          "order_by": title
        }
     1
     2
     3

     即可让文章使用标题来进行排序。

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

limit、offset和切片操作：

1. limit：可以限制每次查询的时候只查询几条数据。

2. offset：可以限制查找数据的时候过滤掉前面多少条。

   # 选出从第11本书开始（包括）后面10本书，即第11至20本书
   part_book = session.query(Book).offset(10).limit(10).all() 
   1
   2

3. 切片：可以对Query对象使用切片操作，来获取想要的数据。可以使用slice(start,stop)方法来做切片操作。也可以使用[start:stop]的方式来进行切片操作。一般在实际开发中，中括号的形式是用得比较多的。希望大家一定要掌握。示例代码如下：

   # 拿到最后10本书
   books = session.query(Book).order_by(Book.id.desc())[0:10]
   1
   2

懒加载

在一对多，或者多对多的时候，如果想要获取多的这一部分的数据的时候，往往能通过一个属性就可以全部获取了。比如有一个作者，想要或者这个作者的所有文章，那么可以通过user.articles就可以获取所有的。但有时候我们不想获取所有的数据，比如只想获取这个作者今天发表的文章，那么这时候我们可以给relationship传递一个lazy=‘dynamic’，以后通过user.articles获取到的就不是一个列表，而是一个AppenderQuery对象了。这样就可以对这个对象再进行一层过滤和排序等操作。

通过lazy='dynamic'，获取出来的多的那一部分的数据，就是一个AppenderQuery对象了。这种对象既可以添加新数据，使用append方法，也可以跟Query一样，可以再进行一层过滤。
总而言之一句话：如果你在获取数据的时候，想要对多的那一边的数据再进行一层过滤，那么这时候就可以考虑使用lazy='dynamic'。
lazy可用的选项：

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

author = db.relationship('Author', backref='books', lazy='dynamic')
1

group_by：

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

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

分组常常配合聚合函数使用

having：

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

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

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()
1

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()
1
2