python - "NOT NULL constraint failed"持久化 ORM 对象时

Question

我正在使用 FlaskSQLAlchemy 连接到 SQLite 数据库。在创建模型的实例时，它为他提供了除主键 id 之外的所有列值。
models.py(仅重要列):

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    account_id = db.Column(db.Integer, nullable=True)

class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    order_id = db.relationship("Order", backref="address", lazy=True)

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, nullable=False) # za sada jedan item samo
    person_id = db.Column(db.Integer, nullable=False)

    address_id = db.Column(db.Integer, db.ForeignKey("address.id"), nullable=True)
    account_id = db.Column(db.Integer, db.ForeignKey("account.id"), nullable=True)

class Account(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)

    products = db.relationship("Product", backref="seller", lazy=True)
    orders = db.relationship("Order", backref="buyer", lazy=True)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    seller_id = db.Column(db.Integer, db.ForeignKey("account.id"), nullable=False)

当函数的这部分被执行时，我收到一个错误。

   address = Address(
            country=form.country.data) # and some other data

   db.session.add(address)

   if current_user.is_authenticated:
      person = Person( # and some other data
          account_id=current_user.id)

      db.session.add(person)
      print(person.id) #prints None
      order = Order(
         product_id=product.id,\
         address_id=address.id,\
         person_id=person.id,\
         account_id=current_user.id)

      db.session.add(order)
      db.session.commit()

打印语句打印 id 为 None，但打印其他属性(不是关系)就好了。
由于 person id 为 None 并且 order.person_id 为 NOT NULLABLE，我收到以下错误:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: order.person_id
[SQL: INSERT INTO "order" (product_id, person_id, date, address_id, account_id) VALUES (?, ?, ?, ?, ?)]
[parameters: (26, None, '2021-02-05 11:01:45.668675', 1, 1)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

为什么这个人的id是None？

Answer 1

您看到空值是因为您没有利用对象之间的关系。对于像这样的模型

class Address(Base):
    __tablename__ = "address"
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
    address = db.Column(db.String, nullable=False)
    is_default = db.Column(db.Boolean, nullable=False, default=False)
    user = relationship("User", back_populates="addresses")

    def __repr__(self):
        return (
            f"<Address(user={self.user}, address='{self.address}'"
            f", is_default={self.is_default})>"
        )


class User(Base):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    addresses = relationship("Address", back_populates="user")

    def __repr__(self):
        return f"<User(name='{self.name}')>"

如果我创建一个 User 对象和一个 Address 对象

def show_addr_info(addr):
    print(f"id={addr.id}, user_id={addr.user_id}, user={addr.user}")


with db.orm.Session(engine, future=True) as session:
    gord = User(name="Gord")
    gord_addr = Address(user=gord, address="123 Old Ave", is_default=True)
    show_addr_info(gord_addr)
    # id=None, user_id=None, user=<User(name='Gord')>

我们可以看到主键 id当前是 None ，外键 user_id目前也是 None ，但 SQLAlchemy 仍然知道该地址属于 <User(name='Gord')>通过 user关系。
我们将对象添加到 session 后的情况是一样的

    session.add_all([gord, gord_addr])
    show_addr_info(gord_addr)
    # id=None, user_id=None, user=<User(name='Gord')>

但在我们之后 flush()已创建主键和外键值的 session (通过在事务中创建表行)并应用于对象

    session.flush()
    show_addr_info(gord_addr)
    # id=1, user_id=1, user=<User(name='Gord')>

道德:避免直接操作外键值，尤其是在创建新的相关对象对时。相反，使用 relationship关联对象并让 SQLAlchemy 为您处理键值。