Thanks Mike. That approach is working well. On Sunday, 15 January 2017 03:48:19 UTC+11, Mike Bayer wrote: > > > > On 01/13/2017 11:13 PM, Stephen Davies wrote: > > (Using latest SqlAlchemy with either sqlite or postgres on a Flask > server) > > > > I have a table of entities and stats with columns: id, date, stat1, > > stat2, etc. > > > > There are a lot of endpoints on the Flask server that need to do > > processing on an aggregated form of the above, something like: > > | > > SELECT id,date,avg(stat1),avg(stat2),avg(...)FROM t1 GROUP BY id,date > > | > > > > If I was doing this in a pure SQL way, I'd probably create a view of > > this result and build other queries from that view. How can I do > > something similar within SqlAlchemy's nice ORM? > > > > I've read the SqlAlchemy section on mapping to arbitrary selects > > < > http://docs.sqlalchemy.org/en/latest/orm/nonstandard_mappings.html#mapping-a-class-against-arbitrary-selects> > > and > > that seems to be roughly what I want to do, as it leaves me open to do > > the following: > > > > | > > > > q > > > =session.query(MyTable.id,MyTable.date,func.avg(MyTable.stat1).label('agg_stat1'),func.avg(MyTable.stat2).label('agg_stat2'),func.avg(MyTable.stat3).label('agg_stat3')).group_by(MyTable.id,MyTable.date) > > > > > > > > classAggregated(Base): > > __table__ =q.selectable.alias() > > > > # A nicely laid-out query using Aggregated.attr to select the needed > > columns and to filter on > > > session.query(Aggregated.stat2,Aggregated.mytable_id,Aggregated.mytable_date).filter(Aggregated.mytable_id > > > > ==5) > > | > > > > Apart from needing a session to construct the class (I guess I could use > > select instead to avoid this), this is a nice syntax for dealing with my > > new "view" Aggregated. > > I noticed there was a note in the SqlAlchemy docs discouraging using > > this functionality, instead query should be used. Is it possible to use > > the query method and still be able to refer to column names using > > Class.attribute syntax, or something similar? > > So the mapping here is fine. Since you're looking to get a namespace > with those custom label names (eliminates using aliased(MyTable) as an > option) and also looking to build upon the query (eliminates using > Bundle as an option) I think that's probably what you want to do. > > The way Query links to the Session is a little bit artificial. The > Session isn't used until the Query executes so it is safe to just say > Session().query(...), e.g. with a new Session, or even use the Query > constructor directly e.g. q = Query(...), it doesn't need a Session. > > > > > Ideally I would like a mapping object that's created at the same time as > > other tables and acts like an SQL view, or some advice that there's a > > better way to do this in SqlAlchemy without thinking in terms of views. > > the original idea for mappings was that they would work just the way > you're doing - before there was declarative, there was just the mapper() > function, and I had in mind that "views" would be commonplace, as people > would be declaring some_mapper = mapper(SomeClass, some_select) all over > the place. SQLAlchemy 0.1 didn't have a Query object, you used the > mapper and called mapper.select_by() or similar. SQLAlchemy's API > wasn't like Requests or something like that, e.g. born perfect :). it > was forged by fire. The early user base suffered greatly :) > > The downside is that once you map to a SELECT statement, the ORM when > constructing things on top of that "view" has no choice but to put the > SELECT as a parenthesized subquery, and that's the part that is very > unpopular - the MySQL database has terrible optimization problems with > subqueries, the queries are hard to read, and often there's a need to > modify what's inside the subquery which from the "view" perspective is > not possible. But if none of those things are issues in your case then > you are fine. > > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > 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 sqlalchemy+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. >
-- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
