第七章 SQLAlchemy(二)

6.5 第五节 SQLAlchemy的ORM（3）

查找方法：

介绍完过滤条件后，有一些经常用到的查找数据的方法也需要解释一下：

1. all()：返回一个Python列表（list）：

   query = session.query(User).filter(User.name.like(‘%ed%‘).order_by(User.id)
   # 输出query的类型
   print type(query)
   > <type ‘list‘>
   # 调用all方法
   query = query.all()
   # 输出query的类型
   print type(query)
   > <class ‘sqlalchemy.orm.query.Query‘>

2. first()：返回Query中的第一个值：

   user = session.query(User).first()
   print user
   > <User(name=‘ed‘, fullname=‘Ed Jones‘, password=‘f8s7ccs‘)>

3. one()：查找所有行作为一个结果集，如果结果集中只有一条数据，则会把这条数据提取出来，如果这个结果集少于或者多于一条数据，则会抛出异常。总结一句话：有且只有一条数据的时候才会正常的返回，否则抛出异常：

   # 多于一条数据
   user = query.one()
   > Traceback (most recent call last):
   > ...
   > MultipleResultsFound: Multiple rows were found for one()
   # 少于一条数据
   user = query.filter(User.id == 99).one()
   > Traceback (most recent call last):
   > ...
   > NoResultFound: No row was found for one()
   # 只有一条数据
   > query(User).filter_by(name=‘ed‘).one()

4. one_or_none()：跟one()方法类似，但是在结果集中没有数据的时候也不会抛出异常。

5. scalar()：底层调用one()方法，并且如果one()方法没有抛出异常，会返回查询到的第一列的数据：

   session.query(User.name,User.fullname).filter_by(name=‘ed‘).scalar()

文本SQL：

SQLAlchemy还提供了使用文本SQL的方式来进行查询，这种方式更加的灵活。而文本SQL要装在一个text()方法中，看以下例子：

from sqlalchemy import text
for user in session.query(User).filter(text("id<244")).order_by(text("id")).all():
    print user.name

如果过滤条件比如上例中的244存储在变量中，这时候就可以通过传递参数的形式进行构造：

session.query(User).filter(text("id<:value and name=:name")).params(value=224,name=‘ed‘).order_by(User.id)

在文本SQL中的变量前面使用了:来区分，然后使用params方法，指定需要传入进去的参数。另外，使用from_statement方法可以把过滤的函数和条件函数都给去掉，使用纯文本的SQL:

sesseion.query(User).from_statement(text("select * from users where name=:name")).params(name=‘ed‘).all()

使用from_statement方法一定要注意，from_statement返回的是一个text里面的查询语句，一定要记得调用all()方法来获取所有的值。

计数（Count）：

Query对象有一个非常方便的方法来计算里面装了多少数据：

session.query(User).filter(User.name.like(‘%ed%‘)).count()

当然，有时候你想明确的计数，比如要统计users表中有多少个不同的姓名，那么简单粗暴的采用以上count是不行的，因为姓名有可能会重复，但是处于两条不同的数据上，如果在原生数据库中，可以使用distinct关键字，那么在SQLAlchemy中，可以通过func.count()方法来实现：

from sqlalchemy import func
session.query(func.count(User.name),User.name).group_by(User.name).all()
# 输出的结果
> [(1, u‘ed‘), (1, u‘fred‘), (1, u‘mary‘), (1, u‘wendy‘)]

另外，如果想实现select count(*) from users，可以通过以下方式来实现：

session.query(func.count(*)).select_from(User).scalar()

当然，如果指定了要查找的表的字段，可以省略select_from()方法：

session.query(func.count(User.id)).scalar()

6.6 第六节 SQLAlchemy的ORM（4）

表关系：

表之间的关系存在三种：一对一、一对多、多对多。而SQLAlchemy中的ORM也可以模拟这三种关系。因为一对一其实在SQLAlchemy中底层是通过一对多的方式模拟的，所以先来看下一对多的关系：

外键：

在Mysql中，外键可以让表之间的关系更加紧密。而SQLAlchemy同样也支持外键。通过ForeignKey类来实现，并且可以指定表的外键约束。相关示例代码如下：

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‘))

    def __repr__(self):
        return "<Article(title:%s)>" % self.title

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

外键约束有以下几项：

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

一对多：

拿之前的User表为例，假如现在要添加一个功能，要保存用户的邮箱帐号，并且邮箱帐号可以有多个，这时候就必须创建一个新的表，用来存储用户的邮箱，然后通过user.id来作为外键进行引用，先来看下邮箱表的实现：

from sqlalchemy import ForeignKey
    from sqlalchemy.orm import relationship

    class Address(Base):
        __tablename__ = ‘address‘
        id = Column(Integer,primary_key=True)
        email_address = Column(String,nullable=False)
        # User表的外键，指定外键的时候，是使用的是数据库表的名称，而不是类名
        user_id = Column(Integer,ForeignKey(‘users.id‘))
        # 在ORM层面绑定两者之间的关系，第一个参数是绑定的表的类名，
        # 第二个参数back_populates是通过User反向访问时的字段名称
        user = relationship(‘User‘,back_populates="addresses")

        def __repr__(self):
            return "<Address(email_address=‘%s‘)>" % self.email_address

    # 重新修改User表，添加了addresses字段，引用了Address表的主键
    class User(Base):
        __tablename__ = ‘users‘
        id = Column(Integer,primary_key=True)
        name = Column(String(50))
        fullname = Column(String(50))
        password = Column(String(100))
        # 在ORM层面绑定和`Address`表的关系
        addresses = relationship("Address",order_by=Address.id,back_populates="user")

其中，在User表中添加的addresses字段，可以通过User.addresses来访问和这个user相关的所有address。在Address表中的User字段，可以通过Address.user来访问这个user。达到了双向绑定。表关系已经建立好以后，接下来就应该对其进行操作，先看以下代码：

jack = User(name=‘jack‘,fullname=‘Jack Bean‘,password=‘gjffdd‘)
  jack.addresses = [Address(email_address=‘‘),
                  Address(email_address=‘‘)]
  session.add(jack)
  session.commit()

首先，创建一个用户，然后对这个jack用户添加两个邮箱，最后再提交到数据库当中，可以看到这里操作Address并没有直接进行保存，而是先添加到用户里面，再保存。

一对一：

一对一其实就是一对多的特殊情况，从以上的一对多例子中不难发现，一对应的是User表，而多对应的是Address，也就是说一个User对象有多个Address。因此要将一对多转换成一对一，只要设置一个User对象对应一个Address对象即可，看以下示例：

class User(Base):
    __tablename__ = ‘users‘
    id = Column(Integer,primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(100))
    # 设置uselist关键字参数为False
    addresses = relationship("Address",back_populates=‘addresses‘,uselist=False)
  class Address(Base):
    __tablename__ = ‘addresses‘
    id = Column(Integer,primary_key=True)
    email_address = Column(String(50))
    user_id = Column(Integer,ForeignKey(‘users.id‘)
    user = relationship(‘Address‘,back_populates=‘user‘)

从以上例子可以看到，只要在User表中的addresses字段上添加uselist=False就可以达到一对一的效果。设置了一对一的效果后，就不能添加多个邮箱到User.addresses字段了，只能添加一个：

user.addresses = Address(email_address=‘‘)

多对多：

多对多需要一个中间表来作为连接，同理在SQLAlchemy中的ORM也需要一个中间表。假如现在有一个Teacher和一个Classes表，即老师和班级，一个老师可以教多个班级，一个班级有多个老师，是一种典型的多对多的关系，那么通过SQLAlchemy的ORM的实现方式如下：

association_table = Table(‘teacher_classes‘,Base.metadata,
  Column(‘teacher_id‘,Integer,ForeignKey(‘teacher.id‘)),
  Column(‘classes_id‘,Integer,ForeignKey(‘classes.id‘))
  )
  class Teacher(Base):
    __tablename__ = ‘teacher‘
    id = Column(Integer,primary_key=True)
    tno = Column(String(10))
    name = Column(String(50))
    age = Column(Integer)
    classes = relationship(‘Classes‘,secondary=association_table,back_populates=‘teachers‘)
  class Classes(Base):
    __tablename__ = ‘classes‘
    id = Column(Integer,primary_key=True)
    cno = Column(String(10))
    name = Column(String(50))
    teachers = relationship(‘Teacher‘,secondary=association_table,back_populates=‘classes‘)

要创建一个多对多的关系表，首先需要一个中间表，通过Table来创建一个中间表。上例中第一个参数teacher_classes代表的是中间表的表名，第二个参数是Base的元类，第三个和第四个参数就是要连接的两个表，其中Column第一个参数是表示的是连接表的外键名，第二个参数表示这个外键的类型，第三个参数表示要外键的表名和字段。
创建完中间表以后，还需要在两个表中进行绑定，比如在Teacher中有一个Classes属性，来绑定Classes表，并且通过secondary参数来连接中间表。同理，Classes表连接Teacher表也是如此。定义完类后，之后就是添加数据，请看以下示例：

teacher1 = Teacher(tno=‘t1111‘,name=‘xiaotuo‘,age=10)
  teacher2 = Teacher(tno=‘t2222‘,name=‘datuo‘,age=10)
  classes1 = Classes(cno=‘c1111‘,name=‘english‘)
  classes2 = Classes(cno=‘c2222‘,name=‘math‘)
  teacher1.classes = [classes1,classes2]
  teacher2.classes = [classes1,classes2]
  classes1.teachers = [teacher1,teacher2]
  classes2.teachers = [teacher1,teacher2]
  session.add(teacher1)
  session.add(teacher2)
  session.add(classes1)
  session.add(classes2)

6.7 第七节 SQLAlchemy的ORM（6）

ORM层面的CASCADE：

如果将数据库的外键设置为RESTRICT，那么在ORM层面，删除了父表中的数据，那么从表中的数据将会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：可以指定根据这个表中的某个字段进行排序，如果在前面加了一个-，代表的是降序排序。

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

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

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

     __mapper_args__ = {
          "order_by": title
        }

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

3. 正向排序和反向排序：默认情况是从小到大，从前到后排序的，如果想要反向排序，可以调用排序的字段的desc方法。

limit、offset和切片：

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

懒加载：

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

查询高级：

group_by：

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

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

having：

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

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

join方法：

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

for u,a in session.query(User,Address).filter(User.id==Address.user_id).all():
    print u
    print a
  # 输出结果：
  > <User (id=1,name=‘ed‘,fullname=‘Ed Jason‘,password=‘123456‘)>
  > <Address id=4,,user_id=1>

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

for u,a in session.query(User,Address).join(Address).all():
    print u
    print a
  # 输出结果：
  > <User (id=1,name=‘ed‘,fullname=‘Ed Jason‘,password=‘123456‘)>
  > <Address id=4,,user_id=1>

当然，如果采用outerjoin，可以获取所有User，而不用在乎这个User是否有Address对象，并且outerjoin默认为左外查询：

for instance in session.query(User,Address).outerjoin(Address).all():
    print instance
  # 输出结果：
  (<User (id=1,name=‘ed‘,fullname=‘Ed Jason‘,password=‘123456‘)>, <Address id=4,,user_id=1>)
  (<User (id=2,name=‘xt‘,fullname=‘xiaotuo‘,password=‘123‘)>, None)

别名：

当多表查询的时候，有时候同一个表要用到多次，这时候用别名就可以方便的解决命名冲突的问题了：

from sqlalchemy.orm import aliased
  adalias1 = aliased(Address)
  adalias2 = aliased(Address)
  for username,email1,email2 in session.query(User.name,adalias1.email_address,adalias2.email_address).join(adalias1).join(adalias2).all():
    print username,email1,email2

子查询：

SQLAlchemy也支持子查询，比如现在要查找一个用户的用户名以及该用户的邮箱地址数量。要满足这个需求，可以在子查询中找到所有用户的邮箱数（通过group by合并同一用户），然后再将结果放在父查询中进行使用：

from sqlalchemy.sql import func
  # 构造子查询
  stmt = session.query(Address.user_id.label(‘user_id‘),func.count(*).label(‘address_count‘)).group_by(Address.user_id).subquery()
  # 将子查询放到父查询中
  for u,count in session.query(User,stmt.c.address_count).outerjoin(stmt,User.id==stmt.c.user_id).order_by(User.id):
    print u,count

从上面我们可以看到，一个查询如果想要变为子查询，则是通过subquery()方法实现，变成子查询后，通过子查询.c属性来访问查询出来的列。以上方式只能查询某个对象的具体字段，如果要查找整个实体，则需要通过aliased方法，示例如下：

stmt = session.query(Address)
  adalias = aliased(Address,stmt)
  for user,address in session.query(User,stmt).join(stmt,User.addresses):
    print user,address

6.8 第八节 Flask-SQLAlchemy插件

另外一个框架，叫做Flask-SQLAlchemy对SQLAlchemy进行了一个简单的封装，使得我们在flask中使用SQLAlchemy更加的简单。可以通过pip install flask-sqlalchemy。使用Flask-SQLAlchemy的流程如下：

• 数据库初始化：数据库初始化不再是通过create_engine，请看以下示例：

  from flask import Flask
  from flask_sqlalchemy import SQLAlchemy
  from constants import DB_URI
  app = Flask(__name__)
  app.config[‘SQLALCHEMY_DATABASE_URI‘] = DB_URI
  db = SQLAlchemy(app)

• ORM类：之前都是通过Base = declarative_base()来初始化一个基类，然后再继承，在Flask-SQLAlchemy中更加简单了（代码依赖以上示例）：

  class User(db.Model):
    id = db.Column(db.Integer,primary_key=True)
    username = db.Column(db.String(80),unique=True)
    email = db.Column(db.String(120),unique=True)
    def __init__(self,username,email):
        self.username = username
        self.email = email
    def __repr__(self):
        return ‘<User %s>‘ % self.username

• 映射模型到数据库表：使用Flask-SQLAlchemy所有的类都是继承自db.Model，并且所有的Column和数据类型也都成为db的一个属性。但是有个好处是不用写表名了，Flask-SQLAlchemy会自动将类名小写化，然后映射成表名。
  写完类模型后，要将模型映射到数据库的表中，使用以下代码创建所有的表：

  db.create_all()

• 添加数据：这时候就可以在数据库中看到已经生成了一个User表了。接下来添加数据到表中：

  admin = User(‘admin‘,‘‘)
  guest = User(‘guest‘,‘‘)
  db.session.add(admin)
  db.session.add(guest)
  db.session.commit()

  添加数据和之前的没有区别，只是session成为了一个db的属性。

• 查询数据：查询数据不再是之前的session.query了，而是将Query属性放在了db.Model上，所以查询就是通过Model.query的方式进行查询了：

  users = User.query.all()
  # 再如：
  admin = User.query.filter_by(username=‘admin‘).first()
  # 或者：
  admin = User.query.filter(User.username=‘admin‘).first()

• 删除数据：删除数据跟添加数据类似，只不过session是db的一个属性而已：

  db.session.delete(admin)
  db.session.commit()