SqlAlchemy cache with dogpile

Question

I have three models with inheritance and relationship and I want to cache query to this models.

class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    type = Column(String(50))
    __mapper_args__ = {
        'polymorphic_identity': 'object',
        'polymorphic_on': type
        }

class Man(Person):
    __tablename__ = 'man'
    id = Column(Integer, ForeignKey('person.id'), primary_key=True)
    age = Column(String(100), nullable=False)
    __mapper_args__ = {'polymorphic_identity': 'man'}

class Config(Base):
    __tablename__ = "config"
    id = Column(Integer, primary_key=True)
    person = Column(Integer, ForeignKey('person.id'))
    address = Column(String)
    person_ref = relationship(Person)

There are a lot of others models inherited from Personal. For example I need to get access to Man attributes through Config relationship. Normally I would do:

config = session.query(Config).join(Config.person_ref).filter(Person.type == 'man').first()
print config.person_ref.age

How can I cache query like this with dogpile? I can cache query to Config, but I can't cache query to attributes of Man, emits SQL every time. I tried to use with_polymorphic, but it's only works without joinedload. (don't undestand why)

config = session.query(Config).options(FromCache("default")).first()
people = session.query(Person).options(FromCache("default")).with_polymorphic('*').get(config.person)

but I need joinedload to filter for types.

define "can't". Stack trace? no results? emits SQL every time? not clear. — zzzeek, Apr 19 '14 at 23:04
not sure then, you need to look at the cache keys that are being generated when the query is being called. Use pdb.set_trace() to step through what's happening. Caching is not simple which is why this isn't a built-in feature; by having it as a recipe it's to encourage users to step through it. — zzzeek, Apr 20 '14 at 15:31
@zzzeek How do you think it should work? When I use **joined**, it join only **Person** table and when I try to access **Man** attributes, SQL emit, because attributes of inherited object lazy loaded. — aborilov, Apr 21 '14 at 05:40
if you want the lazy-loaded attributes to be cached, your two options are 1. use joinedload() or 2. use the RelationshipCache feature that's also illustrated in the dogpile example. — zzzeek, Apr 21 '14 at 14:35
@zzzeek Could you show some example for my models. Because as I tested, both joinedload and RelationshipCache load only **Person** attribute, for **Man** attributes emits SQL. — aborilov, Apr 21 '14 at 14:44

zzzeek · Accepted Answer · 2014-04-24T14:18:43.730

5

in order to ensure that the "man" table is loaded, of_type() can be used for any pattern of subtypes. We can instead join to a full polymorphic selectable using with_polymorphic(). See the examples at http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#creating-joins-to-specific-subtypes for details on this. As long as the data you want comes out in one SELECT query, then that data will be within what is cached via FromCache. It's true that the caching recipe does not currently include a system by which the deferred load of additional joined inheritance attributes can be cached after the fact.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from examples.dogpile_caching.caching_query import query_callable, FromCache, RelationshipCache
from hashlib import md5
from dogpile.cache.region import make_region

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    type = Column(String(50))
    __mapper_args__ = {
        'polymorphic_identity': 'object',
        'polymorphic_on': type
        }

class Man(Person):
    __tablename__ = 'man'
    id = Column(Integer, ForeignKey('person.id'), primary_key=True)
    age = Column(String(100), nullable=False)
    __mapper_args__ = {'polymorphic_identity': 'man'}

class SomethingElse(Person):
    __tablename__ = 'somethingelse'

    id = Column(Integer, ForeignKey('person.id'), primary_key=True)
    age = Column(String(100), nullable=False)
    __mapper_args__ = {'polymorphic_identity': 'somethingelse'}

class Config(Base):
    __tablename__ = "config"
    id = Column(Integer, primary_key=True)
    person = Column(Integer, ForeignKey('person.id'))
    address = Column(String)
    person_ref = relationship(Person)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

def md5_key_mangler(key):
    """Receive cache keys as long concatenated strings;
    distill them into an md5 hash.

    """
    return md5(key.encode('ascii')).hexdigest()

regions = {}
regions['default'] = make_region(
            key_mangler=md5_key_mangler
            ).configure(
                'dogpile.cache.memory_pickle',
            )

Session = scoped_session(
                sessionmaker(
                    bind=e,
                    query_cls=query_callable(regions)
                )
            )

sess = Session()
sess.add(Config(person_ref=SomethingElse(age='45', name='se1')))
sess.add(Config(person_ref=Man(age='30', name='man1')))
sess.commit()

all_types = with_polymorphic(Person, "*", aliased=True)

conf = sess.query(Config).options(joinedload(Config.person_ref.of_type(all_types)), FromCache("default")).first()
sess.commit()
sess.close()

print "_____NO MORE SQL!___________"


conf = sess.query(Config).options(joinedload(Config.person_ref.of_type(all_types)), FromCache("default")).first()
print conf.person_ref.age

edited Apr 24 '14 at 14:18

answered Apr 21 '14 at 17:57

zzzeek

72,307
23
193
185

as I undestand I can't cache query like this `man = session.query(Config).options(joinedload(Config.person_ref)).filter(Person.type == 'man').first()`. As I said in question, I have a lot of objects inherits from Person and I have to do `with_polymorphic('*')`, that's not good I think. – aborilov Apr 22 '14 at 07:26
that query does not make sense. you are querying only for Config, not Person, so you can't filter on Person.type like that - it will produce a cartesian product. the joinedload() is a separate aspect of the query, please read http://docs.sqlalchemy.org/en/latest/orm/loading.html#the-zen-of-eager-loading. the example and documentation above illustrates the correct approaches to joinedload objects limiting to a certain type. – zzzeek Apr 22 '14 at 20:27
Sorry, my bad. Query is `session.query(Config).join(Config.person_ref).options(joinedload(Config.person_ref)).filter(Person.type == 'man').first()` and I can cache it with **FromCache**, but if I access to attributes of **Man** new SQL emits. In real app I didn't event filter by **type**, I filter by some of **Config** attributes and print out all attributes of **person_ref** (I don't know it type). Only solution that I see is to use **with_polymorphic('*')**. Can you tell my where I wrong? – aborilov Apr 23 '14 at 12:17
yes, its works, but if you do `sess.commit()` before `sess.close()` like I do, sql `SELECT somethingelse.id AS somethingelse_id, person.id AS person_id, person.name AS person_name, person.type AS person_type, somethingelse.age AS somethingelse_age FROM person JOIN somethingelse ON person.id = somethingelse.id WHERE person.id = ? 2014-04-24 11:03:23,975 INFO sqlalchemy.engine.base.Engine (1,)` emits. I also have to expunge all objects in `CachingQuery.__iter__` as describes in the doc of method. – aborilov Apr 24 '14 at 07:04
that's an artifact of the cache backend, in that it isn't producing copies of the object so the second query returns the same one that contains expired attributes. example is updated with memory_pickle backend. – zzzeek Apr 24 '14 at 14:21
Now its work! Thanks a lot. Is any options to do this without **with_polymorphic('*')** ? – aborilov Apr 25 '14 at 07:02
you can put the with_polymorphic on your mapper config directly, if you want to emit that whole series of OUTER JOINs every time – zzzeek Apr 25 '14 at 16:18
no, I mean without so many OUTER JOINs(like it would be if I don't use cache). I have a lot of objects inherited from **Person**. It would be big SQL. – aborilov Apr 25 '14 at 16:42
you've got the data you want to load across that many tables. what kind of SQL do you want ? you can of course hand-write whatever you think it should be – zzzeek Apr 26 '14 at 13:02
if the question is, is there a cache hook for that second load it does, not right now. – zzzeek Apr 26 '14 at 13:03
Yes, the question was this. Thank you again. By the way, I choose to use **cache_on_arguments** , it's much faster on my arm device – aborilov Apr 26 '14 at 15:31

SqlAlchemy cache with dogpile

1 Answers1