MySQL Bugs: #81572: Allow MySQLCursorPrepared.execute() to accept %(key

Bug #81572	Allow MySQLCursorPrepared.execute() to accept %(key_name)s in operations and dic
Submitted:	24 May 2016 13:28	Modified:	16 Nov 2022 20:04
Reporter:	OCA Admin (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	2.1.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This bug tracks a contribution by Luke Weber (Github user: lukeweber) as described in http://github.com/mysql/mysql-connector-python/pull/19

How to repeat:
See description

Suggested fix:
See contribution code attached

Contribution submitted via Github - Allow MySQLCursorPrepared.execute() to accept %(key_name)s in operations and dic (*) Contribution by Luke Weber (Github lukeweber, mysql-connector-python/pull/19#issuecomment-221028191): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_69942485.txt (text/plain), 5.10 KiB.

Hello Luke Weber,

Thank you for the report and contribution.

Thanks,
Umesh

Take into replacements in query being duplicate

Attachment: updated_changes.patch (application/octet-stream, text), 2.00 KiB.

Please note the validation of query parameters in the previous patch would not allow for duplicates replacements from a single key in a dictionary. There are many cases where this could be useful, but one example:

INSERT INTO `table_name` SET `colum1`= %(column1)s,  `colum2`= %(column2)sON DUPLICATE KEY UPDATE SET `colum1`= %(column1)s;

data = dict(column1="testdata1", column2="testdata2")

In this case the replacements(3) would not equal the count of keys, but would be valid.

I've added a new patch file and checked if len(set(query_replacement_keys)) != len(params) and also check for KeyError on replacement in case the dictionary does not define one of the parameters.

Posted by developer:
 
Thank you for the contribution.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Python 8.0.32 release, and here's the proposed changelog entry from the documentation team:

Enable the use of dictionaries as parameters in prepared statements using
the '%(param)s' format as placeholders. 

Thanks to Luke Weber for the contribution.

Thank you for the bug report.