[CONPY-208] Maintain last query executed in cursor Created: 2022-06-16  Updated: 2022-06-16  Resolved: 2022-06-16

Status: Closed
Project: MariaDB Connector/Python
Component/s: Other
Affects Version/s: 1.0.11
Fix Version/s: N/A

Type: New Feature Priority: Minor
Reporter: Gavin D'souza Assignee: Georg Richter
Resolution: Won't Fix Votes: 0
Labels: None

Python Version: 3.10

 Description   

cursor.statement provides the statement passed but there's no track of the values or the final mogrified query executed by the client. I couldn't find anything else in the cursor that gave any related information on the query.

Example

cursor.statement - "SELECT `value` FROM `tabSingles` WHERE `doctype`=%(param1)s AND `field`=%(param2)s"

Given the query may be generated by the client anyway, it would be nifty to have something like the following which clients like psycopg2 and pymysql offer.

cursor.query - "SELECT `value` FROM `tabSingles` WHERE `doctype`='System Settings' AND `field`='app_name'"

 Comments   
Comment by Georg Richter [ 2022-06-16 ]

MariaDB Connector/C 1.0 doesn't support this, since it will not substitute parameters. Instead it uses the binary protocol between client and server:

In Binary protocol the statement will be first prepared on the server, afterwards a COM_STMT_EXECUTE command will be send to the server, followed by data for place holders.

For several SQL statements (e.g. without parameters or SELECT) MariaDB Connector/C 1.1 (GA expected still this month) uses the text protocol, the transformed statement can be obtained by cursor._transformed_statement:

>>> cursor.execute("select * from t2 where a like '%s' or a=%s", ("FOO",))
 
>>> cursor._transformed_statement
 
b'select * from t2 where a like \'%s\' or a="FOO"'

Btw. PyMySQL's mogrify seems to be broken, since an exception is raised for the above SQL statement.

Comment by Gavin D'souza [ 2022-06-16 ]

Thanks Georg for the clarification. Looking forward to the 1.1 release

I wasn't aware of the PyMySQL mogrify bug - I'll raise a bug report for the same there :cheers:

Generated at Thu Feb 08 03:31:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.