Passing parameter in psycopg2

Question

I am trying to access PostgreSQL using psycopg2:

sql = """
SELECT
    %s
FROM
    table;
"""

cur = con.cursor()
input = (['id', 'name'], )
cur.execute(sql, input)

data = pd.DataFrame.from_records(cur.fetchall())

However, the returned result is:

             0
0   [id, name]
1   [id, name]
2   [id, name]
3   [id, name]
4   [id, name]

If I try to access single column, it looks like:

     0
0   id
1   id
2   id
3   id
4   id

It looks like something is wrong with the quoting around column name (single quote which should not be there):

In [49]: print cur.mogrify(sql, input)

SELECT
    'id'
FROM
    table;

but I am following doc: http://initd.org/psycopg/docs/usage.html#

Anyone can tell me what is going on here? Thanks a lot!!!

Answer 1

Use the AsIs extension

import psycopg2
from psycopg2.extensions import AsIs

column_list = ['id','name']
columns = ', '.join(column_list)

cursor.execute("SELECT %s FROM table", (AsIs(columns),))

And mogrify will show that it is not quoting the column names and passing them in as is.

Answer 2

Nowadays, you can use sql.Identifier to do this in a clean and secure way :

from psycopg2 import sql

statement = """
SELECT
    {id}, {name}
FROM
    table;
"""

with con.cursor() as cur:
  cur.execute(sql.SQL(statement).format(
    id=sql.Identifier("id"),
    name=sql.Identifier("name")
  ))

  data = pd.DataFrame.from_records(cur.fetchall())

More information on query composition here : https://www.psycopg.org/docs/sql.html

Answer 3

The reason was that you were passing the string representation of the array ['id', 'name'] as SQL query parameter but not as the column names. So the resulting query was similar to

SELECT 'id, name' FROM table

Looks your table had 5 rows so the returned result was just this literal for each row.

Column names cannot be the SQL query parameters but can be just the usual string parameters which you can prepare before executing the query-

sql = """
SELECT
    %s
FROM
    table;
"""

input = 'id, name'
sql = sql % input
print(sql)

cur = con.cursor()
cur.execute(sql)

data = pd.DataFrame.from_records(cur.fetchall())

In this case the resulting query is

SELECT
    id, name
FROM
    table;