I wrote a little script that iterates a feed in a social network and publishes not yet published postings in another social network. I do that in 2 steps: first I read the feed of the first social network and put every post in my database, including a 'published' boolean. Second, I go through all entries in my database where the published boolean is set false and publish the messages on the second social network. I use the dataset library to connect to my local SQLite database. Now the problem is: everything works fine within the first step. In the second step I iterate the database entries with the mentioned boolean set to false, post the entry to the second social network and then I want to update the boolean in my database. There lies the problem: The update works only once, and then I get the following error:
Traceback (most recent call last):
File "xy.py", line 56, in <module>
table.update(data, ['sc_id'])
File "/Users/.../lib/python2.7/site-packages/dataset/persistence/table.py", line 137, in update
rp = self.database.executable.execute(stmt)
File "/Users/.../lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1752, in execute
return connection.execute(statement, *multiparams, **params)
File "/Users/.../lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
return meth(self, multiparams, params)
File "/Users/.../lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/Users/.../lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
compiled_sql, distilled_params
File "/Users/.../lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 997, in _execute_context
self._commit_impl(autocommit=True)
File "/Users/.../lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 530, in _commit_impl
self.connection._reset_agent is self.__transaction:
File "/Users/.../lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 236, in connection
return self._revalidate_connection()
File "/Users/.../lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 247, in _revalidate_connection
raise exc.ResourceClosedError("This Connection is closed")
sqlalchemy.exc.ResourceClosedError: This Connection is closed
This is the way I update the database field (according to the dataset documentation):
unpublished_messages = table.find(published = False)
for posting in unpublished_messages:
# here i post to the second social network...
data = dict(sc_id = posting['sc_id'], published = True)
table.update(data, ['sc_id'])
It works perfectly fine in the first step, and it works once in the second step when iterating the database entries, but at the second loop iteration it crashes at the table.update... line.
I absolutely don't understand what's going on.
Somehow one can not iterate through the ResultIter ('unpublished_messages') and modify the table in the same step. I solved this by going through the ResultIter and putting the elements into an ordinary python list. Then I iterated the list and updated the table.