ORM SQLAlchemy

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', password='%s')>" % ( self.name, self.fullname, self.password)

>>> 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 __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address



现在，当我们创建一个User对象、将出现一个空白Addresses集合

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

向User对象自由的添加Address对象

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

当使用一个双向关系时, 元素在一侧被添加后，会自动在出现在另一侧。这种行为的发生，基于属性的改变事件，并且由 Python 判断,不需要使用任何SQL语句

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

>>> jack.addresses[1].user
<User(name='jack', fullname='Jack Bean', password='gjffdd')>

将Jack Bean添加到数据库会话，并提交到数据库。jack以及相应的addresses集合中的两个Address成员都被一次性添加到会话中, 这使用了一个叫级联的处理

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

看一下addresses集合。观察SQL

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

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

当我们访问addresses集合时,SQL突然提交了。这是一个延迟加载的例子。addresses集合现在被加载，并且行为就像一个普通的列表