MySQL Programming in Python

by K. Yue

1. MySQL Drivers for Python

• In order to access MySQL from Python, drivers are needed for the connection.
• There are many MySQL drivers for Python. Some examples:
  1. MySQLDB Python: http://sourceforge.net/projects/mysql-python/
  2. MySQL Connector Python: https://dev.mysql.com/downloads/connector/python/: written with pure Python. Come with Python distribution.
  3. MySQL Connector Python's Developer Guides: http://dev.mysql.com/doc/connector-python/en/index.html
• Selecting the right driver is important and not straight forward. Examples:
  • An example comparing driver performance: https://github.com/Benoss/PythonMysqlDriversTest
  • PyMySQL evaluation: https://wiki.openstack.org/wiki/PyMySQL_evaluation
• We wil use PyMySQL in this class.
• Because of the problem of MySQL Connector in Python 3, we will use PyMySQL.

2. MySQL Driver

PyMySQL:

• Installation:
  • Install Pip for Python, if necessary: https://pip.pypa.io/en/stable/installing/.
  • Command: "$pip install PyMySQL"
• Documentation: http://pymysql.readthedocs.io/en/latest/
• Support: https://stackoverflow.com/questions/tagged/pymysql

MySQL Connector Python (not used in this course, but it is a good resource)

• API reference: http://dev.mysql.com/doc/connector-python/en/connector-python-reference.html
• Read MySQL Connector Python's Guideline for developer: http://dev.mysql.com/doc/connector-python/en/connector-python-coding.htm
• Examples of good tips and insight:
  1. Use config.py module to store database connection information."
  2. "Any application that accepts input must expect to handle bad data."
  3. "Data that you choose to store in MySQL instead is likely to have special characteristics."
  4. "you can use Python's triple-quoting mechanism to enclose the entire statement."
  5. "Oracle recommends the ENGINE=INNODB clause for most tables, and makes InnoDB the default storage engine in MySQL 5.5 and up."
• Compliant with Python Database API Specification v2.0: https://www.python.org/dev/peps/pep-0249/#module-interface.

Python Programming with MySQL

• Embedded SQL: embedding SQL in a host language.
• SQL: DB language, not a general purpose language.
• Host languages:
  1. general purpose language
  2. usually have a vast library and tools
  3. may have a good community in a specific application area
• Examples of host languages: Java, C#, C, C++, PHP, Perl, Python, Ruby, etc.
• Basic ideas of embedded SQL programming.
  1. Making connections to the database
  2. Preparing and executing SQL statements through the connections
  3. Using the results of SQL statements
  4. House cleaning and closing DB connections
• Step (2) => learn your SQL well.
• Some issues in step (3):
  1. Data type mismatch: relations/tables usually not supported natively in the host languages.
  2. Performance consideration: DB operations are usually much more expensive (take longer time) than CPU operations.
  3. Security consideration: DB breaches are serious.
• To resolve the data type mismatch problem, the concept of cursor is provided.
• A cursor allows the programmer to iterate through the result set, one row at a time.
• Cursor:
  • PyMySQL: http://pymysql.readthedocs.io/en/latest/modules/cursors.html
  • MySQL Connector: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor.html
• There may be different cursor types for performance, security, and access method consideration:
• MySQL Connctor:
  • http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-cursor.html.
  • Examples:
    1. buffered: results buffered in Python; small dataset result
    2. raw: no conversion to Python results
    3. prepared: use prepared statements
    4. named_tuple: results in a dictionary
• PyMySQL's cursor types:
  1. SSCursor: unbuffered cursor
  2. DictCursor: return a dictionary
  3. SDictCursor: an unbuffered cursor returning a dictionary

Example:

#   Import pymysql connector to MySQL
import pymysql

#   [1] Making connection to the MySQL server
cnx = pymysql.connect(user='demo', password='....',
   host='localhost',
   database='toyu')
  
#   Create a cursor using the connection.
cursor = cnx.cursor()

#   [2] Prepare a SQL query for the problem
query = '''
select concat(s.fname, ' ', s.lname) as student,
   d.deptName,
   concat(f.fname, ' ', f.lname) as advisor
from student s, department d, faculty f
where s.major = d.deptCode
and s.advisor = f.facId;
'''

#   Execute the query
cursor.execute(query)

#   [3] Use the result in the query
for (student, major, advisor) in cursor:
   print("{}: major={}; advisor={}".format(student, major, advisor))

#   [4] Housekeeping
cursor.close()
cnx.close()

The output of running this program:

Tony Hawk: major=Computer Science; advisor=Paul Smith
Mary Hawk: major=Computer Science; advisor=Paul Smith
David Hawk: major=Computer Science; advisor=Paul Smith
Catherine Lim: major=Information Technology; advisor=Deborah Gump
Larry Johnson: major=Information Technology; advisor=Deborah Gump
Linda Johnson: major=Computer Information Systems; advisor=Daniel Kim
Lillian Johnson: major=Computer Information Systems; advisor=Daniel Kim
Bill Ching: major=Arts; advisor=Art Allister
Linda King: major=Arts; advisor=Art Allister

Some tips:

• Some tips:
  1. Test your SQL statements thoroughly first.
  2. Use typical input parameters for testing.
  3. Be mindful of:
     • security, especially SQL injection
     • special characters of the languages involved
     • performance
• It is better to use configuration module and data ini file (e.g. dbconfig.py and dbconfig.ini). Why?
  1. Reuse and maintenance
  2. Security

• In general, using a configuration data file is a good programming practice.

dbconfig.py:

import configparser

#  simplistic and no error handling.
def get_mysql_param(filename='dbconfig.ini', section='mysql'):

    config = configparser.ConfigParser()
    config.read(filename)

    return config['mysql']

dbconfig.ini:

mysql]
host = localhost
database = toyu
user = your_account
password = your_password
 

In your Python program:

from dbconfig import *
import pymysql


db = get_mysql_param()
cnx = pymysql.connect(user=db['user'], password=db['password'],
                      host=db['host'],
                      database=db['database'])
cursor = cnx.cursor()
 

• Your SQL statement may use parameters.
• Placeholder parameter uses %s.
• See Format Specification Mini-Language: https://docs.python.org/3.3/library/string.html#formatspec

Example from http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html:

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

select_stmt = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
cursor.execute(select_stmt, {'emp_no': 2})
 

Example with toyu:

Get the department code as the command line argument

#   Import pymysql connector to MySQL
import pymysql
import sys

#   [1] Making connection to the MySQL server
cnx = pymysql.connect(user='.....', password='.....',
   host='localhost',
   database='toyu')
  
#   Create a cursor using the connection.
cursor = cnx.cursor()

#   [2] Prepare a SQL query for the problem
query = '''
select concat(s.fname, ' ', s.lname) as student,
   d.deptName,
   concat(f.fname, ' ', f.lname) as advisor
from student s, department d, faculty f
where s.major = d.deptCode
and s.advisor = f.facId
and d.deptCode = %s;
'''

#   [2b] Get input values
dept_code = sys.argv[1],

#   Execute the query
cursor.execute(query, (dept_code,))

#   [3] Use the result in the query
for (student, major, advisor) in cursor:
   print("{}: major={}; advisor={}".format(student, major, advisor))

#   [4] Housekeeping
cursor.close()
cnx.close()

Notes:

• %s is used as a placeholder.
• The second argument of cursor.execute is ('Smith',). Note the ',' following 'dept_code.
• (dept_code,) is not the same as (dept_code):
  1. (dept_code) is the same as dept_code: () is used to enforce precedence.
  2. (dept_code,) is a tuple: () is a tuple builder.