PyMySQL assigning a variable to table name, in Python

Question

I am using PyMySQL in Python 2.7. I have to create a function - where, given a table name, the query will find unique values of all the column names.

Since there are more than one tables involved, I do not want to hard-code table name. Now, a simpler query is like:

cursor.execute("  SELECT DISTINCT(`Trend`) AS `Trend` FROM `Table_1`  ORDER BY `Trend` DESC      ")

I want to do something like:

tab = 'Table_1'
cursor.execute("  SELECT DISTINCT(`Trend`) AS `Trend` FROM tab ORDER BY `Trend` DESC      ")

I am getting the following error:

ProgrammingError: (1146, u"Table 'Table_1.tab' doesn't exist")

Can someone please help. TIA

Answer 1

Make sure the database you're using is correct,and use %s to format you sql statement.

DB_SCHEMA='test_db'
table_name='table1'
connection = pymysql.connect(host=DB_SERVER_IP,
                             port=3306,
                             db=DB_SCHEMA,
                             charset='UTF8',
                             cursorclass=pymysql.cursors.DictCursor
                             )
try:
    with connection.cursor() as cursor:
        sql = "SELECT DISTINCT(`Trend`) AS `Trend` FROM `%s` ORDER BY `Trend` DESC"%(table_name)

        cursor.execute(sql)

    connection.commit()
except Exception as e:
    print e
finally:
    connection.close()

Hope this helps.