Database Programming in Python: Accessing MySQL

转自：http://www.devshed.com/c/a/python/database-programming-in-python-accessing-mysql/

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.

Data forms the base of all application programs. Whatever the domain, the core of the application’s logic goes into extraction, processing, storage and presentation of the data. Each of these has evolved into a specialized domain of its own.

If storage is the context, then anything from persisting data to a file to persisting it into a database can become a part of the core logic. In the early days of programming, any level of storage of data was considered complicated, especially in databases. However in this era of Very High Level Programming Languages, complexity has become a thing of past (almost).

The major issue with VHLL is that most of them sacrifice flexibility for simplicity. Equilibrium between flexibility and simplicity must exist so that data-bound applications need not be termed either complex or user-unfriendly.  This is where languages such as Python play a pivotal role.

The beauty of Python is that it can provide abstraction in data access and processing at various levels. In other words, Python provides for database access both at the SQL level as well as at the object level and to almost any database.

That justifies the first part of this article’s title. Coming to the second part, of all the relational databases, MySQL is the best bet. There are two reasons. The first one is that MySQL is free. The second one is that MySQL is comparatively less resource hungry. Hence, a Python-MySQL combination is a good option for a data-bound application.

The first sections of this article will deal with the steps required for connecting with and retrieving data from MySQL. In the last section I will put  the theory into practice by applying it in a real-world example. That’s the agenda for this discussion.

{mospagebreak title=Accessing MySQL, Step By Step}

Python has provided aspecification for API for database access. This specification is in its second version and is known as DB-API 2.0. Any database access module has to conform to the DB-API 2.0 specification. Almost all the modules for RDBMSs conform to the specification. MySQLdb module is no exception. For accessing MySQL I will use MySQLdb. Four steps must be taken to make it work. They are:

1. Connecting with the database

2. Creation of the cursor

3. Execution of the SQL statement

4. Fetching the result set and iterating over the result set

The fourth step can incorporate any of the following: Create, Retrieve, Update and Delete, or CRUD in short. If the operation is Retrieve or Projection in terms of SQL, a fifth step is required. For all else, only the first four are required, and the fifth is optional. Here are the details:

1. Connecting with the database

A table resides within a database. This is particularly true for MySQL. To create a table, a database must be created first, or at least a database must be present. So to retrieve data from a table, a connection to the database must be established. This is done by using the connect() method. In other words, connect is the constructor of the MySQLdb. The parameters are as follows:

• host is the name of the system where the MySQL server is running. It can be a name or an IP address. If no value is passed, then the default value used is localhost.
  
• user is the user id, which must be authenticated. In other words, this is the authentic id for using the services of the Server. The default value is the current effective user. Most of the time it is either ‘nobody’ or ‘root’.
  
• passwd — It is by using a combination of the user id and a password that MySQL server (or for that matter any server) authenticates a user. The default value is no passwords. That means a null string for this parameter.
  
• db is the database that must be used once the connection has been established with the server. However, if the database to be used is not selected, the connection established is of no use. There is no default value for this parameter. 
  
• port is the port where the server is running. The default value is 3306.

There are more parameters. But these are the most important and most commonly used parameters. For example, to connect to a database ‘test’ running on a system having the name ‘Demo’ with the user ‘root’ and password ‘adm1n,’ the statement would be:

db= MySQLdb.connect(host=’Demo’, user=’root’ , passwd=’adm1n’, db=’test’)

The connect() returns a connection object. In the above example db contains the connection object. It is this object that will be used in the next step.

2. Creation of the cursor

In the terminology of databases, cursor is that area in the memory where the data fetched from the data tables are kept once the query is executed. In essence it is the scratch area for the database.

MySQL does not support cursors. But it is easy to emulate the functionality of cursors. That’s what the MySQLdb does. To get a cursor, the cursor() method of connection object has to be used. There is only one parameter to this method — the class that implements cursor behavior. This parameter is optional. If no value is given, then it defaults to the standard Cursor class. If more control is required, then custom Cursor class can be provided. To obtain a cursor object the statement would be:

cursor= db.cursor()   

Once the above statement is executed, the cursor variable would have a cursor object.

{mospagebreak title=Accessing MySQL, Step by Step continued}  

3. Execution of the SQL statement

The steps enumerated until now have done the job of connecting the application with the database and providing an object that simulates the functionality of cursors. The stage has been set for execution of SQL statements. Any SQL statement supported by MySQL can be executed using the execute() method of the Cursor class. The SQL statement is passed as a string to it. Once the statement is executed successfully, the Cursor object will contain the result set of the retrieved values. For example, to retrieve all the rows of a table named USER_MASTER the statement would be:

cursor.execute(“select * from USER_MASTER”)

Once the above statement is executed, the cursor object would contain all the retrieved. This brings us to the fourth step, fetching of the resultset. Before moving on to the next step, there is one point you must understand. The execute() function accepts and executes any valid SQL statement, including DDL statements such as delete table, alter table, and so on. In the case of DDL statements, there is no fifth step (i.e. iteration over the results fetched).

4. Fetching the resultset

The flexibility of Python comes to the fore in this step also. In the real world, fetching all the rows at once may not be feasible. MySQLdb answers this situation by providing different versions of the fetch() function of Cursor class. The two most commonly used versions are:

• fetchone(): This fetches one row in the form of a Python tuple. All the data types are mapped to the Python data types except one — unsigned integer. To avoid any overflow problem, it is mapped to the long. 
  
• fetchall(): This fetches all the rows as tuple of tuples. While fetchone() increments the cursor position by one, fetchall() does nothing of that kind. Everything else is similar.

The subtleties will become clear from the following example. To fetch one row at a time and display the result, the block would be:

numrows = int(cursor.rowcount) #get the count of total
rows in the #resultset

# get and display one row at a time
for x in range(0,numrows):
        row = cursor.fetchone()
        print row[0], “–>”, row[1]

The above result can be achieved by using fetchall() as shown below:

result = cursor.fetchall()

# iterate through resultset
for record in result:
        print record[0] , “–>”, record[1]

 

The iteration is through the core Python APIs only. As the returned data structure is tuple, no extra API is required.

That covers all the steps required to access MySQL. What I have discussed up to now is “the approach of tackling a problem of the type database connectivity.” However, in real life, reusability plays a more important role than it has in what you have seen so far. Hence in the next section I will be using the steps discussed until now to create a generic class for accessing MySQL.

{mospagebreak title=Accessing MySQL in the Real World}

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.