Flask SQLAlchemy NOT NULL constraint failed on primary key
Solution 1
Add explicit autoincrement=True
to Trips class definition:
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
If table is created without explicit AUTOINCREMENT, you need to pass Trips.id=NULL in order to increment it, see https://www.sqlite.org/faq.html#q1
Solution 2
In the version: SQLAlchemy 1.2.7 Flask-SQLAlchemy 2.3.2 autoincrement=True is not a required para, but be careful with your primary key definition, since you have defined 'id' column as a primary key, you should not define another column as primary key if not necessary, otherwise, you must set the value of id column by yourself when you inserting the record.
HLH
Updated on July 07, 2022Comments
-
HLH almost 2 years
I'm trying to create a database in SQLite that has two tables, one for a list of airports, and another for a list of trips between pairs of those airports. I've set it up as a self-referential, many-to-many relationship:
class Trips(db.Model): __tablename__ = 'trips' id = db.Column(db.Integer, primary_key=True) airport_from = db.Column(db.Integer, db.ForeignKey('airport.id')) airport_to = db.Column(db.Integer, db.ForeignKey('airport.id')) price = db.Column(db.Float) date = db.Column(db.Date) class Airport(db.Model): __tablename__ = 'airport' id = db.Column(db.Integer, primary_key=True) iata = db.Column(db.String(8), index=True, unique=True) name = db.Column(db.String(120), index=True, unique=True) city = db.Column(db.String(120)) region = db.Column(db.String(120)) country = db.Column(db.String(120)) flying_from = db.relationship('Trips', backref='end', primaryjoin=(id==Trips.airport_to)) flying_to = db.relationship('Trips', backref='start', primaryjoin=(id==Trips.airport_from)) def __repr__(self): return '<Airport: {0}; IATA: {1}>'.format(self.name, self.iata)
When I open up my Python shell and import these models, I have the SQLAlchemy session add Airport objects and commit just fine, but when I do something like:
>>> t = models.Trips(airport_from=3, airport_to=4, price=230.0) >>> db.session.add(t) >>> db.session.commit()
It gives me this traceback:
Traceback (most recent call last): File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context context) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute cursor.execute(statement, parameters) sqlite3.IntegrityError: NOT NULL constraint failed: trips.id
The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/scoping.py", line 150, in do return getattr(self.registry(), name)(*args, **kwargs) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 813, in commit self.transaction.commit() File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 392, in commit self._prepare_impl() File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 372, in _prepare_impl self.session.flush() File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2027, in flush self._flush(objects) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2145, in _flush transaction.rollback(_capture_exception=True) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 183, in reraise raise value File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2109, in _flush flush_context.execute() File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute rec.execute(self) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute uow File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj mapper, table, insert) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 800, in _emit_insert_statements execute(statement, params) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute return meth(self, multiparams, params) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement compiled_sql, distilled_params File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context context) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception exc_info File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 189, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=exc_value) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 182, in reraise raise value.with_traceback(tb) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context context) File "/Users/heli/nomad/flask/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: trips.id [SQL: 'INSERT INTO trips (airport_from, airport_to, price, date) VALUES (?, ?, ?, ?)'] [parameters: (3, 4, 230.0, None)]
The key part seems to be the bottom line:
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: trips.id [SQL: 'INSERT INTO trips (airport_from, airport_to, price, date) VALUES (?, ?, ?, ?)'] [parameters: (3, 4, 230.0, None)]
It looks like because I didn't give a value to the id parameter, that it's refusing to add the object. But I thought that this id would be automatically added and incremented, as happened with the Airport objects' id parameter. What am I missing here?
-
HLH over 8 yearsThanks D, this worked! Any idea as to why I have to do this for Trips, but not when I'm adding an Airport object? I.e. when I create an Airport object without an explicit id, I can add it and commit the session without any issues.
-
MOCKBA over 8 yearsGood question, He ) Is it possible that you're passing Airport.id=NULL when you create new Airport object or Airport table is already defined in SQLite with AUTOINCREMENT attribute?
-
HLH over 8 yearsI double-checked, and I've done neither, although I could be mistaken. It's not a big problem though, as long as everything works
-
Dominik George almost 8 yearsI stumbled across the same issue for no obvious reason. I think it first occured when I reordered table definitions in the source file. dunno. @HeLi Maybe you forgot to accept the answer ;)?