Flask SQLAlchemy Transaction error when using server_default in model in models_committed signal handler

Question

My model (before) contains client-side defaults:

created_ts = db.Column(db.DateTime(timezone=True), default=dt.datetime.now)

My model (after) now contains server-side defaults:

 created_ts = db.Column(db.DateTime(timezone=True), server_default=text('NOW()'))

However, I now start seeing the error:

InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction.

In my models_committed hook:

@models_committed.connect_via(app)
def handle(sender, changes):
    for model, operation in changes:
        model.to_dict() # error here

I stole to_dict from flask_sandboy:

def to_dict(self):
    """Return dict representation of class by iterating over database
    columns."""
    value = {}
    for column in self.__table__.columns:
        attribute = getattr(self, column.name) # error here
        if isinstance(attribute, datetime.datetime):
            attribute = str(attribute)
        value[column.name] = attribute
    return value

So, getattr(self, column.name) seems to trigger the server-side default somehow (presumably, since that's the change I introduced).

From this line in my own code, I provide the rest of the stack trace:

  File "/code/models/session.py", line 20, in to_dict
    attribute = getattr(self, column.name)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 239, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 589, in get 
    value = callable_(state, passive)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 433, in __call__
    self.manager.deferred_scalar_loader(self, toload)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 613, in load_scalar_attributes
    only_load_props=attribute_names)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 235, in load_on_ident
    return q.one()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2398, in one 
    ret = list(self)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2441, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2454, in _execute_and_instances
    close_with_result=True)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2445, in _connection_from_session
    **kw)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 880, in connection
    execution_options=execution_options)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 885, in _connection_for_bind
    engine, execution_options)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 305, in _connection_for_bind
    self._assert_active()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 196, in _assert_active
    "This session is in 'committed' state; no further "
InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction.

How do I get around this problem?

pip freeze:

aniso8601==0.92
blinker==1.3
boto==2.36.0
Flask==0.10.1
Flask-Cors==1.10.3
Flask-HTTPAuth==2.4.0
Flask-SQLAlchemy==2.0
gunicorn==19.3.0
itsdangerous==0.24
psycopg2==2.6
pytz==2014.10
six==1.9.0
SQLAlchemy==0.9.9
Werkzeug==0.10.1

Are you trying to convert your result(s) to a list of dictionaries? Example results = [{}, {}, {}]? — CodeLikeBeaker, Mar 18 '15 at 12:42
No, I've omitted the code which uses the resulting dictionary. It gets nested in another dictionary which is sent as a JSON message elsewhere with ```pika```. — opyate, Mar 18 '15 at 12:48

score 1 · Answer 1 · edited May 23 '17 at 10:29

TL;DR solution #4 below works

The created_ts field is not known in that request thread, nor in the subsequent signal handler, since the value is chosen server-side by PostgresQL.

getattr(self, column.name) then tries to ~~hydrate~~populate that value by going back to the DB server, but unfortunately is now outside of a transaction.

A few options here:

Start a new session in the signal handler so as to retrieve the value from the DB (this is an untested suggestion)
Use the built-in _dict to get the model's state, but it won't have the created_ts field, because it is not known at this point:

Code

modeldict = dict(model.__dict__)
modeldict.pop('_sa_instance_state', None)

Stick with default=dt.datetime.now, but non-Python apps which write to the database need to provide created_ts.

So, the trade-off is whether the consumer of the model (as alluded to with the pika comment) needs created_ts or not.

(tested to work) Thanks to a combination of advice from agronholm on the #sqlalchemy IRC room and kbussel on this ticket, I tried to serialize the data while the session is still open, and when I'm sure the data is committed, I re-use the serialized data and by-pass going back to the DB:

from sqlalchemy.event import listens_for
from flask.ext.sqlalchemy import SignallingSession

@listens_for(SignallingSession, 'after_flush')
def after_flush_handler(session, tx):
        try:
            d = session._model_changes
        except AttributeError:
            return

        if d:
            changes = []
            for model, operation in list(d.values()):
                changes.append((model.to_dict(), operation))
            session.info['my_changes'] = changes
            d.clear()


@listens_for(SignallingSession, 'after_commit')
def after_commit_handler(session):
        if 'my_changes' in session.info:
            changes = session.info['my_changes']
            for model, operation in changes:
                # use model here, with all data populated

Flask SQLAlchemy Transaction error when using server_default in model in models_committed signal handler

1 Answers1