Question 89701 in PostgreSQL Question posted in
The official documentation can be found here.

Using CTE in Python with Postgresql and psycopg2

I’m trying to create a query using CTE where I am creating 2 subtables and then the select statement. I believe the following syntax would work for full SQL, but it isn’t working in this situation using psycopg2 in Python.

The idea is that I should be able to pull a query that shows the Name of all events (E.Event), the E.EDate, the E.ETemp and SmithTime. So it should have the full list of Events but the time column only shows times recorded for Smith (not in all Events).

query = ("""WITH cte AS (SELECT E.Event, O.Time AS "SmithTime"
            FROM event E JOIN outcome O ON E.EventID = O.EventID
                        JOIN name N ON N.ID = O.ID
            WHERE Name = 'Smith'),

WITH cte2 AS (SELECT E.Event, O.Time, E.EDate, E.ETemp
    FROM event E JOIN outcome O ON E.EventID = O.EventID
                        JOIN name N ON N.ID = O.ID)
                
SELECT cte2.Event, cte2.EDate, cte2.ETemp, cte.SmithTime
    FROM cte JOIN cte2 ON cte.Event = cte2.Event
    ORDER BY 2 ASC""")

query = pd.read_sql(query, conn)
print(query)

This is just my latest iteration, I’m not sure what else to try. It is currently generating a DatabaseError:

DatabaseError: Execution failed on sql 'WITH cte AS (SELECT E.Event, O.Time AS "SmithTime"
            FROM event E JOIN outcome O ON E.EventID = O.EventID
                        JOIN name N ON N.ID = O.ID
            WHERE Name = 'Smith'),

WITH cte2 AS (SELECT E.Event, O.Time, E.EDate, E.ETemp
    FROM event E JOIN outcome O ON E.EventID = O.EventID
                        JOIN name N ON N.ID = O.ID)
                
SELECT cte2.Event, cte2.EDate, cte2.ETemp, cte.SmithTime
    FROM cte JOIN cte2 ON cte.Event = cte2.Event
    ORDER BY 2 ASC': syntax error at or near "WITH"
LINE 6: WITH cte2 AS (SELECT E.Event, O.Time, E.EDate, E.ETemp
Tags:

2

Answers


  1. 0

    I have no idea whether or not your current query even be logically correct. But we can get around the SQL error by inlining the common table expressions:

    SELECT cte2.Event, cte2.EDate, cte2.ETemp, cte.SmithTime
FROM (
    SELECT E.Event, O.Time AS "SmithTime"
    FROM event E
    INNER JOIN outcome O ON E.EventID = O.EventID
    INNER JOIN name N ON N.ID = O.ID
    WHERE Name = 'Smith'
) cte
INNER JOIN (
    SELECT E.Event, O.Time, E.EDate, E.ETemp
    FROM event E
    INNER JOIN outcome O ON E.EventID = O.EventID
    INNER JOIN name N ON N.ID = O.ID
) cte2
    ON cte.Event = cte2.Event
ORDER BY 2;
    Login or Signup to reply.
  2. 0

    It’s an SQL syntax error, nothing specific to psycopg2.

    There’s only one WITH in a CTE query. It should be WITH cte AS (...), cte2 AS (...) SELECT ..., not WITH cte AS (...), WITH cte2 AS (...) SELECT ....

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search