为什么SQLAlchemy执行更新不起作用 [英] Why is SQLAlchemy execute Update not working

问题描述

我有以下内容:

@periodic_task(run_every=crontab(minute="*/1"))
def PeriodicUpdateAgentLastRica():
    query = """update agents
            inner join
               (select sims.consignment_agents_id as agents_id,
                MAX(sims.rica_current_stamp) 
            as last_rica_stamp
            from sims
            where sims.rica_current_stamp > DATE_SUB(now(), INTERVAL 3 MONTH) and sims.consignment_agents_id is not NULL
            group by sims.consignment_agents_id) as dt on dt.agents_id = agents.id
            set agents.last_rica_stamp = dt.last_rica_stamp"""
    res = Session.execute(query)
    print res
    if res.rowcount:
        log.info("Updated %s agents" % res.rowcount)
    #transaction.commit()

当我运行celery worker -B时，将返回以下内容:

When I run celery worker -B This is what is returned:

[2014-02-12 09:15:00,012: INFO/MainProcess] Received task: prepaid.models.Prepaid.PeriodicUpdateAgentLastRica[9ca091c8-595f-4163-8ddf-2742e573b90c]
[2014-02-12 09:15:01,812: WARNING/Worker-7] <sqlalchemy.engine.result.ResultProxy object at 0x776f310>
[2014-02-12 09:15:01,813: INFO/Worker-7] Updated 2923 agents 
[2014-02-12 09:15:01,816: INFO/MainProcess] Task prepaid.models.Prepaid.PeriodicUpdateAgentLastRica[9ca091c8-595f-4163-8ddf-2742e573b90c] succeeded in 1.798980095s: None

即使它说它已更新:Updated 2923 agents，当我检查数据库时，记录也没有改变.

Even though it says that it updated: Updated 2923 agents, when I check the DB, no record is changed.

查询没有问题，因为当我在mySQL Workbench中运行该查询时就可以正常工作.

There is nothing wrong with the query, as when I run it in mySQL Workbench it works.

当我尝试强制 AutoCommit 时，也会发生同样的情况，即

The same happens when I try forcing AutoCommit, i.e.

res = Session.execute(text(query).execution_options(autocommit=True))

所以它提交了，但是Update仍然没有影响数据库. 为什么不影响数据库?为什么它不起作用?

So it committing, but still the Update is not affecting the DB. Why is it not affecting the database? Why is it not working?

更新

我也尝试过这样做:

with transaction.manager:
    #Rest of code

但是它仍然不会改变数据库中的任何内容

But it still does not change anything in the DB

推荐答案

这似乎是您在金字塔中使用zodb事务管理器.

This looks like you are using zodb transaction manager in pyramid.

您必须将会话标记为已更改

You have to mark the session as changed

http://www.upfrontsystems.co.za/Members/izak/sysadman/how-to-commit-a-transaction-even-when-sqlalchemy-thinks-the-session-is-干净

您应该做类似的事情

from zope.sqlalchemy import mark_changed
mark_changed(Session)

如果这不起作用，请尝试

if that does not work try

from zope.sqlalchemy import ZopeTransactionExtension
Session.configure(extension=ZopeTransactionExtension('changed'))

这篇关于为什么SQLAlchemy执行更新不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！