9. Using the MySQL DBMS
 |
9.1. Installing the MySQLdb module
We will write scripts using a MySQL database:
 |
The Python functions for managing a MySQL database are encapsulated in a MySQLdb module that is not included in the initial Python distribution. You must therefore download and install the module. Here is one way to do this:
 |
- In the Programs menu, select [1] the Python package manager. The command window [2] will then appear.
Search for the keyword mysql in the packages:
C:\Documents and Settings\st>pypm search mysql
Get: [pypm-be.activestate.com] :repository-index:
Get: [pypm-free.activestate.com] :repository-index:
autosync: synced 2 repositories
chartio Setup wizard and connection client for connecting
chartio-setup Setup wizard and connection client for connecting
cns.recipe.zmysqlda Recipe for installing ZMySQLDA
collective.recipe.zmysqlda Recipe for installing ZMySQLDA
django-mysql-manager DESCRIPTION_DESCRIPTION_DESCRIPTION
jaraco.mysql MySQLDB-compatible MySQL wrapper by Jason R. Coomb
lovely.testlayers mysql, postgres nginx, memcached cassandra test la
mtstat-mysql MySQL Plugins for mtstat
mysql-autodoc Generate HTML documentation from a MySQL database
mysql-python Python interface to MySQL
mysqldbda MySQL Database adapter
products.zmysqlda MySQL Zope2 adapter.
pymysql Pure Python MySQL Driver
pymysql-sa PyMySQL dialect for SQLAlchemy.
pymysql3 Pure Python MySQL Driver
sa-mysql-dt Alternative implementation of DateTime column for
schemaobject Iterate over a MySQL database schema as a Python object
schemasync A MySQL Schema Synchronization Utility
simplestore A datastore layer built on top of MySQL in Python.
sqlbean An auto-mapping ORM for MySQL that can bind with memc
sqlwitch sqlwitch offers idiomatic SQL generation on top of
tiddlywebplugins.mysql MySQL-based store for tiddlyweb
tiddlywebplugins.mysql2 MySQL-based store for tiddlyweb
zest.recipe.mysql A Buildout recipe to set up a MySQL database.
All modules whose name or description contains the keyword "mysql" have been listed. The one we're interested in is [mysql-python], line 14. Let's install it:
C:\Documents and Settings\st>pypm install mysql-python
The following packages will be installed into "%APPDATA%\Python" (2.7):
mysql-python-1.2.3
Hit: [pypm-free.activestate.com] mysql-python 1.2.3
Installing mysql-python-1.2.3
C:\Documents and Settings\st>echo %APPDATA%
C:\Documents and Settings\st\Application Data
- Line 5: The mysql-python-1.2.3 package was installed in the "%APPDATA%\Python" folder, where APPDATA is the folder specified on line 8.
This operation may fail if:
9.2. Installing MySQL
There are various ways to install the MySQL DBMS. Here we have used WampServer, a package that combines several software components:
- an Apache web server. We will use it to write web scripts in Python;
- the MySQL database management system;
- the PHP scripting language;
- a MySQL database administration tool written in PHP: phpMyAdmin.
WampServer can be downloaded (June 2011) at the following address:
 |
- In [1], download the appropriate version of WampServer;
- in [2], once installed, launch it. This will start the Apache web server and the MySQL database management system;
- in [3], once launched, WampServer can be managed via an icon [3] located at the bottom right of the taskbar;
- In [4], launch the MySQL administration tool.
Create a database [dbpersonnes]:
Create a user [admpersonnes] with the password [nobody]:
 |  |
 |
- in [1], the user name;
- in [2], the DBMS server on which you grant them permissions;
- in [3], their password [nobody];
- in [4], same as above;
- in [5], we grant no privileges to this user;
- in [6], create the user.
 |
- in [7], return to the phpMyAdmin home page;
- in [8], use the [Privileges] link on this page to modify the privileges for the user [admpersonnes] [9].
 |
- in [10], specify that you want to grant the user [admpersonnes] rights to the [dbpersonnes] database;
- In [11], confirm the selection.
 |
- Using the [12] [Select All] link, grant the user [admpersonnes] all rights to the [dbpersonnes] database [13];
- We confirm in [14].
Now we have:
- a MySQL database [dbpersonnes];
- a user [admpersonnes / nobody] who has full access to this database.
We will write Python scripts to work with the database.
9.3. Connecting to a MySQL Database - 1
Program (mysqldb_01)
# Import the MySQLdb module
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# Connect to a MySQL database
....
Notes:
- Lines 2–4: Scripts containing operations with the MySQL DBMS must import the MySQLdb module. Recall that we installed this module in the [%APPDATA%\Python] folder. The [%APPDATA%\Python] folder is automatically searched when a Python script requests a module. In fact, all folders listed in sys.path are searched. Here is an example that displays these folders:
# -*- coding=utf-8 -*-
import sys
# display the folders in sys.path
for folder in sys.path:
print folder
The screen output is as follows:
Line 8, the [site-packages] folder where MySQLdb was originally installed. We are moving the [site-packages] folder, which is where the pypm utility installs Python modules. To add a new folder that Python will search for modules in, we add it to the sys.path list:
# Import the MySQLdb module
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# Connect to a MySQL database
....
On line 3, we add the folder where the MySQLdb module was moved.
The complete code for the example is as follows:
# Import the MySQLdb module
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# Connect to a MySQL database
# The user ID is (admpersonnes, nobody)
user="admpersonnes"
pwd="nobody"
host="localhost"
connection=None
try:
print "Connecting..."
# connection
connection = MySQLdb.connect(host=host, user=user, passwd=pwd)
# log
print "Connection to MySQL successful with credentials host={0},user={1},passwd={2}".format(host,user,pwd)
except MySQLdb.OperationalError as message:
print "Error: {0}".format(message)
finally:
try:
connection.close()
except:
pass
- lines 8–11: the script will connect (line 15) the user [admpersonnes / nobody] to the MySQL database on the machine [localhost]. It does not connect to a specific database;
- lines 12–24: the connection may fail. Therefore, it is wrapped in a try/except/finally block;
- line 15: the connect method of the MySQLdb module accepts various named parameters:
- user: the user owning the connection [admpersonnes];
- pwd: the user’s password [nobody];
- host: the machine running the MySQL DBMS [localhost];
- db: the database to connect to. Optional.
- line 18: if an exception is thrown, it is of type [MySQLdb.OperationalError] and the associated error message will be found in the [message] variable;
- lines 20–23: in the [finally] clause, the connection is closed. If an exception occurs, it is caught (line 23) but no action is taken (line 24).
Results
Connecting...
Connection to MySQL successful under the credentials host=localhost,user=admpersonnes,passwd=nobody
9.4. Connecting to a MySQL database - 2
Program (mysqldb_02)
# Import the MySQLdb module
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# ---------------------------------------------------------------------------------
def testConnection(host, login, pwd):
# connects and then disconnects (login, pwd) from the MySQL database on the host server
# raises the MySQLdb.operationalError exception
# connection
connection = MySQLdb.connect(host=host, user=login, passwd=pwd)
print "Connection to MySQL successful using credentials (%s,%s,%s)" % (host, login, passwd)
# Close the connection
connection.close()
print "MySQL connection closed successfully\n"
# ---------------------------------------------- main
# Connect to the MySQL database
# user ID
user="admpersonnes"
passwd="nobody"
host="localhost"
# connection test
try:
testConnection(host, user, passwd)
except MySQLdb.OperationalError as message:
print message
# with a non-existent user
try:
testConnection(host, "xx", "xx")
except MySQLdb.OperationalError as message:
print message
Notes:
- lines 7–15: a function that attempts to connect and then disconnect a user from a MySQL DBMS. Displays the result;
- lines 18–34: main program – calls the testConnection method twice and displays any exceptions.
Results
9.5. Creating a MySQL table
Now that we know how to establish a connection with a MySQL DBMS, we will begin issuing SQL commands over this connection. To do this, we will connect to the created database [dbpersonnes] and use the connection to create a table in the database.
Program (mysqldb_03)
# Import the MySQLdb module
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# ---------------------------------------------------------------------------------
def executeSQL(connection, update):
# executes an update query on the connection
# request a cursor
cursor = connection.cursor()
# executes the SQL query on the connection
try:
cursor.execute(update)
connection.commit()
except Exception, error:
connection.rollback()
raise
finally:
cursor.close()
# ---------------------------------------------- main
# Connect to the MySQL database
# User ID
ID="admpersonnes"
PWD="nobody"
# the DBMS host machine
HOST="localhost"
# database name
BASE="dbpeople"
# connection
try:
connection = MySQLdb.connect(host=HOST, user=ID, passwd=PWD, db=DATABASE)
except MySQLdb.OperationalError as message:
print message
sys.exit()
# delete the 'people' table if it exists
# if it doesn't exist, an error will occur
# ignore it
query = "drop table people"
try:
executeSQL(connection, query)
except:
pass
# Create the 'people' table
query = "create table people (first_name varchar(30) NOT NULL, last_name varchar(30) NOT NULL, age integer NOT NULL, primary key(last_name, first_name))"
try:
executeSQL(connection, query)
except MySQLdb.OperationalError as message:
print message
sys.exit()
# Disconnect and exit
try:
connection.close()
except MySQLdb.OperationalError as message:
print message
sys.exit()
Notes:
- line 7: the executeSQL function executes an SQL query on an open connection;
- line 10: SQL operations on the connection are performed through a special object called a cursor;
- Line 10: Obtain a cursor;
- line 13: execute the SQL query;
- line 14: the current transaction is committed;
- line 15: if an exception occurs, the error message is stored in the error variable;
- line 16: the current transaction is rolled back;
- line 17: the exception is rethrown;
- line 19: whether there is an error or not, the cursor is closed. This frees the resources associated with it.
Results
create table people (first_name varchar(30) NOT NULL, last_name varchar(30) NOT NULL, age integer NOT NULL, primary key(last_name, first_name)): query successful
Verification with phpMyAdmin:
 |
- The database [dbpersonnes] [1] has a table [personnes] [2] with the structure [3] and the primary key [4].
9.6. Filling the [people] table
After previously creating the [people] table, we now populate it.
Program (mysqldb_04)
# Import the MySQLdb module
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# other modules
import re
# ---------------------------------------------------------------------------------
def executeCommands(HOST, ID, PWD, DATABASE, SQL, tracking=False, stop=True):
# uses the connection (HOST,ID,PWD,DATABASE)
# executes the SQL commands contained in the SQL text file on this connection
# this file is a file of SQL commands to be executed, one per line
# if tracking=True, then each execution of an SQL command is followed by a message indicating whether it succeeded or failed
# if stop=True, the function stops at the first error encountered; otherwise, it executes all SQL commands
# the function returns a list (number of errors, error1, error2, ...)
# Check for the presence of the SQL file
data=None
try:
data = open(SQL, "r")
except:
return [1, "The file %s does not exist" % (SQL)]
# connection
try:
connection = MySQLdb.connect(host=HOST, user=ID, passwd=PWD, db=DATABASE)
except MySQLdb.OperationalError as error:
return [1, "Error connecting to MySQL as (%s,%s,%s,%s): %s" % (HOST, ID, PASSWORD, DATABASE, error)]
# request a cursor
cursor = connection.cursor()
# execute the SQL queries contained in the SQL file
# put them into an array
queries = data.readlines()
# execute them one by one - initially, no errors
errors=[0]
for i in range(len(queries)):
# store the current query
request = requests[i]
# Is the request empty? If so, move on to the next request
if re.match(r"^\s*$", query):
continue
# execute query i
error=""
try:
cursor.execute(query)
except Exception, error:
pass
#Was there an error?
if error:
# one more error
errors[0] += 1
# error message
msg = "%s: Error (%s)" % (request, error)
errors.append(msg)
# Log to screen or not?
if logging:
print msg
# Should we stop?
if stop:
return errors
else:
if continued:
print "%s: Execution successful" % (request)
# Close connection and release resources
cursor.close()
connection.commit()
# disconnect
try:
connection.close()
except MySQLdb.OperationalError, error:
# another error
errors[0] += 1
# error message
msg = "%s: Error (%s)" % (query, error)
errors.append(msg)
# return
return errors
# ---------------------------------------------- main
# connect to the MySQL database
# user ID
ID="admpersonnes"
PWD="nobody"
# the DBMS host machine
HOST="localhost"
# database name
BASE="dbpersonnes"
# name of the text file containing the SQL commands to be executed
TEXT="sql.txt";
# create and populate the table
errors = executeCommands(HOST, ID, PWD, DATABASE, TEXT, True, False)
# display number of errors
print "There were %s error(s)" % (errors[0])
for i in range(1, len(errors)):
print errors[i]
Results
The sql.txt file:
An error has been intentionally inserted in line 5.
Screen results:
drop table people: Execution successful
create table people (first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, primary key (last_name, first_name)): Execution successful
insert into people values('Paul','Langevin',48): Execution successful
insert into people values ('Sylvie','Lefur',70): Execution successful
xx: Error ((1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at
line 1"))
insert into people values ('Pierre','Nicazou',35) : Execution successful
insert into people values ('Geraldine','Colou',26): Execution successful
insert into people values ('Paulette','Girond',56): Execution successful
There was 1 error
xx: Error ((1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1"))
Verification with phpMyAdmin:
- In [1], the [View] link allows you to see the contents of the [people] table [2].
9.7. Executing arbitrary SQL queries
The following script allows you to execute an SQL command file and display the result of each one:
- the result of the SELECT if the command is a SELECT;
- the number of rows modified if the command is INSERT, UPDATE, or DELETE.
Program (mysqldb_05)
# Import the MySQLdb module
import sys
sys.path.append("D:\Programs\ActivePython\site-packages")
import MySQLdb
# Other modules
import re
def cutNewLineChar(line):
# Remove the end-of-line character from [line] if it exists
l = len(line)
while(line[l-1]=="\n" or line[l-1]=="\r"):
l -= 1
return(line[0:l])
# ---------------------------------------------------------------------------------
def displayInfo(cursor):
# displays the result of an SQL query
# Was it a SELECT query?
if cursor.description:
# there is a description—so it's a SELECT
# description[i] is the description of column i in the SELECT
# description[i][0] is the name of column i in the SELECT
# display the field names
title=""
for i in range(len(cursor.description)):
title += cursor.description[i][0] + ","
# display the list of fields without the trailing comma
print title[0:len(title)-1]
# separator line
print "-" * (len(title) - 1)
# current row of the select
row = cursor.fetchone()
while line:
print line
# next line from the select
row = cursor.fetchone()
else:
# there are no fields - it wasn't a SELECT
print "%s row(s) have been modified" % (cursor.rowcount)
# ---------------------------------------------------------------------------------
def executeCommands(HOST, ID, PWD, DATABASE, SQL, tracking=False, stop=True):
# uses the connection (HOST,ID,PWD,DATABASE)
# executes the SQL commands contained in the SQL text file on this connection
# this file is a file of SQL commands to be executed, one per line
# if tracking=1, then each execution of an SQL command is followed by a message indicating whether it succeeded or failed
# if stop=1, the function stops at the first error encountered; otherwise, it executes all SQL commands
# the function returns an array (number of errors, error1, error2, ...)
# Check for the presence of the SQL file
data=None
try:
data = open(SQL, "r")
except:
return [1, "The file %s does not exist" % (SQL)]
# connection
try:
connection = MySQLdb.connect(host=HOST, user=ID, passwd=PWD, db=DATABASE)
except MySQLdb.OperationalError as error:
return [1, "Error connecting to MySQL as (%s,%s,%s,%s): %s" % (HOST, ID, PWD, DATABASE, error)]
# request a cursor
cursor = connection.cursor()
# execute the SQL queries contained in the SQL file
# put them into an array
queries = data.readlines()
# execute them one by one - initially, no errors
errors=[0]
for i in range(len(queries)):
# store the current query
request = requests[i]
# Is the request empty? If so, move on to the next request
if re.match(r"^\s*$", query):
continue
# execute query i
error=""
try:
cursor.execute(query)
except Exception, error:
pass
#Was there an error?
if error:
# one more error
errors[0] += 1
# error message
msg = "%s: Error (%s)" % (request, error)
errors.append(msg)
# Log to screen or not?
if tracking:
print msg
# Should we stop?
if stop:
return errors
else:
if continued:
print "%s: Execution successful" % (query)
# information about the result of the executed query
displayInfo(cursor)
# Close connection and release resources
cursor.close()
connection.commit()
# log out
try:
connection.close()
except MySQLdb.OperationalError, error:
# another error
errors[0] += 1
# error message
msg = "%s: Error (%s)" % (query, error)
errors.append(msg)
# return
return errors
# ---------------------------------------------- main
# connect to the MySQL database
# user ID
ID="admpersonnes"
PWD="nobody"
# DBMS host machine
HOST="localhost"
# Database name
DATABASE="dbpersonnes"
# Name of the text file containing the SQL commands to execute
TEXT="sql2.txt"
# Create and populate the table
errors = executeCommands(HOST, ID, PWD, DATABASE, TEXT, True, False)
# display number of errors
print "There were %s error(s)" % (errors[0])
for i in range(1, len(errors)):
print errors[i]
Notes:
- The new feature is line 100 of the script: after executing an SQL statement, we request information about the cursor used by that query. This information is provided by the displayInfo function on lines 16–40;
- Lines 19 and 39: If the executed SQL query was a SELECT, the [cursor.description] attribute is an array where element i describes field i of the SELECT result. Otherwise, the [cursor.rowcount] attribute (line 39) is the number of rows modified by the INSERT, UPDATE, or DELETE query;
- lines 32 and 36: the [cursor.fetchone] method retrieves the current row of the SELECT. There is a [cursor.fetchall] method that retrieves all rows at once.
Results
The file of executed queries:
Screen results:
PhpMyAdmin verification:
 |