SQLAlchemy – Relationships

How to link multiple PostgreSQL database table models to each other in SQLAlchemy.

Relationship Example

I already once wrote an article that touched on the topic of SQLAlchemy relationships. This article will be an evolutionary continuation of the previous one.

Let’s take an example from the official documentation:
https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html

class Association(Base):
__tablename__ = “association_table”
left_id = Column(foreign key(“left_table.id”), primary_key=True)
right_id = Column(foreign key(“right_table.id”), primary_key=True)
extra_data = Column(String(50))
child = relationship(“Child”, back_populates=“parents”)
parent = relationship(“Parent”, back_populates=“children”)

class parent(Base):
__tablename__ = “left_table”
id = Column(Integer, primary_key=True)
children = relationship(“Association”, back_populates=“parent”)

class Child(Base):
__tablename__ = “right_table”
id = Column(Integer, primary_key=True)
parents = relationship(“Association”, back_populates=“child”)

The example above has everything you need. I will only draw attention to specific places so that there is no confusion.

1. We begin to build our connections from the table through which the other two will be connected.

2. Let’s pay attention to the first model, the child property:

child = relationship(“Child”, back_populates=“parents”)

The name of the variable is in the singular gender and it also refers to the class (model, table) with the name in the singular gender. The principle of constructing this relationship is easily remembered by the criterion – the only gender.

3. The second parameter from the example above (but for a change, let’s take a different relationship) back_populates:

parent = relationship(“Parent”, back_populates=“children”)

We see that back_populates refers to a property children in class parent.

4. In both models (tables) that we link, there are references to the linked model through the association model:

children = relationship(“Association”, back_populates=“parent”)

Those. classParent(Base) we communicate indirectly through the class classChild(Base)and through the class class Association(Base) and property in it parentwhich already refers to another class classChild(Base).

It is important to understand how connections are built, otherwise errors will occur:

SAWarning: relationship ‘Task.cat_Task’ will copy column Task.id to column categories_Task.Task_id, which conflicts with relationship(s): ‘Category.Task’ (copies Task.id to categories_Task.Task_id), ‘Task.cats’ ( copies Task.id to categories_Task.Task_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. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. To silence this warning, add the parameter ‘overlaps=”cats,Task”‘ to the ‘Task.cat_Task’ relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)

Example of Accessing Related Objects

Consider the following example. Let’s say we need to get an object from one table, but pull relationships from other tables. The whole code looks like this:

class TaskCategory(db.Model):
__tablename__ = ‘task_categories’
id = db.Column(db.Integer, primary_key=True, unique=True)
category = db.relationship(‘Category’, back_populates=“tasks”)
task = db.relationship(‘Task’, back_populates=“categories”)

@property
def get_task_cats(self):
return {
‘task_cat_id’: self.id,
}

@property
def get_cats(self):
return self.category.get_cats

class task(db.Model):
__tablename__ = ‘tasks’
id = db.Column(db.Integer, primary_key=True, unique=True)
categories = db.relationship(‘TaskCategory’, back_populates=“task”)

@property
def serialize(self):
return {
‘task_id’: self.id,
‘cats’: [x.get_cats for x in self.categories],
‘task_cats’: [x.get_task_cats for x in self.categories],
}

class Category(db.Model):
__tablename__ = ‘categories’
tasks = db.relationship(‘TaskCategory’, back_populates=category)

@property
def get_cats(self):
return {}

I’m talking about this section:

@property
def serialize(self):
return {
‘task_cats’: [x.get_task_cats for x in self.categories],
‘cats’: [x.get_cats for x in self.categories],
}

In the example above, we want to get by property serialize data from table class Task(db.Model)but at the same time we request processed data from two other related tables.

In this case:

‘task_cats’: [x.get_task_cats for x in self.categories]

access to data class TaskCategory(db.Model).

Otherwise:

‘cats’: [x.get_cats for x in self.categories]

the call also goes to the class class TaskCategory(db.Model)but to a function that accesses the data already to the third table.

Thus, we take data on the connection from the third table through the second one (the table that links both).

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *