Querying DB2 with Python

In this article, we’ll write a simple Python script that will query a DB2 database and print the results. To link Python and DB2, we’ll use the PyDB2 interface. To install PyDB2 download a copy and follow the instructions in the README file.

#!/usr/bin/env python

import DB2

# Instantiate connection object and connect to DB2 database
db = DB2.connect('dbname', 'username', 'password')

# Instantiate cursor object
cursor = db.cursor()

# Build sql statement using python's multiline quotation syntax
sql = """select address_line_1 , address_line_2,
        city, state, zip from property"""

try:

    # Execute statement and fetch results
    cursor.execute(sql)
    results = cursor.fetchall()

    # Loop through rows and print all columns
    for row in results:
        print row[0:]

except:
    print "Error: Unable to fetch data from DB2"

# Close database connection
db.close()

If you read the last couple posts about performing CRUD operations using Python and MySQL, you might have noticed that the code used above to query DB2 is nearly identical to the code used to query MySQL. We have Python’s DB-API to thank for the standardization. There are actually only two lines of code that differentiate the code written for DB2 versus the code written for MySQL. The first difference is the import statement, of course, and the second difference the is database connection string:

import DB2

# Instantiate connection object and connect to DB2 database
db = DB2.connect('dbname', 'username', 'password')
import MySQLdb

# Instantiate connection object and connect to MySQL database
db = MySQLdb.connect("localhost", "username", "password", "dbname")

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s