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. 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.
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?
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 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.
