Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 417
Sigils Avatar asked Sep 04 '14 14:09

Sigils


2 Answers

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() 
like image 200
mehdix Avatar answered Oct 11 '22 23:10

mehdix


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.

like image 28
Devy Avatar answered Oct 12 '22 00:10

Devy