Home arrow Python arrow Page 4 - Database Programming in Python: Accessing MySQL

Accessing MySQL in the Real World - Python

While data storage for applications is no longer considered complex, it is now simplified to the point that flexibility is sacrificed. There is a good compromise, however. In this article, you will learn how to access a a MySQL database with Python.

TABLE OF CONTENTS:
  1. Database Programming in Python: Accessing MySQL
  2. Accessing MySQL, Step By Step
  3. Accessing MySQL, Step by Step continued
  4. Accessing MySQL in the Real World
By: A.P.Rajshekhar
Rating: starstarstarstarstar / 49
February 21, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Python Articles          >>> More By A.P.Rajshekhar
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PYTHON ARTICLES

- 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: