I have set up a simple system for adding comments to items in my database using a one-to-many relation (one item can have many comments), but I can't seem to get it to work. It should be simple, I know. It's probably something simple that I've overlooked. I would very much appriciate a second set of eyes on this. Can anyone help?
I have a model defined with SQLAlchemy that looks like this:
class Item(Base):
__tablename__ = 'items' # Parent
id = Column(Integer, primary_key=True)
comments = relationship("Comment", backref='items')
class Comment(Base):
__tablename__ = 'comments' # Child
id = Column(Integer, primary_key=True)
comment_text = Column(Text, nullable=False)
item_id = Column(Integer, ForeignKey('items.id'), nullable=False)
def __init__(self, comment_text, item_id):
self.comment_text = comment_text
self.item_id = item_id
In my view, I do some work, then try to add a comment object:
item = DBSession.query(Item).filter(Item.id == item_id).first()
try:
print('Item id:', item.id, 'Comment text:', comment)
print('Item Comments:', item.comments)
cm = Comment(comment_text=comment,
item_id=item.id)
print('a')
item.comments.append(cm)
#DBSession.add(cm)
print('b')
DBSession.commit()
except:
DBSession.rollback()
print('c')
Note that I tried both item.comments.append(cm) and DBSession.add(cm) with identical results. That's why one of the two is commented out in the above code block. I also tried item.comments.append(Comment(...)) with identical results.
Now, when I try to add a comment, I get a stacktrace, culminating in:
sqlalchemy.exc.ResourceClosedError: This transaction is closed
The whole trace, including the debug prints, look like this:
Item id: 1 Comment text: test
Item Comments: []
a
c
Traceback (most recent call last):
File "C:\Python33\lib\wsgiref\handlers.py", line 137, in run
self.result = application(self.environ, self.start_response)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\pyramid\router.py", line 251, in __call__
response = self.invoke_subrequest(request, use_tweens=True)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\pyramid\router.py", line 227, in invoke_subrequest
response = handle_request(request)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\pyramid\tweens.py", line 21, in excview_tween
response = handler(request)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\pyramid_tm\__init__.py", line 82, in tm_tween
reraise(*exc_info)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\pyramid_tm\compat.py", line 13, in reraise
raise value
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\pyramid_tm\__init__.py", line 70, in tm_tween
manager.commit()
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\transaction\_manager.py", line 111, in commit
return self.get().commit()
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\transaction\_transaction.py", line 280, in commit
reraise(t, v, tb)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\transaction\_compat.py", line 55, in reraise
raise value
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\transaction\_transaction.py", line 271, in commit
self._commitResources()
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\transaction\_transaction.py", line 417, in _commitResources
reraise(t, v, tb)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\transaction\_compat.py", line 55, in reraise
raise value
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\transaction\_transaction.py", line 394, in _commitResources
rm.tpc_vote(self)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\zope\sqlalchemy\datamanager.py", line 100, in tpc_vote
self.tx.commit()
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\sqlalchemy\orm\session.py", line 352, in commit
self._assert_active(prepared_ok=True)
File "C:\Users\[user]\PYTHON~1.5\lib\site-packages\sqlalchemy\orm\session.py", line 203, in _assert_active
raise sa_exc.ResourceClosedError(closed_msg)
sqlalchemy.exc.ResourceClosedError: This transaction is closed
Well, turns out the problem was a few corrupt files, not a programming error. Sigh. Well, problem solved. :)