CODEWITHSUNDEEP
pythonflaskflask-sqlalchemy
Diego Rueda
Diego Rueda

Reputation: 2506

SqlAlchemy Flask - association table with more than two columns

I am creating a shopping cart, for this I am using the following models in Flask:

line_item = db.Table('line_item',
                     db.Column('cart_id', db.Integer, db.ForeignKey('cart.id')),
                     db.Column('product_id', db.Integer, db.ForeignKey('product.id')),
                     db.Column('price', db.Float)
                     )


class Cart(db.Model):
    id = db.Column(db.Integer, primary_key=True)


class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.Unicode(64), index=True, unique=True)
    description = db.Column(db.Unicode(500), index=True, unique=True)
    image_url = db.Column(db.Unicode(128))
    price = db.Column(db.Float)
    line_item = db.relationship('Cart', secondary=line_item, backref=db.backref('products', lazy='dynamic'))

I want to use an extra column in the association table "line_item" to include price in order to save the price of a product at the moment the user adds it to the cart.

I know that without the price column I would do the following to add this data.

p = Product()
c = Cart()
p.line_item.append(c)
db.session.add(p)
db.session.commit()

How am I supposed to insert the price in the association table?

Upvotes: 0

Views: 1398

Answers (1)

pjcunningham
pjcunningham

Reputation: 8046

Use a db.Model for your LineItem - untested code as follows:

class LineItem(db.Model):
    __tablename__ = 'line_items'        
    cart_id = db.Column(db.Integer, db.ForeignKey('carts.id'), primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('products.id'), primary_key=True)
    price = db.Column(db.Float)    
    cart = db.relationship("Cart", back_populates="line_items")
    product = db.relationship("Product", back_populates="carts")


class Cart(db.Model):
    __tablename__ = 'carts'
    id = db.Column(db.Integer, primary_key=True)
    line_items = db.relationship(LineItem, back_populates="cart")    


class Product(db.Model):
    __tablename__ = 'products'  
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.Unicode(64), index=True, unique=True)
    description = db.Column(db.Unicode(500), index=True, unique=True)
    image_url = db.Column(db.Unicode(128))
    price = db.Column(db.Float)
    carts = db.relationship(LineItem, back_populates="product")    


p = Product()
c = Cart()

line_item = LineItem()
line_item.price = p.price
line_item.product = p

c.line_items.append(line_item)

Upvotes: 1

Related Questions