On 02/22/2017 10:17 AM, Michael Williamson wrote:
Using CTEs directly inside union() (or similar functions such as
intersect()) causes an error:
query_1 = s.query(Record.id).cte()
query_2 = s.query(Record.id).cte()
select_from = union(query_1, query_2)
what does the above intend to mean? I don't know what SQL one would
expect from that. A CTE is specifically a SELECT that goes on top of
another SELECT as a sort of "aliased" subquery. a UNION doesn't make
any sense on the "outside" of that unless you can show me.
I can see this instead:
q1 = s.query(Record.id)
q2 = s.query(Record.id)
select_from = union(q1, q2).cte()
but that's not what the above is saying.
print(s.query(func.count(1)).select_from(select_from).all())
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax
error at or near "SELECT" LINE 2: SELECT record.id AS id
^
[SQL: 'WITH anon_2 AS \nSELECT record.id AS id \nFROM record,
\nanon_3 AS \nSELECT record.id AS id \nFROM record\n SELECT
count(%(count_2)s) AS count_1 \nFROM (anon_2 UNION anon_3) AS
anon_1'] [parameters: {'count_2': 1}]
The way I got things working was to wrap the CTE in a SELECT rather than
using it directly:
select_from = union(
select([query_1.c.id]).select_from(query_1),
select([query_2.c.id]).select_from(query_2),
)
print(s.query(func.count(1)).select_from(select_from).all())
However, the original error didn't make it especially obvious to me
what was going on. Perhaps either the API could be changed to allow
using the CTE directly, or a clearer error could be emitted? Or am I
just thinking about this all wrong?
I also got an error when using subquery() instead of cte(), which was
fixed in the same way -- presumably this is the same issue?
Thanks
Michael
--
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.
