StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODetermining actual loaded/unloaded state of sqlalchemy object and its relations
text
Body
copied!<p>In my case, queries to underlying database are pretty expensive, so we're trying to save database requests wherever possible.</p> <p>Here's simplified database which I'll use for examples:</p> <pre><code>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")}) </code></pre> <p>Then we fetch some Item object and one of the groups:</p> <pre><code>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 </code></pre> <p>In this particular case, itm.groupID = 831, so itm's group is already loaded into the memory. However:</p> <pre><code>>>>attributes.instance_state(itm).unloaded set(['group', 'name', 'ID']) </code></pre> <p>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.</p> <p>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 session) or exist just in the underlying database.</p> <p>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.</p> <p>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.</p> <p>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).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload