Querying the database postgreSQL

user_10313862 0
Viewed 48

Good afternoon, I study the library for working with postgresql from python, it’s written in the description:

Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

I want to output from the reports table, columns object, data I tried to make a function like this:

def select(self, column, table):
    with conn.cursor() as cursor:
        stmt = sql.SQL('SELECT {} FROM {}').format(
            sql.Identifier(column),
            sql.Identifier(table))
        cursor.execute(stmt)
        for row in cursor:
            print(row)

But I get an error:

psycopg2.ProgrammingError: column "object, data" does not exist
LINE 1: SELECT "object, data" FROM "object"

I managed to achieve the desired result using the function:

def select(self, column, table):
    with conn.cursor() as cursor:
        cursor.execute("SELECT %s FROM %s" %(column,table))
        return cursor.fetchall()

Can you please tell me how to make a function without using %s?

psycopg2 python-3.x postgresql
1 Answers

Instead of passing the string columns, you should have an list of the columns names you want to pass. Now you can use sql.SQL(', ').join() to join them. 

def select(self, columns, table):
    with conn.cursor() as cursor:
        stmt = sql.SQL('SELECT {} FROM {}').format(
            sql.SQL(', ').join(sql.Identifier(n) for n in columns),
            sql.Identifier(table))
        cursor.execute(stmt)
        for row in cursor:
            print(row)
user_84770660
Related