How to print the query being sent to mySQL using 'pymysql'?

Question

Let's say I have the following code:

with open('saved_response.json') as data_file: 
    #data_ordered = json.load(data_file, object_pairs_hook=OrderedDict)
    response=json.load(data_file)

for user_data in response['itemList']:
    field_names=""
    field_values=[]
    for i in user_data:
        field_names+=","+i
        field_values.append(user_data[i])
    print(field_names[1:])
    print(field_values)
    with con.cursor() as c:
        c.execute("INSERT into user(%s) values(%s);",(field_names[1:],field_values))

I am getting the following error:

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 ''id,parentId,username,creationTime,role,state,userProfile') values((562949953421' at line 1")

Is there any way to print the SQL query being sent to MySQL for execution so that we can resolve syntax errors like this? Also, a solution to this error will be appreciated.

Have you seen https://stackoverflow.com/questions/7071166/print-the-actual-query-mysqldb-runs ? — ChatterOne, Jul 04 '18 at 14:39

Ilja Everilä · Accepted Answer · 2018-07-04T22:02:51.013

As pointed out in the comments, also PyMySQL cursor objects have the undocumented attribute _last_executed, which contains the sent query.

Your syntax errors are a result of trying to pass identifiers, in this case column names, using placeholder(s). From the error message you can see that

'id,...,userProfile') values((...

was sent. In other words the columns were sent as a single (string) literal. Unfortunately it is not possible to use placeholders to pass identifiers. That case requires query string building. You should whitelist the columns and then build your query.

The VALUES clause is also problematic, due to the double brackets. It turns out that PyMySQL knows how to encode sequences passed as "scalar" arguments:

In [65]: with conn.cursor() as cur:
    ...:     cur.execute('select %s in %s',
    ...:                 ('z', ['\');DROP TABLE students -- ', 'x', 'y']))
    ...:     print(cur.fetchone())
    ...:     print(cur._last_executed)
    ...:     
(0,)
select 'z' in ('\');DROP TABLE students -- ','x','y')

So instead of wrapping the value list placeholder in brackets, just place a placeholder where the value list should be.

ALLOWED_COLUMNS = {'id', 'parentId', 'username', 'creationTime',
                   'role', 'state', 'userProfile'}

for user_data in response['itemList']:
    field_names, field_values = zip(*user_data.items())

    unknown = set(field_names) - ALLOWED_COLUMNS
    if unknown:
        raise RuntimeError(f"Unknown columns: {unknown}")

    field_names = ','.join(field_names)

    with con.cursor() as c: 
        # Note: field names have been whitelisted. Normally one should avoid
        # string formatting SQL queries.
        c.execute(f"INSERT INTO user ({field_names}) VALUES %s", (field_values,))

As a final note, if your columns really use mixed case and were created as such, you might have to quote them.

Undocumented Cursor._last_executed went away in version 1.0.3 — Rich Andrews, May 23 '23 at 17:08
There is a mogrify() function in there as well as an _executed member that may be a comparable for Cursor._last_executed. — Rich Andrews, May 24 '23 at 20:30
Will have to read about it and update accordingly. Thank you for the heads up. — Ilja Everilä, May 25 '23 at 06:26

score 0 · Answer 2 · answered Aug 10 '21 at 08:48

0

try: 
    c.execute("INSERT into user(%s) values(%s);",(field_names[1:],field_values))
except:
    print(c._last_executed)
    raise

answered Aug 10 '21 at 08:48

YoungHyeong Ryu

929
7
9

Gabriel Ferraté · Answer 3 · 2018-07-06T11:25:38.827

with open('saved_response.json') as data_file: 
    #data_ordered = json.load(data_file, object_pairs_hook=OrderedDict)
    response=json.load(data_file)

for user_data in response['itemList']:
    field_names=[]
    field_values=[]
    for i in user_data:
        field_names.append(i)
        field_values.append(user_data[i])
    print(field_names)
    print(field_values)
    with con.cursor() as c:
        c.execute("INSERT into user(%s) values(%s);",(','.join(field_names), ','.join(str(field) for field in field_values)))

The problem was that you where passing a list into the %s. You have to separate the field_values with commas.

How to print the query being sent to mySQL using 'pymysql'?

3 Answers3