[SOLVED] Use Common Table Expression with Pony ORM

Use Common Table Expression with Pony ORM

I have the following query that contains a common table expression:

WITH example AS (
    SELECT unnest(ARRAY['foo', 'bar', 'baz']) as col
)
SELECT *
FROM example

Trying to use it in database.select(query) throws pony.orm.dbapiprovider.ProgrammingError: syntax error at or near "WITH", and database.select(raw_sql(query)) throws TypeError: expected string or bytes-like object.

How can I select data using a CTE with ponyorm?

Solution

To use a query containing a CTE, call the execute function on the database and fetch the rows with the returned cursor:

cursor = database.execute("""
    WITH example AS (
        SELECT unnest(ARRAY['foo', 'bar', 'baz']) as col
    )
    SELECT *
    FROM example
""")
rows = cursor.fetchall()

Note: The cursor is a class from psycopg2, so while this solution does use the pony library the solution may differ depending on the database being used.