Psycopg2 ProgrammingError: syntax error at or near SELECT

Question

Trying to pass a variable to a psql query. Code below. I'm ultimately trying to copy the results to a CSV file and an error occurs trying to execute the module cur.copy_expert.

date1 = ('2019-05-06',)
query = ('''SELECT * FROM product WHERE (product.esb_timestamp > %s AND product.esb_timestamp < '2019-05-11')''', date1)

# Copy the results to a new file
output = "COPY ({0}) to STDOUT WITH CSV HEADER".format(query)
with open('Database_Query.csv', 'w') as file1:
    cur.copy_expert(output, file1)

Error below:

Traceback (most recent call last):
  File "database_query.py", line 55, in <module>
    cur.copy_expert(output, file1)
psycopg2.ProgrammingError: syntax error at or near ""SELECT * FROM nwwproduct WHERE (nwwproduct.esb_timestamp > %s AND nwwproduct.esb_timestamp < '2019-05-11')""
LINE 1: COPY (("SELECT * FROM nwwproduct WHERE (nwwproduct.esb_times...

Get rid of the brackets, change the quotation marks to " for clarity, and pass the single argument as a tuple: query = ("""SELECT * FROM product WHERE product.esb_timestamp > %s AND product.esb_timestamp < '2019-05-11'""", (date1,)). Does that work? If not, swap %s for ?; I can't be sure on the binding parameter expected here. — roganjosh
– roganjosh, Commented May 10, 2019 at 18:18
Restructuring the brackets results in the same error, as does using ?. — klex52s
– klex52s, Commented May 10, 2019 at 18:27
You replaced the entire line of code with a copy/paste of my suggestion? — roganjosh
– roganjosh, Commented May 10, 2019 at 18:28
According to this answer from postgresql.org, COPY doesn't support parameters. link — klex52s
– klex52s, Commented May 10, 2019 at 18:38

Parfait · Accepted Answer · 2019-05-10 19:22:56Z

As psycopg2 docs mentions

If you need to compose a COPY statement dynamically (because table, fields, or query parameters are in Python variables) you may use the objects provided by the psycopg2.sql module.

This is also confirmed from this GitHub ticket by one of the psycopg2 authors and current maintainer: @dvarrazzo.

from psycopg2 import sql

stmt = """COPY (SELECT * FROM product 
                WHERE (product.esb_timestamp > {dt} 
                  AND  product.esb_timestamp < '2019-05-11')
               ) TO STDOUT WITH CSV HEADER"""

query = sql.SQL(stmt).format(dt=sql.Literal("2019-05-06"))

with open('Database_Query.csv', 'w') as file1:
    cur.copy_expert(query, file1)

Do be aware this is different from Python's str.format and safely interpolates values to prepared statement.

klex52s · Accepted Answer · 2019-05-10 18:43:31Z

0

COPY does not support parameters. Reference

answered May 10, 2019 at 18:43

klex52s

4371 gold badge7 silver badges23 bronze badges

Collectives™ on Stack Overflow

Psycopg2 ProgrammingError: syntax error at or near SELECT

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related