烧瓶多对多插入数据

小开

最佳答案

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

小开

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.

小开

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

小开

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