Flask sqlalchemy many-to-many insert data

I am trying to make a many to many relation here in Flask-SQLAlchemy , but it seems that I don't know how to fill the "many to many identifier database". Could you please help me understand what I am doing wrong and how it is supposed to look?

    class User(db.Model):
        __tablename__ = 'users'
        user_id = db.Column(db.Integer, primary_key=True)
        user_fistName = db.Column(db.String(64))
        user_lastName = db.Column(db.String(64))
        user_email = db.Column(db.String(128), unique=True)


    class Class(db.Model):
        __tablename__ = 'classes'
        class_id = db.Column(db.Integer, primary_key=True)
        class_name = db.Column(db.String(128), unique=True)

and then my identifier database:

    student_identifier = db.Table('student_identifier',
        db.Column('class_id', db.Integer, db.ForeignKey('classes.class_id')),
        db.Column('user_id', db.Integer, db.ForeignKey('users.user_id'))
    )

so far it looks like this when I try to insert the data into the database.

    # User
    user1 = User(
                user_fistName='John',
                user_lastName='Doe',
                user_email='[email protected]')

    user2 = User(
                user_fistName='Jack',
                user_lastName='Doe',
                user_email='[email protected]')

    user3 = User(
                user_fistName='Jane',
                user_lastName='Doe',
                user_email='[email protected]')

    db.session.add_all([user1, user2, user3])
    db.session.commit()

    # Class
    cl1 = Class(class_name='0A')
    cl2 = Class(class_name='0B')
    cl3 = Class(class_name='0C')
    cl4 = Class(class_name='Math')
    cl5 = Class(class_name='Spanish')
    db.session.add_all([cl1, cl2, cl3, cl4, cl5])
    db.session.commit()

Now my problem is, how do I add to the many to many database, since I really can't create a 'student_identifier' object? If I could it could perhaps have looked like this:

    # Student Identifier
    sti1  = StiClass(class_id=cl1.class_id, class_name=user1.user_id)
    sti2  = StiClass(class_id=cl3.class_id, class_name=user1.user_id)
    sti3  = StiClass(class_id=cl4.class_id, class_name=user1.user_id)
    sti4  = StiClass(class_id=cl2.class_id, class_name=user2.user_id)
    db.session.add_all([sti1, sti2, sti3, sti4])
    db.session.commit()

How I am supposed to insert into a many to many table with ORM?

You don't need to add anything directly to your association table, SQLAlchemy will do that. This is more or less from SQLAlchemy documentations:

    association_table = db.Table('association', db.Model.metadata,
        db.Column('left_id', db.Integer, db.ForeignKey('left.id')),
        db.Column('right_id', db.Integer, db.ForeignKey('right.id'))
    )

    class Parent(db.Model):
        __tablename__ = 'left'
        id = db.Column(db.Integer, primary_key=True)
        children = db.relationship("Child",
                        secondary=association_table)

    class Child(db.Model):
        __tablename__ = 'right'
        id = db.Column(db.Integer, primary_key=True)


    p = Parent()
    c = Child()
    p.children.append(c)
    db.session.add(p)
    db.session.commit()

Therefore your sample would be like this:

    student_identifier = db.Table('student_identifier',
        db.Column('class_id', db.Integer, db.ForeignKey('classes.class_id')),
        db.Column('user_id', db.Integer, db.ForeignKey('students.user_id'))
    )

    class Student(db.Model):
        __tablename__ = 'students'
        user_id = db.Column(db.Integer, primary_key=True)
        user_fistName = db.Column(db.String(64))
        user_lastName = db.Column(db.String(64))
        user_email = db.Column(db.String(128), unique=True)


    class Class(db.Model):
        __tablename__ = 'classes'
        class_id = db.Column(db.Integer, primary_key=True)
        class_name = db.Column(db.String(128), unique=True)
        students = db.relationship("Student",
                                   secondary=student_identifier)

    s = Student()
    c = Class()
    c.students.append(s)
    db.session.add(c)
    db.session.commit()

From: stackoverflow.com/q/25668092

Back to homepage or read more recommendations: