SQLAlchemy_定義(一對一/一對多/多對多)關系

• 目錄
• Basic?Relationship?Patterns
• One To Many
• One To One
• Many To Many

Basic Relationship Patterns

基本關系模式?
The imports used for each of the following sections is as follows:?
下列的 import 語句，應用到接下來所有的代章節中：

?

1 2 3 4

from sqlalchemy import Table, Column, Integer, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base()

One To Many

A one to many relationship places a foreign key on the child table referencing the parent.?
表示一對多的關系時，在子表類中通過 foreign key (外鍵)引用父表類。?
relationship()?is then specified on the parent, as referencing a collection of items represented by the child:?
然后，在父表類中通過?relationship()?方法來引用子表的類：

?

1 2 3 4 5 6 7 8 9 10 11

class Parent(Base): ????__tablename__ = 'parent' ????id = Column(Integer, primary_key=True) ????children = relationship("Child") ???# 在父表類中通過 relationship() 方法來引用子表的類集合 class Child(Base): ????__tablename__ = 'child' ????id = Column(Integer, primary_key=True) ????parent_id = Column(Integer, ForeignKey('parent.id')) ????# 在子表類中通過 foreign key (外鍵)引用父表的參考字段

?To establish a bidirectional relationship in one-to-many, where the “reverse” side is a many to one,?
在一對多的關系中建立雙向的關系，這樣的話在對方看來這就是一個多對一的關系，?
specify an additional?relationship()?and connect the two using the?relationship.back_populates?parameter:?
在子表類中附加一個?relationship()?方法，并且在雙方的?relationship()?方法中使用?relationship.back_populates?方法參數：

?

1 2 3 4 5 6 7 8 9 10 11 12

class Parent(Base): ????__tablename__ = 'parent' ????id = Column(Integer, primary_key=True) ????children = relationship("Child", 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="children") ????# 子表類中附加一個 relationship() 方法 ????# 并且在(父)子表類的 relationship() 方法中使用 relationship.back_populates 參數

?Child will get a parent attribute with many-to-one semantics.?
這樣的話子表將會在多對一的關系中獲得父表的屬性

Alternatively, the backref option may be used on a single relationship() instead of using back_populates:?
或者，可以在單一的?relationship()?方法中使用?backref?參數來代替?back_populates?參數：

?

1 2 3 4 5 6 7 8 9

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

?

One To One

One To One is essentially a bidirectional relationship with a scalar attribute on both sides.?
一對一是兩張表之間本質上的雙向關系。?
To achieve this, the uselist flag indicates the placement of a scalar attribute instead of a collection on the “many” side of the relationship.?
要做到這一點，只需要在一對多關系基礎上的父表中使用?uselist?參數來表示。?
To convert one-to-many into one-to-one:

?

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

?To convert many-to-one into one-to-one:

?

1 2 3 4 5 6 7 8 9 10

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

?As always, the?relationship.backref?and?backref()?functions may be used in lieu of the?relationship.back_populates?approach; to specifyuselist?on a?backref, use the?backref()?function:

同樣的，可以使用下面這種方式：

?

1 2 3 4 5 6 7 8 9 10 11 12

from sqlalchemy.orm import backrefclass Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) child_id = Column(Integer, ForeignKey('child.id')) child = relationship("Child", backref=backref("parent", uselist=False))

Many To Many

Many to Many adds an association table between two classes.?
多對多關系會在兩個類之間增加一個關聯的表。?
The association table is indicated by the?secondary?argument to?relationship().?
這個關聯的表在?relationship()?方法中通過?secondary?參數來表示。?
Usually, the Table uses the MetaData object associated with the declarative base class,?
通常的，這個表會通過?MetaData?對象來與聲明基類關聯，?
so that the?ForeignKey?directives can locate the remote tables with which to link:?
所以這個?ForeignKey?指令會使用鏈接來定位到遠程的表：

定義中間表可以定義中間關系表相關的類，也可以直接通過Base.metdata生成對應關系表對象，不過基于code first準則，還是推薦將中間關系寫成類。

構建第三張關系類實現多對多。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

Base = declarative_base() #生成sqlorm基礎類 class HostUserToGroup(Base): ????__tablename__ = 'hostuser_to_group' # 表名hostuser_to_group ????nid = Column(Integer, primary_key=True,autoincrement=True) ????hostuser_id = Column(Integer,ForeignKey('host_user.id'),primary_key=True)# 外鍵關聯host_user表的id字段 ????group_id = Column(Integer,ForeignKey('group.id'),primary_key=True) # 外鍵關聯group表的id字段 class UserProfileToGroup(Base): ????__tablename__ = 'userprofile_to_group'# 表名userprofile_to_group ????nid = Column(Integer, primary_key=True,autoincrement=True) ????userprofile_id = Column(Integer,ForeignKey('user_profile.id'),primary_key=True)# 外鍵關聯user_profile表的id字段 ????group_id = Column(Integer,ForeignKey('group.id'),primary_key=True)# 外鍵關聯group表的id字段 class UserProfileToHostUser(Base): ????__tablename__ = 'userprofile_to_hostuser'# 表名userprofile_to_hostuser ????nid = Column(Integer, primary_key=True,autoincrement=True) ????userprofile_id = Column(Integer,ForeignKey('user_profile.id'),primary_key=True)# 外鍵關聯user_profile表的id字段 ????hostuser_id = Column(Integer,ForeignKey('host_user.id'),primary_key=True)# 外鍵關聯host_user表的id字段 class Host(Base): ????__tablename__ = 'host' #表名host ????id = Column(Integer, primary_key= True, autoincrement= True)# id字段，主鍵，自動增長 ????hostname = Column(String(64),unique= True,nullable= False)# hostname字段，唯一，不能為空 ????ip_addr = Column(String(64),unique= True,nullable= False)#ip_addr字段，唯一，不能為空 ????port = Column(Integer, default = 22) # port字段，整形，默認22 ????def __repr__(self): ????????return "<Hostobject:id=%s, hostname=%s, ip_addr=%s, port=%s>" %(self.id, self.hostname, self.ip_addr, self.port) class Group(Base): ????__tablename__ = 'group' # 表名group ????id = Column(Integer, primary_key = True) # id字段，主鍵，自動增長 ????name = Column(String(64), unique = True, nullable = False) # name字段，唯一，不為空 ????def __repr__(self): ????????return "<Groupobject: id=%s, name=%s>" %(self.id, self.name) class UserProfile(Base): ????__tablename__ = 'user_profile' # 表名user_profile ????id = Column(Integer, primary_key = True) # id字段，主鍵，自動增長 ????username = Column(String(64), unique = True, nullable = False) # username字段，唯一，不為空 ????password = Column(String(255), nullable = False) # password字段，不為空 ????hostusers = relationship('HostUser', secondary = UserProfileToHostUser.__tablename__, backref = 'user_profiles') # 多對多關聯HostUser表類（注意不是表名），中間表類UserProfileToHostUser（注意不是表名），反向字段為user_profiles ????groups = relationship('Group', secondary = UserProfileToGroup.__tablename__, backref = 'user_profiles') # 多對多關聯Group表類（注意不是表名），中間表類UserProfileToGroup（注意不是表名），反向字段為user_profiles ????def __repr__(self): ????????return "<UserProfileobject: id=%s, username=%s>" %(self.id, self.username) class HostUser(Base): ????__tablename__ = 'host_user' # 表名host_user ????id = Column(Integer, primary_key = True) # id字段，主鍵，自動增長 ????host_id = Column(Integer, ForeignKey('host.id')) # host_id，外鍵關聯host表的id字段 ????AuthTypes = [ ????????(u'ssh-password', u'SSH/Password'), ????????(u'ssh-key', u'SSH/Key'), ????] # 選項列表 ????auth_type = Column(ChoiceType(AuthTypes)) # auth_type字段，只能是選項列表里規定的值 ????username = Column(String(64), nullable = True) # username字段，不為空 ????password = Column(String(255)) # password字段 ????host = relationship('Host', backref = 'host_users') ????groups = relationship('Group', secondary = HostUserToGroup.__tablename__, backref = 'host_users') # 多對多關聯Group表類（注意不是表名），中間表類HostUserToGroup（注意不是表名），反向字段為host_users ????__table_args = (UniqueConstraint('host_id', 'username', name = '_host_username_uc')) # host_id和username組成聯合唯一約束 ????def __repr__(self): ????????return "<HostUserobject: id=%s, host_id=%s, username=%s>" %(self.id, self.host_id, self.username)

?

構建關系表實現多對多實例如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

from sqlalchemy import create_engine,and_,or_,func,Table from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,\ ????ForeignKey, UniqueConstraint, DateTime from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy_utils import ChoiceType,PasswordType from datetime import datetime Base = declarative_base() #生成sqlorm基礎類 HostUserToGroup = Table('hostuser_to_group', Base.metadata,? # 表名hostuser_to_group ????Column('hostuser_id', ForeignKey('host_user.id'), primary_key = True), # 外鍵關聯host_user表的id字段 ????Column('group_id', ForeignKey('group.id'), primary_key = True), # 外鍵關聯group表的id字段 ) UserProfileToGroup = Table('userprofile_to_group', Base.metadata, # 表名userprofile_to_group ????Column('userprofile_id', ForeignKey('user_profile.id'), primary_key = True), # 外鍵關聯user_profile表的id字段 ????Column('group_id', ForeignKey('group.id'), primary_key = True), # 外鍵關聯group表的id字段 ) UserProfileToHostUser = Table('userprofile_to_hostuser', Base.metadata, # 表名userprofile_to_hostuser ????Column('userprofile_id', ForeignKey('user_profile.id'), primary_key = True), # 外鍵關聯user_profile表的id字段 ????Column('hostuser_id', ForeignKey('host_user.id'), primary_key = True), # 外鍵關聯host_user表的id字段 ) class Host(Base): ????__tablename__ = 'host' #表名host ????id = Column(Integer, primary_key= True, autoincrement= True)# id字段，主鍵，自動增長 ????hostname = Column(String(64),unique= True,nullable= False)# hostname字段，唯一，不能為空 ????ip_addr = Column(String(64),unique= True,nullable= False)#ip_addr字段，唯一，不能為空 ????port = Column(Integer, default = 22) # port字段，整形，默認22 ????def __repr__(self): ????????return "<Hostobject:id=%s, hostname=%s, ip_addr=%s, port=%s>" %(self.id, self.hostname, self.ip_addr, self.port) class Group(Base): ????__tablename__ = 'group' # 表名group ????id = Column(Integer, primary_key = True) # id字段，主鍵，自動增長 ????name = Column(String(64), unique = True, nullable = False) # name字段，唯一，不為空 ????def __repr__(self): ????????return "<Groupobject: id=%s, name=%s>" %(self.id, self.name) class UserProfile(Base): ????__tablename__ = 'user_profile' # 表名user_profile ????id = Column(Integer, primary_key = True) # id字段，主鍵，自動增長 ????username = Column(String(64), unique = True, nullable = False) # username字段，唯一，不為空 ????password = Column(String(255), nullable = False) # password字段，不為空 ????hostusers = relationship('HostUser', secondary = UserProfileToHostUser, backref = 'user_profiles') # 多對多關聯HostUser表類（注意不是表名），中間表類UserProfileToHostUser（注意不是表名），反向字段為user_profiles ????groups = relationship('Group', secondary = UserProfileToGroup, backref = 'user_profiles') # 多對多關聯Group表類（注意不是表名），中間表類UserProfileToGroup（注意不是表名），反向字段為user_profiles ????def __repr__(self): ????????return "<UserProfileobject: id=%s, username=%s>" %(self.id, self.username) class HostUser(Base): ????__tablename__ = 'host_user' # 表名host_user ????id = Column(Integer, primary_key = True) # id字段，主鍵，自動增長 ????host_id = Column(Integer, ForeignKey('host.id')) # host_id，外鍵關聯host表的id字段 ????AuthTypes = [ ????????(u'ssh-password', u'SSH/Password'), ????????(u'ssh-key', u'SSH/Key'), ????] # 選項列表 ????auth_type = Column(ChoiceType(AuthTypes)) # auth_type字段，只能是選項列表里規定的值 ????username = Column(String(64), nullable = True) # username字段，不為空 ????password = Column(String(255)) # password字段 ????host = relationship('Host', backref = 'host_users') ????groups = relationship('Group', secondary = HostUserToGroup, backref = 'host_users') # 多對多關聯Group表類（注意不是表名），中間表類HostUserToGroup（注意不是表名），反向字段為host_users ????__table_args = (UniqueConstraint('host_id', 'username', name = '_host_username_uc')) # host_id和username組成聯合唯一約束 ????def __repr__(self): ????????return "<HostUserobject: id=%s, host_id=%s, username=%s>" %(self.id, self.host_id, self.username)

?

Linking Relationships with Backref?

簡單來說,?relationship函數是sqlalchemy對關系之間提供的一種便利的調用方式, backref參數則對關系提供反向引用的聲明。

The backref keyword argument was first introduced in Object Relational Tutorial, and has been mentioned through- out many of the examples here. What does it actually do ? Let’s start with the canonical User and Address scenario:?

?

1 2 3 4 5 6 7 8 9 10 11

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

?

The above configuration establishes a collection of Address objects on User called User.addresses. It also establishes a .user attribute on Address which will refer to the parent User object.

In fact, the backref keyword is only a common shortcut for placing a second relationship() onto the Address mapping, including the establishment of an event listener on both sides which will mirror attribute op- erations in both directions. The above configuration is equivalent to:?

?

1 2 3 4 5 6 7 8 9 10 11 12

from sqlalchemy import Integer, ForeignKey, String, Column <br>from sqlalchemy.ext.declarative import declarative_base <br>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", back_populates="user") class Address(Base): ????__tablename__ = 'address' ????id = Column(Integer, primary_key=True) ????email = Column(String) ????user_id = Column(Integer, ForeignKey('user.id')) ????user = relationship("User", back_populates="addresses")

Above, we add a .user relationship to Address explicitly. On both relationships, the back_populates directive tells each relationship about the other one, indicating that they should establish “bidirectional” behavior between each other. The primary effect of this configuration is that the relationship adds event handlers to both attributes which have the behavior of “when an append or set event occurs here, set ourselves onto the incoming attribute using this particular attribute name”. The behavior is illustrated as follows. Start with a User and an Address instance. The .addresses collection is empty, and the .user attribute is None:?

下面來看下backref相關的源碼

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

def backref(name, **kwargs): ????"""Create a back reference with explicit keyword arguments, which are???? the same arguments one can send to :func:`relationship`. ????Used with the ``backref`` keyword argument to :func:`relationship` in ????place of a string argument, e.g.:: ????????'items':relationship( ????????????SomeItem, backref=backref('parent', lazy='subquery')) ????.. seealso:: ????????:ref:`relationships_backref` ????""" ????return (name, kwargs)

?

?

1 2 3 4 5 6 7

param backref: ??????????indicates the string name of a property to be placed on the related ??????????mapper's class that will handle this relationship in the other ??????????direction. The other property will be created automatically ??????????when the mappers are configured.? Can also be passed as a ??????????:func:`.backref` object to control the configuration of the ??????????new relationship.

?

轉載于:https://www.cnblogs.com/wxzbk/p/10499534.html

總結

以上是生活随笔為你收集整理的SQLAlchemy_定义(一对一/一对多/多对多)关系的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。