Data Access Example

Here's a simple example showing how to create a DB2 data object, run a query against that object and retrieve records.

#
# PRIMARY DATA MODULE
#
import os,sys
import string,types
import db2

class Connect:
    def __init__(self,system=''):
        #
        # BE SURE TO USE YOUR AS/400'S RDB NAME (WRKRDBDIRE) IF
        # USING A REMOTE AS/400.
        # LEAVE system BLANK IF ACCESSING LOCAL SYSTEM.
        #
        self.dbc    =    db2.connect(system)
        self.cursor =    self.dbc.cursor()

    def Logout(self):
        self.cursor.close()
        self.dbc.close()

    def Execute(self, sql, fetch='none'):
        #
        # EXECUTE SQL QUERY ON THE SELECTED AS/400
        #
        # Parameters:
        #  sql   - String variable containing the SQL statement
        #          (ie. "Select Count(*) From MYLIB/MYFILE")
        #  fetch - Either of "fetchall", "fetchone" or "none" if left blank
        #          "fetchall" retrieves all records
        #          "fetchone" retrieves only a single record
        #          "none" or not specified is for UPDATE and INSERT statements typically.
        #          (A variation of this parm might be to specify a method for "fetchmany")
        #
        try:
            self.cursor.execute(sql)
        except:
            return 'error','Your SQL Statement Returned an error: %s\n\n%s'\
                           % (sys.exc_info()[0],sys.exc_info()[1])
        if fetch == 'none':
            return 'Ok',''
        elif fetch == 'fetchone':
            result    =    self.cursor.fetchone()
        elif fetch == 'fetchall':
            result    =    self.cursor.fetchall()

        x = 0
        col = {}
        for each in self.cursor.description:
            col[string.upper(each[0])] = x
            x += 1

        #
        # "result" contains an array of the returned records
        # "col" is a dictionary containing an array index for the column names
        #
        # Example:
        #    If a single row is: ["Joe","Smith",31,12.05,"Full Time"]
        #    And the columns are:[fname,lname,age,wage,jobtype]
        #    Then "col" contains:  { "fname":0,"lname":1,"age":2,"wage":3,"jobtype":4 }
        #
        #    Fields within the returned rows are then accessed like this:
        #    firstname = result[col['fname']]

        return result,col

    def Fix(self,text):
        #
        # FIX TEXT FOR SQL QUERY ON AS/400
        #
        # A tiny routine for fixing string statements so they won't fail during SQL execution
        #
        text = text.replace("'","''")

        return text

def RunQuery():
    #
    # RUN A SAMPLE QUERY
    #
    data = Connect()

    sql = '''Select DIVSN,count(*) as "TOTAL" From MYLIB/EMPLYS Group By DIVSN'''
    result,col = data.Execute(sql,'fetchall')

    for row in result:
        print row[col['DIVSN']], " : ", row[col['TOTAL']], "Employees"

    data.Logout()
    return