CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyfastapimodel-associations
Mark
Mark

Reputation: 312

Can't update SQLAlchemy association table extra columns

My models look like this:

class Company(DB_BASE):
    __tablename__ = 'Company'

    id = Column(Integer, primary_key=True, index=True)
    ...
    products = relationship('Product', secondary=Company_Products, backref='Company')

class Product(DB_BASE):
    __tablename__ = 'Product'

    id = Column(Integer, primary_key=True, index=True)
    ...
    companies = relationship('Company', secondary=Company_Products, backref='Product')

This is my association table

Company_Products = Table(
    'Company_Products',
    DB_BASE.metadata,
    Column('id', Integer, primary_key=True),

    Column('company_id', Integer, ForeignKey('Company.id')),
    Column('product_id', Integer, ForeignKey('Product.id')),

    Column('quantity', Integer, default=0),
    Column('price_per_unit', Integer, default=0),
)

And this is how I'm querying the association table.

company_product = db.query(Company_Products).filter_by(product_id=id, company_id=user.company_id).first()
company_product.quantity = data.data['quantity']
company_product.price = data.data['price']

After creating the many-to-many relationship between a Company and a Product, I would like to modify the relationship extra data, quantity and price_per_unit in this instance. After querying the association object, modifying any attribute yields:

AttributeError: can't set attribute 'quantity'

Upvotes: 0

Views: 547

Answers (1)

Mark
Mark

Reputation: 312

Follow up on my question, the solution which ended up working for me is making a new model and using it to somewhat simulate an association table.

class Company_Products(DB_BASE):
    __tablename__ = 'Company_Products'

    id = Column(Integer, primary_key=True, index=True)
    ...
    quantity = Column(String) # 1 - client, 2 - furnizor
    price_per_unit = Column(String)

    company_id = Column(Integer, ForeignKey('Company.id'))
    company = relationship('Company', back_populates='products', lazy='select')
    product_id = Column(Integer, ForeignKey('Product.id'))
    product = relationship('Product', back_populates='companies', lazy='select')

This is definitely not the best solution, if I come up with something else or come across something which might work out, I will edit this.

Upvotes: 1

Related Questions