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?