建立关系

操作数据库中的表，表之间不能只是独立存在而和其他的表没有任何的依赖关系，所以我们接下来学习SQLAlchemy中的关系。

让我们考虑如何映射和查询与用户相关的第二个表。我们系统中的用户可以存储任意数量的与其用户名相关联的电子邮件地址。这意味着从用户到存储电子邮件地址的新表的基本一对多关联，我们称之为地址。使用声明，我们定义这个表及其映射类Address:

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship
>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...     user = relationship("User", back_populates="addresses")
...     def __repr__(self):
...             return "<Address(email_address='%s')>" % self.email_address

上面的类引入了ForeignKey结构，它是一个应用于Column的指令，表示列中的值是参照与其他的表中的某一列。这也是关系数据库的核心特性，就是我们说的外键约束。上面的ForeignKey表示，addresses.user_id列中的值应该参照users.id列中的值，就是参照users的主键。

从类定义中还使用了一个新的函数，称为 relationship（），告诉ORM，Address类本身应该使用属性Address.user链接到User类。 relationship（）使用两个表之间的外键关系来确定此连接的性质，确定Address.user将是多对一的关系。在用户映射类的属性User.addresses下放置一个附加relationship（）指令。在relationship（）指令中，参数relationship.back_populates被分配以引用补充属性名称;通过这样做，每个relationship()可以做出与反向表示相同关系的智能决策;Address.user引用一个User实例，User.addresses引用一个Address实例的列表,因为用户和地址是一对多的关系。

Note

relationship.back_populates参数是一个非常常见的SQLAlchemy功能的一个较新版本，称为relationship.backref。relationship.backref参数没并没有丢弃，将始终保持可用。

关于外键

• 大多数但是不是全部的关系型数据库中的FOREIGNKEY约束只能连接到主键列或者具有UNIQUE约束的列。
• 引用多列主键并且自身具有多个列的FORENGN KEY约束称为"复合外键"。
• FOREIGN KEY列可以自动更新自己，以响应引用列或行中的更改。这被称为CASCADE参考动作就是级联，并且是关系数据库的内置函数。
• FOREIGN KEY可以参考自己的表。这被称为“自引用”外键,就是有一些表不是依赖其他的表，而是参照自身的。

下面我们来创建我们的addresses表

 Base.metadata.create_all(ngine)
2017-03-10 17:20:26,394 INFO sqlalchemy.engine.base.Engine select version()
2017-03-10 17:20:26,395 INFO sqlalchemy.engine.base.Engine {}
2017-03-10 17:20:26,398 INFO sqlalchemy.engine.base.Engine select current_schema()
2017-03-10 17:20:26,398 INFO sqlalchemy.engine.base.Engine {}
2017-03-10 17:20:26,401 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-03-10 17:20:26,401 INFO sqlalchemy.engine.base.Engine {}
2017-03-10 17:20:26,403 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-03-10 17:20:26,404 INFO sqlalchemy.engine.base.Engine {}
2017-03-10 17:20:26,405 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2017-03-10 17:20:26,407 INFO sqlalchemy.engine.base.Engine {}
2017-03-10 17:20:26,408 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2017-03-10 17:20:26,410 INFO sqlalchemy.engine.base.Engine {'name': u'addresses'}
2017-03-10 17:20:26,417 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2017-03-10 17:20:26,417 INFO sqlalchemy.engine.base.Engine {'name': u'users'}
2017-03-10 17:20:26,421 INFO sqlalchemy.engine.base.Engine
CREATE TABLE addresses (
        id SERIAL NOT NULL,
        email_address VARCHAR NOT NULL,
        user_id INTEGER,
        PRIMARY KEY (id),
        FOREIGN KEY(user_id) REFERENCES users (id)
)


2017-03-10 17:20:26,426 INFO sqlalchemy.engine.base.Engine {}
2017-03-10 17:20:27,009 INFO sqlalchemy.engine.base.Engine COMMIT

使用相关的对象

现在我们创建一个User用户时，会出现一个空的地址addresses集合，这是因为我们给User新增加了一个属性叫做addresses，默认情况下这个集合是一个python列表。

>>> jack = User(name='jack', fullname='Jack Bean', password='gjfdd')
>>> jack.addresses
[]  # 访问User对象的addresses属性，可以看到是一个list类型，这是因为还没有任何的地址对象。

下面我们给这个列表添加两个地址对象

>>> jack.addresses = [Address(email_address='jack@qq.com'), Address(email_address='j12@gamil.com')]

当使用双向关系时，在一个方向上添加的元素自动在另一个方向上可见。不使用任何SQL语句就可以实现：

>>> jack.addresses[1]
<Address(email_address='j12@gamil.com')>
>>> jack.addresses[1].user
<User(name=jack, fullname=Jack Bean, password=gjfdd)>

从上面我们可以看见，直接使用User的addresses属性添加的地址对象，再通过地址对象访问User对象也是能访问的。

让我们将Jack用户添加并提交到数据库。 jack以及相应地址集合中的两个Address成员都使用称为级联的过程同时添加到会话：

>>> session.add(jack)
>>> session.commit()

现在来查询数据库看看：

>>> jack = session.query(User).filter(User.name=='jack').one()
2017-03-10 19:16:59,690 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-03-10 19:16:59,690 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.name = %(name_1)s
2017-03-10 19:16:59,690 INFO sqlalchemy.engine.base.Engine {'name_1': 'jack'}
>>> jack
<User(name=jack, fullname=Jack Bean, password=gjfdd)>

查询到jack用户后，看看是否有地址信息

>>> jack.addresses
2017-03-10 19:17:15,247 INFO sqlalchemy.engine.base.Engine SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM addresses
WHERE %(param_1)s = addresses.user_id ORDER BY addresses.id
2017-03-10 19:17:15,247 INFO sqlalchemy.engine.base.Engine {'param_1': 4}
[<Address(email_address='jack@qq.com')>, <Address(email_address='j12@gamil.com')>]
>>>

我们可以从上面打印的信息看出，在执行jack.addresses时，才执行了SQL查询语句。这就是延迟加载关系。

使用连接(joins)查询

现在我们已经有了两个表，我们可以展示一些Query的新特性，特别是如何处理两个表的查询。

要在User和Address之间构造一个简单的隐式连接，我们可以使用Query.filter（）来将它们相关的列等同在一起。下面我们使用这种方法一次加载用户和地址实体。

>>> for u, a in session.query(User, Address).filter(User.id==Address.user_id).filter(Address.email_address=='jack@qq.com').all():
...     print u
...     print a
...
2017-03-10 19:48:39,792 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM users, addresses
WHERE users.id = addresses.user_id AND addresses.email_address = %(email_address_1)s
2017-03-10 19:48:39,795 INFO sqlalchemy.engine.base.Engine {'email_address_1': 'jack@qq.com'}
<User(name=jack, fullname=Jack Bean, password=gjfdd)>
<Address(email_address='jack@qq.com')>
>>>

从运行日志可以看出其实就是转化成SQL语句：

SELECT users.id, name, password, adddresses.id, email_address, user_id FROM users, addresses
WHERE users.id=addresses.user_id
AND addresses.email_address='jack@qq.com'

我们学习过数据库知道，当对两个表进行连接查询时，如果两个表中列名有重名，需要使用tableName.columnName来做区分，但是使大量使用连接查询会影响数据库性能。

另一方面，实际的SQL JOIN语法使用````Query.join()```方法更加容易实现。

>>> session.query(User).join(Address).filter(Address.email_address=='jack@qq.com').all()
2017-03-10 19:53:49,559 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = %(email_address_1)s
2017-03-10 19:53:49,559 INFO sqlalchemy.engine.base.Engine {'email_address_1': 'jack@qq.com'}
[<User(name=jack, fullname=Jack Bean, password=gjfdd)>]
>>>

数据库中我们学习过自然连接，使用join()方法将两张表连接，，然后对连接后的表进行过滤。由于query()方法中的参数是User,所以连接后的表会抽取指定的列。

Query.join()知道如何将User和Address进行连接，这是因为他们之间有唯一的外键关联，如果没有外键或者有几个外键，Query.join()想更好的工作需要使用下面的方式：

query.join(Address, User.id==Address.user_id)    # 显示的指定
query.join(User.addresses)                       # 指定从左到右的关系
query.join(Address, User.addresses)             
query.join('addresses')                          # 使用字符串

疑问？#####

为什么使用字符串就可以？我认为join()方法中的字符串会被转化成python表达式。

关于数据库中的多个表格进行连接，分为自然连接、等值连接。自然连接中可能出现悬浮元组，为了避免丢掉悬浮元组，使用外连接将这些悬浮元组中填上NULL。对于只保留左边的悬浮元组就叫做左连接，同理又有右连接。
左连接我们使用

query.outerjoin(User.addresses)   # LEFT OUTER JOIN

如果有多个实体,Query该怎么选择？

操作数据库肯定会遇到同时需要操作多个表格，通常Query.join()选择最左边的实体进行连接，要控制Join列表中的第一个实体，使用Query.select_from()方法。

>>> session.query(User, Address).select_from(Address).join(User).filter(Address.email_address=='jack@qq.com').all()
2017-03-10 20:15:59,964 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM addresses JOIN users ON users.id = addresses.user_id
WHERE addresses.email_address = %(email_address_1)s
2017-03-10 20:15:59,964 INFO sqlalchemy.engine.base.Engine {'email_address_1': 'jack@qq.com'}
[(<User(name=jack, fullname=Jack Bean, password=gjfdd)>, <Address(email_address='jack@qq.com')>)]
>>>

这个方法主要是控制连个表连接时在前面，就是谁是主动连的一方，使用上面的语句，主动连接的一方是Address。

使用别名

当我们查询多个表时，如果相同的表要使用多次，SQL通常需要该表使用另一个名称进行别名，以便可以区分该表与其他出现的表。下面我们加入到地址实体两次，以找到同时有两个不同电子邮件地址的用户：

>>> from sqlalchemy.orm import aliased
>>> adalias1 = aliased(Address) # 别名1
>>> adalias2 = aliased(Address) # 别名2
>>> for username, email1, email2 in \
... session.query(User.name, adalias1.email_address, adalias2.email_address).\
... join(adalias1, User.addresses).\
... join(adalias2, User.addresses).\
...filter(adalias1.email_address=='jack@google.com').\
...filter(adalias2.email_address=='j25@yahoo.com'):
... print username, email1, email2
jack jack@google.com j25@yahoo.com

使用EXISTS

SQL中的EXISTS关键字是一个布尔运算符，如果给定的表达式包含任何行，则返回True。它可以在许多情况下用于代替联接，并且也用于定位在相关表中没有对应行的行。

>>> from sqlalchemy.sql import exists
>>> stmt = exists().where(Address.user_id==User.id)
>>> for name in session.query(User.name).filter(stmt):
...     print name
...
2017-03-10 20:30:51,213 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT *
FROM addresses
WHERE addresses.user_id = users.id)
2017-03-10 20:30:51,213 INFO sqlalchemy.engine.base.Engine {}
(u'jack',)

今天先学到这了，继续学没有效率！