for SELECTING from PG's special functions, see if you can find a solution in the many recipes at https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs for now.
I haven't looked closely at your specific problem yet but it seems to correspond with those. On Wed, Nov 14, 2018 at 5:13 AM Tomáš Sandrini <[email protected]> wrote: > > Hi everyone, > > I have a database of some `items` which have a range (tsarange => > DateTimeRange) column that usually ranges in intervals of a bunch of seconds > and I basically want to create an availability statistics of those items > (based on their `lower(Item.range)`) for some period (month/day) in some > given interval (last year, last half of a year or custom). > This is obviously not a hard task to do, one just needs to extract the month > or day, that he wants and group by the extracted part, however in a database > with a ~3.10^6 rows this takes around 8-9s which is not really good for user > experience hence I came up with a bit more complicated query which results in > the same thing but taking only 800ms. > Let's say for example that we want to know how many `items` there are per day > in the whole year, the query would look followingly: > > ``` > SELECT day.date, count(i.id) > FROM ( > SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS > date > FROM generate_series(0, 365, 1) AS offs > ) AS day > LEFT OUTER JOIN items AS i > ON (day.date=to_char(date_trunc('day', lower(i.range)), 'YYYY-MM-DD')) > GROUP BY day.date; > ``` > > My issue is that I am not able to reproduce this query into SQLAlchemy query > builder. > Specifically I am struggling with this part > > `(current_date - offs)` > This may seem harmless at first sight rewritten in the following form > > ``` > series = alias(func.generate_series(0, 365, 1)) > subq = sess.query(func.to_char(func.date_trunc('day', (func.current_date() - > series)), 'YYYY-MM-DD')).\ > select_from(series).\ > subquery() > ``` > > but SQLAlchemy is trying to cast `series` to a scalar due to the comparison, > which is obviously not possible and available thus failing and yielding an > error that `generate_series` doesn't have a `as_scalar()` method. > > Is it even possible to rewrite this query? > I hope so. I am using SQLAlchemy ORM without any additional framework. > > I will appreciate any help. > > -- > 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. -- 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.
