query.all() tries to update table having no priviledges

[Former user]

It seems like sqlalchemy tries to fix text fields with bad symbols inside(though I'm not sure about that) via update but can't do that as a user have read-only access. And raises an exception.

Code:

q = session.query(TvVideosLng)
q = q.filter( TvVideosLng.id > last_id )
q = q.limit( limit )
rows = q.all()

Traceback: rows = q.all() File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/query.py", line 1343, in all return list(self) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/query.py", line 1450, in iter self.session._autoflush() File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/session.py", line 879, in _autoflush self.flush() File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/session.py", line 1348, in flush self._flush(objects) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/session.py", line 1429, in _flush flush_context.execute() File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 293, in execute rec.execute(self) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 426, in execute uow File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/mapper.py", line 1523, in _save_obj execute(statement, params) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py", line 1109, in execute return Connection.executorsc(self, object, multiparams, params) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py", line 1186, in _execute_clauseelement return self.__execute_context(context) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py", line 1215, in __execute_context context.parameters0, context=context) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py", line 1284, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py", line 1282, in _cursor_execute self.dialect.do_execute(cursor, statement, parameters, context=context) File "/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/default.py", line 277, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python2.6/site-packages/MySQL_python-1.2.3c1-py2.6-linux-i686.egg/MySQLdb/cursors.py", line 173, in execute File "/usr/local/lib/python2.6/site-packages/MySQL_python-1.2.3c1-py2.6-linux-i686.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler sqlalchemy.exc.OperationalError: (OperationalError) (1142, "UPDATE command denied to user 'myname@myhost' for table 'table_name'")

Using unladen swallow, sqlalchemy 0.6, mysql, declarative.

[Mike Bayer]

query.all() issues an autoflush if you have autoflush=True on your session, which is the default. This is documented here: http://www.sqlalchemy.org/docs/session.html#flushing

If your database is read only, turn this setting off.