psycopg2 pass arguments to SQL Programming Error

Question

I tried to pass parameters the way it is described in the docs, but I get the following error: File "slug_word.py", line 100, in get_col cur.execute("select %s from %s" , data ) psycopg2.ProgrammingError: syntax error at or near "E'catalog_category'" LINE 1: select E'slug' from E'catalog_category'

Here are extracts from my code:

def get_col(cxn, table, col):
    "fetch a column"
    cur = cxn.cursor()
    data = (col, table)
    cur.execute("select %s from %s" , data ) 
    rows = cur.fetchall()
    return rows

def main():

    cxn = connect('galleria')
    table = 'catalog_category'
    col = 'slug'
    rows = get_col(cxn, table, col)

sjngm · Accepted Answer · 2012-01-11 07:48:32Z

2

By rereading a post Steve Holden about this issue I found the hint that in my code the parameters have to be passed the python way:

 ..."select %s from %s" % data )

Only 'real' data which goes into the database has to use the psycopg2 parameter method and not things like table and column names. Unfortunately mixing of data and table name does not work.

edited Jan 11, 2012 at 7:48

sjngm

13k16 gold badges90 silver badges118 bronze badges

answered Jan 11, 2012 at 7:40

slooow

3691 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vlad Bezden · Accepted Answer · 2019-03-03 15:52:15Z

1

You can use AsIs psycopg2 function:

Adapter conform to the ISQLQuote protocol useful for objects whose string representation is already valid as SQL representation.

import psycopg2
from psycopg2.extensions import AsIs

def get_col(conn, table, col):
    '''Fetch a column'''

    QUERY = 'SELECT %(col)s from %(table)s'
    data = {'col': AsIs(col), 'table': AsIs(table)}
    with conn.cursor() as cursor:
        cursor.execute(QUERY, data)
        rows = cursor.fetchall()        
    return rows

edited Mar 3, 2019 at 15:52

answered Feb 6, 2018 at 15:24

Vlad Bezden

90.7k27 gold badges261 silver badges190 bronze badges

Collectives™ on Stack Overflow

psycopg2 pass arguments to SQL Programming Error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related