I am building a system for experiment data organization. What is the most efficient way to insert new entries to a table that has a many-to-many relationship with another table?
Simple example
I have two tables: Subject & Task. Subjects can run multiple Tasks (experiments) and Tasks can be run by many Subjects. They are associated by an association table r_Subject_Task.
Questions
How would I insert multiple entries at a time into
Subjectsthat share the same task that does not already exist in theTaskdatabase?Q1 but if the Task does already exist in the
Taskdatabase?How can I add a Task that relates to an existing Subject?
Code Example
Setup engine and classes
import sqlalchemy as db
from sqlalchemy import Column, ForeignKey, Integer, String, Table
from sqlalchemy.orm import declarative_base, relationship
Base = declarative_base()
engine = db.create_engine('sqlite:///test.db')
r_Subject_Task = Table(
'r_subject_task',
Base.metadata,
Column('subject_id', ForeignKey('subject.id'), primary_key=True),
Column('task_id', ForeignKey('task.id'), primary_key=True)
)
class Subject (Base):
__tablename__ = 'subject'
id = Column(String(30), primary_key = True)
tasks = relationship("Task", secondary=r_Subject_Task, back_populates='subjects')
def __repr__(self):
return f'Subject(id = {self.id}), tasks = {self.tasks}'
class Task (Base):
__tablename__ = 'task'
id = Column(String(30), primary_key=True)
subjects = relationship("Subject", secondary=r_Subject_Task, back_populates='tasks')
def __repr__(self):
return f'Task(id = {self.id}, subjects = {self.subjects})'
Base.metadata.create_all(engine)
Great, the tables are set up. Now I'd like to insert data.
from sqlalchemy.orm import Session
with Session(engine) as session:
subject001 = Subject(
id = 'subj001',
tasks = [Task(id = 'task1')]
)
subject002 = Subject(
id = 'subj002',
tasks = [Task(id = 'task1'), Task(id = 'task2')]
)
subject003 = Subject(
id = 'subj003'
)
task3 = Task(
id = 'task3'
subjects = [Subject(id = 'subj003')]
)
session.add_all([subject001, subject002, subject003, task3])
session.commit()
This results in two errors.
Error1: committing multiple subjects with matching tasks. When I try to create multiple Subjects with matching Tasks, the UNIQUE constraint is failed; I am trying to pass in multiple instances of 'task1'.
Error2: committing a task with an existing subject. When I try to create Task 'task3' and apply it to 'subj004', the UNIQUE constraint is failed; I am trying to add a subject that already exists.
What is the most efficient way to insert many-to-many data dynamically? Should I go one by one? This seems like a standard use case but I'm having difficulty finding good resources in the docs. Any links to relevant tutorials or examples would be much appreciated.
Thanks