MySQL Programming in Python

by K. Yue

1. MySQL Drivers for Python

2. PyMySQL:

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

3. Python Programming with MySQL

3.1 Basic steps

3.2 Connection and Cursor Objects

Example:

student1.py: (All .py and .ini files have an extra extension, .txt, which should be removed when downloading).

import pymysql

# [1] Making connection to the MySQL server
cnx = pymysql.connect(user='demo', password='abcdef',
    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 AS s LEFT JOIN department AS d
        ON (s.major = d.deptCode)
     LEFT JOIN faculty AS f
        ON (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:

C:\...>python student1.py
Tony Hawk: major=Computer Science; advisor=Paul Smith
Mary Hawk: major=Computer Science; advisor=Paul Smith
David Hawk: major=Computer Science; advisor=Mary Tran
Catherine Lim: major=Information Technology; advisor=None
Larry Johnson: major=Information Technology; advisor=Deborah Gump
Linda Johnson: major=Computer Information Systems; advisor=Daniel Kim
Lillian Johnson: major=Computer Information Systems; advisor=Andrew Byre
Ben Zico: major=None; advisor=None
Bill Ching: major=Arts; advisor=None
Linda King: major=Arts; advisor=Art Allister
Cathy Johanson: major=None; advisor=Art Allister

3.3 Configuration files

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[section]

If running in Windows IIS (such as in the dcm server):

import configparser
from pathlib import Path

#  simplistic and no error handling.
def get_mysql_param(filename='dbconfig.ini', section='mysql'):
    config = configparser.ConfigParser()
    file_path = (Path(__file__).parent / filename).resolve()
    config.read(file_path)

    return config[section]

dbconfig.ini:

[mysql]
host = localhost
port = 3306
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'],
      port=int(db['port']))
cursor = cnx.cursor()

If you use the default port (i.e., 3306), you may adjust by

  1. remove the line "port=3306" in dbconfig.ini, and
  2. remove the argument "port" in the call of pymysql.connect() in the main program.

In your Python program,

student2.py:

from dbconfig import *
import pymysql

db = get_mysql_param()

print (db)
cnx = pymysql.connect(user=db['user'], password=db['password'],
      host=db['host'],
      database=db['database'],
      port=int(db['port']))

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 AS s LEFT JOIN department AS d
        ON (s.major = d.deptCode)
     LEFT JOIN faculty AS f
        ON (s.advisor = f.facId);
'''

# Execute the query
cursor.execute(query)

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

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

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:

Get the school code as the command line argument.

student3.py

from dbconfig import *
import pymysql
import sys

# Use the school code in command line arguments
# to list all students majoring in a department
# in the school.
# [1] Making connection to the MySQL server

db = get_mysql_param()
cnx = pymysql.connect(user=db['user'], password=db['password'],
      host=db['host'],
      database=db['database'],
      port=int(db['port']))
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 AS s LEFT JOIN department AS d
        ON (s.major = d.deptCode)
     LEFT JOIN faculty AS f
        ON (s.advisor = f.facId)
WHERE d.schoolCode = %s;
'''

# [2b] Get input values
school_code = 'CSE'
if len(sys.argv) > 1:
    school_code = sys.argv[1]

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

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

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

Result:

C:\...>python student3.py CSE
Linda Johnson: major=Computer Information Systems; advisor=Daniel Kim
Lillian Johnson: major=Computer Information Systems; advisor=Andrew Byre
Tony Hawk: major=Computer Science; advisor=Paul Smith
Mary Hawk: major=Computer Science; advisor=Paul Smith
David Hawk: major=Computer Science; advisor=Mary Tran
Catherine Lim: major=Information Technology; advisor=None
Larry Johnson: major=Information Technology; advisor=Deborah Gump
 
Notes: