Sqlalchemy_ definition (one-to-one/one-to-many/many-to-many) relationships

• Directory
• Basic Relationship Patterns
• One to many
• One to one
• Many to many

Basic Relationship Patterns

Basic Relational Mode
The imports used for each of the following sections are as follows:
The following import statements are applied to all the next generation chapters:

From SQLAlchemy import Table, Column, Integer, foreignkeyfrom sqlalchemy.orm import Relationshipfrom Sqlalchemy.ext.declarative Import declarative_basebase = Declarative_base ()

One to many

A one to many relationship places a foreign key on the child table referencing the parent.
When representing a one -to-many relationship, the parent table class is referenced in the child table class by foreign key (foreign key).
relationship () is and then specified on the parent, as referencing a collection of items represented by the child:
Then, in the parent table class, use the relationship () method to refer to the child table's class:

Class parent (Base):    __tablename__ = ' parent '    id = Column (Integer, primary_key=true)    children = Relationship ("child")   # refers to the class collection of the child table in the parent table class by means of the relationship () method (Base):    __tablename__ = ' children '    id = Column (integer, primary_key=true)    parent_id = Column (Integer, ForeignKey (' parent.id '))    # in the child table class through the foreign key (foreign key) refers to the reference field of the parent table

 to establish a bidirectional relationship in One-to-many, where the "reverse" side are a many to one, 
at a one-to-many relationship in which two-way relationships are established, so that in the other side it is a many-to-one relationship,  
Specify an additional  relationship ()  and connect the both using the  relationship.back_populates  parameter: 
Append a   to the child table class, relationship ()   method, and   on both sides; Relationship ()   method using   relationship.back_populates   method parameters:

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")    # A relationship () method is appended to the child table class    # and is used in the relationship () method of the (parent) child table class Relationship.back_ Populates parameters

Child would get a parent attribute with Many-to-one semantics.
In this case, the child table will get the properties of the parent table in a many-to-one relationship

Alternatively, the BACKREF option is used on a single relationship () instead of using back_populates:
Alternatively, you can use the backref parameter in a single relationship () method instead of the back_populates parameter:

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 are essentially a bidirectional relationship with a scalar attribute on both sides.
One-to-one is an essentially bidirectional relationship between two tables.
To achieve this, the USELIST flag indicates the placement of a scalar attribute instead of a collection on the "many" side of the relationship.
To do this, you only need to use the uselist parameter in the parent table based on a one -to-many relationship.
To convert One-to-many into one-to-one:

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:

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 is used in lieu of the relationship. Back_populates approach; To specifyuselist in a backref, use the backref () function:

Similarly, you can use the following method:

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)) class child (Base):    __tablename__ = ' child '    id = Column (Integer, Primary_key=true)    parent_id = Column (Integer, ForeignKey (' parent.id '))

Many to many

Many to many adds an association table between the classes.
A many-to-many relationship adds an associated table between two classes.
The association table is indicated by the secondary argument to relationship ().
The associated table is represented by the secondary parameter in the relationship () method.
Usually, the Table uses the MetaData object associated with the declarative base class,
Typically, this table is associated with the declaration base class through the MetaData object.
So, the ForeignKey directives can locate the remote tables with which to link:
So this foreignkey instruction uses a link to navigate to the remote table:

Defining an intermediate table can define a class related to an intermediate relational table, or it can generate a corresponding relational table object directly from Base.metdata, although it is recommended to write the intermediate relationship as a class based on the code first criterion.

Construct the third relationship class to implement many-to-many.

Base = Declarative_base () #生成sqlorm基础类class Hostusertogroup (base): __tablename__ = ' hostuser_to_group ' # table name Hostuser_to_ Group nid = Column (Integer, primary_key=true,autoincrement=true) hostuser_id = column (Integer,foreignkey (' Host_user. Id '), primary_key=true) # Foreign Key Association host_user table ID Field group_id = Column (Integer,foreignkey (' group.id '), Primary_key=true) # ID field for Foreign Key Association Group table Class Userprofiletogroup (Base): __tablename__ = ' userprofile_to_group ' # table name Userprofile_to_group nid = Column (Integer, primary_key=true,autoincrement=true) userprofile_id = column (Integer,foreignkey (' user_profile.id ') , primary_key=true) # Foreign Key Association user_profile table ID Field group_id = Column (Integer,foreignkey (' group.id '), Primary_key=true) # ID field for Foreign Key Association Group table Class Userprofiletohostuser (Base): __tablename__ = ' userprofile_to_hostuser ' # table name Userprofile_to_ Hostuser nid = Column (Integer, primary_key=true,autoincrement=true) userprofile_id = column (Integer,foreignkey (' User _profile.id '), primary_key=true) # Foreign Key Association User_profile I of the tabled Field hostuser_id = Column (Integer,foreignkey (' host_user.id '), primary_key=true) # Foreign KEY Association host_user the ID field of the Table class host (Base): __tablename__ = ' host ' #表名host id = Column (Integer, primary_key= True, autoincrement= true) # ID field, primary key, auto grow hostname = Column (string, unique= true,nullable= False) # hostname field, unique, cannot be empty ip_addr = Column (String (), unique= true,nullabl E= False) #ip_addr字段, unique, cannot be null port = Column (Integer, default = $) # port field, shaping, default Def __repr__ (self): return "
Build a relational table to implement many-to-many instances as follows:
From SQLAlchemy import create_engine,and_,or_,func,tablefrom sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, datetimefrom sqlalchemy.orm import Sessionmake R,relationshipfrom sqlalchemy_utils Import choicetype,passwordtypefrom datetime import datetimebase = Declarative_base () #生成sqlorm基础类HostUserToGroup = table (' Hostuser_to_group ', base.metadata, # table name Hostuser_to_group Column (' hostuser_id ') , ForeignKey (' host_user.id '), Primary_key = True), # Foreign Key Association host_user table ID field Column (' group_id ', ForeignKey (' group.id '), PRI Mary_key = True), # FOREIGN key association the ID field of the Group table) Userprofiletogroup = table (' Userprofile_to_group ', base.metadata, # table name Userprofile_ To_group Column (' userprofile_id ', ForeignKey (' user_profile.id '), Primary_key = True), # Foreign Key Association user_profile table ID field colum N (' group_id ', ForeignKey (' group.id '), Primary_key = True), # FOREIGN key association the ID field of the Group table) Userprofiletohostuser = Table (' Userprofile_to_hostuser ', base.metadata, # table name UserprofIle_to_hostuser Column (' userprofile_id ', ForeignKey (' user_profile.id '), Primary_key = True), # Foreign Key Association user_profile table ID Field Column (' hostuser_id ', ForeignKey (' host_user.id '), Primary_key = True), # Foreign Key Association host_user table ID field) class host (Base): __tab lename__ = ' host ' #表名host id = Column (Integer, primary_key= True, autoincrement= true) # ID field, primary key, auto grow hostname = Colu Mn (String (+), unique= true,nullable= False) # hostname field, unique, cannot be empty ip_addr = Column (String), unique= true,nullable= Fal SE) #ip_addr字段, unique, cannot be null port = Column (Integer, default = $) # port field, reshape, default Def __repr__ (self): return "
Linking relationships with Backref
In a nutshell, the relationship function is a convenient way to call SQLAlchemy to a relationship, and the Backref parameter provides a declaration of a reverse reference to the relationship. 
The BACKREF keyword argument is first introduced in Object relational Tutorial, and have been mentioned through-out many of the examples here. What does it actually do? Let's start with the canonical User and Address scenario:
From SQLAlchemy import Integer, ForeignKey, String, Column from sqlalchemy.ext.declarative import declarative_base from sq Lalchemy.orm Import relationshipbase = 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 would refer to the parent user object.
In fact, the BACKREF keyword are only a common shortcut for placing a second relationship () onto the Address mapping, inclu Ding the establishment of an event listener on both sides which would mirror attribute op-erations in both directions. The above configuration is equivalent to:
from sqlalchemy import Integer, ForeignKey, String, Column 
 from Sqlalchemy.ext.declarative import declarative_base 
 from sqlalchemy.orm import relationshipbase = 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 ("U Ser ", back_populates=" addresses ") 
Above, we add a. User relationship to Address explicitly. On both relationships, the Back_populates directive tells all relationship about the other one, indicating that they shou LD establish "bidirectional" behavior between each of the other. The primary effect of this configuration is, the relationship adds event handlers to both attributes which has the BE Havior of "When a append or set event occurs here, set ourselves onto the incoming attribute using this particular attrib Ute 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:
below to see the Backref related source code
def backref (name, **kwargs): "" "    Create a back reference with explicit keyword arguments, which is the     same argume NTS one can send to:func: ' relationship '.    Used with the "backref" keyword argument to:func: ' Relationship '    in place of a string argument, e.g.::        ' items ': r Elationship (            Someitem, Backref=backref (' Parent ', lazy= ' subquery '))    ... seealso:::        ref: ' Relationships_ Backref ' "" "    return (name, Kwargs)
Param backref: Indicates the string name of a property to being placed on the          related          mapper ' s class that would hand Le this relationship and the other          direction. The other property is created automatically when the          mappers is configured.  Can also is passed as a          : Func: '. Backref ' object to control the configuration of the          new relationship.
Sqlalchemy_ definition (one-to-one/one-to-many/many-to-many) relationships