On Thu, May 3, 2018 at 6:05 PM, Björn Nadrowski <[email protected]> wrote:
>
> Thanks for your reply. I guessed that this should be possible using alias
> and functions. But I was unable to write a simple script that achieves that.
> Could you help me with that?
> Or do you want me to show the things that I tried but do not work?
no need, can you try this query?
T2 = aliased(CTestTable, name="T2")
T1 = aliased(CTestTable, name="T1")
max_subq = session.query(
func.max(T2.datetime)
).filter(T2.info == T1.info).filter(
T2.datetime < T1.datetime).correlate(T1).label("PreviousDate")
subq = session.query(
T1.id, T1.datetime, T1.val, T1.info, max_subq).subquery("T")
q = session.query(
subq.c.id, subq.c.datetime, subq.c.val, subq.c.info,
(
func.julianday("datetime") -
func.julianday("PreviousDate")).label("datediff")
).select_from(
subq
)
q.all()
log output is:
SELECT "T".id AS "T_id", "T".datetime AS "T_datetime", "T".val AS
"T_val", "T".info AS "T_info", julianday(?) - julianday(?) AS datediff
FROM (SELECT "T1".id AS id, "T1".datetime AS datetime, "T1".val AS
val, "T1".info AS info, (SELECT max("T2".datetime) AS max_1
FROM "testTable" AS "T2"
WHERE "T2".info = "T1".info AND "T2".datetime < "T1".datetime) AS "PreviousDate"
FROM "testTable" AS "T1") AS "T"
2018-05-03 20:11:30,082 INFO sqlalchemy.engine.base.Engine
('datetime', 'PreviousDate')
is that right? note the uppercase names need to be quoted, literal
values are turned into bind parameters, etc.
>
> Thanks, Bjoern
>
>
> Am Montag, 23. April 2018 14:37:43 UTC+2 schrieb Mike Bayer:
>>
>> We can help you with this query but I'd first ask that you have
>> reviewed the documentation that covers these patterns, within the Core
>> tutorial: http://docs.sqlalchemy.org/en/latest/core/tutorial.html
>> specifically, how to do aliased subqueries
>> (http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-aliases),
>> functions
>> (http://docs.sqlalchemy.org/en/latest/core/tutorial.html#functions),
>> etc.
>>
>>
> --
> 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.
