SQLAlchemy Relationship 配置

翻译自:Relationship Configuration

基本关系模式

下面每一节需要导入如下模块:

1
2
3
4
5
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

一对多

1
2
3
4
5
6
7
8
9
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'))

多对一

1
2
3
4
5
6
7
8
9
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)

一对一

1
2
3
4
5
6
7
8
9
10
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child = relationship("Child", uselist=False, back_populates="parent")

class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship("Parent", back_populates="child")

多对多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)

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

关联对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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")

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)

邻接列表关系

1
2
3
4
5
6
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('node.id'))
data = Column(String(50))
children = relationship("Node")
1
2
3
4
root --+---> child1
+---> child2 --+--> subchild1
| +--> subchild2
+---> child3
1
2
3
4
5
6
7
8
id       parent_id     data
--- ------- ----
1 NULL root
2 1 child1
3 1 child2
4 3 subchild1
5 3 subchild2
6 1 child3

反向链接关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

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

addresses = relationship("Address", backref="user")

class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user.id'))

配置关系联结(join)

配置与技巧

大数据下的配置

自定义访问

自定义集合实现

Collection Internals

特殊的关系持久模式

指向自己的行 / 相互依赖的行

可变主键 / 更新串联(Cascades)

关系 API