On Fri, May 11, 2018 at 3:17 PM, Björn Nadrowski <[email protected]> wrote:
>
>
> Hi Mike,
> that looks very good,
>
> However, I am still left with three problems:
>
> a) the query returns "None" in the datediff column. It seems that the
> strings "datetime" and "PreviousDate" are somehow not recognized. When I
> replace
> "(
> func.julianday("datetime") -
> func.julianday("PreviousDate")).label("datediff") "
> by
> (max_subq).label("datediff")
>
> then I do get a non-None result.
does the raw SQL statement previously w/ the julianday function work?
I'm not familiar with this function on SQLite.
The constructed SQL is:
> SELECT "T".id, "T".datetime, "T".val, "T".info, (SELECT max("T2".datetime)
> AS max_1
> FROM "testTable" AS "T2", "testTable" AS "T1"
> WHERE "T2".info = "T1".info AND "T2".datetime < "T1".datetime) 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"
>
> The "datediff" column contains, in this case, the value "2018-01-03" in each
> of the 6 rows.
>
> b) I do not know what the "correlate(T1)" statement does. I looked up the
> docs for correlate but was unable to find out what that really does.
> The SQL produced by your code is identical regardless of whether
> correlate(T1) is used or not.
then you can omit it, correlation is automatic if there are no
correlation directives given, documentation at
http://docs.sqlalchemy.org/en/latest/core/tutorial.html#correlated-subqueries
>
> c) The code is still sqlite-specific (use of julianday()). Suppose I find
> out the reason why the code does not work and succeed reproducing the
> desired output. Then the code would still be SQLite-specific and not generic
> which is the main reason for using sqlalchemy. What do I have to do to make
> it databse-backend-indpeendent?
unfortunately date arithmetic is complicated and extremely different
on pretty much every database, and SQLAlchemy doesn't have an
abstraction layer for date arithmetic functions. What I usually do is
figure out the correct expression for each of the databases I'm
targeting, then I build custom function, example at
http://docs.sqlalchemy.org/en/latest/core/compiler.html#utc-timestamp-function
.
>
> Thanks for your help,
> Bjoern
>
>
>>
>> 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.
>>
>>
> --
> 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.
