I'm using Flask-SQLAlchemy with MySQL.

I have a relationship table for reproductions with a primary key formed of 3 columns:

user_id, song_id, count

user_id and song_id are foreing keys to other tables and count is an AUTO_INCREMENT/Sequence that counts the reproduction number for each user on a specific song.

This is my model:

class Listen(db.Model):
    user_id = db.Column(db.Integer,
                        db.ForeignKey("user.id"), primary_key=True)
    song_id = db.Column(db.String(SONG_ID_LENGTH),
                        db.ForeignKey("song.id"), primary_key=True)
    count = db.Column(db.Integer, db.Sequence(
        'seq_listen_id', start=1, increment=1), primary_key=True)
    date = db.Column(db.DateTime, nullable=False)

    def __init__(self, user_id, song_id):
        self.user_id = user_id
        self.song_id = song_id
        self.date = datetime.now()
    def save(self):

The problem that the count column is independent to the other two and I get something like:

What I get

user_id song_id count
1 1 1
1 1 2
1 2 3
2 1 4

Is there any way to make it work like this without having to make a query on the constructor?:

What I want

user_id song_id count
1 1 1
1 1 2
1 2 1
2 1 1


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.