Determining actual loaded/unloaded state of sqlalchemy object and its relations

In my case, queries to underlying database are pretty expensive, so we're trying to save database requests wherever possible.

Here's simplified database which I'll use for examples:

items_table = Table("invtypes", gdata_meta,
                    Column("typeID", Integer, primary_key = True),
                    Column("typeName", String, index=True),
                    Column("groupID", Integer, ForeignKey("invgroups.groupID"), index=True))

mapper(Item, items_table,
       properties = {"group" : relation(Group, backref = "items"),
                     "ID" : synonym("typeID"),
                     "name" : synonym("typeName")})

groups_table = Table("invgroups", dbmetadata,
                     Column("groupID", Integer, primary_key = True),
                     Column("groupName", String))

mapper(Group, groups_table,
       properties = {"ID" : synonym("groupID"),
                     "name" : synonym("groupName")})

Then we fetch some Item object and one of the groups:

engine = create_engine("sqlite:///<path-to-db>", echo=True)
Session = sessionmaker(bind=engine)
session = Session()

itm = session.query(Item).get(11184)   #1
grp = session.query(Group).get(831)    #2

In this particular case, itm.groupID = 831, so itm's group is already loaded into the memory. However:

>>>attributes.instance_state(itm).unloaded
set(['group', 'name', 'ID'])

SQLAlchemy indicates that 'group' property/relation of an 'itm' is unloaded. However, in this case, when accessing it, no queries are issued to underlying database because it's already loaded in line labeled #2. After 1st access it's no longer marked as unloaded.

Now let's finish with the example above and talk in general. When accessing group property of an Item object, group with requested ID can be already referenced somewhere (i.e. already mapped in current sessio开发者_JAVA技巧n) or exist just in the underlying database.

My current goal is to find a way to reliably determine if Item with given ID and its 'group' property/relation is already loaded into memory OR perform itm load, then itm.group acccess, and if SQLAlchemy cannot pull something out of its object map and is about to issue query to underlying database - intercept it and issue another database request. This should guarantee it takes at most one database request (with eager loading where necessary) to load everything i need, or zero requests if everything is already loaded.

As i described in one of the paragraphs above, attributes.instance_state().unloaded is not a reliable way. It doesn't detect actual loaded state of a property/relation that has never been accessed.

I plan to use it to optimize queries which involve eager loading (namely, in cases when all requested objects are already loaded, which happens often in my cases, and when these queries are different enough so that query cache doesn't work for them).