研究Python的ORM框架中的SQLAlchemy库的映射关系

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship, backref
 
>>> 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", backref=backref('addresses', order_by=id))
...
...   def __init__(self, email_address):
...     self.email_address = email_address
...
...   def __repr__(self):
...     return "〈Address('%s')〉" % self.email_address

让我们注意一下新出现的东东，首先就是user_id的ForeignKey结构，学过数据库的同学都知道ForeignKey意味着外键，这是关系型数据库的核心理论之一，即该列user_id与其外键引用的列users.id存在引用约束(constrained)关系，在数据库层面上来讲，就是表users的user_id列被表users的id列约束，值得注意的是，外键关联的必定是另外一张表的主键。

其次新出现的就是relationship()函数，这个将会告知ORM通过Address.userAddress类自身必须链接到User类。relationship()使用两个表的外键约束来判定这种链接的性质，比如说判定Address.user将会是多对一(many-to-one)关系。

另外在relationship()内还有另外一个函数称为backref()，它将提供一种用于反向查询的细节，比如说在对象User上的Address对象集是通过User.addresses属性引用，那么多对一的关系(many-to-one)反向总会是一对多关系(one-to-many)。还有对于Address.user和User.addresses的关系来说总是双向的。

假设使用了Declarative系统，那么relationship()的关系到远端类(remote class)的参数能够被指定为字符串。一旦所有的映射都被成功加载，那么这些字符串将会被计算出Python的表达式，再产生实际的参数(上文中User类的情况)。这些可以使用的字符串名字必须通过定义的基类创建好然后才被计算为实际的类参数，说白了，你字符串引用的类必须是ORM映射管理的类，然后这些类被映射完毕后，这些字符串才能被真正翻译为相应类的引用。

接下来我们举个例子同样创建用User取代Address的”addresses/user”双向关系：

class User(Base):
  # ....
  addresses = relationship("Address", order_by="Address.id", backref="user")

>>> Base.metadata.create_all(engine) 
PRAGMA table_info("users")
()
PRAGMA table_info("addresses")
()
CREATE TABLE addresses (
  id INTEGER NOT NULL,
  email_address VARCHAR NOT NULL,
  user_id INTEGER,
  PRIMARY KEY (id),
   FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT

>>> jack = User('jack', 'Jack Bean', 'gjffdd')
>>> jack.addresses
[]

>>> jack.addresses = [
...         Address(email_address='jack@google.com'),
...         Address(email_address='j25@yahoo.com')]

>>> jack.addresses[1]
<Address('j25@yahoo.com')>
 
>>> jack.addresses[1].user
<User('jack','Jack Bean', 'gjffdd')>

>>> session.add(jack)
>>> session.commit()
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('jack', 'Jack Bean', 'gjffdd')
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('jack@google.com', 5)
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('j25@yahoo.com', 5)
COMMIT

>>> jack = session.query(User).\
... filter_by(name='jack').one() 
BEGIN (implicit)
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 = ?
('jack',)
>>> jack
<User('jack','Jack Bean', 'gjffdd')>

>>> jack.addresses 
SELECT addresses.id AS addresses_id,
    addresses.email_address AS
    addresses_email_address,
    addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.id
(5,)
[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]