It may seem rather unnecessary to start a book on MySQL for Python with a chapter on setting it up. There are, in fact, several ways to get MySQL for Python in a place such that your local Python installation can use it. Which one you use will depend as much on your familiarity with your operating system and with Python itself, as it will on which operating system and version of Python you are running.
In this chapter we will cover the following:
Where you can get MySQL for Python
Installing MySQL for Python
Importing the module into your programs
Accessing online help about the MySQL for Python API and its accompanying modules
How to connect to a database
How to create a MySQL cursor proxy within your Python program
How to close the database connection from Python
How to access multiple databases within one program
How you get MySQL for Python depends on your operating system and the level of authorization you have on it. In the following subsections, we walk through the common operating systems and see how to get MySQL for Python on each.
Package managers are used regularly on Linux, but none come by default with Macintosh and Windows installations. So users of those systems can skip this section.
A package manager takes care of downloading, unpacking, installing, and configuring new software for you. In order to use one to install software on your Linux installation, you will need administrative privileges.
Administrative privileges on a Linux system can be obtained legitimately in one of the following three ways:
Log into the system as the root user (not recommended)
Switch user to the root user using
sudoto execute a single command as the root user
The first two require knowledge of the root user's password. Logging into a system directly as the root user is not recommended due to the fact that there is no indication in the system logs as to who used the root account. Logging in as a normal user and then switching to root using
su is better because it keeps an account of who did what on the machine and when. Either way, if you access the root account, you must be very careful because small mistakes can have major consequences. Unlike other operating systems, Linux assumes that you know what you are doing if you access the root account and will not stop you from going so far as deleting every file on the hard drive.
Unless you are familiar with Linux system administration, it is far better, safer, and more secure to prefix the
sudo command to the
package manager call. This will give you the benefit of restricting use of administrator-level authority to a single command. The chances of catastrophic mistakes are therefore mitigated to a great degree.
More information on any of these commands is available by prefacing either
info before any of the preceding commands (
Which package manager you use depends on which of the two mainstream package management systems your distribution uses. Users of RedHat or Fedora, SUSE, or Mandriva will use the RPM Package Manager (RPM) system. Users of Debian, Ubuntu, and other Debian-derivatives will use the
apt suite of tools available for Debian installations. Each package is discussed in the following:
sudo yum search mysqldb
yum returns a hit, you can then install MySQL for Python with the following command:
sudo yum install mysqldb
If you use Mandriva, you will need to use the
urpm package manager in a similar fashion. To search use
sudo urpmq mysqldb
And to install use
sudo urpmi mysqldb
Whether you run a version of Ubuntu, Xandros, or Debian, you will have access to aptitude, the default Debian package manager. Using
sudo we can search for
MySQLdb in the
apt sources using the following command:
sudo aptitude search mysqldb
On most Debian-based distributions, MySQL for Python is listed as
sudo aptitude install python-mysqldb
Using a package manager automates the entire process so you can move to the section Importing MySQL for Python.
Windows users will need to use the older 1.2.2 version of MySQL for Python. Using a web browser, go to the following link:
This page offers a listing of all available files for all platforms. At the end of the file listing, find mysql-python and click on it. The listing will unfold to show folders containing versions of MySQL for Python back to 0.9.1. The version we want is 1.2.2.
Windows binaries do not currently exist for the 1.2.3 version of MySQL for Python. To get them, you would need to install a C compiler on your Windows installation and compile the binary from source.
This is outside the purpose of the present book, but tips for how to do this are contained in the
README file that accompanies the 1.2.3 version.
Click on 1.2.2 and unfold the file listing. As you will see, the Windows binaries are differentiated by Python version—both 2.4 and 2.5 are supported. Choose the one that matches your Python installation and download it. Note that all available binaries are for 32-bit Windows installations, not 64-bit.
After downloading the binary, installation is a simple matter of double-clicking the installation EXE file and following the dialogue. Once the installation is complete, the module is ready for use. So go to the section Importing MySQL for Python.
One of the easiest ways to obtain MySQL for Python is as an
egg file, and it is best to use one of those files if you can. Several advantages can be gained from working with
egg files such as:
They can include metadata about the package, including its dependencies
They allow for the use of egg-aware software, a helpful level of abstraction
Eggs can, technically, be placed on the Python executable path and used without unpacking
They save the user from installing packages for which they do not have the appropriate version of software
They are so portable that they can be used to extend the functionality of third-party applications
One of the best known
egg utilities—Easy Install, is available from the PEAK Developers' Center at http://peak.telecommunity.com/DevCenter/EasyInstall. How you install it depends on your operating system and whether you have package management software available. In the following section, we look at several ways to install
Easy Install on the most common systems.
shell> sudo aptitude install python-setuptools
On RedHat or CentOS you can try using the
yum package manager:
shell> sudo yum install python-setuptools
On Mandriva use
shell> sudo urpmi python-setuptools
You must have administrator privileges to do the installations just mentioned.
If you do not have access to a Linux package manager, but nonetheless have a Unix variant as your operating system (for example, Mac OS X), you can install Python's setuptools manually. Go to:
Download the relevant
egg file for your Python version.
When the file is downloaded, open a terminal and change to the download directory. From there you can run the
egg file as a shell script. For Python 2.5, the command would look like this:
This will install several files, but the most important one for our purposes is
easy_install, usually located
On Windows, one can download the
setuptools suite from the following URL:
From the list located there, select the most appropriate Windows executable file.
Once the download is completed, double-click the installation file and proceed through the dialogue. The installation process will set up several programs, but the one important for our purposes is
easy_install.exe. Where this is located will differ by installation and may require using the search function from the Start Menu.
On 64-bit Windows, for example, it may be in the
Program Files (x86) directory. If in doubt, do a search. On Windows XP with Python 2.5, it is located here:
Note that you may need administrator privileges to perform this installation. Otherwise, you will need to install the software for your own use. Depending on the setup of your system, this may not always work.
Installing software on Windows for your own use requires the following steps:
Copy the setuptools installation file to your Desktop.
Right-click on it and choose the runas option.
Enter the name of the user who has enough rights to install it (presumably yourself).
After the software has been installed, ensure that you know the location of the
easy_install.exe file. You will need it to install MySQL for Python.
EasyInstall, you still need to install the MySQL for Python
egg files for MySQL for Python can be downloaded from the following URL:
There you will see a list of all available files relevant to MySQL for Python.
Which one you use depends on your operating system and your installed Python version. Currently, the only
egg files available for MySQL for Python version 1.2.3c1 are for Linux running either Python 2.5 or 2.6. Mac users should use a
tar.gz) file as discussed in the next section.
To get an
egg file for Windows, click on the MySQL-python directory and select the 1.2.2 version. This is the same directory used for the Windows binaries discussed earlier in this chapter. This time, however, you need to select an
egg for Windows that fits either Python 2.4 or 2.5. There is no 2.6 version.
Once you have the
egg file for MySQL for Python, you simply need to invoke
EasyInstall over the newly-downloaded
egg file. How you do that will depend on the permissions you have for your operating system.
With administrator permissions, you can simply call the
EasyInstall binary. For Linux, it will look like this:
shell> easy_install <name of egg file>
For Windows, you will use a command similar to this one:
C:\Python25\Scripts\easy_install.exe <name of egg file>
Note that you must have administrator privileges to do this. Otherwise, Windows users will have to install the software locally. Linux users can use
EasyInstall will then unpack the archive, install it in your default Python installation folders, and configure it for immediate use.
For Windows users, if you had to install
setuptools locally, you may also require a local installation of Python itself in order to install MySQL for Python. See the section On Microsoft Windows under Installing egg-handling software, for help with this. If you need to go through this process, all of your configurations will be local, so you are best to use full path command-line calls.
If your system has MySQL, Python, and
setuptools, but you still don't have administrative access, it is advisable to unpack the
egg file manually and call it as a local module. To do this, use an archiving program to unzip the file.
The content listing for the Windows egg will look like this:
And the Linux egg unpacks to the following files:
With the exception of the
egg-info directory, the contents are the basic ingredients of a Python module and can be imported locally if one's program resides in the same directory as the files are located.
Due to the need for certain programming libraries, this method of installation applies only to users of Unix-derived operating systems. This method involves installing from the source files and so requires the necessary C libraries to compile a binary version. Windows users should therefore use one of the other methods discussed previously.
If you cannot use
egg files or if you use an earlier version of Python, you should use the
tar.gz file, a
gzip archive. The
tar.gz archive follows the Linux
egg files in the file listing. The current version of MySQL for Python is 1.2.3c1, so the file we want is as following:
This method is by far more complicated than the others. If at all possible, use your operating system's installation method or an
This version of MySQL for Python is compatible up to Python 2.6. It is worth noting that MySQL for Python has not yet been released for Python 3.0 or later versions. In your deployment of the library, therefore, ensure that you are running Python 2.6 or earlier. As noted, Python 2.5 and 2.6 have version-specific releases. Prior to Python 2.4, you will need to use either a
tar.gz version of the latest release or use an older version of MySQL for Python. The latter option is not recommended.
Most Unix-derived operating systems (Linux, Mac) come with the
gzip utilities pre-installed. For users of these systems, unpacking the archive is as simple as the following command:
shell> tar xvzf MySQL-python-1.2.3c1.tar.gz
The archive will then unpack into a directory called
Windows users can use any of the following archive programs to unpack the
Once the file is unpacked, you need to ensure that you have the program
mysql_config in your path. For Mac users, this usually comes with the MySQL installation itself. For Linux, if you are using bash or another shell with command-line completion, you can check this by typing the following in a terminal:
Then press the tab key. If the command is completed to
mysql_config, there are no issues, otherwise your operating system does not know of any such command, and you need to either find it or install it.
An alternative way of checking is to use the
whereis command. Type the following from the command-line:
shell> whereis mysql_config
If it is installed, the system will return its location. Then echo your current
PATH value by typing:
shell> echo $PATH
and compare the results. If the location of
mysql_config is one of the values in your path, there are no issues otherwise, we need to either find it or install it.
mysql_config program comes with the MySQL client development libraries. If you have these installed, check the directory that holds the MySQL client binary (use
whereis mysql if necessary). If you are unsure, you can check with a package manager using the following commands:
shell> aptitude search mysql | grep client | grep dev
This will work for Debian-based systems. Users of RPM-based systems should substitute either
yum search or
aptitude search. This query will return results for the development files and for the MySQL client, and you can then see if the appropriate package is installed. If it is not, you can install it with the
install argument (for either
yum) or by using
mysql_config program is installed, but is outside your path, you need to indicate its location to the MySQL for Python setup configuration. Navigate to the
MySQL-python-1.2.3c1 directory and open the file
site.cfg in your favorite text editor. The file is not large, and the following section is easily seen as the second part of the file:
#The path to mysql_config #Only use this if mysql_config is not on your PATH,or you have some weird setup that requires it #mysql_config = /usr/local/bin/mysql_config
mysql_config is outside of your path, uncomment the last line of the part cited here and enter the correct path. So, if
mysql_config is installed to:
The last line should read:
mysql_config = /usr/local/bin/mysql/bin/mysql_config
Then save the file and close it.
Next, we should build the package using the instructions that came with it in
setup.py. Use the following command to attempt a build without installing it:
shell> python setup.py build
If the process goes through without error, which it usually does, the build is successful. If there is an error, it usually involves the lack of a module or software package. In which case, confirm that you have all the prerequisites needed for the task by checking the list in the
readme file that comes with the archive.
Be sure to read the
readme file that comes with the source code. It contains a lot of help on the installation process.
Once the build is successful, installation can be done with the following command:
shell> python setup.py install
The name of the project MySQL for Python is the current version of a project that began under the rubric
MySQLdb. Consequently, unlike most Python modules, the MySQL for Python module is not called by its name, but by its historic handle. To import the module, insert the following into a Python program or simply type it in a following Python shell:
To make working with the module easier, you can also import it with an alias:
import MySQLdb as mysql
This allows us to use
mysql instead of
MySQLdb when we access parts of the module.
When you do this, several things will occur. You need not be concerned about most of them, but you should be aware that
MySQLdb depends upon a module called
_mysql module is largely a Python adaptation of the MySQL C API.
MySQL for Python is a wrapper for accessing the
_mysql API. A wrapper is essentially a system of macros, or trusted code, that allows you to do common tasks quickly. It allows you to program without having to repeat commonly used or accessed variables and functions. The
_mysql module is a powerful and proven way of accessing a MySQL database. However, controlling it within a Python program can pose a challenge for some, like driving a Formula 1 car for the first time. So consider MySQL for Python as a system that allows you to harness the power of a Formula 1 racing car even if you're merely driving a Hyundai.
Unlike some systems of macros, MySQL for Python still allows you to access the classes and functions of
_mysql. This is due to the nature of Python's
As with other modules, Python is able to provide online help about MySQL for Python. In the following sections, we look at the
_mysql modules in greater depth using Python's built-in
MySQLdb, you can read over the documentation that accompanies the module. In a Python shell, type:
You will then see a manual page detailing all of the functions and classes of MySQL for Python. It is well worth giving this a cursory read to familiarize yourself with the module. In the course of this book, we will cover most of these items from various angles.
As the help page indicates,
MySQLdb includes the following modules:
Each of these is abstracted to the point of its own module in the source tree. Without a doubt, the most important part of the module is
connections.py, without which we could not interface with MySQL. Where the others are static, the conversion module,
convertors.py, allows you to define your own convertor on-the-fly.
MySQLdb module itself has only one operating class that does not pertain to errors—DBAPISet. This is
MySQLdb's internal object class for processing data. To interface with MySQL, however, we use functions. Of the several listed at the end of the
MySQLdb help page, one uses
connect() in every
At first glance, it may here be confusing to see that
MySQLdb seems to have three ways of connecting with a database. In the list of functions, these are as follows:
Knowing the ins and outs of these functions is not necessary. It is, however, important to know that they exist and to recognize that the latter two are simply different ways of transferring data to the first.
Connect() then passes the arguments to the
MySQLdb's MySQL database connection class, in the
In looking over the module, you may also note that reference is made to the
_mysql module, but it is not explicitly detailed. This is because it is a dependency and not part of the module itself. However, you can access the documentation for
_mysql without importing it directly by using the
In the previous discussion about
connections.Connection(), we stopped following the trail of the connection and any ensuing data transmission where
MySQLdb stopped. In reality, however, the data does not stop there. When a connection or operational request is received by
connections.Connection(), it is processed and passed to
_mysql and subsequently to the MySQL API in C to perform it.
To handle this interface,
_mysql uses two classes:
The first is used to establish communication with MySQL and thus returns a
connection object. The second, as the name implies, returns a set containing the results from a MySQL command that a program sends. These results can be either the query results or an error.
_mysql naturally passes the error to the calling process. In the case of
MySQLdb, we then have a comprehensive toolbox to handle the errors that may arise.
In making a phone call, one picks up the handset, dials a number, talks and listens, and then hangs up. Making a database connection through MySQL for Python is nearly as simple. The four stages of database communication in Python are as follows:
Interacting with the database
Closing the connection
As mentioned previously, we use
connect() to create an object for the program's connection to the database. This process automates logging into the database and selecting a database to be used.
[variable] = MySQLdb.connect([hostname], [username], [password],[database name])
Naming these variables as you assign the values is not required, but it is good practice until you get used to the format of the function call. So for the first few chapters of this book, we will use the following format to call the
[variable] = MySQLdb.connect(host="[hostname]", user="[username]", passwd="[password]", db="[database name]")
Let's say we have a database-driven application that creates the menu for a seafood restaurant. We need to query all of the fish from the menu database in order to input them into a new menu. The database is named menu.
If you do not have a database called
menu, you will obviously not be able to connect to it with these examples. To create the database that we are using in this example, put the following code into a text file with the name
CREATE DATABASE `menu`; USE menu; DROP TABLE IF EXISTS `fish`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `fish` ( `ID` int(11) NOT NULL auto_increment, `NAME` varchar(30) NOT NULL default ‘’, `PRICE` decimal(5,2) NOT NULL default ‘0.00’, PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; LOCK TABLES `fish` WRITE; INSERT INTO `fish` VALUES (1,’catfish’,’8.50’),(2,’catfish’,’8.50’),(3,’tuna’,’8.00’),(4,’catfish’,’5.00’),(5,’bass’,’6.75’),(6,’haddock’,’6.50’),(7,’salmon’,’9.50’),(8,’trout’,’6.00’),(9,’tuna’,’7.50’),(10,’yellowfin tuna’,’12.00’),(11,’yellowfin tuna’,’13.00’),(12,’tuna’,’7.50’); UNLOCK TABLES;
Then log into your MySQL session from the directory in which the file
menu.sql is located and type the following:
This will cause MySQL to create and populate our example database.
For this example, the database and program reside on the same host, so we can use localhost. The user for the database is skipper with password mysecret. After importing the MySQL for Python module, we would call the
connect() function as follows:
mydb = MySQLdb.connect(host="localhost", user="skipper", passwd="mysecret", db="menu")
connect() function acts as a foil for the
connection class in
connections.py and returns an object to the calling process. So in this example, assigning the value of
mydb as a
connection object. To illustrate this, you can create the necessary database in MySQL, connect to it as shown previously, then type
help(mydb) at the Python shell prompt. You will then be presented with large amounts of information pertinent to
connection object is created, you cannot interact with the database until you create a cursor object. The name cursor belies the purpose of this object. Cursors exist in any productivity application and have been a part of computing since the beginning. The point of a cursor is to mark your place and to allow you to issue commands to the computer. A cursor in MySQL for Python serves as a Python-based proxy for the cursor in a MySQL shell session, where MySQL would create the real cursor for us if we logged into a MySQL database. We must here create the proxy ourselves.
[cursor name] = [connection object name].cursor()
Using our example of the menu database above, we can use a generic name
cursor for the
database cursor and create it in this way:
cursor = mydb.cursor()
Now, we are ready to issue commands.
Many SQL commands can be issued using a single function as:
There are other ways to issue commands to MySQL depending on the results one wants back, but this is one of the most common. Its use will be addressed in greater detail in future chapters.
In MySQL, you are expected to close the databases and end the session by issuing either
To do this in Python, we use the
close() method of the database object. Whether you close a database outright depends on what actions you have performed and whether MySQL's auto-commit feature is turned on. By default, MySQL has autocommit switched on. Your database administrator will be able to confirm whether auto-commit is switched on. If it is not, you will need to commit any changes you have made. We do this by calling the
commit method of the database object. For
mydb, it would look like this:
After all changes have been committed, we can then close the database:
In MySQL for Python, all database objects are discrete. All you need do is to connect with each under a different name. Consider the following:
mydb1 = MySQLdb.connect(host="localhost", user="skipper", passwd="mysecret", db="fish") mydb2 = MySQLdb.connect(host="localhost", user="skipper", passwd="mysecret", db="fruit") cursor1 = mydb1.cursor() cursor2 = mydb2.cursor()
The objects then function like any other variable or object. By calling their methods and attributes separately, you can interact with either or even copy from one to the other.
In this chapter we have looked at where to find MySQL for Python, as it is not part of Python by default. We have also seen how to install it on both Windows and non-Windows systems—UNIX-like and Linux distributions. The authors of MySQL for Python have taken the pain out of this by providing a very easy way to install through an egg utility like
Like most modules, MySQL for Python must be imported before you can use it in Python. So we then looked at how to import it. Unlike most modules, we saw that MySQL for Python needs to be imported by its earlier moniker,
After that, we took a peek at what is waiting for us under the MySQL for Python covers using
help(). We saw that MySQL for Python is not an interface to MySQL itself but to a MySQL Database API that is built into Python. It has a large number of classes for handling errors, but only one for processing data (There are different kinds of cursors). Further, it does not even use classes to access MySQL, but uses functions to process and pass information to
_mysql, which then passes it to the C MySQL database interface.
Following this trail, we also saw that
_mysql does not have a robust facility for handling errors, but only passes them to the calling process. That is why MySQL for Python has such a robust error handling facility.
Next, we saw how to connect to a MySQL database. As with most parts of Python, this is easy for beginners. But the function used is also sufficiently robust to handle the more complex needs of advanced solutions.
After connecting, we created a
MySQLdb cursor and prepared to interact with the database. This showed that, while there are many things that
MySQLdb will take care of for us (like connection closure), there are some things we need to do manually. In this instance, it is creating the
cursor object that represents the MySQL cursor.
Finally, we saw that one can connect to multiple databases by simply using different object names for each connection. This has the consequence of necessitating different namespaces as we refer to the methods and attributes of each object. But it also allows one to bridge between databases across multiple hosts seamlessly and to present a unified interface for a user.
In the next chapter, we will see how to form a MySQL query and pass it from Python using variables from the system, MySQL, and the user.