Home arrow Python arrow Page 7 - Writing CGI Programs in Python

Simple Database Access - Python

And now for something completely different... Python has a very extensive, well documented and portable module library that provides a large variety of useful functions. The Internet-related collection is particularly impressive, with modules to deal with everything from parsing and retrieving URL's to retrieving mail from POP servers and everything in between.

  1. Writing CGI Programs in Python
  2. Why should my next CGI project be in Python?
  3. Your First CGI program in Python
  4. Getting some real work done
  5. Defining a useful Display function
  6. Putting the pieces together
  7. Simple Database Access
  8. Other Resources and Links
By: Preston Landers
Rating: starstarstarstarstar / 78
August 25, 1999

print this article


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
escort Bursa Bursa escort Antalya eskort


- Python Big Data Company Gets DARPA Funding
- Python 32 Now Available
- Final Alpha for Python 3.2 is Released
- Python 3.1: String Formatting
- Python 3.1: Strings and Quotes
- Python 3.1: Programming Basics and Strings
- Tuples and Other Python Object Types
- The Dictionary Python Object Type
- String and List Python Object Types
- Introducing Python Object Types
- Mobile Programming using PyS60: Advanced UI ...
- Nested Functions in Python
- Python Parameters, Functions and Arguments
- Python Statements and Functions
- Statements and Iterators in Python

Developer Shed Affiliates


Dev Shed Tutorial Topics: