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.
Please sign in to leave a comment.