Writing Data Driven Applications with Python

Posted On July 10, 2007 by Raja Kishore Reddy filed under

Python programming language has moved from the days when it used to be a plumbing tool for web programmers and system administrators. Today it is ubiquitous and is used in practically every application area one can think of! The fact that it is free and Open Source, coupled with the simple, intuitive and clean design of the language makes it attractive for just about any kind of development.

Python can be used to write database driven applications. There are Database APIs for practically every popular database available in the marketplace. In this article, we will explore Python programming using the MySQLdb module, which helps you write Python programs for MySQL database servers.

As a prerequisite, we need to have Python and MySQL installed on your system. It is recommended that you have Python 2.0 and above. To get some of the code working, you need to start your MySQL servers.

You can download MySQLdb module from the web site http://sourceforge.net/projects/mysql-python or find a copy in this month’s CDs.

The installation will range from smooth to rough, depending on your distribution and the packages installed. In case you have one of the latest versions of Red Hat family of products installed on your system (such as Fedora Core 4), it is advisable to install it either from the distribution CD or from the web using yum or apt-get, depending on the flavor. These utilities will take care of all dependencies.

Otherwise, untar the file and move it to the hard disk. Then run the following commands from the directory where the files have been extracted:

%python setup.py build
%python setup.py install

This should get it up and running. In case you are on one of those Debian derivatives, ensure that you have the Python developer binaries installed.

If everything is working fine, then you will see no error when you try importing the MySQLdb module.

Python 2.4.1 (#1, May 16 2005, 15:19:29)
[GCC 4.0.0 20050512 (Red Hat 4.0.0-5)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>>

Now let us try writing a simple script to connect to MySQL database. Before that, let us refresh some of the basic MySQL details.

Usually you require a user name and a password to connect to a MySQL server. This is true for most administered accounts. However, in case you have installed MySQL as a standard service when you installed your distribution, it is likely that your user name and password is not registered. You may be able to connect without a user name and password.

However, a proper MySQL account will have a host, user name and password. You need to know these details of your mysql server running on your PC. To figure this out, just try connecting to the server using the mysql client.

In this article, I have created a database called books and a table titled books in the database. I have populated the table with some data as shown in table 1.

mysql> select * from books;

Table 1: The table books

To establish a connection to the MySQL database server from Python, you need to create a connection object. This can be done easily with the following command:

>>> import MySQLdb
>>> db =MySQLdb.connect(host = “nameofthehost”, user =”nameofthehost”, password = “password”, db ="books")

Make sure you give the correct options, depending on your settings. If you do not require to enter a host name or user name, ignore those parameters. It is imperative that you need to have a database created already.

We will now try to create a cursor, which is essential for us to execute SQL statements:
cursor = db.cursor()

Let us execute a simple SQL statement such as displaying the entire table:
cursor.execute("select * from books")

This loads the details of the table books in the database books into the object cursor. Now, the object cursor is pregnant with information that we can tap easily using some of the methods and properties of the object.

For example, you can find number of rows in the table books with the following command:
>>> cursor.rowcount
6L
>>> result1=cursor.fetchone()
>>> print result1
('Tom Sawyer', 'Mark Twain', 320L)
recordset = cursor.fetchall()

There are methods such as fetchone, fetchmany and fetch all, which provide information in a tuple of tuples. Check these code snippets below:

>>> cursor1.execute(" select * from books where price >200")
4L
>>> cursor1.fetchmany()
(('Tom Sawyer', 'Mark Twain', 320L),)
>>> cursor1.fetchmany()
(('Huckle Berry Finn', 'Mark Twain', 340L),)
>>> cursor1.fetchmany()
(('Oliver Twist', 'Charles Dickens', 240L),)
>>> cursor1.fetchmany()
(('Tale of Two Cities', 'Charles Dickens', 280L),)
>>> cursor1.fetchmany()

You can write a simple script to move the contents of the SQL table into a Comma Separated Value file. Let us write that script (code 1).
The script starts from the facts we learned in the above paragraphs. We create a database connection. We create a cursor and execute an SQL script. The data is stored in a tuple of tuples, which is then extracted into a CSV file. This is not the best way to create a CSV file and you can cut down a line or two in this code.
You need to change a few parameters, depending on your SQL Server settings.

Code 1
import MySQLdb
import os
""" Change the values of these variables as deemed"""
__host = "localhost"
__user = "username"
__password = "secret"
def sql2csv(database,table):
        db = MySQLdb.connect (host= __host,
        user= __user,
        password = __password,
        db = database)
        cursor = db.cursor()
        cursor.execute("select * from %s" % table)
        records = cursor.fetchall()
        f = open("%s.csv" % table, "w")

        for record in records:
                line = ''
                for rec in record:
                        line = line + str(rec) + " ,"
                f.write(line[:(len(line)-1)] + '\n')

f.close()

        print "Created %s.csv file in %s directory" % (table, os.getcwd())
if __name__ == "__main__":
        database = raw_input(" Enter Database Name ")
        table    = raw_input (" Enter Table Name")

sql2csv(database, table)

I will explain this piece of code

f.write(line[:(len(line)-1)] + '\n')

The code essentially clips the last comma of the line and adds the new line character (‘\n’), which most windows and Linux editors recognize as a new line. Each line is thus added to the file. Rest of the code should be simple for even Python newbies to understand.

The same idea can be used to write programs that will insert and update databases. One of my favorite programs are those that can convert XML data into SQL and SQL data back to XML.

I am not providing the code, but merely giving tips on how you can go about doing that. Suppose you have a simple XML file such as the following:

<name>
<first_name> Dev </first_name>
<middle_name> Pishorimal </middle_name>
<surname> Anand </surname>
</name>

You may be having thousands of such names stored in an XML file. If you want to store the data inside an SQL table these are the steps:

>>> cursor = db.cursor()

>>> cursor.execute("create table Title (first_name VARCHAR(20), middle_name VARCHAR(18), surname VARCHAR(20))")

You need to then parse and generate a list of the data contained by each element. Then using the insert into Table values.... statement in SQL, you can populate content from the XML documents.

You can create connection class in case you are writing commerce applications and do transaction-based programs. You can create, commit and even rollback SQL statements using these methods, making it an excellent choice for writing financial applications.

In the forthcoming editions, we will revisit SQL and Python and even figure out how you can write such applications.

Added on July 10, 2007 Comment

Writing Data Driven Applications with Python

Comments

Post a comment