title: Flask SQLAlchemy NOT NULL constraint failed on primary key
body: <p>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:</p>
  
  <pre><code>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 '&lt;Airport: {0}; IATA: {1}&gt;'.format(self.name, self.iata)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>&gt;&gt;&gt; t = models.Trips(airport_from=3, airport_to=4, price=230.0)
  &gt;&gt;&gt; db.session.add(t)
  &gt;&gt;&gt; db.session.commit()
  </code></pre>
  
  <p>It gives me this traceback:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The above exception was the direct cause of the following exception:</p>
  
  <pre><code>Traceback (most recent call last):
    File "&lt;stdin&gt;", line 1, in &lt;module&gt;
    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)]
  </code></pre>
  
  <p>The key part seems to be the bottom line:</p>
  
  <pre><code>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)]
  </code></pre>
  
  <p>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?</p>