How do I use named placeholders in a WITH statement (common table expression) with psycopg2?

Question

I use psycopg2 to connect to a postgres database. More precisely it is a schema on the Denodo platform.

When I try to do a database query with a common table expression and use named placeholders I get the following error:

DatabaseError: Execution failed on sql 'WITH table as (
SELECT INH_PAR_TECH_ID 
FROM tgesges 
WHERE PLNK_ID in %(PLNK)s
) 
SELECT * FROM table': Syntax error: Exception parsing query near '''
DETAIL: java.sql.SQLException: Syntax error: Exception parsing query near '''

The query that leads to this error looks like this:

PLNK = ('0000130433', '0000130434', '0000130296', '0000130292', '0000130293')

params = {'PLNK':PLNK}

query ="""WITH table as (
SELECT INH_PAR_TECH_ID 
FROM tgesges 
WHERE PLNK_ID in %(PLNK)s
) 
SELECT * FROM table""

conn = psycopg2.connect(user = user, password = password, host = hostname, port = port, dbname = schema)
df = pd.read_sql(sql=query, con=conn, params=params, parse_dates=parse_dates)
conn.close()

If I do the query without Common Table Expression the query works fine:

query = """
SELECT INH_PAR_TECH_ID as ID                   
FROM tgesges          
WHERE PLNK_ID in %(PLNK)s
"""

conn = psycopg2.connect(user = user, password = password, host = hostname, port = port, dbname = schema)
df = pd.read_sql(sql=query, con=conn, params=params, parse_dates=parse_dates)
conn.close()

Even if I make the query as Common Table Expression and do not use a named placeholder the query works without problems:

query = """WITH table as (
SELECT INH_PAR_TECH_ID 
FROM tgesges 
WHERE PLNK_ID in ('0000130433', '0000130434', '0000130296', '0000130292', '0000130293')
) 
SELECT * FROM table""

conn = psycopg2.connect(user = user, password = password, host = hostname, port = port, dbname = schema)
df = pd.read_sql(sql=query, con=conn, params=params, parse_dates=parse_dates)
conn.close()

Does anyone know how I can use a WITH statement together with named placeholders?

Thanks for your help!

In the meantime I have found out that table is a reserved word. But even if I change the name of the table, it doesn't work. Here is the output of my query when I use mogrify:

Do you have any idea what I have to change to get rid of the letters 'E' in my placeholder?

CODE

PLNK = ("0000130433", "0000130434", "0000130296", "0000130292", "0000130293")
params = {'PLNK':PLNK}

query = """\
  
WITH grundselektion (INH_PAR_TECH_ID, d_min_eroe_sp3, d_start_ru) AS
(
SELECT      INH_PAR_TECH_ID                       
        ,   min(EROE_DT)
        ,   min(EROE_DT) + Interval '-1' YEAR     
FROM        tgesges         
WHERE       PLNK_ID in %(PLNK)s
AND         mutcd     ne 'D'
AND         MANDCD    = '001'
AND         PLNK_ID in %(PLNK)s
GROUP BY    INH_PAR_TECH_ID
)
 
SELECT    *
FROM      grundselektion AS gsel
"""
 
conn = psycopg2.connect(user = user, password = password, host = hostname, port = port, dbname = schema)
cur = conn.cursor()
print(cur.mogrify(query, params).decode('utf-8'))
cur.execute(query, params)
print(cur.fetchall())

OUT

WITH grundselektion (INH_PAR_TECH_ID, d_min_eroe_sp3, d_start_ru) AS
(
SELECT      INH_PAR_TECH_ID                        
        ,   min(EROE_DT)
        ,   min(EROE_DT) + Interval '-1' YEAR      
FROM        tgesges          
WHERE       PLNK_ID in (E'0000130433', E'0000130434', E'0000130296', E'0000130292', E'0000130293')
AND         mutcd     ne 'D'
AND         MANDCD    = '001'
AND         PLNK_ID in (E'0000130433', E'0000130434', E'0000130296', E'0000130292', E'0000130293') 
GROUP BY    INH_PAR_TECH_ID
)

SELECT    *
FROM      grundselektion AS gsel

Many thanks & best regards Tom

Answer 1

table is a reserved word, just rename your variable. Besides that you can use a with statement as usual:

# PLNK = ('0000130433', '0000130434', '0000130296', '0000130292', '0000130293')
PLNK = (1,5,999,1307)
params = {'PLNK':PLNK}

query ="""WITH foo as (
SELECT * 
FROM accounts 
WHERE id in %(PLNK)s
)
SELECT id FROM foo"""

print(cur.mogrify(query, params).decode('utf-8'))
cur.execute(query, params)
print(cur.fetchall())

Out:

WITH foo as (
SELECT * 
FROM accounts 
WHERE id in (1, 5, 999, 1307)
)
SELECT id FROM foo
[(999,), (1307,)]