sqlalchemy 0.7 对象关系备忘录 (1)

sqlalchemy对象关系笔记，参照官方文档。0.7版本

One To Many¶ 一对多

1.单向

class Parent(Base): #父表
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child") #建立关系

class Child(Base): #子表
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))  #外键，关联父表主键id

操作： Parent.chidren()  #获取子表集合

2.双向

class Parent(Base): #父类
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref="parent") #添加backref属性，反向引用父类，many To One

class Child(Base): #子类
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

Many To One¶ 多对一

1.单向

class Parent(Base): #父类
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child")

class Child(Base): #子类
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

2.双向 （one To Many）

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", backref="parents") #原理同上

Many To Many¶ 多对多

参照SQLalchemy对象关系一文中的对应章节，有助于理解。

1.单向

#中间关系表
association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),  #外键，引用父类ID
    Column('right_id', Integer, ForeignKey('right.id')) #外键，引用子类ID
)

#父表
class Parent(Base):
    __tablename__ = 'left'  #表名
    id = Column(Integer, primary_key=True)
    children = relationship("Child", secondary=association_table) #增加scondary属性，指定中间表为association_table


#子表
class Child(Base):
    __tablename__ = 'right'    #表名
    id = Column(Integer, primary_key=True)  

2.双向

#中间表
association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')), #外键
    Column('right_id', Integer, ForeignKey('right.id')) #外键
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",secondary=association_table,backref="parents") #增加backref反向代理

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

Association Object¶ 关联对象

当中间表中增加了其他字段时，即不仅仅包括（id+外键+外键）三个字段时，就要用这种模式了。

1.单向

#中间类
class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True) #外键，父表ID
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True) #外键，子类ID
    extra_data = Column(String(50))  #额外字段
    child = relationship("Child") #与子类建立关系

#父类
class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association") #与中间类建立关系，就能自动与子类建立关系

#子类
class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

2.双向

#中间类
class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child", backref="parent_assocs")

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", backref="parent") #添加backref=“parent”属性，反向引用中间表

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

操作示例：

# create parent, append a child via association
p = Parent()
a = Association(extra_data="some data")
a.child = Child()
p.children.append(a)

# iterate through child objects via association, including association
# attributes
for assoc in p.children:
    print assoc.extra_data
    print assoc.child

Simplifying Scalar Collections  简化ManyToMany操作

1.原来

from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    kw = relationship("Keyword", secondary=lambda: userkeywords_table)

    def __init__(self, name):
        self.name = name

class Keyword(Base):
    __tablename__ = 'keyword'
    id = Column(Integer, primary_key=True)
    keyword = Column('keyword', String(64))

    def __init__(self, keyword):
        self.keyword = keyword

userkeywords_table = Table('userkeywords', Base.metadata,
    Column('user_id', Integer, ForeignKey("user.id"),
           primary_key=True),
    Column('keyword_id', Integer, ForeignKey("keyword.id"),
           primary_key=True)
)

操作：

>>> user = User('jek')  #创建user实例
>>> user.kw.append(Keyword('cheese inspector')) 为user添加keyword
>>> print(user.kw)
[<__main__.Keyword object at 0x12bf830>]
>>> print(user.kw[0].keyword)
cheese inspector
>>> print([keyword.keyword for keyword in user.kw])
['cheese inspector']

2.现在

from sqlalchemy.ext.associationproxy import association_proxy

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

    kw = relationship("Keyword", secondary=lambda: userkeywords_table)  #与Keyword建立关系

    def __init__(self, name):
        self.name = name

    # proxy the 'keyword' attribute from the 'kw' relationship
    keywords = association_proxy('kw', 'keyword')  #关系代理，绑定到keyword字段

操作示例：

>>> user = User('jek')
>>> user.keywords.append('cheese inspector') #为user增加keyword属性值，会自动新增Keyword记录，*这样就简化了有木有*
>>> user.keywords
['cheese inspector']
>>> user.keywords.append('snack ninja')
>>> user.kw
[<__main__.Keyword object at 0x12cdd30>, <__main__.Keyword object at 0x12cde30>]

primaryjoin

当一张表中的两个外接引用另一张表中的同一个字段，需要用primaryjoin

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)
#引用address表中主键
    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address",
                    primaryjoin="Address.id==Customer.billing_address_id")
    shipping_address = relationship("Address",
                    primaryjoin="Address.id==Customer.shipping_address_id")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)

本人能力有限，以上仅供参考。