python-2.7 - 在 models_committed 信号处理程序中的模型中使用 server_default 时出现 Flask SQLAlchemy 事务错误

Question

我的模型(之前)包含客户端默认值:

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

我的模型(之后)现在包含服务器端默认值:

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

但是，我现在开始看到错误:

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

在我的 models_committed 钩子(Hook)中:

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

我从 flask_sandboy 中窃取了 to_dict :

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

因此，getattr(self, column.name) 似乎以某种方式触发了服务器端默认设置(大概是因为这是我引入的更改)。

从我自己代码中的这一行，我提供了堆栈跟踪的其余部分:

  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.

我该如何解决这个问题？

点卡住:

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

Answer 1

TL;下面的 DR 解决方案 #4 有效

created_ts 字段在该请求线程中是未知的，在随后的信号处理程序中也是未知的，因为该值是由 PostgresQL 在服务器端选择的。

getattr(self, column.name) 然后尝试 hydrate 通过返回到数据库服务器来填充该值，但不幸的是现在不在事务中。

这里有几个选项:

1. 在信号处理程序中启动一个新 session ，以便从数据库中检索值(这是一个未经测试的建议)
2. 使用内置的 _dict 获取模型的状态，但它不会有 created_ts 字段，因为此时它是未知的:<

代码

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

1. 坚持使用 default=dt.datetime.now，但写入数据库的非 Python 应用需要提供 created_ts。

因此，权衡是模型的消费者(如 pika 评论所暗示的那样)是否需要 created_ts。

1. (测试工作)感谢 agronholm 在 #sqlalchemy IRC 房间和 kbussel 在 this ticket 上的建议，我尝试在 session 仍处于打开状态时序列化数据，当我确定数据已提交时，我重新使用序列化数据并绕过返回数据库:

   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