SQLAlchemy - Create new entries in Table_A related to existing entries in Table_B

Viewed 16

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

  1. How would I insert multiple entries at a time into Subjects that share the same task that does not already exist in the Task database?

  2. Q1 but if the Task does already exist in the Task database?

  3. 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

0 Answers
Related