Many-to-many relationships in sqlalchemy
I just switched from MySQL to SQLalchemy and I have to say that I have a harder time getting my head over sqlalchemy than I thought. I am currently having problems with many relationships. I have users and queries in my model. The user may have many requests and the request suggests a new article to read every day. I want to keep track of which user read which request on which day. 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 requests. To store which user read which request at which date, I have a ReadIndex class that has an additional field named read_datetime where I store the association date of the user request. I want to add 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 respective IDs for selecting my objects. If I run this I get the 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'm not sure what the problem is? I don't seem to be breaking NOT NOT NULL
edit: I noticed that I assigned query = None to a.read_query. Fixing this changed the error slightly
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's what's going on:
>>> 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')]
limitation? thanks Karl
source to share
You are getting this error because the user_id and query_id fields in the ReadIndex table are None. So when you commit, the ReadIndex object tries to create without having values ββin its primary key fields. So do it,
user = models.User.query.filter_by(id=user_id).first() query = models.Queries.query.filter_by(id=query_id).first() a=models.ReadIndex(user_id=user.id,query_id=query.id,read_datetime=dt.utcnow()) db.session.add(a) db.session.commit()
You don't need to manually add anything to the relationship. SQLAlchemy does this automatically. You can cross check if you like
for ud in user.read_dates:
print(ud)
Hope this helps!
source to share