Table relationships in SQL have always been one of the more difficult areas to understand. They are also implemented by SQLAlchemy, which is easier to understand if you have a good understanding of table relationships in SQL.

Why do we need to define Relationships

Within the associated tables, we can simply refer to each other’s IDs without creating definitions for the table associations. However, it is much more difficult to query and use:

# Addresses like this: # Addresses like this: # Addresses like this: # Addresses like this: https://www.zhihu.com/question/38456789/answer/90470689 def get_addresses_from_user(user_name): user = session.query(User).filter_by(name=user_name).first() addresses = session.query(Address).filter_by(user_id=user.id).all() return addresses

As you can see, this is very inefficient. Fortunately, native SQL has a relationship setting, which SQLAlchemy introduces into the ORM model.

It allows us to declare the relationships between tables only in the table, and then completely eliminate the need for manual use each timeCross searchInstead, use it directly as if it were data in a table.

Why don’t we define relationships?

The “relationship” of SQLAlchemy in practice, to some extent, makes the overall table association relationship extremely complicated and inefficient.

If you only have two tables in your database, relationship can be defined and used arbitrarily. If you only have a few hundred pieces of data, feel free to play with them.

However, when there are more than dozens of tables in the database, a single level of association is more than the level of association of more than three tables, and each data volume is in units of ten thousand. Then, “relationship” can be a complete wreck, less clear and understandable than a handwritten SQL statement, and less effective at the second versus millisecond level.

SQLAlchemy can only be very easy to handle
Many to Many, but if it is common
Many to Many to Many, or the
Many to Many to Many to ManyIt was a nightmare.

However, we all know that up to a certain point, projects can’t get rid of ORM. Whether you build your own wheels or use someone else’s, whether you start with pure SQL or not, you end up with ORM. So what to do?

Suggestions are: use SQLAlchemy to establish a variety of ORM objects, do not use the built-in correlation, directly in the query manual SQL statement!

After practice, my advice is as follows:

  • Easy SQL-Injection place with SQLAlchemyquery
  • When creating ORM objects, use SQLAlchemy
  • Do not use SQLAlchemy for multi-level associations
  • When querying, use SQL
  • When inserting data, do not use SQLAlchemy. (The official statement is that when inserting millions, and SQL plugins are second level)

The relationship () function

Refer to official documentation: Linking Relationships with Backref

SQLAlchemy uses the function relationshi() when creating table associations. It returns a property of a class, such as the children property of the Father class. However, it doesn’t actually create any column called children in the FATHER table. Instead, it automatically finds the data in the associated children table, making it feel like there’s no difference between what you’re using. It’s very convenient!

Relationship () has a lot of arguments, and each argument has a lot to understand. Because all the forms of the table associations are defined in this function. The following are explained separately.

Reference:

The traditional approach is to define a relationship or forward Reference in the parent class, and the subclass only needs to define a foreign key. Such as:

class Father(..) : id = Column(..) children = relationship('Child') class Child(..) : father_id = Column( Integer, ForeignKey('father. Id ') # add data Daddy = father () Jason = Child() Emma = Child() # daddy.children.append(jason) daddy.children.append(emma)

So every time we use father. Children, we will automatically return all the children associated with that father.

Back Reference

The purely defined relationship(‘ child name ‘) is only a forward reference, meaning that only the parent class can call the child object. Conversely, children are not asked who their father is.

So, we need one moreBack ReferenceThat lets the child object know who the parent object is.

The method is defined in the superclass Relationship (..). Add a parameter backref:

class Father(..) : children = relationship( 'Child', backref='parent' )

Note:

  1. The arbitrary writing used in the backref parameter is mainly used for reference to subsequent subclasses.
  2. Backref parameter isbiphasicThat means it only needs to be declared once in the superclass, soThe father ⇄ sonThe bidirectional relationship is established, and you don’t have to subclass it again.

At this point, we can call each other like this:

>>> Jason = Child()
>>> print( Jason.parent )
 <__main__.Father object at 0x10222f860>

Bidirectional & Unidirectional Back Reference

Later, SQLAlchemy found it a bit unintuitive to define the bidirectional backref on only one side, so it added another parameter, back_populates, which is one-way, i.e. : back_populates () back_populates (); To establish a bi-directional relationship, you must declare it in both classes. That’s a little bit more intuitive.

Can put the
backrefand
back_populatesPronounce them both “as, “so they’ll be easier to remember.

Such as:

class Father(..) : id = Column(..) children = relationship( 'Child', back_populates='parent' ) class Child(..) : father_id = Column( Integer, ForeignKey('father.id') ) parent = relationship( 'Father', back_populates='children' )

Note:back_populatesThe relationship names of the parent and subclasses must be strictly “symmetric” :

  • The name of the relationship attribute of the parent classchildrenMust correspond to the relationship of a subclassback_populatesThe values in the
  • The relationship attribute name of the subclassparentMust correspond to the relationship of the parent classback_populatesThe values in the

The relationship created with the backreference parameter is now established. Note, however, that if we have to add an association between the parent and child objects in order to reference them, no one will know who is whose father and son, regardless of whether the association is created with backref or back_populates:

>>> daddy = Father() >>> son = Child() >>> daughter = Child() >>> daddy.children [] >>> son.parent None >>> daddy.children.append( son ) >>> daddy.children.append( daughter ) >>> daddy.children [ <Child ...>, <Child ...> ] >>> son.parent <Father ... >

Append (parent=daddy); append (parent=daddy); append (parent=daddy); append (parent=daddy); append (parent=daddy); append (parent=daddy); append (parent=daddy); append (parent=daddy); append (parent=daddy)

Backreference parameter comparison:

  • backrefParameters: Both directions. You can define it in the parent class. Only throughdaddy.children.append()Method to add a subobject association.
  • back_populatesParameter: Single direction. Must be defined in both parent and child classes, and the attribute names must be strictly symmetric. You can also go throughChild(parent=daddy)Way to add a parent object association.

Table relationships in SQL

Corresponding relation:

  • One to Many:
  • Many to One:
  • Many to Many:

One to Many

Create a one-to-many multi-table association:

#... class Person(Base): id = Column(...) name = Column(...) Pets = relationship('Pet', backref='owner') Backref = 'backref'; backref = 'backref'; backref = 'backref'; backref = 'backref'; backref = 'backref'; id = Column(...) name = Column(...) Owner_id = Column(Integer, foreignKey ('person.id'));

With the associated table created, we can insert the data directly. Note that inserting associated data is also somewhat different from SQL inserting:

#... Andy = Person(name='Andrew') Session. Add (Andy) Session.com MIT () # Add dog pp01 = Pet(name='Puppy') Owner = Andy) pp02 = Pet(name='Puppy', owner= Andy) SQL Alchemy automatically fetches the object's id and matches the foreignKey in the side table. SQL Alchemy automatically fetches the object's id and matches the foreignKey in the side table. session.add(pp01) session.add(pp02) session.commit() print( andy.pets ) # >>> [<Pet 1>, <Pet, Print (pp01.owner) # >>> <Person 'Andrew'> # print(pp01.owner) # >>> <Person 'Andrew'> #

Many to One

For example, the relationship between employees and the company is many-to-one. What’s the difference between a company and its employees versus a one-to-many? The difference is in the SQL statement: a many-to-one relationship is defined in a table of one party of many, and no relationship is defined in a table of one party of one:

class Company(...) : id = Column(...) class Employee(..) : id = Column(...) company_id = Column( ... , ForeignKey('company.id') ) company = relationship("Company")

Many to Many

Many-to-many relationships are also common, such as the relationship between User and Radio: a Radio can have multiple users to subscribe to, and a User can subscribe to multiple radios.

When dealing with many-to-many relationships in SQL, it splits many-to-many into two one-to-many relationships. Here’s how to do it: create a new table dedicated to storing the mapping relationships. The original two tables do not need to set any foreign keys.

The practice of SQLAlchemy is the same as that of SQL.

Note: There is no need to register any ForeignKey foreign keys for each of the tables now that there is a dedicated Mapping table.

Example:

1. This table has two "id" that are not the primary key, because it is a many-to-many relationship, so both of them can have multiple data. # 2. The mapping table must be defined in the front, otherwise, when the following class references, The compiler will not find radio_users = Table('radio_users', base.metadata, Column('whatever_name1', Integer, ForeignKey('radios.id')), Column('whatever_name2', Integer, foreignKey ('users.id'))) __tablename__ = 'radios' rid = Column('id', Integer, primary_key=True) followers = relationship('User', Secondary =radio_users, # 'secondary' is used to indicate back_populates=' SUBSCRIPTS '; __tablename__ = 'users' uid = Column('id', Integer, primary_key=True) subscriptions = relationship('Radio', Secondary =radio_users, back_populates='followers' #

Secondary is used to specify the map table.

Note: we can also use many-to-many
backrefParameters to add cross-references. But this method is too unintuitive and confusing. So it’s recommended here
back_populatesParameter, adding references to both sides, represents a parallel status that is easier to understand.

Then insert data like this:

R1 = Radio() r2 = Radio() r3 = Radio() u1 = User() u2 = User() u3 = User() # add object to r1.followers += [u1, u2, += [R2, R3] # This will be a very SUBSCRIPTIONAL SUBSCRIPTIONS.

Many to Many to Many (deep connection)

To avoid the difficulty of understanding deep associations, the dumbest way is to simply use the foreign key ID and manually search for the corresponding ID in another table.

(To be continued)