How to use passed parameter as table Name in Select query python?

Question

i have the following function which extracts data from table, but i want to pass the table name in function as parameter...

def extract_data(table):
    try:
        tableName = table
        conn_string = "host='localhost' dbname='Aspentiment' user='postgres' password='pwd'"
        conn=psycopg2.connect(conn_string)
        cursor = conn.cursor()    
        cursor.execute("SELECT aspects_name, sentiments FROM ('%s') " %(tableName))
        rows = cursor.fetchall()
        return rows
    finally:
        if conn:
            conn.close()

when i call function as extract_data(Harpar) : Harpar is table name but it give an error that 'Harpar' is not defined.. any hepl ?

Noyer282 · Accepted Answer · 2017-03-24 15:29:41Z

1

Update: As of psycopg2 version 2.7:

You can now use the sql module of psycopg2 to compose dynamic queries of this type:

from psycopg2 import sql
query = sql.SQL("SELECT aspects_name, sentiments FROM {}").format(sql.Identifier(tableName))
cursor.execute(query)

Pre < 2.7:

Use the AsIs adapter along these lines:

from psycopg2.extensions import AsIs
cursor.execute("SELECT aspects_name, sentiments FROM %s;",(AsIs(tableName),))

Without the AsIs adapter, psycopg2 will escape the table name in your query.

edited Mar 24, 2017 at 15:29

answered Feb 12, 2016 at 14:55

Noyer282

9847 silver badges20 bronze badges

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

2 Comments

Antoine Dusséaux Over a year ago

AsIs should not be used for this purpose, the new sql module should be used instead: stackoverflow.com/a/42980069/5285608

Noyer282 Over a year ago

@AntoineDusséaux: Agreed. The new sql module provides a much cleaner method for composing dynamic queries. I've updated the old answer accordingly.

Collectives™ on Stack Overflow

How to use passed parameter as table Name in Select query python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related