many-to-many relationship in sqlalchemy
Question: I just switched from MySQL to SQLalchemy and I have to say that it is more
difficult to get my head around sqlalchemy than I thought. Currently I have
some trouble with a many-to-many relationship. I have users and queries in my
model. A user can have many queries and a query offers a new article to read
every day. I want to store which user read which query on what date. My
models.py looks like this

    
    
    class User(db.Model):
    
        id = db.Column(db.Integer, primary_key=True)
        read_dates = db.relationship("ReadIndex", backref="user")
    
        def __repr__(self):
            return '<User %r>' % (self.id)
    
    class Queries(db.Model):
    
        id = db.Column(db.Integer, primary_key=True)
        user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    
        def __repr__(self):
            return '<Queries %r>' % (self.id)
    
    
    class ReadIndex(db.Model):
    
        user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
        query_id = db.Column(db.Integer, db.ForeignKey('queries.id'), primary_key=True)
        read_datetime = db.Column(db.DateTime)
        read_query = db.relationship("Queries", backref="user_assocs")
    
        def __repr__(self):
            return '<ReadIndex>'
    

so I have users and queries. To store which user read which query on what date
I have the class ReadIndex, which has an extra field called read_datetime,
where I store the date of a user-query association. I than want to add such an
association like

    
    
    user = models.User.query.filter_by(id=user_id).first()
    query = models.Queries.query.filter_by(id=query_id).first()
    a = models.ReadIndex(read_datetime=dt.utcnow())
    a.read_query = query
    user.read_dates.append(a)
    db.session.commit()
    

where query_id and user_id are the corresponding ids to select my objects. If
I run this I get an error

    
    
    sqlalchemy.exc.IntegrityError
    IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed:
    read_index.query_id [SQL: u'INSERT INTO read_index (user_id, read_datetime) 
    VALUES (?, ?)'] [parameters: (1, '2015-07-29 20:55:50.898366')]
    

I am not sure what the problem is? I don't seem to violate any NOT NULL

edit: I noticed that I assigned query=None to a.read_query. Correcting this
slightly changed the error

    
    
    IntegrityError: (raised as a result of Query-invoked autoflush;  
    consider using a session.no_autoflush block if this flush is occurring prematurely) 
    (sqlite3.IntegrityError) NOT NULL constraint failed: read_index.user_id 
    [SQL: u'INSERT INTO read_index (query_id, read_datetime) VALUES (?, ?)'] 
    [parameters: (1, '2015-07-29 23:11:11.934038')]
    

here is exactly what happens:

    
    
    >>> from app import app, db, models
    >>> user = models.User.query.filter_by(id=1).first()
    >>> user
    <User u'Florian Beutler'>
    >>> query = models.Queries.query.filter_by(id=1).first()
    >>> query
    <Queries 1>
    >>> import datetime
    >>> a = models.ReadIndex(read_datetime=datetime.datetime.utcnow())
    >>> a.read_query = query
    >>> user.read_dates.append(a)
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
      File "/usr/local/lib/python2.7/site-    packages/sqlalchemy/orm/attributes.py", line 237, in __get__
        return self.impl.get(instance_state(instance), dict_)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 578, in get
        value = self.callable_(state, passive)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 529, in _load_for_state
        return self._emit_lazyload(session, state, ident_key, passive)
      File "<string>", line 1, in <lambda>
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 599, in _emit_lazyload
        result = q.all()
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2399, in all
        return list(self)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2515, in __iter__
        self.session._autoflush()
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1292, in _autoflush
        util.raise_from_cause(e)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1282, in _autoflush
        self.flush()
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2004, in flush
        self._flush(objects)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2122, in _flush
        transaction.rollback(_capture_exception=True)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
        compat.reraise(exc_type, exc_value, exc_tb)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2086, in _flush
        flush_context.execute()
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
        rec.execute(self)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
        uow
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj
    mapper, table, insert)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 781, in _emit_insert_statements
        execute(statement, params)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
        return meth(self, multiparams, params)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
        compiled_sql, distilled_params
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
        context)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
        exc_info
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
        context)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) NOT NULL constraint failed: read_index.user_id [SQL: u'INSERT INTO read_index (query_id, read_datetime) VALUES (?, ?)'] [parameters: (1, '2015-07-29 23:17:06.013485')]
    

constraint? thanks carl


Answer: The `.first()` method either returns the first result or `None` if there isn't
one, are you sure you're not assigning `a.read_query` to `None`?

