MySQL for Python

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:

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.

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

sudo yum install mysqldb

sudo urpmq mysqldb

sudo urpmi mysqldb

sudo aptitude search mysqldb

sudo aptitude install python-mysqldb

shell> sudo aptitude install python-setuptools

shell> sudo yum install python-setuptools

shell> sudo urpmi python-setuptools

sh setuptools-0.6c11-py2.5.egg

C:\Python25\Scripts\easy_install.exe

shell> easy_install <name of egg file>

C:\Python25\Scripts\easy_install.exe <name of egg file>

shell> tar xvzf MySQL-python-1.2.3c1.tar.gz

shell> mysql_conf

shell> whereis mysql_config

shell> echo $PATH

shell> aptitude search mysql | grep client | grep dev

#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

shell> python setup.py build

shell> python setup.py install

As with other modules, Python is able to provide online help about MySQL for Python. In the following sections, we look at the MySQLdb and _mysql modules in greater depth using Python's built-in help() function.

After importing MySQLdb, you can read over the documentation that accompanies the module. In a Python shell, type:

help(MySQLdb)

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.

The 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 MySQLdb program.

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 connections. Connection() class, MySQLdb's MySQL database connection class, in the connections.py module.

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 MySQLdb namespace:

help(MySQLdb._mysql)

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.

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.

The syntax for calling the connect() function and assigning the results to a variable is as follows:

[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 connect() function:

[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.

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;

source menu.sql

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")

The 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 MySQLdb.connect() to mydb renders 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 MySQLdb.connections objects.

After the 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.

To create the cursor, we use the cursor() method of the MySQLdb.connections object we created for the connection. The syntax is as follows:

[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:

cursor.execute()

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 quit or exit.

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:

mydb.commit()

After all changes have been committed, we can then close the database:

mydb.close()