研究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

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 ("htmlcode">

> 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 = "htmlcode">

> 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')>]




由上可知，当我们访问addresses集合的时候，相关SQL语句才被执行，这也是延迟加载关系(惰性加载关系, lazy loading relationship)的例子，至此addresses集合方被作为普通列表加载了。