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