Go4Expert - Access MySQL Databases in Python

Python being one of the most popular languages, it's being used increasingly used for creating dynamic websites or for specific parts of websites, like Google's support fully runs on Python scripts. But, for this type of usage the ability to access RDBMS is indispensable, although unlike PHP, Python does not provide any in-built functions to access the popular relational database system used on the web, it has plenty of stable & feature-rich third party modules which can help accomplish the requirement.

In this article we'll look into the module MySQLdb which allows Python scripts to connect & MySQL databases.

Installing MySQLdb

MySQLdb requires Python 2.3.4 or higher to work, also it'll work only with MySQL 3.23.32 or higher. Download latest stable release of MySQLdb from http://sourceforge.net/projects/mysql-python/, extract and follow the instructions in the README file, the installation is pretty simple if you have the dependencies in place. If you have to running Debian or Ubuntu, all you need to do is to run the following command as root:

Code:

apt-get install python-mysqldb

Basic Usage

In basic usage we'll see how to connect to a MySQL database server, execute a SQL & fecthing a record, see the following sample, I've added comments to help understand the important steps.

Code: Python

import MySQLdb

## connect to database with the required credentials & details like, host, user, password etc.
db = MySQLdb.connect(
    host = "localhost",
    user = "pradeep",
    passwd = 'mypass',
    db = "g4e")

## get a cursor
my_cursor = db.cursor()

user_name = 'pradeep'

## execute the query
my_cursor.execute("""SELECT * FROM posts WHERE user = %s""", (user_name,))

## fetch a single row
row = my_cursor.fetchone()

## mostly we'll be needing to fetch multiple rows, let's see how to go about it

my_cursor.execute("""SELECT * FROM posts WHERE user = %s""", (user_name,))

while (1):
     row = my_cursor.fetchone()
     ## row empty marks end of resultset
     if row == None:
       break
     ## do something with the rows
   
## getting no of rows returned
print "rows returned: %d" % my_cursor.rowcount

Advanced Usage

In the following code example I would demonstrate advanced usage like error handling, fetching the whole resultset at once, closing statements handles & connection handles. Follow the sample code below:

Code: Python

import MySQLdb
import sys

## connect to database with the required credentials & details like, host, user, password etc.
## we'll try to catch any errors, report error no. & message and exit gracefully
try:
    db = MySQLdb.connect(
        host = "localhost",
        user = "pradeep",
        passwd = 'mypass',
        db = "g4e")
except MySQLdb.Error, e:
    print "Errorno %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)

## get a cursor
my_cursor = db.cursor()

user_name = 'pradeep'

try:
    ## execute the query
    my_cursor.execute("""SELECT * FROM posts WHERE user = %s""", (user_name,))

    ## fetch alls rows in the resultset
    all_rows = my_cursor.fetchone()

    for row in all_rows:
        print "%s => %s" % (row["post_id"], row["post_url"])

    print "Total posts for user %s: %d" % (user_name, cursor.rowcount)

    ## now that we are done working with the cursor, let's close it
    my_cursor.close()
except MySQLdb.Error, e:
    print "Errorno %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)


## lastly close the connection to database
db.close()

References

http://mysql-python.sourceforge.net/MySQLdb.html