Python Flask sqlalchemy 多对多插入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25668092/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Flask sqlalchemy many-to-many insert data
提问by Sigils
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?
我试图在Flask-SQLAlchemy 中建立多对多关系，但似乎我不知道如何填充“多对多标识符数据库”。你能帮我理解我做错了什么以及它应该是什么样子吗？
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='Hyman',
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' 对象？如果可以的话，它可能看起来像这样：
# 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?
我应该如何使用 ORM 插入多对多表？
采纳答案by Mehdi Sadeghi
You don't need to add anything directly to your association table, SQLAlchemy will do that. This is more or less from SQLAlchemy documentations:
您不需要直接向关联表添加任何内容，SQLAlchemy 会这样做。这或多或少来自SQLAlchemy 文档：
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()
回答by Devy
First off, student_identifieris defined as a SQLAlchemy reflection table not a database.
首先，student_identifier定义为 SQLAlchemy 反射表而不是数据库。
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.
通常，如果您在模型和反射表对象之间正确设置了所有关系，则只需处理相关模型（通过将模型对象附加到关系 InstrumentList 中）即可将数据插入反射表，例如，答案@上面提供了 mehdi-sadeghi。
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_identifierreflection table. Don't forget to commit after you execute each SQL statement as it's done in a transaction.
之后，您应该能够看到多对多关系行插入到student_identifier反射表中。不要忘记在执行每个 SQL 语句后提交，因为它是在事务中完成的。
Hope that helps you with an alternative approach.
希望可以帮助您找到替代方法。
