Flask sqlalchemy many-to-many insert data

Question

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='john@doe.es')

user2 = User(
            user_fistName='Jack',
            user_lastName='Doe',
            user_email='jack@doe.es')

user3 = User(
            user_fistName='Jane',
            user_lastName='Doe',
            user_email='jane@doe.es')

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?

score 193 · Accepted Answer · edited Mar 22 '19 at 00:35

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()

Let's say my students already exist in a table. How do I append them to a class without having to query the whole data for each student each time (using foreing key only)? — axwell, Jan 30 '20 at 13:28
@axwell, If you often require students for Class (i.e. when check on update), you could tune relationship with lazy='subquery' like this: `students = db.relationship("Student", secondary=student_identifier, lazy='subquery', backref=db.backref('classes', lazy=True))` — Andrii Danyleiko, Oct 18 '20 at 03:45
@axwell You can also do the insertion like this if the student already exist: `session.execute( student_identifier.insert(), params={"class_id": class_id, "user_id": user_id}, )` — Edword, Nov 19 '20 at 10:18

score 25 · Answer 2 · answered Sep 22 '17 at 20:54

25

First off, student_identifier is defined as a SQLAlchemy reflection table not a database.

Normally if you have all the relationship setup properly between models and reflection table objects, you will only need to deal with related models (by appending model objects into the relationship InstrumentList) in order to insert data into reflection tables, for instance, the answer @mehdi-sadeghi provided above.

However, there is indeed a way to insert directly into reflection tables if you don't want to setup the relationship. For example:

statement = student_identifier.insert().values(class_id=cl1.id, user_id=sti1.id)
db.session.execute(statement)
db.session.commit()

After that, you should be able to see that a many-to-many relationship row is inserted into the student_identifier reflection table. Don't forget to commit after you execute each SQL statement as it's done in a transaction.

Hope that helps you with an alternative approach.

answered Sep 22 '17 at 20:54

Devy

9,655
8
61
59

how can I import this reflection table or association table? – jibin mathew Dec 20 '18 at 19:34
1

@jibinmathew just as you would with classes and other symbols. – Devy Jan 02 '19 at 15:54
@jibinmathew I also have a problem when import the association table, so I import the table locally, exactly at the above where I calling that. – Tri Sep 14 '19 at 03:17
1

@GofyandKitty that could be an indicator on circular dependency/import in your code base so that you need to use local import (to provide namespace) in order to make it work. – Devy Sep 16 '19 at 06:31
1

Thanks a lot for sharing this solution. You literally saved me – Chidiebere Aug 28 '20 at 13:59
This is awesome! Is there something similar but to delete an association? – guckmalmensch Dec 01 '20 at 04:09
This ignores the check for duplicates – Jonatan Dec 07 '20 at 15:22

score 3 · Answer 3 · answered Jul 21 '20 at 08:56

3

To extend cowgills answer, you can also add multiple entries at once using extend:

class_ = db.session.query(Class).first()
new_students = db.session.query(Student).all()
class_.students.extend(new_students)
db.session.add(class_)
db.session.commit()

answered Jul 21 '20 at 08:56

NMO

748
8
16

I need to reassign all the students. How do I reset the students before extending it? Simply with `class_.students = []` and then extend it? – Pynchia May 31 '22 at 08:08
I think there is no need to `class_.students = []`. Simply `class_.students=new_students` should override existing relations. – NMO May 31 '22 at 09:35
I see, thank you. But what about the existing ones? I am removing them explicitly now, but what if I do not? would the ORM do it? – Pynchia May 31 '22 at 17:48

score 0 · Answer 4 · answered Aug 09 '22 at 09:32

I had this on my models

show = db.Table('Show',
  db.Column('artist_id', db.Integer, db.ForeignKey('artists.id'), primary_key = True),
  db.Column('venue_id', db.Integer, db.ForeignKey('venues.id'), primary_key = True),
  db.Column('start_time',db.DateTime, nullable = False )
)

class Venue(db.Model):
    __tablename__ = 'venues'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    city = db.Column(db.String(120))
    state = db.Column(db.String(120))
    address = db.Column(db.String(120))
    phone = db.Column(db.String(120))
    facebook_link = db.Column(db.String(120))
    image_link = db.Column(db.String(500))
    genres = db.Column(db.String())
    website_link = db.Column(db.String(120))
    seeking_talent = db.Column(db.Boolean, nullable = False, default= False)
    seeking_description = db.Column(db.String())
    created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
    
    def __repr__(self):
            return f'<Venue ID: {self.id}, Name: {self.name}>'


class Artist(db.Model):
    __tablename__ = 'artists'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    city = db.Column(db.String(120))
    state = db.Column(db.String(120))
    phone = db.Column(db.String(120))
    genres = db.Column(db.String())
    facebook_link = db.Column(db.String(120))
    image_link = db.Column(db.String(500))
    website_link = db.Column(db.String(120))
    seeking_venue = db.Column(db.Boolean, nullable = False, default= False)
    seeking_description = db.Column(db.String())
    venues = db.relationship('Venue', secondary = show, backref = db.backref('artists', lazy = True))
    created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)

    def __repr__(self):
            return f'<Artist ID: {self.id}, Name: {self.name}>'

And I performed the insertion as follows in my app.py (Inserting into the association table)

 new_show = show.insert().values(
        artist_id = form.artist_id.data,
        venue_id = form.venue_id.data ,
        start_time = form.start_time.data
         )
      db.session.execute(new_show)
      db.session.commit()

Flask sqlalchemy many-to-many insert data

4 Answers4

Linked