"NOT NULL constraint failed" when persisting ORM object

user_14387867 0
Viewed 679

I am using FlaskSQLAlchemy to connect to a SQLite database. When making an instance of a model, it gives him all the column values except the id which is the primary key.

models.py (only important columns):

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)

When this part of the function gets executed, I get an error.

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

The print statement prints id that is None, but prints other attributes(not relationships) just fine. As a result of the person id being None and order.person_id being NOT NULLABLE, I get the following error:

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)

Why is the person id None?

flask sqlalchemy sqlite python
1 Answers

You are seeing null values because you are not taking advantage of the relationships between your objects. For a model like

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}')>"

if I create a User object and an Address object

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

we can see that the primary key id is currently None, the foreign key user_id is also currently None, but SQLAlchemy is nonetheless aware that the address belongs to <User(name='Gord')> via the user relationship.

The situation is the same after we add the objects to the session

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

but after we flush() the session the primary key and foreign key values have been created (by creating the table rows within a transaction) and applied to the object

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

Moral: Avoid manipulating foreign key values directly, especially when creating new pairs of related objects. Instead, use a relationship to associate the objects and let SQLAlchemy take care of the key values for you.

user_21443900
Related