I am trying to create a database in SQLite that has two tables: one for the list of airports and the other for the list of trips between pairs of these airports. I established it as a 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 my Python shell and import these models, my SQLAlchemy session adds Airport objects and captures only a penalty, 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()
This gives me this trace:
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 is represented by 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 seems because I did not give a value to the id parameter that it refuses to add an object. But I thought that this identifier would be automatically added and increased, as happened with the id parameter of airport objects. What am I missing here?