CODEWITHSUNDEEP
pythonsqlalchemy
Barry
Barry

Reputation: 21

Flask SQLAlchemy: adding third column to joining table

Context: I'm making an auctioning website for which I am using Flask-SQLAlchemy. My tables will need to have a many-to-many relationship (as one artpiece can have many user bids and a user can bid on many artpieces)

My question is: it is possible to add another column to my joining table to contain the id of the user bidding, the id of artpiece that they are bidding on and also how much they bid? Also if yes, how would I include this bid in the table when I add a record to said table?

bid_table = db.Table("bid_table",
db.Column("user_id", db.Integer, db.ForeignKey("user.user_id")),
db.Column("item_id", db.Integer, db.ForeignKey("artpiece.item_id"))
)


class User(db.Model):
    user_id = db.Column(db.Integer, unique=True, primary_key=True, nullable=False)
    username = db.Column(db.Integer, unique=True, nullable=False)
    email = db.Column(db.String(50), unique =True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    creation_date = db.Column(db.DateTime, default=str(datetime.datetime.now()))
    bids = db.relationship("Artpiece", secondary=bid_table, backref=db.backref("bids", lazy="dynamic"))


class Artpiece(db.Model):
    item_id = db.Column(db.Integer, unique=True, primary_key=True, nullable=False)
    artist = db.Column(db.String(40), nullable=False)
    buyer = db.Column(db.String(40), nullable=False)
    end_date = db.Column(db.String(40))
    highest_bid = db.Column(db.String(40))

Upvotes: 1

Views: 563

Answers (1)

Dan Safee
Dan Safee

Reputation: 1618

It is possible to do this with SQL Alchemy, but it's very cumbersome in my opinion.

SQLAlchemy uses a concept called an Association Proxy to turn a normal table into an association table. This table can have whatever data fields you want on it, but you have to manually tell SQLAlchemy which columns are foreign keys to the other two tables in question.

This is a good example from the documentation.

In your case, the UserKeyword table is the association proxy table that you want to build for your user/bid scenario.

The special_key column is the arbitrary data you would store like the bid amount.

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import backref, declarative_base, relationship

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

    # association proxy of "user_keywords" collection
    # to "keyword" attribute
    keywords = association_proxy('user_keywords', 'keyword')

    def __init__(self, name):
        self.name = name

class UserKeyword(Base):
    __tablename__ = 'user_keyword'
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
    special_key = Column(String(50))

    # bidirectional attribute/collection of "user"/"user_keywords"
    user = relationship(User,
                backref=backref("user_keywords",
                                cascade="all, delete-orphan")
            )

    # reference to the "Keyword" object
    keyword = relationship("Keyword")

    def __init__(self, keyword=None, user=None, special_key=None):
        self.user = user
        self.keyword = keyword
        self.special_key = special_key

class Keyword(Base):
    __tablename__ = 'keyword'
    id = Column(Integer, primary_key=True)
    keyword = Column('keyword', String(64))

    def __init__(self, keyword):
        self.keyword = keyword

    def __repr__(self):
        return 'Keyword(%s)' % repr(self.keyword)

Check out the full documentation for instructions on how to access and create this kind of model.

Having used this in a real project, it's not particularly fun and if you can avoid it, I would recommend it.

https://docs.sqlalchemy.org/en/14/orm/extensions/associationproxy.html

Upvotes: 1

Related Questions