I've got a problem with SQLAlchemy 0.9.8 with a Routing Session setup and using the query count method.
I started from this post on Mike Bayer's blog: http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ I did some tweaks such that I could specify the database engine to use within the class definition of a model. The RoutingSession class ended up like this: class RoutingSession(Session): def get_bind(self, mapper=None, clause=None): if mapper: try: return engines[mapper.class_.__db_source__] except (AttributeError, KeyError): return engines['primary'] else: return engines['primary'] So in one of my model classes, i can switch the database engine by simply adding a class attribute as such: class HWInventory(Base): __tablename__ = 'Inventory' __db_source__ = 'inventory_db' So, if I do a DBSession.query(HWInventory), the inventory_db engine would be used. If I queried some other table, the primary engine would be used. However, this only works if I execute the query using .one(), or .first, or .all(). If i attempt to count the results using .count(), the primary engine gets used. Adding a bit of debugging into this class, it turns out that for the .count() call, there is no mapper when get_bind is called! So the result is that i send back the primary engine by default. Of course when i count the HWInventory table, I get an error from the database server saying that it has no idea what I'm talking about. If i change the query to be something like DBSession.query(func.count(HWInventory.id)), it works, but I had been using WebHelpers pagination classes, which uses the generic DBSession.query(HWInventory).count() form of the query. The only thing I can think of here is that .count() causes an anonymous query to be executed. The main query is not connected to a table, but the subquery within it is. Why doesn't the subquery cause the query to bind to the correct engine? Why doesn't the subquery provide a mapper for the whole thing? Of course, what I can do to fix this? Is there a way in the get_bind() to maybe detect if there are subqueries and select the engine based upon something in there? Jeff -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
