PyMySQL - Using a string as cursor.execute argument [duplicate]

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
– roganjosh, Commented May 31, 2018 at 22:15

Gordon Linoff · Accepted Answer · 2018-05-31 22:15:46Z

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, 2018 at 22:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

6 Comments

roganjosh Over a year ago

We both expressed the same sentiment about the fact you can't do this. Why can't it actually be implemented?

Gordon Linoff Over a year ago

@roganjosh . . . You can only pass constants as parameters. Not identifiers, function names, operators, and so on.

roganjosh Over a year ago

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?

Gordon Linoff Over a year ago

@roganjosh . . . Because one purpose of stored queries is to cache the execution plan. You need all this information to compile the query.

ChumiestBucket Over a year ago

I think @roganjosh is speaking from more of a sentimental position; the "why" question might be rhetorical

|

Collectives™ on Stack Overflow

PyMySQL - Using a string as cursor.execute argument [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related