MySQL Programming in Python
by K. Yue
1. MySQL Drivers for Python
2. MySQL Driver
PyMySQL:
MySQL Connector Python:
Python Programming with MySQL
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:
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()
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='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 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: