After trying to construct tables with SQLAlchemy following the documentation, I still didn’t understand the “backref” attribute when constructing the relationship between models. But, first things first.
So, given the general one-to-many scheme:
This is one-to-many scheme from parent to children, i.e. one parent belongs to many children and children have only one parent.
Following the SQLAlchemy Documentation let’s reconstruct these tables.
First, import necessary modules
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Then define the models:
1. class Parent(Base):
2. __tablename__ = 'parents'
3. id = Column(Integer, primary_key=True)
4. children = relationship("Child")
5.
6. class Child(Base):
7. __tablename__ = 'children'
8. id = Column(Integer, primary_key=True)
9. parent_id = Column(Integer, ForeignKey('parents.id'))
The purpose of ORM is to provide the object oriented way of conducting SQL operations. Instead of constructing long sql statements to get all children of a certain parent what we want is to do something like parent.children. This is possible if we specify the relationship construct (line 4 in the code snippet above).
So far so good.
Since we talk about accessing the child objects that refer to the parent object - parent.children, it should be logical to also consider the access of a parent object from a child object - child.parent. Well, let’s see what happens if we add the parent attribute to the Child class:
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
children = relationship("Child")
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parents.id'))
parent = relationship("Parent")
Ok, let’s quickly check this code.
First, let’s create an in-memory database and associate it with SQLAlchemy:
from sqlalchemy import create_engine
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)
Then create the declared models in database:
Base.metadata.create_all(engine)
With all set, now we can create instances of models and do some manipulation with them:
from sqlalchemy.orm import Session
with Session(bind=engine) as session:
parent1 = Parent()
print('2Begin--------------------------------') # these are for debugging purposes
session.add(parent1)
session.commit()
print('2End----------------------------------')
child1 = Child()
print('3Begin--------------------------------')
parent1.children.append(child1)
session.commit()
print('3End----------------------------------')
print('4Begin--------------------------------')
print(parent1.children)
print(child1.parent)
print('4End----------------------------------')
Here is the complete code
Running the code, produced the expected output and also interesting warning after line 1End--------------------------- :
SAWarning: relationship 'Child.parent' will copy column parents.id to column children.parent_id, which conflicts with relationship(s): 'Parent.children' (copies parents.id to children.parent_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only.
which also gives link to the problem 1.
So, basically both relationships write their values to the same place, in this case children.parent_id, in essence overwriting each other. As stated in 1: “ORM does not have any means of coordinating these relationships together”.
Ok, we’ve reached to the problem. By the way, notice that output between 1Begin——————- and 1End—————- sends SQL commands for creating tables Parent and Child, and in there no children and parent table columns are declared. From this we can see that these attributes are pure object constructs that do not have sql counterparts unlike such attributes as id and parent_id in Child class.
And now comes the main point of this article: backref. In documentation about one-to-many relationship, it is stated that to establish the bidirectional relationship, which allows us to use parent.children and child.parent, we should add one more argument back_populate to both relationship constructs:
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
children = relationship("Child", back_populates="parent") # back_populate here
def __repr__(self):
return f'parent with id {self.id}'
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parents.id'))
parent = relationship("Parent", back_populates="children") # back_populate here
def __repr__(self):
return f'child with id {self.id}'
Alternatively, as stated in documentation, “backref” can be used on a single relationship. I went ahead and added the “backref” argument to the Parent model:
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
children = relationship("Child", backref="parent")
def __repr__(self):
return f'parent with id {self.id}'
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parents.id'))
parent = relationship("Parent")
def __repr__(self):
return f'child with id {self.id}'
Testing with this setup, the code produced the error when the runtime reached the command parent1 = Parent():
sqlalchemy.exc.ArgumentError: Error creating backref 'parent' on relationship 'Parent.children': property of that name exists on mapper 'mapped class Child->children'
Now everything became clear! “backref” just creates the relationship relationship("Parent") in the Child model.
So, what we need is just to specify only one relationship with backref argument:
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
children = relationship("Child", backref="parent")
def __repr__(self):
return f'parent with id {self.id}'
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parents.id'))
def __repr__(self):
return f'child with id {self.id}'
Running this setup produced the expected output with no warnings.
Summary
This article resolved the issue around “backref” or “back_populates” arguments used in relationship patterns in SQLAlchemy. What was understood is that the relationship attributes are purely class constructs that do not have the SQL column counterparts. The “backref” or “back_populates” arguments used in relationship are the way to indicate the existence of another such relationship in other model that refers to the current one in order to avoid the problem where they overwrite the values of each other.