使用 SQLAlchemy 在关联表中插入数据时出现 IntegrityError [英] IntegrityError when inserting data in an association table using SQLAlchemy

问题描述

我正在尝试在此关联表中插入角色数据:

类关联(db.Model):__tablename__ = '关联'user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)team_id = db.Column(db.Integer, db.ForeignKey('teams.id'), primary_key=True)角色 = db.Column(db.String)user = db.relationship("用户", back_populates="团队")team = db.relationship("Team", back_populates="users")

它链接到另外 2 个表:

class User(db.Model, UserMixin):__表名__ = '用户'id = db.Column(db.Integer, primary_key=True)电子邮件 = db.Column(db.String)用户名 = db.Column(db.String, nullable=False, unique=True, index=True)password_hash = db.Column(db.String)团队 = db.relationship("关联", back_populates="user")班级团队(db.Model):__tablename__ = '团队'id = db.Column(db.Integer, primary_key = True)名称 = db.Column(db.String)玩家 = db.Column(db.String)users = db.relationship("协会", back_populates="团队")

但是，当我尝试在关联表中插入角色时:

t = Team.query.filter_by(id=team_name1_id).first()a = 关联(角色=表单.角色.数据)a.user = User.query.filter_by(id=current_user.id).first()t.users.append(a)db.session.add(t)db.session.commit()

我收到此错误:

<块引用>

IntegrityError:(由于查询调用的自动刷新而引发；如果正在发生此刷新，请考虑使用 session.no_autoflush 块过早) (sqlite3.IntegrityError) NOT NULL 约束失败:Association.team_id [SQL: u'INSERT INTO 关联(user_id, role)VALUES (?, ?)'] [参数:(1, u'控球后卫')]

你知道我该如何解决吗?

解决方案

导致 autoflush 是

t.users.append(a)

您尚未定义关系加载策略，所以他们默认为 选择".由于您是第一次访问 Team.users 关系属性，它将发出 SELECT 以获取相关对象.

您还间接对会话进行了更改

a.user = User.query.filter_by(id=current_user.id).first()

由于保存更新级联，默认情况下，将新的 Association 实例也放置到会话中.

为了保持数据库和会话的状态一致，SQLAlchemy 必须 flush 在发出查询之前对数据库所做的更改，因此在与 Team 实例关联之前，将一半初始化的 Association 发送到数据库.

可能的解决方案:

1. 暂时禁用用于该特定附加的自动刷新功能，因为您知道要添加新的Association:

    使用 db.session.no_autoflush:t.users.append(a)

   仍然会发出 SELECT.

2. 反向操作.不是将 Association 实例附加到 Team.users 集合，而是将 Team 分配给 Association.team:

   # 代替 t.users.append(a):a.team = t # 先生

3. 一个不太明显的解决方案是 eager在获取 Team 实例时加载 关系的内容，这样就不需要 SELECT 了:

   t = db.session.query(Team).options(db.joinedload(Team.users)).first()

   或者不使用db.noload(Team.users) 选项.

注意

db.session.add(t)

是多余的.获取的 Team 实例已经在会话中——否则 Team.users 的延迟加载 不可能有 首先进行.

I'm trying to insert the role data in this association table:

class Association(db.Model):
    __tablename__ = 'associations'
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    team_id = db.Column(db.Integer, db.ForeignKey('teams.id'), primary_key=True)
    role = db.Column(db.String)
    user = db.relationship("User", back_populates="teams")
    team = db.relationship("Team", back_populates="users")

It's linked to 2 other tables:

class User(db.Model, UserMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String)
    username = db.Column(db.String, nullable=False, unique=True, index=True)
    password_hash = db.Column(db.String)
    teams = db.relationship("Association", back_populates="user")

class Team(db.Model):
    __tablename__ = 'teams'
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String)
    player = db.Column(db.String)
    users = db.relationship("Association", back_populates="team")

However, when I try to insert the role in the association table:

t = Team.query.filter_by(id=team_name1_id).first()
a = Association(role=form.role.data)
a.user = User.query.filter_by(id=current_user.id).first()
t.users.append(a)
db.session.add(t)
db.session.commit()

I get this error:

IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) NOT NULL constraint failed: association.team_id [SQL: u'INSERT INTO association (user_id, role) VALUES (?, ?)'] [parameters: (1, u'point guard')]

Do you know how can I fix it?

解决方案

The line that causes the autoflush is

t.users.append(a)

You've not defined your relationship loading strategies, so they default to "select". Since you're accessing the Team.users relationship attribute for the first time, it will emit a SELECT in order to fetch the related objects.

You've also made changes to the session indirectly in

a.user = User.query.filter_by(id=current_user.id).first()

which due to save-update cascade, on by default, places the new Association instance to the session as well.

In order to keep the DB's and session's state consistent SQLAlchemy has to flush your changes to the DB before the query is emitted, and so the half initialized Association is sent to the DB before being associated with the Team instance.

Possible solutions:

1. Temporarily disable the autoflush feature for that particular append because you know you're adding a new Association:

   with db.session.no_autoflush:
       t.users.append(a)
   

   The SELECT will still be emitted, though.

2. Reverse the operation. Instead of appending the Association instance to the Team.users collection, assign the Team to the Association.team:

   # Instead of t.users.append(a):
   a.team = t  # Mr.
   

3. A less obvious solution is to eager load the contents of the relationship when you fetch the Team instance, so that no SELECT is necessary:

   t = db.session.query(Team).options(db.joinedload(Team.users)).first()
   

   or not load them at all using the db.noload(Team.users) option.

Note that

db.session.add(t)

is redundant. The fetched Team instance is already in the session – otherwise the lazy load of Team.users could not have proceeded in the first place.

这篇关于使用 SQLAlchemy 在关联表中插入数据时出现 IntegrityError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！