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?

You, unfortunately, can't specify table names this way. You have to `format()` the table name into the query and you're responsible for sanitising that. — roganjosh, May 31 '18 at 22:15

score 3 · Accepted Answer · answered May 31 '18 at 22:15

3

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)

answered May 31 '18 at 22:15

Gordon Linoff

1,242,037
58
646
786

We both expressed the same sentiment about the fact you can't do this. Why can't it actually be implemented? – roganjosh May 31 '18 at 22:16
@roganjosh . . . You can only pass constants as parameters. Not identifiers, function names, operators, and so on. – Gordon Linoff Jun 01 '18 at 01:42
1

Sorry, I wasn't clear. I'd already put a comment under the answer before you answered saying the same thing. It's a common question and both our replies say "unfortunately"/ "alas". My question was why this cannot be supported in the syntax since it obviously has value? – roganjosh Jun 01 '18 at 06:28
@roganjosh . . . Because one purpose of stored queries is to cache the execution plan. You need all this information to compile the query. – Gordon Linoff Jun 01 '18 at 12:19
I think @roganjosh is speaking from more of a sentimental position; the "why" question might be rhetorical – ChumiestBucket Jun 01 '18 at 14:39
In part I was, but since it's so fundamental I assumed there was a practical limitation that I was not aware of, and that's answered by the response. I need to understand more about what happens in python on a fundamental level when I define a query because I don't understand why compiling a query with variable data inserts should be any more expensive/less possible than the table you're inserting into. But that's probably my limitation in understanding as of now. – roganjosh Jun 01 '18 at 16:34

PyMySQL - Using a string as cursor.execute argument

1 Answers1