Flask-SQLAlchemy Many-to-Many with composite key

Question

I am trying to build a Many-to-Many relationship using Flask-SQLAlchemy using two primary keys from one model and one from another. My models are the following:

Service:

class Service(db.Model):
    """
        Service model object
    """
    name = db.Column(db.String(120), primary_key=True, nullable=False)
    description = db.Column(db.Text)

ContactClosure:

class ContactClosure(db.Model):

    module = db.Column(db.Integer, primary_key=True, nullable=False)
    relay = db.Column(db.Integer, primary_key=True, nullable=False)
    status = db.Column(db.Boolean)
    description = db.Column(db.Text)

    #Relationships
    hostname = db.Column(db.String(120), db.ForeignKey('ip2cc.hostname'), primary_key=True, nullable=False)
    device_name = db.Column(db.String(120), db.ForeignKey('device.name'), primary_key=True, nullable=False)
    services = db.relationship('Service', secondary=cc_services, backref=db.backref('contact_closures', lazy='dynamic'))

This is the related table:

cc_services = db.Table('cc_services',
                    db.Column('service_name', db.String(120), db.ForeignKey('service.name')),
                    db.Column('hostname', db.String(120), db.ForeignKey('contact_closure.hostname')),
                    db.Column('device_name', db.String(120), db.ForeignKey('contact_closure.device_name')),
                    )

And this is the error I am getting:

"AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship ContactClosure.services - there are multiple foreign key paths linking the tables via secondary table 'cc_services'. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables."

If anybody can tell what is the problem here I will be highly thankful, I've been stuck on this for a while...

Answer 1

If you're using an association table or fully declared table metadata, you can use the primary_key=True in both columns, as suggested here.

Association table example:

employee_role = db.Table(
    "employee_role",
    db.Column("role_id", db.Integer, db.ForeignKey("role.id"), primary_key=True),
    db.Column("employee_id", db.Integer, db.ForeignKey("agent.id"), primary_key=True),
)

Metadata example:

# this is using SQLAlchemy
class EmployeeRole(Base):
    __tablename__ = "employee_role"

    role_id = Column(Integer, primary_key=True)
    employee_id = Column(Integer, primary_key=True)

# this is using Flask-SQLAlchemy with factory pattern, db gives you access to all SQLAlchemy stuff
class EmployeeRole(db.Model):
    __tablename__ = "employee_role"

    role_id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, primary_key=True)

Alembic migration for it:

op.create_table(
        'employee_role',
        sa.Column('role_id', sa.Integer(), nullable=False),
        sa.Column('employee_id', sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint('role_id', 'employee_id')
    )

SQL:

CREATE TABLE agent_role (
    role_id INTEGER NOT NULL, 
    employee_id INTEGER NOT NULL, 
    PRIMARY KEY (role_id, employee_id)
);

In terms of relationship, declare it on one side (this should give you role.employees or employee.roles which should return a list):

# this is using Flask-SQLAlchemy with factory pattern, db gives you access to all SQLAlchemy stuff
class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    roles = db.relationship("Role", secondary=employee_role, backref="employee")

Your Role class can be:

# this is using Flask-SQLAlchemy with factory pattern, db gives you access to all SQLAlchemy stuff
class Role(db.Model):
    __tablename__ = "role"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(25), nullable=False, unique=True)

Answer 2

Ok, finally I found the solution to this problem:

cc_services = db.Table('cc_services',
    db.Column('service_name', db.String(120),
    db.ForeignKey('service.name')),
    db.Column('cc_hostname', db.String(120)),
    db.Column('cc_module', db.Integer),
    db.Column('cc_relay', db.Integer),
    ForeignKeyConstraint(
        ('cc_hostname', 'cc_module', 'cc_relay'),
        ('contact_closure.hostname', 'contact_closure.module', 'contact_closure.relay')
    )
)

If a model has multiple keys, they must be declared on the helper table on a ForeignKeyConstraint statement.

Answer 3

You need to define 'foreign_keys' argument as the error text says, like this:

services = db.relationship('Service', secondary=cc_services, backref=db.backref('contact_closures', lazy='dynamic'), foreign_keys=[column_name])