Sqlalchemy Many to Many not adding data into DB

Question

I have a many to many relationship in my DB. The problem is that the many to many table is not populated when I submit my form.

That is my code for my models:

CompanyCategory = Table('CompanyCategory', Base.metadata,
    Column('id', Integer, primary_key=True),
    Column('categoryId', Integer, ForeignKey('categories.id')),
    Column('companyId', Integer, ForeignKey('companies.id')))

class Company(Base):
    __tablename__ = 'companies'
    id = Column(Integer, primary_key=True)
    company_name = Column(Text, nullable=False)
    users_id = Column(Integer, ForeignKey('users.id'))
    users = relationship("User", backref=backref('users', cascade="all, delete-orphan"),
                           lazy='joined')

    # foreign key for category
    addresses_category = relationship('Category', secondary=CompanyCategory, backref='companies')

    def __init__(self, company_name=None, users_id=None):
        self.company_name = company_name        
        self.users_id = users_id

    def get(self, id):
        if self.id == id:
            return self
        else:
            return None    

    def __repr__(self):
        return '<%s(%r, %r, %r)>' % (self.__class__.__name__, self.id, self.company_name, self.users_id)

class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    category_name = Column(Text, nullable=False)
    addresses_company = relationship('Company', secondary=CompanyCategory, backref='categories')

    def __init__(self, category_name=None):
        self.category_name = category_name

    def get(self, id):
        if self.id == id:
            return self
        else:
            return None    

    def __repr__(self):
        return '<%s(%r, %r)>' % (self.__class__.__name__, self.id, self.category_name)

I have two classes Company and Category. A company can have multiple categories (later there will be a fixed number of categories which I will create).

To show this I created a simple many to many relation CompanyCategory.

As far as I understand sqlalchemy should automatically add the foreign keys of company and categorie if these are created.

That is the part where I am adding a company and a category to the DB:

new_user_company = Company(company_name=form.company_name.data.strip(), users_id = new_user.id)

if new_user_company: 
    db_session.add(new_user_company)

    db_session.commit()                   

    new_user_company_category = Category(category_name=form.category_name.data)

    if new_user_company_category:

        db_session.add(new_user_company_category)

        db_session.commit()

In this case the data is added to Company and also to Category but the there is no entry to CompanyCategory.

I have already tried a few solutions here but they are mostly for flask-sqlalchemy and not for bidirectional behavior. I need bidirectional behavior because if a company is deleted all many to many relations where the company occures should be also deleted. Also if a category will be deleted (wont probably happen) all relations with companies where these category occured should be deleted.

Thanks

Answer 1

As SQLAlchemy ORM tutorial explains: you need to specify relationship attribute for an instance. In your case you need to set Company.addresses_category or Category.addresses_company:

new_user_company = Company(company_name=form.company_name.data.strip(), users_id = new_user.id)

if new_user_company: 
    db_session.add(new_user_company)

    db_session.commit()                   

    new_user_company_category = Category(category_name=form.category_name.data)

    new_user_company_category.addresses_company.append(new_user_company)

    if new_user_company_category:

        db_session.add(new_user_company_category)

        db_session.commit()

Also if you need the relations to be deleted if one of parent object is deleted it is better to set ondelete attributes:

CompanyCategory = Table('CompanyCategory', Base.metadata,
    Column('id', Integer, primary_key=True),
    Column('categoryId', Integer, ForeignKey('categories.id', ondelete='CASCADE')),
    Column('companyId', Integer, ForeignKey('companies.id', ondelete='CASCADE')))