Writing Python code to use MySQL

Python is a programming language that lets you work more quickly and integrate your systems more effectively.

I’ve been programming primarily in Python lately, and had the need to open a socket to a MySQL database to pull data. By default, older versions of Python does not currently have a mySQL library that can easily interact with MySQL databases. Rather than upgrading Python and possibly breaking your existing scripts, this article will teach you how to open a MySQL socket using MySQLdb from the DB-API.

DB-API’s design is similar to that used by Perl and Ruby DBI modules, the PHP PEAR DB class, and the Java JDBC interface: It uses a two-level architecture in which the top level provides an abstract interface that is similar for all supported database engines, and a lower level consisting of drivers for specific engines that handle engine-dependent details. This means, of course, that to use DB-API for writing Python scripts, you must have a driver for your particular database system. For MySQL, DB-API provides database access by means of the MySQLdb driver.

Install setuptools

First, we’ll need to install Python “setuptools” which allows us to easy install Python libraries. (official site: http://pypi.python.org/pypi/setuptools)

Install it with a package manager, or download the source from the official site.

RHEL/CentOS:

yum install python-setuptools

Debian/Ubuntu:

apt-get install python-setuptools

Install MySQLdb from DB-API

Download the latest version of MySQL-python from sourceforge.

Note: you must have mysql header libraries installed on the server you wish to install the library (these can be found in the ‘mysql-devel’ packages on most distributions)

Now unpack it on your server:

$ tar xfz MySQL-python-1.2.1.tar.gz
$ cd MySQL-python-1.2.1

You can edit site.cfg if necessary for your environment, but it is not required.

$ python setup.py build
$ sudo python setup.py install (or su first)

Import the library in your code

Now, all you need to do is “import MySQLdb” and open a connection:

This very simply Python script written by Paul DuBois (paul@kitebird.com) can be used as an example to open a MySQL connection and print the server’s version:

conn = MySQLdb.connect (host = “localhost”,
user = “testuser”,
passwd = “testpass”,
db = “test”)
cursor = conn.cursor ()
cursor.execute (“SELECT VERSION()”)
row = cursor.fetchone ()
print “server version:”, row[0]
cursor.close ()
conn.close ()

If you wanted to add in error handling for failures, you could wrap the connection
in a try: statement and then do something like this:

except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
sys.exit (1)

Query execution examples

Executing queries is very simple in MySQL Python. All you need to do is take your cursor object and call the ‘execute’ function. The execute function requires one parameter, the query. If the query contains any substitutions then a second parameter, a tuple, containing the values to substitute must be given.

Example 1: Create Table

cur.execute(“CREATE TABLE song ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, title TEXT NOT NULL )”)
In this example you can see how a basic query without any parameters is executed.

Example 2: Execute Insert / Single Substitution Query

songs = (‘Purple Haze’, ‘All Along the Watch Tower’, ‘Foxy Lady’)
for song in songs:
cur.execute(“INSERT INTO song (title) VALUES (%s)”, song)
print “Auto Increment ID: %s” % cur.lastrowid

In this example, you can see how a query is executed with parameters and you can see how to get the id generated from an auto increment column.

Example 3: Multiple Substitution Query

cur.execute(“SELECT * FROM song WHERE id = %s or id = %s”, (1,2))

It is important to note that when there are multiple parameters to substitue, you must use a tuple to enclose all of the parameters that need to be passed. The parameters are then substituted from left to right with tupe[0] being the left most substitution and tuple[n] being the right most substitution.

Example 4: Execute Select

numrows = cur.execute(“SELECT * FROM song”)
print “Selected %s rows” % numrows
print “Selected %s rows” % cur.rowcount

From this you can see that executing select queries is very easy. There are two ways you can get the number of rows the query returned. The MySQLdb specific way is to save the return value from the execute statement. This is NOT the preferred way. You should use the second method which is the Python DB-API 2.0 way because it will make it easier if you ever have to change databases. Both method’s are illustrated in this example.

This is too complicated for me

Well, maybe you shouldn’t be coding in Python then. If you want the easy way out, install the latest version of Python which comes with MySQL support out of the box.

Share on Facebook

Tweet