Thiết kế website giá rẻ

Question

I’m working on a small cycling community project that uses Flask and SQLAlchemy, with MySQL as db. The ORM isn’t complex one, but I tried to give it some flexibility. Four tables in total, but at the core are three tables (for users, challenges and mileage), users and challenges tables are connected in many-to-many relationship to each other via association object instead of association table (I thought it will simplify the model, as I can use that object to store mileage-per-challenge for each user too, instead of having a fifth table). I’ll be attaching the full model at the end of the post, for ease of read.

I’m having issues when I tried to dump some generated test entries into yet empty database.
First, I do this:

    with app_context:
        db.session().add_all(faked_challenges)
        db.session().add_all(faked_users)
        db.session.flush()
        db.session.commit()

It works and I see users and challenges records appearing in the db. There are still no connections between them (milage’s table stays empty).

Then I try to create the many-to-many mappings, that’s when trouble begins. First, I do this:

    with app_context:
        chal = models.Challenge.query.filter_by(id = 2).first()
        user = models.User.query.filter_by(id = 4).first()

        user.challenges.append(chal)

Then I commit:

        db.session.flush()
        db.session().commit()

I expect this code to map challenge with id=2 to user with id=4 in one go (that’s what association proxy is for, if I’m not mistaken). Instead, it always maps the user I pass to challenge with id=1, no matter what actually challenge object is referenced by the chal variable. Example record:

mysql> select * from mileagePerchallengesTbl;
+----+--------+-------------+---------------+-----------------------------+-------------+
| id | userId | challengeId | challengeName | totalDistancePerChallengeKm | contextData |
+----+--------+-------------+---------------+-----------------------------+-------------+
|  1 |      4 |           1 | NULL          |                           0 | null        |
+----+--------+-------------+---------------+-----------------------------+-------------+

But at least it works this way. If in the code above I use chal.users.append(user) instead of user.challenges.append(chal) it throws a cryptic error when it reaches session.flush() step:

MySQLdb.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`tangodown$bbsitedb`.`milagePerchallengesTbl`, CONSTRAINT `milagePerchallengesTbl_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `usersTbl` (`id`))')
...
  File "/home/tangodown/.virtualenvs/my_bb_project/lib/python3.10/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: <exception str() failed>

I can’t see any issues with foreign key in my ORM, there should be any problems with mappings as entries already exist in db and primary keys are generated.
Moreover, I can’t understand why, while almost symmetrical, this many-to-many relationship works in one direction, and fails in the other. I was expecting these two lines:

        chal.users.append(user)
        user.challenges.append(chal)

..to produce exactly the same result (a mapping via mileage table) and be completely inter-exchangeable. But one works, and other doesn’t. What am I missing here?

from . import db
from flask_login import UserMixin
from sqlalchemy import text
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.associationproxy import AssociationProxy

class Base(db.DeclarativeBase):
    #this will make the base class abstract, so it won't be mapped to a table.
    __abstract__ = True
    pass

class User(db.Model):
    def __init__(self, email=None, password=None, givenName=None, sn=None, totalDistanceKm=None, groups=None, isActive=False, contextData=None, userAvatarPath=None):
        self.email  = email
        self.password = password
        self.givenName = givenName
        self.sn = sn
        self.totalDistanceKm = totalDistanceKm
        self.groups = groups
        self.isActive = isActive
        self.contextData = contextData
        self.userAvatarPath = userAvatarPath

    __tablename__ = "usersTbl"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True) # primary keys are required by SQLAlchemy
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(1000))
    givenName = db.Column(db.String(100))
    sn = db.Column(db.String(100))
    totalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    groups = db.Column(db.JSON, nullable=False)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    contextData = db.Column(db.JSON, nullable=True)
    userAvatarPath = db.Column(db.String(150))
    rides = db.relationship('Ride', backref='usersTbl', lazy=True)
    mileage = db.relationship('MileagePerChallenge', back_populates='user', lazy=True)
    challenges = association_proxy('mileage', 'challengesTbl')


    def __repr__(self):
        return f'<User "{self.email}">'

class Ride(db.Model):
    def __init__(self, rideGPXPath=None, rideName=None, distanceKm=None, duration=None, rideDateTime=None, uploadDateTime=None, gpxHash=None, ownerId=1, contextData=None, ridePlottedMapPath=None):
        self.rideGPXPath = rideGPXPath
        self.rideName = rideName
        self.distanceKm = distanceKm
        self.duration = duration
        self.rideDateTime = rideDateTime
        self.uploadDateTime = uploadDateTime
        self.gpxHash = gpxHash
        self.ownerId = ownerId
        self.contextData = contextData
        self.ridePlottedMapPath = ridePlottedMapPath

    __tablename__ = "rideRecordingsTbl"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    rideGPXPath = db.Column(db.String(150))
    rideName = db.Column(db.String(150))
    distanceKm = db.Column(db.Float, server_default=text("0.0"))
    duration = db.Column(db.Time)
    rideDateTime = db.Column(db.DateTime)
    uploadDateTime = db.Column(db.DateTime)
    gpxHash = db.Column(db.String(1000))
    ownerId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), nullable=False)
    contextData = db.Column(db.JSON, nullable=True)
    ridePlottedMapPath = db.Column(db.String(150))

    def __repr__(self):
        return f'<Ride "{self.rideName[:20]}...">'

class Challenge(db.Model):
    def __init__(self, challengeName=None, challengeTargetDistanceKm=None, challengeCurrentTotalDistanceKm=None, challengeStarts=None, challengeEnds=None, createdByUserId=None, isActive=False, contextData=None, challengeLogoPath=None):
        self.challengeName = challengeName
        self.challengeTargetDistanceKm = challengeTargetDistanceKm
        self.challengeCurrentTotalDistanceKm = challengeCurrentTotalDistanceKm
        self.challengeStarts = challengeStarts
        self.challengeEnds = challengeEnds
        self.createdByUserId = createdByUserId
        self.isActive = isActive
        self.contextData = contextData
        self.challengeLogoPath = challengeLogoPath

    __tablename__ = "challengesTbl"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    challengeName = db.Column(db.String(150))
    challengeTargetDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeCurrentTotalDistanceKm = db.Column(db.Float, server_default=text("0.0"))
    challengeStarts = db.Column(db.DateTime)
    challengeEnds = db.Column(db.DateTime)
    createdByUserId = db.Column(db.Integer)
    isActive = db.Column(db.Boolean, default=True, nullable=False, index=True)
    contextData = db.Column(db.JSON, nullable=True)
    challengeLogoPath = db.Column(db.String(150))
    mileage = db.relationship('MileagePerChallenge', back_populates='challenge', lazy=True)
    users = association_proxy('mileage', 'usersTbl')

    def __repr__(self):
        return f'<Challenge "{self.challengeName[:20]}...">'

class MileagePerChallenge(db.Model):
    def __init__(self, userId=1, challengeId=1, challengeName=None, totalDistancePerChallengeKm=None, contextData=None):
        self.challengeName = challengeName
        self.userId = userId
        self.challengeId = challengeId
        self.totalDistancePerChallengeKm = totalDistancePerChallengeKm
        self.contextData = contextData

    __tablename__ = "mileagePerchallengesTbl"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    userId = db.Column(db.Integer, db.ForeignKey('usersTbl.id'), nullable=False, index=True)
    challengeId = db.Column(db.Integer, db.ForeignKey('challengesTbl.id'), nullable=False, index=True)
    challengeName = db.Column(db.String(150))
    totalDistancePerChallengeKm = db.Column(db.Float, server_default=text("0.0"))
    contextData =  db.Column(db.JSON, nullable=True)
    user = db.relationship('User', back_populates='mileage', lazy=True)
    challenge = db.relationship('Challenge', back_populates='mileage', lazy=True)

    def __repr__(self):
        return f'<DistancePerChallenge "{self.challengeName[:20]}...">'

Thiết kế website giá rẻ

Danh mục

SQLAlchemy: records on association table are not correctly created (association object and proxy approach)