How do I add to a particular column in an association table in Flask?

29 Views Asked by At

I have a table called SetList which are setlists that a user may have. I want users to put songs in the setlist. It's a many to many relationship so here is my SetList class:

class SetList(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key = True)
    name = db. Column(db.String(75), nullable=False)
    owner = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    songs = db.relationship('Song', secondary="setlist_song", back_populates='setlists')

I needed to create an association table to my SetList table. I also want users to be able to rearrange the songs within the setlist as they see fit. So I thought I would add an "order" column in the association table. It looks like this:

setlist_song = db.Table('setlist_song',
        db.Column('song_id', db.Integer, db.ForeignKey('song.id')),
        db.Column('setlist_id', db.Integer, db.ForeignKey('set_list.id')),
        db.Column('order', db.Integer, nullable=True)
        )

I know how to add songs to a setlist, like this:

song_id = 1
songtoadd = Song.query.get(1)
setlist.songs.add(songtoadd)`

and it successfully adds it, but this has no effect on the order column. I tried this

song_id = 1
order = 0
songtoadd=[Song.query.get(song_id), order]
setlist.songs.append(songtoadd)

and I get this error:

AttributeError: 'list' object has no attribute '_sa_instance_state'

What's the proper way to add the order to the column within an association table, or am I supposed to do this some other way?

0

There are 0 best solutions below