python-3.xpostgresqlpsycopg2ponyorm

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.