Fetching rows or columns from result sets in Python

After executing a statement that returns one or more result sets, use one of the functions available in the ibm_db API to iterate through the returned rows. If your result set includes columns that contain large data (such as BLOB or CLOB data), you can retrieve the data on a column-by-column basis to avoid large memory usage.

Before you begin

You must have a statement resource that is returned by either the ibm_db.exec_immediate or ibm_db.execute function that has one or more associated result sets.

Procedure

To fetch data from a result set:

  1. Fetch data from a result set by calling one of the fetch functions.
    Table 1. ibm_db fetch functions
    Function Description
    ibm_db.fetch_tuple Returns a tuple, which is indexed by column position, representing a row in a result set. The columns are 0-indexed.
    ibm_db.fetch_assoc Returns a dictionary, which is indexed by column name, representing a row in a result set.
    ibm_db.fetch_both Returns a dictionary, which is indexed by both column name and position, representing a row in a result set.
    ibm_db.fetch_row Sets the result set pointer to the next row or requested row. Use this function to iterate through a result set.
    These functions accept the listed arguments:
    stmt
    A valid statement resource.
    row_number
    The number of the row that you want to retrieve from the result set. Specify a value for this parameter if you requested a scrollable cursor when you called the ibm_db.exec_immediate or ibm_db.prepare function. With the default forward-only cursor, each call to a fetch method returns the next row in the result set.
  2. Optional: If you called the ibm_db.fetch_row function, for each iteration through the result set, retrieve a value from a specified column by calling the ibm_db.result function. You can specify the column by passing either an integer that represents the position of the column in the row (starting with 0) or a string that represents the name of the column.
  3. Continue fetching rows until the fetch method returns False, which indicates that you have reached the end of the result set.

    For more information about the ibm_db API, see https://github.com/ibmdb/python-ibmdb/wiki/APIs.

Example

Example 1: Fetch rows from a result set by calling the ibm_db.fetch_both function

import ibm_db

conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
    print "The ID is : ",  dictionary["EMPNO"]
    print "The Name is : ", dictionary[1]
    dictionary = ibm_db.fetch_both(stmt)

Example 2: Fetch rows from a result set by calling the ibm_db.fetch_tuple function

import ibm_db

conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
tuple = ibm_db.fetch_tuple(stmt)
while tuple != False:
    print "The ID is : ", tuple[0]
    print "The name is : ", tuple[1]
    tuple = ibm_db.fetch_tuple(stmt)

Example 3: Fetch rows from a result set by calling the ibm_db.fetch_assoc function

import ibm_db

conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_assoc(stmt)
while dictionary != False:
    print "The ID is : ", dictionary["EMPNO"]
    print "The name is : ", dictionary["FIRSTNME"]
    dictionary = ibm_db.fetch_assoc(stmt)

Example 4: Fetch columns from a result set

import ibm_db

conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
while ibm_db.fetch_row(stmt) != False:
    print "The Employee number is : ",  ibm_db.result(stmt, 0)
    print "The last name is : ", ibm_db.result(stmt, "LASTNAME")

What to do next

When you are ready to close the connection to the database, call the ibm_db.close function. If you attempt to close a persistent connection that you created with ibm_db.pconnect, the close request returns True, and the connection remains available for the next caller.