I see this as a perfectly complementary tool to SQLAlchemy Core itself: you can write your queries in Core, reusing as much as necessary -- but there is always a couple of insanely complex queries that join several tables and subqueries and do all kind of magic to get the right report. In this case PugSQL kicks in, and you can keep your complex queries in SQL and call them from Python as needed.
I've found that most of these insanely complex analytic type queries are easy to model in Core if you treat Core as a DSL and use good programming practice (composition, separation of concerns, etc). The final queries end up being beasts, but if the Core-based pieces are modeled well they are basically equivalent to one I would have written by hand, but with the extraordinary benefit that they can be much more easily parameterized (imagine a financial report where you roll up Year-to-Date, Month-to-Date, Week-to-Date metrics, so using lots of window functions - with the proper structure it's trivial to add a further breakdown allowing you to group by any other metric). I've basically used it to create my own version of Looker.
Comments
I see this as a perfectly complementary tool to SQLAlchemy Core itself: you can write your queries in Core, reusing as much as necessary -- but there is always a couple of insanely complex queries that join several tables and subqueries and do all kind of magic to get the right report. In this case PugSQL kicks in, and you can keep your complex queries in SQL and call them from Python as needed.
I've found that most of these insanely complex analytic type queries are easy to model in Core if you treat Core as a DSL and use good programming practice (composition, separation of concerns, etc). The final queries end up being beasts, but if the Core-based pieces are modeled well they are basically equivalent to one I would have written by hand, but with the extraordinary benefit that they can be much more easily parameterized (imagine a financial report where you roll up Year-to-Date, Month-to-Date, Week-to-Date metrics, so using lots of window functions - with the proper structure it's trivial to add a further breakdown allowing you to group by any other metric). I've basically used it to create my own version of Looker.