Simple Database Access - Python

The last thing we're going to learn about in this installment of our series on learning CGI programming in Python is how to make simple database queries. But instead of providing a complete demonstration program, I'm just going to give you a few code snippets for running SQL queries. You can combine these fragments with what you've learned about working with forms to make a useful web application in Python.

Python has a standard API (Application Programming Interface) for working with databases. The interface is the same for every database that is supported, so your program can work unmodified (in theory) with any common database. Currently, the Python DBI (Database Interface) supports most popular SQL relational databases such as mSQL, MySQL, Oracle, PostgreSQL, and Informix.

There is an excellent resource for people interested in interfacing Python program with databases: the Database SIG (Special Interest Group.)

This article will only cover simple read-only queries. In the next installment we'll cover more complex database operations.

While the programming interface is standardized across databases, it is still necessary to have the module that provides access to your particular database available to the Python interpreter. Often these modules are a separate download and are not included with the standard Python distribution. See the Database SIG mentioned above for instructions on obtaining the module for your database. The code that initializes the database connection will be somewhat dependent on a specific database, while the rest of the code that actually uses the database should work across all supported types of database.

Once you've installed the appropriate module on your system, make it available with an import statement.

>>> import Mysqldb

...loads the MySQL DBI module.

Now we want to initialize the database module by connecting to it. The database module must be given a string in a specific format containing the name of the database to use, your username, and so on. The format is "Database_Name @ Machine_Name User Password". If you don't know some of this information, you'll have to ask your friendly local database administrator.

For instance:

>>> import Mysqldb
>>> SQLDatabase = "guestbook"
>>> SQLHost = "localhost"  
>>> SQLUser = "gbookuser"
>>> SQLPassword = "secret!"
>>> connectstring = SQLDatabase + "@" + SQLHost + " " 
>>> connectstring = connectstring + SQLUser + " " + SQLPassword
>>> connection = Mysqldb.mysqldb(connectstring)
>>> cursor = connection.cursor()

Notice that the last statement returned a thing called a cursor. Coincidentally, we've also named the object that holds that 'cursor', but just as easily it could have been named 'bilbobaggins'. All database action is performed through a cursor, which functions as an active connection to the database. The cursor object that we obtained has a number of methods including execute() and fetchall(). execute() is used to actually execute SQL statements. Use fetchall() to get the results of the previous execute() as a list of tuples. Each tuple represents a specific record/row of the database.

Once you have the cursor, you can perform any SQL statement your database will support with cursor.execute(statement). Here is a simple example that will fetch all rows of a guestbook and display them as an HTML list. [Again, it's not a good idea to embed HTML directly into code like this; it's just an example.]

# get all entries from gbook table, ordered by time stamp
myquery = "SELECT * FROM gbook ORDER BY stamp"
handle.execute(myquery)
Results = handle.fetchall()  # fetch all rows into the Results array
total = len(Results)  # find out how many records were returned
# we'll want a blank list to hold all the guestbook entries
entries = []
if total < 1:
print "There weren't any guestbook entries!"
### do something else here
else:
for record in range(total):
entry = {}  # a blank dictionary to hold each record
entry["gid"] = Results[record][0]  # field 0 = guestbook ID
entry["stamp"] = Results[record][1] # field 1 = timestamp
entry["name"] = Results[record][2]  # and so on...
entry["email"] = Results[record][3]
entry["link"] = Results[record][4]
entry["comment"] = Results[record][5]
entries.append(entry)  # add this entry to the master list
# we'll pretend we set up an HTML table here...
### parse variables into table
for entry in entries:
print "<LI>" + entry["name"] + "@" + entry["email"] +  
" said: " + entry["comment"]

Notice that we copied the information out of the Results list into a list of dictionaries. It's not absolutely necessary to do this; in fact it will slow down your program a tiny bit. The benefit to doing that is that you can access each column of a record by name, rather than the number. If you use the record more than once, it becomes much easier to keep the mnemonic names straight than arbitrary numbers.

>>> More Python Articles          >>> More By Preston Landers

blog comments powered by Disqus