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
– return 0, Commented Jan 14, 2016 at 7: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: flask-sqlalchemy.pocoo.org/2.1/quickstart — kylieCatt
– kylieCatt, Commented Jan 14, 2016 at 7:51
@IanAuld It doesn't matter what I use for web framework, the code above is not related to flask at all. — return 0
– return 0, Commented Jan 14, 2016 at 7:57
After adding autoincrement, did you delete the database file and create it again? create_all doesn't perform migrations. — dirn
– dirn, Commented Jan 14, 2016 at 12:38

Community · Accepted Answer · 2017-05-23 12:24:53Z

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?

antonio_antuan · Accepted Answer · 2016-02-15 20:05:03Z

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)

Collectives™ on Stack Overflow

NOT NULL constraint failed for SQLachemy model

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related