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¶ 关联对象
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)
本人能力有限,以上仅供参考。