MySQL for Python: Integrating MySQL and Python can bring a whole new level of productivity to your applications. This practical tutorial shows you how with examples and explanations that clarify even the most difficult concepts.

The four basic functions of any persistent storage system like MySQL spell CRUD:

These are key concepts, which each of the basic MySQL commands reflect.

There is nothing technical about the words themselves, but the concepts are very important. They represent the four activities that you can expect to be able to do in every relational database system you use. There are several alternatives to this acronym and keyword series (for example, SCUD for "select, create, update, and delete" or SIDU for "select, insert, delete, and update"). The point of each of these is that database functionality boils down to two sets of opposing activities:

Each of these will be addressed in the coming chapters. In this one, we start with reading data using SELECT.

In order to best understand how to submit a query through MySQL for Python, it is important to ensure you understand how to submit a query in MySQL itself. The similarities between the two outnumber the differences, but the first may seem confusing if you don't properly understand the second.

MySQL statements have a basic structure. In following a set structure, they are formed like natural language statements. Being a computer program, it understandably responds very poorly to informational statements and only moderately well to questions. Almost all MySQL statements have an imperatival tone, expressing your command. This is reflective of the client-server relationship. The computer is the servant who exists to do the bidding of yourself as the client or, if you prefer, master.

The syntactic structure of a simple MySQL statement is not that different from the language you use every day. Where English would have:

MySQL would need...

We have just seen how to form a query for a generic MySQL session. While that was not particularly difficult, using MySQL for Python is even easier. For this next section, we will be working against a database fish with a table menu that has the following contents:

+----+----------------+-------+
| id | name           | price |
+----+----------------+-------+
|  1 | tuna           |  7.50 | 
|  2 | bass           |  6.75 | 
|  3 | salmon         |  9.50 | 
|  4 | catfish        |  5.00 | 
|  5 | trout          |  6.00 | 
|  6 | haddock        |  6.50 | 
|  7 | yellowfin tuna | 12.00 | 
+----+----------------+-------+

As discussed in Chapter 1, Python's interface with MySQL requires a cursor. It is through the cursor object that we pass commands to MySQL. So, we import MySQL for Python, log into our database fish and create the cursor as follows:

import MySQLdb
mydb = MySQLdb.connect(host = 'localhost', 
                       user = 'skipper', 
                  ...

What if you want to specify a different price floor every time you run the search? What if you didn't want to use a floor but specify the price exactly? What if you wanted to reuse part of the statement and automate queries by fish name instead of retrieving all of them at once? Under such circumstances, you need to be able to handle variables in your SELECT statements.

MySQL for Python passes variables to MySQL in the same way that Python formats other kinds of output. If we wanted to specify just the floor of the search, we would assign the variable as any other and pass it to the execute() method as a string. Consider the following snippet from a Python terminal session:

>>> value = "7.50"
>>> command = cur.execute("""SELECT * FROM menu WHERE price = %s""" %(value))
>>> results = cur.fetchall()
>>> for record in results:
...     print record[0], ". ", record[1], "(%s)" %record[2]
... 
1 .  tuna (7.50)

If we wanted the user...

For reasons of security, one simply must not rely on the user to know the database structure in order to make a query. Even if the user does, one should never write code that assumes this. You never know who the user is and what nefarious results will be sought by the user. Given that there are more people than you know who want to break your code and exploit your data, it is best practice to restrict the user's knowledge of the database and to verify the integrity of any data the end user inputs into the program.

Without doubt, the best way to restrict the user's knowledge of the database is to provide set options for the user in a way that the user cannot edit. In graphical user interfaces (GUIs), this is done most often by drop-down menus and radio buttons. In terminal-based programs, one lists the options. The former keeps the programmer in control of the environment and so funnels the user to the point of either choosing the set...

But what if the user does not want to submit a precise query but needs a list of the possibilities? There are a couple of ways to clarify the search. We could first keep a list of the common search queries. This is something done often by the likes of Google and Yahoo!. This works very well with large datasets served through web servers because it uses a static list of terms and simply culls them out. For more dedicated applications, one can use MySQL's pattern matching ability to present known options on-the-fly.

SELECT * FROM menu WHERE name = 's%'; 		        WRONG
SELECT * FROM menu WHERE...

This chapter has been about querying MySQL from Python. As a project to finish it out, we will build a command-line search utility. Rather than ask the user for the search term, we will expect the user to state the term as an argument for the command-line invocation.

Now, it is true that we could simply take input and feed it through MySQL for Python as a generic SELECT * statement. The logic for this bare bones implementation has been illustrated previously to a great extent. We can create something a bit more sophisticated.

The following characteristics should apply to our search...

While the projection specification that we set for ourselves is fulfilled, there is more that can be done on this program to make it more serviceable in a production environment. Some areas that you might look at for further practice are:

The world database has a few other tables besides City. Can you aggregate their records in Python without using a JOIN statement?

It almost goes without saying that querying and data retrieval is the bread and butter of database programming. In this chapter, we have covered the formation of a MySQL query and how to pass it from Python. We also saw how to use user-defined variables to allow dynamic formation of statements rather than pre-configured queries. In order to adjust our program flow, we also saw how to determine the characteristics of a database and its tables from within Python. Finally, we programmed a command-line search utility that returns data of the user's choice in two different formats.

In the next chapter, we will look at the flipside of the SELECT command, INSERT.