Accessing MySQL in the Real World - Python

Reusability has become the mantra of the current software development paradigm. So this discussion wouldn’t be complete without implementing reusability for database access. The problem is simple; we need to create a generic class that provides the various data manipulation functionalities. I will be developing retrieve and delete functionalities. I am leaving error handling and other functionalities as an ‘experiment’. So let's begin.

The first step is the required imports and the class name:

import MySQLdb
class GenericDBOP:
            :
            :

Next comes the constructor. It takes two parameters, the database connection and the table name:

import MySQLdb
class GenericDBOP:
          def __init__(self, db, name):
            self.db = db #database connection
            self.name = name #table name
            self.dbc = self.db.cursor() #cursor object
             :
             :

The connection is established and the cursor object is retrieved. Executing the SQL statement is what has to be done next:

import MySQLdb
class GenericDBOP:
          def __init__(self, db, name):
                    self.db = db #database connection
                    self.name = name #table name
                    self.dbc = self.db.cursor() #cursor
object                  

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

Here by encapsulating the retrieval functionality in the special function __getitem__ , the class has been provided the ability to access the database as python lists. Although the __getitem__ is good, it executes select query. To make the class more generic, let's add one more function that takes the query to be executed as a parameter:

import MySQLdb
class GenericDBOP:
          def __init__(self, db, name):
                    self.db = db #database connection
                    self.name = name #table name
                    self.dbc = self.db.cursor() #cursor object
                     self.debug=1                  

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

          def _query(self, q):
                  if self.debug: print "Query: %s" % (q)
                  self.dbc.execute(q)

                :
                :

Execution is done, but what if there is a requirement to see the statement? In that case, a debug variable is added to the class. This variable can have more utility in the future if the class needs to be more verbose in its output and logging. If the executed query is for retrieval (in other words, a select statement), then a function to fetch the result is required. An iterator pattern would be better in this case. So let's add an iterator and a function that uses the iterator to return the value one by one:

class GenericDBOP:
          def __init__(self, db, name):
                    self.db = db #database connection
                    self.name = name #table name
                    self.dbc = self.db.cursor() #cursor object
                     self.debug=1                  

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

          def _query(self, q):
                if self.debug: print "Query: %s" % (q)
                self.dbc.execute(q)

          def __iter__(self):
            "creates a data set, and returns an iterator (self)"
                        q = "select * from %s" % (self.name)
                        self._query(q)
                         return self  # an Iterator is an object 
                                                # with a next() method

def next(self):
                        "returns the next item in the data set, 
                        or tells Python to stop"
                        r = self.dbc.fetchone()
                        if not r:# Ok here error is handled and rethrown
                                    raise StopIteration
                   return r

That completes the class. So now it's time to test it. Here it goes:

if __name__==’__main__’:
         db = MySQLdb.connect(user="user", passwd="passwd", 
                    db="library")
         books = GenericDBOP (db, "book")

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

Since the __getitem__() has been implemented in the class, we can access the database as python lists. Other functionalities can also be tested in the same way. That brings us to the end of this tutorial/discussion. From this discussion it is obvious that accessing databases from Python is really simple. This time the center of attention was MySQL. However, in the world of relational databases there are other big players and Oracle is one of them. I will be discussing them in future. Till next time.