From 99d69d4fd348bbbb14eaffec21789a4ff744b671 Mon Sep 17 00:00:00 2001 From: Luke Weber Date: Thu, 12 May 2016 17:58:16 -0700 Subject: [PATCH 1/3] Allow prepared cursors to support dictionary params and named entries in operation i.e. %(param_name)s and dict(param_name=value) --- lib/mysql/connector/cursor.py | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) diff --git a/lib/mysql/connector/cursor.py b/lib/mysql/connector/cursor.py index 7cc7362..c4cd331 100644 --- a/lib/mysql/connector/cursor.py +++ b/lib/mysql/connector/cursor.py @@ -48,6 +48,7 @@ b''';(?=(?:[^"'`]*["'`][^"'`]*["'`])*[^"'`]*$)''') RE_SQL_FIND_PARAM = re.compile( b'''%s(?=(?:[^"'`]*["'`][^"'`]*["'`])*[^"'`]*$)''') +RE_SQL_FIND_PYTHON_STRING_PARAM = re.compile('%\([\w_]+\)s') ERR_NO_RESULT_TO_FETCH = "No result set to fetch from" @@ -1076,7 +1077,7 @@ def _handle_result(self, res): self._connection.unread_result = True self._have_result = True - def execute(self, operation, params=(), multi=False): # multi is unused + def execute(self, operation, params=None, multi=False): # multi is unused """Prepare and execute a MySQL Prepared Statement This method will preare the given operation and execute it using @@ -1085,6 +1086,16 @@ def execute(self, operation, params=(), multi=False): # multi is unused If the cursor instance already had a prepared statement, it is first closed. """ + if type(params) == dict: + replaced_fields = re.findall(RE_SQL_FIND_PYTHON_STRING_PARAM, operation) + operation = re.sub(RE_SQL_FIND_PYTHON_STRING_PARAM, '?', operation) + if len(params) != len(replaced_fields): + raise errors.ProgrammingError( + "Not all parameters were used in the SQL statement") + + #Replace params dict with params tuple in correct order. + params = tuple([ params[field[2:-2]] for field in replaced_fields]) + if operation is not self._executed: if self._prepared: self._connection.cmd_stmt_close(self._prepared['statement_id']) From 644cc4096e9a2b6776dbba4daff008ada2b8a53f Mon Sep 17 00:00:00 2001 From: Luke Weber Date: Thu, 12 May 2016 20:54:51 -0700 Subject: [PATCH 2/3] Making the replacement keys unicode safe and using two regexes for replacement in place of an odd string slice --- lib/mysql/connector/cursor.py | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) diff --git a/lib/mysql/connector/cursor.py b/lib/mysql/connector/cursor.py index c4cd331..8df14f0 100644 --- a/lib/mysql/connector/cursor.py +++ b/lib/mysql/connector/cursor.py @@ -48,7 +48,8 @@ b''';(?=(?:[^"'`]*["'`][^"'`]*["'`])*[^"'`]*$)''') RE_SQL_FIND_PARAM = re.compile( b'''%s(?=(?:[^"'`]*["'`][^"'`]*["'`])*[^"'`]*$)''') -RE_SQL_FIND_PYTHON_STRING_PARAM = re.compile('%\([\w_]+\)s') +RE_SQL_PYTHON_REPLACE_PARAM = re.compile('%\(.*?\)s') +RE_SQL_PYTHON_CAPTURE_PARAM_NAME = re.compile('%\((.*?)\)s') ERR_NO_RESULT_TO_FETCH = "No result set to fetch from" @@ -1087,14 +1088,14 @@ def execute(self, operation, params=None, multi=False): # multi is unused first closed. """ if type(params) == dict: - replaced_fields = re.findall(RE_SQL_FIND_PYTHON_STRING_PARAM, operation) - operation = re.sub(RE_SQL_FIND_PYTHON_STRING_PARAM, '?', operation) - if len(params) != len(replaced_fields): + query_replacement_keys = re.findall(RE_SQL_PYTHON_CAPTURE_PARAM_NAME, operation) + operation = re.sub(RE_SQL_PYTHON_REPLACE_PARAM, '?', operation) + if len(params) != len(query_replacements_keys): raise errors.ProgrammingError( "Not all parameters were used in the SQL statement") #Replace params dict with params tuple in correct order. - params = tuple([ params[field[2:-2]] for field in replaced_fields]) + params = tuple([ params[key] for key in query_replacement_keys]) if operation is not self._executed: if self._prepared: From e8867cc54d2510f9a12fc1c13409179bf1f2899e Mon Sep 17 00:00:00 2001 From: Luke Weber Date: Thu, 12 May 2016 21:06:19 -0700 Subject: [PATCH 3/3] Fix typo --- lib/mysql/connector/cursor.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/lib/mysql/connector/cursor.py b/lib/mysql/connector/cursor.py index 8df14f0..6829cdd 100644 --- a/lib/mysql/connector/cursor.py +++ b/lib/mysql/connector/cursor.py @@ -1090,7 +1090,7 @@ def execute(self, operation, params=None, multi=False): # multi is unused if type(params) == dict: query_replacement_keys = re.findall(RE_SQL_PYTHON_CAPTURE_PARAM_NAME, operation) operation = re.sub(RE_SQL_PYTHON_REPLACE_PARAM, '?', operation) - if len(params) != len(query_replacements_keys): + if len(params) != len(query_replacement_keys): raise errors.ProgrammingError( "Not all parameters were used in the SQL statement")