Python – 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.) :-))

1. A worker may have multiple projects to work on.
2. A project may have multiple workers assigned to it to work.
3. For each tuple (worker, project) you have a different rating

I

understand that I have three many-to-many relationships. They are:

1. Workers and projects
2. Staff and ratings
3. 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.

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.