Database Programming in Python: Accessing MySQL - Accessing MySQL in the Real World (Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 20 
