>>> 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
集合现在被加载,并且行为就像一个普通的列表