CODEWITHSUNDEEP
ormsqlalchemyassociations
Tania
Tania

Reputation: 1925

SQLAlchemy many to many relationships confusion

Consider the following model(from pythoncentral tutorials):

class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary='department_employee_link'
    )


class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
    departments = relationship(
        Department,
        secondary='department_employee_link'
    )


class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    extra_data = Column(String(256))
    department = relationship(Department, backref=backref("employee_assoc"))
    employee = relationship(Employee, backref=backref("department_assoc"))

I understand that this piece of code establishes a many-many relationship between employees and depts.Suppose I have to insert the department_id and employee_id into the DepartmentEmployee link table, how do I do it?? The tutorial says:

>>> IT = Department(name="IT")
>>> John = Employee(name="John")
>>> John_working_part_time_at_IT = DepartmentEmployeeLink(department=IT, employee=John, extra_data='part-time')
>>> s = session()
>>> s.add(John_working_part_time_at_IT)
>>> s.commit()

But I want to do it separately. first, I want to add details into the department table, then the employee table. Finally, I need to populate the Dept-employee link where the user enters the extra_data column alone... How do I do this? I tried doing something like this

def mapper:
    que=DBSession.query(Department)
    que2=DBSession.query(Strings)


                        rel=DepartmentEmployeeLink(extra_data=str(x))//__init__ed this
                        rel.department=que
                        rel.employee=que.employees[0].id

            DBSession.add(rel)

This is how I want the insertion to happen since I already have data inside department and employee. Can anyone tell me how to accomplish this i.e., inserting into the link table if I have data in the other 2 tables?

I learnt there's a way to do this like "employees.append.xxx" but I dont understand.. Can someone point me in the right direction please? Thanks in advance.

Upvotes: 0

Views: 125

Answers (1)

Timur Osadchiy
Timur Osadchiy

Reputation: 6209

Here is a better way to define Many to Many relationship in SQLAlchemy using association_table.

association_table = Table('department_employee_link', Base.metadata,
    Column('departmant_id', Integer, ForeignKey('department.id')),
    Column('employee_id', Integer, ForeignKey('employee.id'))
)


class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary=association_table
    )


class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
    departments = relationship(
        Department,
        secondary=association_table
    )


IT = Department(name="IT")
John = Employee(name="John")
IT.employees.append(John)
s = session()
s.add(IT)
s.commit()

Upvotes: 1

Related Questions