时间戳

首页 > 解决方案 > 如何通过flask-sqlalchemy中的关联对象以多对多关系附加对象

python - 如何通过flask-sqlalchemy中的关联对象以多对多关系附加对象

问题描述

我有一个 User 类、Tag Class 和 UserTag 类，它是关联对象。UserTag 是一个关联对象，因为我需要它来存储一个额外的列 tag_type。
以下是代表我的表的三个类：

class UserTag(db.Model):
    __tablename__ = 'usertag'
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    tag_id = db.Column(db.Integer, db.ForeignKey('tag.id'), primary_key=True)
    tag_type = db.Column(db.Boolean, unique=False, nullable=False)
    tag = db.relationship('Tag', back_populates='users')
    user = db.relationship('User', back_populates='tags')


class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    bio = db.Column(db.String(160), unique=False, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpeg')
    password = db.Column(db.String(60), nullable=False)
    tags = db.relationship('UserTag', back_populates='user')


class Tag(db.Model):
    __tablename = 'tag'
    id = db.Column(db.Integer, primary_key=True)
    tagname = db.Column(db.String(120), unique=True, nullable=False)
    users = db.relationship('UserTag', back_populates='tag')

我根据此处的文档创建了它们： https
://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object我首先可以通过运行使用 python shell 创建实例并形成关联：

userdemo = User(username='demo', email='demo@email.com', bio='I love python!', password='password')

tpython = Tag(tagname='python')

assoc = UserTag(tag_type=True)

assoc.tag = tpython

userdemo.tags.append(assoc)

我能够成功地将数据添加到数据库会话并提交数据。然而，在第二次运行 python shell 并尝试添加更多标签并将它们与用户关联时，我收到以下警告。

>AWarning: Column 'usertag.user_id' is marked as a member of the primary key for table 'usertag', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed.  Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends.
  util.warn(msg)

发出警告后，我尝试将 UserTag 附加到用户并收到以下错误

> raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (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: usertag.user_id
[SQL: INSERT INTO usertag (tag_id, tag_type) VALUES (?, ?)]
[parameters: (1, 1)]
(Background on this error at: http://sqlalche.me/e/13/gkpj) (Background on this error at: http://sqlalche.me/e/13/7s2a)

现在我无法将 UserTag 关联附加到用户的标签列。我真的不明白发生了什么。

标签： pythonpython-3.xsqlalchemyflask-sqlalchemy

解决方案

我已禁用自动刷新功能作为错误的解决方案。有关该错误的更多信息，请参见此处使用 SQLAlchemy 在关联表中插入数据时的 IntegrityError

推荐阅读