1

I'm trying to extract SQLAlchemy query parameter values for use in caching, using the function _params_from_query from the SQLAlchemy Beaker Caching example.

Unfortunately when I try it with a query involving a subquery, it seems to only traverse the main query parameters, ignoring the subquery.

The following example code demonstrates this when run from the beaker_cache example folder in the SQLAlchemy distribution.

from environment import Session
from model import Person
from caching_query import _params_from_query

s = Session.query(Person.name).filter(Person.name=="subquery value").subquery()

q = Session.query(s.c.name).filter(s.c.name=="main query value")

print q.params()
print 
print _params_from_query(q)

# SELECT anon_1.name AS anon_1_name 
# FROM (SELECT person.name AS name 
# FROM person 
# WHERE person.name = :name_1) AS anon_1    <- two
# WHERE anon_1.name = :name_2               <- parameters
#
# ['main query value']    <- only one value

Am I using the function incorrectly? How can I get the parameter values from the subquery as well?

Ian Mackinnon
  • 13,381
  • 13
  • 51
  • 67

1 Answers1

3

this is a bug in the example. Here's a workaround that should get everything (though I'd like to improve this to not require the full statement compilation):

diff -r affaa93fad92 examples/beaker_caching/caching_query.py
--- a/examples/beaker_caching/caching_query.py  Tue Feb 14 10:16:16 2012 -0500
+++ b/examples/beaker_caching/caching_query.py  Tue Feb 14 11:57:59 2012 -0500
@@ -268,8 +268,5 @@
             value = bind.value

         v.append(value)
-    if query._criterion is not None:
-        visitors.traverse(query._criterion, {}, {'bindparam':visit_bindparam})
-    for f in query._from_obj:
-        visitors.traverse(f, {}, {'bindparam':visit_bindparam})
+    visitors.traverse(query.statement, {}, {'bindparam':visit_bindparam})
     return v
zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • That works great. I also decided to wrap the ``traverse`` in a [block that catches warnings](http://stackoverflow.com/a/5225951/201665) because I kept getting the warning ``Column ... on table ... being replaced by another column with the same key.`` for any queries that contained a ``joinedload``. – Ian Mackinnon Feb 15 '12 at 10:58
  • 1
    oh, there's ways to make it not do that...you'd say query.with_labels().statement – zzzeek Feb 15 '12 at 18:56