Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import sqlalchemy as sa
- #
- ## setup
- #
- t1 = sa.table(
- "t1",
- sa.column("id", sa.Integer),
- sa.column("a", sa.Integer),
- sa.column("b", sa.Integer)
- )
- t2 = sa.table(
- "t2",
- sa.column("id", sa.Integer),
- sa.column("x", sa.Integer),
- sa.column("y", sa.Integer)
- )
- #
- ## all auto
- #
- l1 = sa.select([ (t1.c.a + t2.c.x).label("a_x") ]).lateral("l1")
- l2 = sa.select([ (l1.c.a_x + t2.c.y).label("a_x_y") ]).lateral("l2")
- join = t1.join(t2, t1.c.id == t2.c.id)\
- .join(l1, sa.true())\
- .join(l2, sa.true())
- query = sa.select([ t1.c.a, t2.c.x, l1.c.a_x, l2.c.a_x_y ]).select_from(join)
- print(str(query))
- """
- sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT t1.a + t2.x AS a_x
- FROM t1, t2' returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.
- """
- #
- ## correlate(None) -> autocorrelation
- #
- l1 = sa.select([ (t1.c.a + t2.c.x).label("a_x") ]).correlate(None).lateral("l1")
- l2 = sa.select([ (l1.c.a_x + t2.c.y).label("a_x_y") ]).correlate(None).lateral("l2")
- join = t1.join(t2, t1.c.id == t2.c.id)\
- .join(l1, sa.true())\
- .join(l2, sa.true())
- query = sa.select([ t1.c.a, t2.c.x, l1.c.a_x, l2.c.a_x_y ]).select_from(join)
- """
- SELECT t1.a, t2.x, l1.a_x, l2.a_x_y
- FROM t1 JOIN t2 ON t1.id = t2.id JOIN LATERAL (SELECT t1.a + t2.x AS a_x
- FROM t1, t2) AS l1 ON true JOIN LATERAL (SELECT l1.a_x + t2.y AS a_x_y
- FROM LATERAL (SELECT t1.a + t2.x AS a_x
- FROM t1, t2) AS l1, t2) AS l2 ON true
- """
- #
- ## correlate_except
- #
- l1 = sa.select([ (t1.c.a + t2.c.x).label("a_x") ]).correlate_except([t1, t2]).lateral("l1")
- l2 = sa.select([ (l1.c.a_x + t2.c.y).label("a_x_y") ]).correlate_except([t1, t2]).lateral("l2")
- join = t1.join(t2, t1.c.id == t2.c.id)\
- .join(l1, sa.true())\
- .join(l2, sa.true())
- query = sa.select([ t1.c.a, t2.c.x, l1.c.a_x, l2.c.a_x_y ]).select_from(join)
- """
- sqlalchemy.exc.ArgumentError: FROM expression expected
- """
- #
- ## proof, postgres 10+
- #
- """
- create table t1 ( id int, a int, b int);
- create table t2 (id int, x int, y int);
- select t1.a, t2.x, l1.a_x, l2.a_x_y
- from t1
- join t2 on t1.id = t2.id
- join lateral (select t1.a + t2.x as a_x) l1 on true
- join lateral (select l1.a_x + t2.y as a_x_y) l2 on true
- """
Add Comment
Please, Sign In to add comment