librelist archives

« back to archive

Flask-SQLAlchemy caching results?

Flask-SQLAlchemy caching results?

From:
Panayiotis Papadopoulos
Date:
2012-08-27 @ 18:41
Hi everyone, in a view I use db.session.query to query my database. Let's
say I have 6 objects that match the query. One row changes and next time I
hit the view I get the same results as previously although the data
changes. If there is a new object match the query (7 now in total) I get
all the data correct. It seems SQLAlchemy caches the data. I have used

    @app.teardown_appcontext
    def shutdown_session(response):
        db.session.remove()
        return response

in my main.py but without any results.

thanks

Re: [flask] Flask-SQLAlchemy caching results?

From:
David McKeone
Date:
2012-08-28 @ 09:33
On 2012-08-27, at 7:41 PM, Panayiotis Papadopoulos wrote:

> Hi everyone, in a view I use db.session.query to query my database. 
Let's say I have 6 objects that match the query. One row changes and next 
time I hit the view I get the same results as previously although the data
changes. If there is a new object match the query (7 now in total) I get 
all the data correct. It seems SQLAlchemy caches the data. I have used 
> 
>     @app.teardown_appcontext
>     def shutdown_session(response):
>         db.session.remove()
>         return response
> 
> in my main.py but without any results.
> 
> thanks 
> 

SQLAlchemy doesn't cache anything, for the most part, so you are likely 
seeing the difference in transactions (perhaps the change that was made 
has not been committed in the other transaction, or the transaction 
remains open)

Might want to take a look at this:

http://docs.sqlalchemy.org/en/latest/orm/session.html#session-frequently-asked-questions

Is the Session a cache ?

Yeee...no. It’s somewhat used as a cache, in that it implements the 
identity map pattern, and stores objects keyed to their primary key. 
However, it doesn’t do any kind of query caching. This means, if you 
saysession.query(Foo).filter_by(name='bar'), even if Foo(name='bar') is 
right there, in the identity map, the session has no idea about that. It 
has to issue SQL to the database, get the rows back, and then when it sees
the primary key in the row, then it can look in the local identity map and
see that the object is already there. It’s only when you say 
query.get({some primary key}) that the Session doesn’t have to issue a 
query.

Additionally, the Session stores object instances using a weak reference 
by default. This also defeats the purpose of using the Session as a cache.

The Session is not designed to be a global object from which everyone 
consults as a “registry” of objects. That’s more the job of a second level
cache. SQLAlchemy provides a pattern for implementing second level caching
using Beaker, via theBeaker Caching example.

Re: [flask] Flask-SQLAlchemy caching results?

From:
Panayiotis Papadopoulos
Date:
2012-08-28 @ 10:16
Yes it can not caching. It was repeatable read and since Flask-SQLAlchemy
session is by default transactional all I had to do was to add:

    @app.teardown_appcontext
    def shutdown_session(response):
        db.session.remove()

which is practically what Flask-SQLAlchemy does but for some reason it was
never called;


On Tue, Aug 28, 2012 at 12:33 PM, David McKeone <david@artsman.com> wrote:

> On 2012-08-27, at 7:41 PM, Panayiotis Papadopoulos wrote:
>
> Hi everyone, in a view I use db.session.query to query my database. Let's
> say I have 6 objects that match the query. One row changes and next time I
> hit the view I get the same results as previously although the data
> changes. If there is a new object match the query (7 now in total) I get
> all the data correct. It seems SQLAlchemy caches the data. I have used
>
>     @app.teardown_appcontext
>     def shutdown_session(response):
>         db.session.remove()
>         return response
>
> in my main.py but without any results.
>
> thanks
>
>
> SQLAlchemy doesn't cache anything, for the most part, so you are likely
> seeing the difference in transactions (perhaps the change that was made has
> not been committed in the other transaction, or the transaction remains
> open)
>
> Might want to take a look at this:
>
> 
http://docs.sqlalchemy.org/en/latest/orm/session.html#session-frequently-asked-questions
>
> Is the Session a cache ?
>
> Yeee...no. It’s somewhat used as a cache, in that it implements the
> identity map pattern, and stores objects keyed to their primary key.
> However, it doesn’t do any kind of query caching. This means, if you say
> session.query(Foo).filter_by(name='bar'), even if Foo(name='bar') is
> right there, in the identity map, the session has no idea about that. It
> has to issue SQL to the database, get the rows back, and then when it sees
> the primary key in the row, *then* it can look in the local identity map
> and see that the object is already there. It’s only when you say
> query.get({some primary key}) that the 
Session<http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session>
doesn’t
> have to issue a query.
>
> Additionally, the Session stores object instances using a weak reference
> by default. This also defeats the purpose of using the Session as a cache.
>
> The 
Session<http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session>
is
> not designed to be a global object from which everyone consults as a
> “registry” of objects. That’s more the job of a *second level cache*.
> SQLAlchemy provides a pattern for implementing second level caching using
> Beaker <http://beaker.groovie.org/>, via the*Beaker 
Caching*<http://docs.sqlalchemy.org/en/latest/orm/examples.html#examples-caching>
>  example.
>
>
>


-- 
Panos Papadopoulos
Founder and VP of Product at BugSense <http://www.bugsense.com/>
Follow us on Twitter @bugsense <http://twitter.com/bugsense>
US Cell (650) 267 0550  EU Cell +30 697 00 1 66 34