How to implement "relationship" caching system in a similar query?

user_330867 0
Viewed 25

I noticed that when having a Model such as :

class User(Model):
    id = ...
    books = relationship('Book')

When calling user.books for the first time, SQLAlchemy query the database (when lazy='select' for instance, which is the default), but sub-sequent call to user.books don't call the database. The results seems to have been cached.

I'd like to have the same feature from SQLAlchemy when using a method that query, for instance:

class User:
    def get_books(self):
        return Book.query.filter(Book.user_id == self.id).all()

But when doing that, if I call 3 times get_books(), SQLAlchemy does call the database 3 times (when setting the ECHO property to True).

How can I change get_books() to use the caching system from SQLAlchemy ?

I insist to mention "from SQLAlchemy" because I believe they handle the refresh/expunge/flush system and changes are then re-queried to the DB if one of these happened. Opposed to if I were to simply create a caching property in the model with a simple:

def get_books(self):
    if self._books is None:
        self._books = Book.query.filter(Book.user_id == self.id).all()
    return self._books

This does not work well with flush/refresh/expunge from SQLAlchemy.

So, How can I change get_books() to use the caching system from SQLAlchemy ?

sqlalchemy python-3.x
1 Answers

Edit 1:

I realized that the solution provided under is not perfect, because it caches for the current object. If you have two instances of the same user, and call get_books on both, two queries will be made because the caching applies only on the instance, not globally, contrary to SQLAlchemy.

The reason is simple - I believe - but still unclear how to apply it in my case: The object is defined at the class level, not the instance (books = relationship()), and they build their own query internally, so they can cache it based on the query.

In the solution I gave, the memoize_getter is unaware of the query made, and as such, cannot cache it for the same value accros multiple instance, so any identical call made to another instance will query the database.

Original answer:

I've been trying to wrap my head around SQLAlchemy's code (wow that's dense!), and I think I figured it out!

A relationship, at least when being set as "lazy='select'" (default), is a InstrumentedAttribute, which contains a get function that does the following :

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        try:
            state = instance_state(instance)
        except AttributeError as err:
            util.raise_(
                orm_exc.UnmappedInstanceError(instance),
                replace_context=err,
            )
        return self.impl.get(state, dict_)

So, a basic caching system, respecting SQLAlchemy, would be something like:

from sqlalchemy.orm.base import instance_dict

def get_books(self):
    dict_ = instance_dict(self)
    if 'books' not in dict_:
        dict_['books'] = Book.query.filter(Book.user_id == self.id).all()

    return dict_['books']

Now, we can push the vice a bit further, and do ... a decorator (oh sweet):

def memoize_getter(f):
    @functools.wraps(f)
    def decorator(instance, *args, **kwargs):
        property_name = f.__name__.replace('get_', '')
        dict_ = instance_dict(instance)
        if property_name not in dict_:
            dict_[property_name] = f(instance, *args, **kwargs)

        return dict_[property_name]

    return decorator

Thus transforming the original method to :

class User:
    @memoize_getter
    def get_books(self):
        return Book.query.filter(Book.user_id == self.id).all()

If someone has a better solution, I'm eagerly interested!

user_3308670
Related