Connecting with a database

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:

• Creating a connection object

• Creating a cursor object

• Interacting with the database

• Closing the connection

Creating a connection object

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.

Note

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 menu.sql:

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:

source menu.sql

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

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.

Creating a cursor object

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.

Interacting with the database

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.

Closing the connection

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