CODEWITHSUNDEEP
parameterspsycopg2
slooow
slooow

Reputation: 359

psycopg2 pass arguments to SQL Programming Error

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)

Upvotes: 0

Views: 1529

Answers (2)

Vlad Bezden
Vlad Bezden

Reputation: 89755

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

Upvotes: 1

slooow
slooow

Reputation: 359

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.

Upvotes: 2

Related Questions