HOWTO: Parameters in MySQL queries in PyCharm Follow

I can't find any posting that definitively covers this question (with clear statement saying "this works") and I found out a clear way to do this which, also, is not open to SQL Injection attacks.

Background:

I have six queries which need to run daily against a MySQL database and I manually create XML output files because the native MySQL XML output is not very suitable. I wanted to have a standard function to which I pass — the MySQL connection object, the cursor, the SQL query statement, the SQL parameters and the filename of the XML output file. Of course one need not use a function, the same concept can be used for one query directly in main body of code.

Assumptions:

• Python 3.4 / Windows (should work on any OS platform running Python 3.4
• MySQL Python Connector 2.1.3 (Python 3.5x is not supported by MySQL Connector/Python yet unfortunately)
• Connection object is 'cnx', cursor object is 'cursor'. Valid connection to database has been made and cursor opened.

Process:

Main body of code, the variables str1 & str2 are string variables defined elsewhere (in my queries they are actually string formatted datetime data used in queries. Example query.

query = '''select
     field1,
     field2,
     field3
 from table     
 where text_field1 = %s
     and text_field2 = %s
 group by field1, field2
 order by field1, field2;'''
 
 query_params = (str1, str2)
 xml_file_name = 'TheOutputFile.xml'
 xml_file = file_path + xml_file_name
 make_xml(cnx, cursor, query, query_params, xml_file)

The database connection, cnx, and cursor have already been created. The XML part can be ignored for the purposes of this discussion.

Note that the query parameters are created as a tuple. The query parameters are not escaped out, they are directly part of the multi-line text variable and are substituted at query runtime.

One important point to remember, as a tuple is used if there is only one parameter then it has to be followed with a comma.

So:

• no parameters: query_params = ()
• two or more parameters: query_params = (param1, param2) etc.
• only one parameter: query_params = (param1,)

obviously other field types, like '%d', can be used, I have only had need for strings so far.

This is the relevant part of the function that is called:

def make_xml(cnx, cursor, query, query_params, xml_file):
     try:
         cursor.execute(query, query_params)
         rows = cursor.fetchall()
     except mysql.connector.Error as query_err:
         [...
         ...]
 
     # Code for writing XML file

after the query the XML file is built up field by field, row by row dynamically using the column names from the cursor.

Again I wasn't able to find anything as detailed as this when did a search on the forum, hopefully this can be of help for someone looking for the same information.