An Introduction to Python on IBM i, Part 2

Note: All of the files needed for this article are available for in one download here.

Welcome back to this introduction to Python on our beloved iSeries. In this second part of the series, you’ll learn how to use Python in something resembling a real world context. You’re going to integrate Python with perhaps the most quintessentially “i” part of IBM i, DB2.

As with Part 1, all the Python scripts featured in this article are available for download here. Unless otherwise stated, all the Python scripts can be run like so:

PYTHON25/PYTHON PROGRAM('/path/to/the/python/script.py')

DB2 is a Pillar

DB2 is one of the major pillars that distinguish OS/400 from lesser business operating systems. A database is a critical part of just about any business, so the fact that most business computers are supplied without one is a little confusing. IBM made the AS/400 the incredible system it is by building in such a powerful database. With a database built right in, it becomes as natural for storing data as a “C:” drive on Windows. But a relational database gives you the enormous power of SQL on structured data, not a mess of files in “My Documents.”

Let’s begin by creating a “sandbox” database.

First, start the QSHELL environment:

qsh

Then enter the lines shown below to create a database called “widgetco” and a table within that database called “customers.”

db2 "create database widgetco"
db2 "create table widgetco.customers (name varchar(200), 
email varchar(200), phone varchar(200))"

Inserting Rows Using Python

Now that you have a database set up for an imaginary Widget Company, you can insert some rows into the “customers” table. Below is a complete Python script that will:

1. Import the “db2” module into Python, so that DB2 classes and functions are available.
2. Connect to the “widgetco” database.
3. Create a cursor, with which we can run some SQL statements.
4. Run three SQL statements, each inserting a customer record.
5. Commit the rows to the table.
6. Close the cursor, and then close the connection.

import db2

def main():

	db2connection = db2.connect()
	db2cursor = db2connection.cursor()
	
	db2cursor.execute('insert into widgetco.customers values(?, ?, ?)',
	 ('Garry Taylor','gt@theescapers.com','07905 9991337'))
	db2cursor.execute('insert into widgetco.customers values(?, ?, ?)',
	 ('Damien DaCruz','damo@yahoo.co.uk','05665 3454564'))
	db2cursor.execute('insert into widgetco.customers values(?, ?, ?)',
	 ('Steve Jobs','sjobs@apple.com','+01 03105 865576'))
	db2connection.commit()
	
	db2cursor.close()
	db2connection.close()
	
main()

On running the code shown above, Python will not appear to do anything, as there are no “print” statements, and (hopefully) no errors. However, if you start up QSHELL again, and run the following code:

db2 "select * from widgetco.customers";

You will now find that the “customers” table contains three rows. If you run the script again, you’ll find the table now has six rows. The number of rows increases as the SQL is blindly inserting the rows, without checking if duplicates exist. If you wish to clear out the “customers” table, run the following code in QSHELL:

db2 "delete from widgetco.customers"

Select Rows Using SQL and Python

Now that you have data in your table, you can use SQL to select from it. The next bit of code selects all the columns in all rows from the table. Once selected, use the cursor method “fetchone()” to fetch one record. When there are no more records to fetch, the fetchone() method returns the “None” value. “None” is a Python keyword, the basic equivalent of NULL in C or *NULL in RPG. Python functions and methods often return None if no other value is available. In Python, we can ask if a value “is not equal to” using the traditional “!=” operator, or the pleasantly readable “is not.”

The “while” loop prints each line to the spool file and fetches the next row. Once “row” is equal to None, no more rows are available and the while loop ends. Close the cursor, then the connection, just as when you were inserting rows.

import db2

def main():

	db2connection = db2.connect()
	db2cursor = db2connection.cursor()
	
	db2cursor.execute('select * from widgetco.customers')
	
	row = db2cursor.fetchone()
	
	while row is not None :
		print row
		row = db2cursor.fetchone()
	
	db2cursor.close()
	db2connection.close()
	
main()

Working with the Data

You have fetched data straight out of DB2. Now modify your script to work with each row as it comes in. Each row is represented as a Python “tuple,” which is something like an array or a list. The first element of the tuple is the first column “name”, and the second element is the second column “email”. Predictably, the third element is the third column “phone”.

The next script reads each row and writes a file containing a message to the customer whose details are recorded in the row. The filename of that message is the customer’s email address. Once those files are written out, you could, of course, use them in a CL script to run SNDDST, or another email solution. This basic mail merge script shows how easy it is to connect to a database, read records, and then write out data based on that record.

import db2

def file_write(filename,data):
	fh = open(filename,"w")
	fh.write(data)
	fh.close()

def save_message(name, email, phone):
	
	message = "Hi "+name+"n We're just checking we've got your correct 
	phone number, please let us know if '"+phone+"' is no longer 
	correct.nn Thanks!"
	
	file_write("/home/gtaylor/"+email,message)
	
	

def main():

	db2connection = db2.connect()
	db2cursor = db2connection.cursor()
	
	db2cursor.execute('select * from widgetco.customers')
	
	row = db2cursor.fetchone()
	
	while row is not None :
		name = row[0]
		email = row[1]
		phone = row[2]
		
		save_message(name, email, phone)
		
		row = db2cursor.fetchone()
			
	db2cursor.close()
	db2connection.close()
	
main()

This script uses two new functions: “save_message” and “file_write”. The “file_write” function is a generic function that writes any given data to any given file name on the IFS. The “save_message” function concatenates the “name” and “phone” variables together with some static text to create a simple message. The “save_message” function then calls the “file_write” function to write that simple message to disk.

I assigned the values of each tuple element to a named variable purely for readability. If you wish to make the code shorter, you could easily remove those variables and use “row[0]” in place of “name” in the function call.

If you wish, you can integrate this Python script with SNDDST or your own program to actually send the email. You can use the “system” method in the “os” module to execute the command.

At the top of your script, import the “os” module in the same way you import the “db2” module:

import os

Then you can call any OS/400 command like so:

os.system("SNDDST TYPE(*LMSG) TOINTNET((someemailaddress@gmail.com))
 DSTD('Subject line…') LONGMSG('Hello!')")

Or, perhaps in the “save_message” function:

os.system("SNDDST TYPE(*LMSG) TOINTNET(("+email+")) 
DSTD('Need to check your email address…') LONGMSG('"+message+"')")

As the last line in the function, this will send out an email after it saves down the file.

More OS Integration to Come!

That wraps up part two of this introduction to Python. In my next article, I will cover more advanced uses of Python, including how you can make your own classes and objects. I’ll also go beyond the OS system and look at further integration with the rest of OS/400.

Garry Taylor is a professional software developer, working with IBM i, Solaris, and Mac. Based in London, Garry is a co-founder of The Escapers, and developer for Kisco Information System. Send your questions or comments for Garry to Ted Holt via the IT Jungle Contact page.

RELATED STORY

An Introduction to Python on IBM i, Part 1

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot