NOT NULL constraint failed for SQLachemy model

Question

I have recently started working with database. I am trying to set up a model using SQLalchemy. The models.py and database.py are shown as below:

#models.py

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Expense(Base):
    __tablename__ = 'expense'
    id = sa.Column(sa.BigInteger, primary_key=True)
    username = sa.Column(
                        sa.String,
                        info={'label': 'Name'},
                        nullable=False
    )
    def __init__(self, username):
        self.username = username

    def __repr__(self):
        return '<User %r>' % (self.username)

and

#database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Base, Expense


def init_db():
    engine = create_engine('sqlite:///test.db')
    Base.metadata.create_all(engine)
    Base.metadata.bind = engine
    DBSession = sessionmaker(bind=engine)
    session = DBSession()

    new_entry = Expense(username="noobie")
    session.add(new_entry)
    session.commit()

init_db()

I am running these on python3, and when I run database.py, I get

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: expense.id [SQL: 'INSERT INTO expense (username) VALUES (?)'] [parameters: ('noobie',)]

One response here suggests that using sqlite_autoincrement=True will solve the problem, but it didn't work on mine. I think the problem occurs somewhere between SQLite3 and sqlalchemy when assigning the primary key... I don't know neither well. Please help this poor noobie, thanks!

@AndyK Thanks for pointing that out. Unfortunately, that's due to my poor copy-pasting skill. The problem isn't in my real code. — return 0, Jan 14 '16 at 07:14
I haven't used SQLAlchemy in a while but I'm fairly certain that is not how you set it up for a Flask app. You should actually be using `flask-sqlalchemy` over the standard SQLAlcehmy: http://flask-sqlalchemy.pocoo.org/2.1/quickstart/ — kylieCatt, Jan 14 '16 at 07:51
@IanAuld It doesn't matter what I use for web framework, the code above is not related to flask at all. — return 0, Jan 14 '16 at 07:57
Your title would seem to indicate otherwise. I'm pretty sure I have had this problem in the past and the way I solved was by using `flask-sqlalchemy`. — kylieCatt, Jan 14 '16 at 07:59
@return0 Me too , I have poor copy-paste skills ! :) Excellent! Have a good day :))) — Andy K, Jan 14 '16 at 08:29
After adding `autoincrement`, did you delete the database file and create it again? `create_all` doesn't perform migrations. — dirn, Jan 14 '16 at 12:38
@dirn I originally had autoincrement and had this problem, then I deleted this "autoincrement" and still have the problem. — return 0, Jan 14 '16 at 19:25

score 3 · Answer 1 · edited May 23 '17 at 12:24

3

I think the reason may be BIGINT is not allowed an primary key with autoincrement in sqlite. see this: Does BIGINT auto increment work for SQLAlchemy with sqlite?

edited May 23 '17 at 12:24

Community

1
1

answered Dec 15 '16 at 17:45

wei zhu

159
1
3

score 0 · Answer 2 · answered Feb 15 '16 at 20:05

0

I haven't much experience in sqlite3,but in postgresql you should use autoincrement=True with your pkey column.

So, try to implement this with id column like this:

id = sa.Column(sa.BigInteger, primary_key=True, autoincrement=True)

answered Feb 15 '16 at 20:05

antonio_antuan

1,293
9
22

NOT NULL constraint failed for SQLachemy model

2 Answers2