ave you ever found it tedious to mix SQL and other languages, or been reluctant to write the same four lines of code again to do a simple database query? This article can help you eliminate the drudgery involved in database access, and make your programming time more efficient, by wrapping simple transactions in friendly native Python syntax. By making the database emulate regular Python objects, you can remove a source of friction and frustration from your development process. Your time spent programming will be more efficient and productive when you can focus on the task at hand, without being constantly sidetracked by unimportant details like where the cursor object is, or whether you need to escape-protect the data in the next query.

Another benefit of using native syntax is better portability. This approach makes it easy for you to change databases without having to rewrite any of your application code. Simply modify a few lines in the database wrapper, and you can support an entirely new database. Though this article focuses on MySQL, the code should work easily with PostgreSQL or even SQLite with only minor modifications.

The approach described here is useful any time you want to write a quick program to access a database. You may find it convenient if you want to generate a handful of new reports quickly, or perform basic modifications to your data.

Selecting Rows
To begin, you'll want to convert the most frequently-used operations. The most basic operation is reading or selecting data, so I will focus on that first.

Applications frequently need to do things such as "retrieve record 87", or "print results 30-40." This calls for an array or list structure, which is easy to do in Python. Normally, to show record 87, you would need to execute something such as the following:

c = db.cursor()
c.execute("select * from book limit 87,1")
row = c.fetchone()
display(row) 

Four lines of code is needlessly long for what is essentially an array lookup. It barely begins to utilize the full power of SQL, yet this type of thing is quite common. So, what if you could use simpler syntax to do the same thing? The following would be much easier:

display(books[87]) 

That sort of easy syntax is supported in Python, using its special class methods. Any object can emulate an array by implementing special functions. In this case, you want to create a Table class with a method called __getitem__().

class Table:
   def __init__(self, db, name):
      self.db = db
      self.name = name
      self.dbc = self.db.cursor()

   def __getitem__(self, item):
      self.dbc.execute("select * from %s limit %s, 1" % 
         (self.name, item))
      return self.dbc.fetchone()

Using this class, you can easily access a database table as if it were a native Python list. It merely provides read-only access to single rows, but it's a good start. The following short program demonstrates its use:

import MySQLdb
db = MySQLdb.connect(user="user", passwd="passwd", 
   db="library")
books = Table(db, "book")

for i in xrange(80, 100):
   print "Book %s: %s" % (i, books[i]) 

Condensing the database lookup into a single short expression greatly increases its expressive power. When you write code to keep track of a cursor, execute queries, and fetch rows from the result, the operation just doesn't fit into as many places. Using a word or two for the same concept allows you to incorporate such condensed operations into other ideas more clearly and easily.