vsbabu.org

MySQL on Mandrake 9.1 with Python

After I got MySQL configured and loaded with data on my Mandrake 9.1, I wanted to see how to access it from Python. Turns out to be pretty easy. In this article, I’ll explain how to configure MySQL driver for Python, and a small self-explanatory Python script explaining data access.

I. Pre-requisites

You need to have basic knowledge of Python. I’m going to assume you’ve read my previous article on setting up MySQL and created the sample database and table.

II. Installation of MySQL driver

Download the source distribution of the driver. Extract it to a folder. Now we need to build it:

su - root
cd /where/you/extracted/source/to/
urpmi python-devel
urpmi mysql-devel
python setup.py build
pybhon setup.py bdist_rpm
cd dist
rpm -Uvh MySQL-python-*-i586.rpm
rpm -ql MySQL-python

• Lines 3-4 installs the necessary development packages to support compilation.
• Line 5 performs the module build. Make sure you didn’t get any errors here.
• Line 6 builds binary RPM and source RPM. Makes it much easier to install and to uninstall later, if necessary.
• Lines 7-8 installs the binary RPM.
• Line -9 lists out the files installed by the RPM.

III. Try out a Python Program

Create a file named connectMySQL.py with the following content:

from MySQLdb import connect
from string import join
from pprint import pprint

#setup connection
db = connect(db='mydb', host='localhost', user='myuser', passwd='hello')
cur = db.cursor()

#cursor is required for queries
query = cur.execute("""SELECT state_code, state_name FROM states""")

print "All the functions in the cursor:"
pprint(dir(cur))

print "Description of the cursor:"

pprint(cur.description)
column_headers = [elem[0] for elem in cur.description]

print "Results:"
print column_headers
while 1:
    rec = cur.fetchone()
    if rec is None: break
    pprint(rec)

cur.close()
db.close()

Running it gives the following output on my system. To setup tables, database and data, go back to Pre-requisites section above.

All the functions in the cursor:
['DataError',
 'DatabaseError',
 'Error',
 ...
 'execute',
 'executemany',
 'fetchall',
 ...
 'tell']
Description of the cursor:
(('state_code', 254, 2, 3, 3, 0, 0), ('state_name', 253, 8, 30, 30, 0, 0))
Results:
['state_code', 'state_name']
('VA', 'Virginia')
('MD', 'Maryland')

IV. Next Steps

• Checkout the MySQLdb module documentation at file:///usr/share/doc/MySQL-python-0.9.2/MySQLdb.html.
• Learn more about database connectivity from Python.

1. very good sample code, thanks

   don't know why use pprint not just print?

   Posted by: zick on September 18, 2003 01:04 PM

2. pprint prints lists and dictionaries nicer :-)

   Posted by: Babu on September 18, 2003 02:50 PM