HOWTO: Parameters in MySQL queries in PyCharm

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.

0

Please sign in to leave a comment.