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
- Creating a
- 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
[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
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:
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 MySQL 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:
After all changes have been committed, we can then close the database:
Multiple database connections
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.