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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
