SQLAlchemy – many-to-many

A description of the many-to-many relationship in SQLAlchemy. An example of creating tables and saving data.

Description of models (classes)

We create two classes: Task and Category, tasks will be saved in one table, in another category.

Task table

Category table

One task can have several categories. For these purposes, it is best to keep the link between the task and the category in a separate table. This will be the third table to be created using the link db.relationship

All this is done by describing model classes in this way:

# ploshadka.net
from datetime import datetime
from flask_login import UserMixin
from flask_security import RoleMixin

tasks_categories = db.Table(
‘tasks_categories’,
db.Column(‘task_id’, db.Integer(), db.ForeignKey(‘task.id’)),
db.Column(‘cat_id’, db.Integer(), db.ForeignKey(‘categories.id’))
)

class Task(db.Model, RoleMixin):
__tablename__ = ‘tasks’
id = db.Column(db.Integer(), primary_key=True, unique=True)
user_id = db.Column(db.Integer, db.ForeignKey(‘users.id’))
name = db.Column(db.String(255))
time = db.Column(db.DateTime())

# To access related objects
cats = db.relationship(‘Category’, secondary=tasks_categories, backref=db.backref(‘tasks’, lazy=‘dynamic’))

class Category(db.Model, RoleMixin):
__tablename__ = ‘categories’
id = db.Column(db.Integer(), primary_key=True, unique=True)
user_id = db.Column(db.Integer, db.ForeignKey(‘users.id’))
name = db.Column(db.String(255))

If you want to further delete all related data with one command, then in db.relationship you need to add a couple of additional properties of this kind:

cats = db.relationship(
‘Category’,
secondary=tasks_categories,
backref=db.backref(‘task’, lazy=‘dynamic’),
single_parent=True,
cascade=“all, delete, delete-orphan”
)

However, in this example, this is not suitable, otherwise, by deleting a task with a category attached to it, we will delete these categories from all other tasks, including.

How to save data

The example below is methods with a route (via blueprint). It receives data through the POST method. Then this data is stored in the database and returned back.

Within the methods, the task, categories and links with them are saved. All the main points are explained in the first method.

Save method to a task of one category

# ploshadka.net
@task.route(‘/ add-new-item /’, methods=[‘POST’])
@login_required
def add_new_item():

# Data from the front
data = request.get_json()
name = data[‘name’]
category = data[‘category’]

# Current user
user_id = flask_login.current_userid

# Define the existence of a category
c1 = db.sessionquery(Category)filter_by(user_id=user_id, name=category)first()

# If there is no category, create it
if c1 is None:
c1 = Category(user_id=user_id, name=category)
db.sessionadd(c1)

# Use flush to get the id of the category to be added
db.sessionflush()

# Add a task
new_task = Task(user_id=user_id, name=name, time=datetimenow())

# Add a link
new_task.catsappend(c1)
db.sessionadd(new_task)

# Now let’s save everything above to our database
db.sessioncommit()

# Return the updated data back to the front
return get_items()

Method of saving to a task of several categories

In this case, our method will change slightly. From the front, we get a comma-separated list of categories. Form a list of categories from this line. We check each category for existence in the database. Then we either add it to the task or create and again add it to the task.

# ploshadka.net
@task_bp.route(‘/ add-new-item /’, methods=[‘POST’])
@login_required
def add_new_item():
user_id = flask_login.current_userid

# Get data from the front
data = request.get_json()
name = data[‘name’]
categories = data[‘categories’]

# Add a new task
new_task = Task(user_id=user_id, name=name, time=datetimenow())

# Let’s make a sheet of several categories (separate them by commas)
categories = [x.strip() for x in categories.split(‘,’)]

# For each category
for category_name in categories:

# Determine if the category exists
category_in_db = db.sessionquery(Category)filter_by(user_id=user_id, name=category_name)first()

if category_in_db:
new_task.catsappend(category_in_db)
else:
c = Category(user_id=user_id, name=category_name)
db.sessionadd(c)
db.sessionflush()
new_task.catsappend(c)

# Save
db.sessionadd(new_task)
db.sessioncommit()

# Return the updated data
return get_items()

Similar Posts

Leave a Reply