Use sqlalchemy in Python to model a database many-to-many relationship between three tables… here is a solution to the problem.
Use sqlalchemy in Python to model a database many-to-many relationship between three tables
Let’s consider the following table model for sqlalchemy in Python.
class Worker(Base):
id Column(Integer, primary = True)
name Column(String, nullable = False)
class Project(Base):
id Column(Integer, primary = True)
name Column(String, nullable = False)
class Rating(Base):
id Column(Integer, primary = True)
description Column(String, nullable = False)
Let’s consider some limitations on these tables. (Please don’t question the significance of this example, this is the best I came up with to describe what I wanted to learn.) :-))
- A
worker
may have multipleprojects
to work on. - A
project
may have multipleworkers
assigned to it to work. - For each tuple
(worker, project)
you have a differentrating
I
understand that I have three many-to-many relationships. They are:
- Workers and projects
- Staff and ratings
- Ratings and projects
I have no problem defining a many-to-many relationship between two tables. I just need to add the following associated tables and relationships to my model.
worker_project = Table('worker_project', METADATA,
Column('worker_id', Integer, ForeignKey('workers.id')),
Column('project_id', Integer, ForeignKey('projects.id')),
class Worker(Base):
# [...]
papers = relationship('Project',
secondary=worker_project,
backref='workers')
Where I failed was linking table “ratings” as described above. Thanks for any help.
Solution
Make the scoring table look like this.
Rating
---------------------
rating_id (primary key)
worker_id (foreign key - worker(worker_id))
project_id (foreign key - project(project_id))
description
If I understand correctly, that is.