SQLAlchemy many to many models not working properly. Any idea what Im doing wrong?

Viewed 37

I have 3 models im trying to connect: Users, Applications, and Sections. Each user can have multiple apps and sections. Each section can also have multiple apps.

Some details:

  • Users to applications is many to many
  • Users to sections is 1 to many
  • Applications to Sections is many to many, because sections are unique to users.
class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(40), nullable=False)
    username = db.Column(db.String(20), nullable=False, unique=True)
    email = db.Column(db.String(320), nullable=False, unique=True)
    password = db.Column(db.String(90), nullable=False)
    applications = db.relationship('Application', secondary='user_app' )
    sections = db.relationship('Section', backref='user')

class Application(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    users = db.relationship( 'User', secondary='user_app' )
    sections = db.relationship( 'Section', secondary='user_app_sections' )

class Section(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    applications = db.relationship( 'Application', secondary='user_app_sections' )

user_app = db.Table(
    'user_app',
    db.Column( 'user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
    db.Column( 'application_id', db.Integer, db.ForeignKey('application.id'), primary_key=True),
    db.Column('last_used_time', db.DateTime, nullable=True))

user_app_sections = db.Table(
    'user_app_sections',
    db.Column( 'application_id', db.Integer, db.ForeignKey('application.id'), 
primary_key=True),
    db.Column( 'section_id', db.Integer, db.ForeignKey('section.id'), primary_key=True )
)


I'm able to add apps for different users with:

user.applications.extend(apps)

But I cannot add multiple sections. It will not let me add the same app to multiple sections.

I'm getting a sqlalchemy.exc.IntegrityError:

(pymysql.err.IntegrityError) (1062, "Duplicate entry '1' for key 'PRIMARY'")

[SQL: INSERT INTO user_app_sections (application_id, section_id) VALUES (%(application_id)s, %(section_id)s)]

I understand what the error means, but I don't know how to modify my model to get it to work properly.

I'm exactly following how all the SQLAlchemy guides are telling me to.

Any help?

0 Answers
Related