sqlalchemy缓存一些查询 [英] sqlalchemy caching some queries

问题描述

我有这个在现场的网站上运行。当用户登录时，我查询他的个人资料以查看他有多少可用信用。积分通过PayPal购买。如果一个人购买积分和支付来通过，查询仍然显示0学分，即使如果我在phpmyadmin运行相同的查询，它会带来正确的结果。如果我重新启动Apache Web服务器并重新加载页面显示正确的信用数量。这里是我的映射器代码，它显示了每个用户的积分数量：

$ p $ mapper（User，users_table，order_by ='user.date_added DESC，user.id DESC'，properties = {
'userCreditsCount'：column_property（
select（
[func.ifnull（func.sum（orders_table.c.quantity），0）] （
orders_table.c.date_added> get_order_expire_limit（），＃order不能比
orders_table.c.status == STATUS_COMPLETED
））.\
label（'userCreditsCount'），
deferred = True
）
＃其他属性....
））


我使用sqlalchemy与瓶颈框架不使用他们的flask-sqlalchemy包（只是纯sqlalchemy）

以下是我如何启动我的数据库：

  engine = create_en gine（config.DATABASE_URI，pool_recycle = True）
 metadata = MetaData（）
 db_session = scoped_session（sessionmaker（bind = engine，autoflush = True，autocommit = False））
     直到commit（），rollback（）或close（）被调用。使用DBAPI，与数据库的连接也保留在事务中，直到事务被提交或回滚为止。
 
 
 
在这种情况下，当您将数据加载到会话，SQLAlchemy不会刷新数据，直到事务结束（或者如果您使用expire（）明确过期某些部分数据）。这是自然的行为，因为由于交易隔离，这很可能那么当前事务不能看到在任何情况下事务启动以来发生的变化。
所以，当使用expire（）或refresh（）时，可能会或可能不会成为如何将最新数据存入会话的一部分，您真的需要结束交易并开始一个新的交易，以便真正了解交易开始后其他地方发生了什么变化。你应该组织你的应用程序，以便在新的请求进入时准备好一个特定的Session（），但是当这个请求完成时，Session（）应该被关闭，而一个新的请求（或者至少一个新的事务）启动了下一个请求。
 
I have this running on a live website. When a user logs in I query his profile to see how many "credits" he has available. Credits are purchased via paypal. If a person buys credits and the payment comes through, the query still shows 0 credits even though if I run the same query in phpmyadmin it brings the right result. If I restart the apache webserver and reload the page the right number of credits are being shown. Here's my mapper code which shows the number of credits each user has:
mapper( User, users_table, order_by = 'user.date_added DESC, user.id DESC', properties = {
    'userCreditsCount': column_property( 
        select( 
            [func.ifnull( func.sum( orders_table.c.quantity ), 0 )],
            orders_table.c.user_id == users_table.c.id
        ).where( and_( 
            orders_table.c.date_added > get_order_expire_limit(), # order must not be older than a month
            orders_table.c.status == STATUS_COMPLETED
        ) ).\
        label( 'userCreditsCount' ),
        deferred = True
    )
    # other properties....
} )
I'm using sqlalchemy with flask framework but not using their flask-sqlalchemy package (just pure sqlalchemy)

Here's how I initiate my database:
engine = create_engine( config.DATABASE_URI, pool_recycle = True )
metadata = MetaData()
db_session = scoped_session( sessionmaker( bind = engine, autoflush = True, autocommit = False ) )
I learned both python and sqlalchemy on this project so I may be missing things but this one is driving me nuts. Any ideas?
解决方案
when you work with a Session, as soon as it starts working with a connection, it holds onto that connection until commit(), rollback() or close() is called.  With the DBAPI, the connection to the database also remains in a transaction until the transaction is committed or rolled back.

In this case, when you've loaded data into your session, SQLAlchemy doesn't refresh the data until the transaction is ended (or if you explicitly expire some part of the data with expire()).  This is the natural behavior to have, since due to transaction isolation, it's very likely that the current transaction cannot see changes that have occurred since that transaction started in any case.

So while using expire() or refresh() may or may not be part of how to get the latest data into your Session, really you need to end your transaction and start a new one to truly see what's been changed elsewhere since that transaction started.    you should organize your application so that a particular Session() is ready to go when a new request comes in, but when that request completes, the Session() should be closed out, and a new one (or at least a new transaction) started up on the next request.

                        
这篇关于sqlalchemy缓存一些查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！