ansaurus

Question

How to remember results from querying SQLAlchemy relations (to implement caching)?

Answer 1

+1 A:

Does SA really issue a query every time you access the relation in the same session? IMO, it should not happen, as the result should get cached automatically within the session.

In order to check that no SQL is issued, just turn on logging and see for yourself:

metadata.bind.echo = 'debug'
c = session.query(Child).first() # issues SELECT on your child table(s)
print "child: ", c
print "c.parent: ", c.parent # issues SELECT on your parent table, then prints
print "c.parent: ", c.parent # just prints (no SQL)
print "c.parent: ", c.parent # just prints (no SQL)

Shall your code work otherwise by default, please provide code snippet.

van 2010-02-28 20:30:41

Oh. This was not what I meant. What I meant was remembering results between sessions - proxying them through some kind of a proxy that would cache them. Can this be done somehow?

bodacydo 2010-02-28 20:34:45

That is not what the question asks. What is the point of remembering value of the relation if your object is out of the session? If for example you would like to load object via SA and then detach it, and make sure that you have the relations loaded, the use `lazy=False` in `relation` configuration to `eager` load them. Else describe your use case.

van 2010-02-28 20:41:36

see my other answer if I understood your question now...

van 2010-02-28 20:42:21

Thanks for the solution, it's interesting, but it's not quite what I had imagined... The point of remembering value of the relation is not to query for it again (to minimize load on the database). I would like to store it in memcached, for example, so that I can retrieve the value quickly without doing queries all the time. I imagine that writing `article.category` would retrieve the `category` value from the database only the first time it is ever executed, and all other times it would get it from memcached. Did I explain my idea better now?

bodacydo 2010-02-28 20:52:35

As mentioned in the answer, SA will "cache" the result for you automatically. so actually you do not need to do anything special. Or I really do not understand what you need, in which case an example of the usage would really help. In case you really think of using memcached, then just use it. Alternatively take a look at "Adaptive Replacement Cache" (see http://code.activestate.com/recipes/576532/)

van 2010-03-01 06:11:03

I will try just using memcached. But the thing I don't fully understand how it will cache relations. I will cache the `article` object, but not the relations...

bodacydo 2010-03-01 11:26:59

Then just make it lazy=False, so that the session will load the relations automatically when you load the object.

van 2010-03-01 12:27:21

Answer 2

A:

In case you really just need to cache the result, see below (very similar solution to another question you posted):

class MyChild(Base):
    __tablename__ = 'MyChild'
    id = Column(Integer, primary_key=True)
    parent = relation('Parent')
    # ... other mapped properties

    def __init__(self):
        _parent_cached = None

    @property
    def parent_cached(self):
        if self._parent_cached is None:
            self._parent_cached = self.parent

But in order to have the result when your object is detached from the session you must call this property before detaching. (Also it does not handle situation when the parent is None. Do you always have parent?).

The option with eager load is simplier and once you load the object, you should have the relation loaded already (key is to have lazy=False):

class MyChild(Base):
    __tablename__ = 'MyChild'
    id = Column(Integer, primary_key=True)
    parent = relation('Parent', lazy=False)
    # ... other mapped properties
    ...

van 2010-02-28 20:46:54

ansaurus

tags:

views:

answers:

How to remember results from querying SQLAlchemy relations (to implement caching)?

related questions