PyMySQL - Using a string as cursor.execute argument

Question

My problem here involves passing a string inside cursor.execute below

import pymsyql
import json

connection = pymysql.connect(
        host='localhost', user='u_u_u_u_u',
        password='passwd', db='test',
        charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor
)

def get_data(table):
    try:
        with connection.cursor() as cursor:
            sql = """
                SELECT * FROM %s;
            """
            cursor.execute(sql, (table,))
            result = cursor.fetchall()
            return json.dumps([dict(ix) for ix in result])

    except (TypeError, pymysql.err.ProgrammingError) as error:
        print(error)
    finally:
        pass

get_data('table_1')

connection.close()

I get the error

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table_1'' at line 1")

It seems that execute does not want a string passed as an argument; when I enter a string directly, like cursor.execute(sql, ('table_1',)), I get the same error.

I'm puzzled as to what is causing the issue, the dual-quotes ''table_1'' are confusing. Can anyone tell me what's going on here?

Answer 1

You cannot pass a table name as a parameter, alas. You have to munge it into the query string:

        sql = """
            SELECT * FROM `{0}`;
        """.format(table)
        cursor.execute(sql)