Personal tools
You are here: Home → DB2 → How To's → Python DB2 utilities
Navigation
Log in


Forgot your password?
 
Document Actions

Python DB2 utilities

The db2util module The db2util module provides a few utilities that make it easier to work with the DB2 interface. The entire source code is provided below. The rest of this section highlights the most useful functions and show examples of thei r use

"""Utilities for use with DB2 and the Python DB2 interface."""

__author__ = "Patrick K. O'Brien <pobrien@orbtech.com>"
__cvsid__ = "$Id: db2util.py,v 1.2 2002/12/04 00:38:09 pobrien Exp $"
__revision__ = "$Revision: 1.2 $"[11:-2]


import DB2


SQLTYPES = (
    'SQL_BIGINT',
    'SQL_BINARY',
    'SQL_BLOB',
    'SQL_BLOB_LOCATOR',
    'SQL_CHAR',
    'SQL_CLOB',
    'SQL_CLOB_LOCATOR',
    'SQL_TYPE_DATE',
    'SQL_DBCLOB',
    'SQL_DBCLOB_LOCATOR',
    'SQL_DECIMAL',
    'SQL_DOUBLE',
    'SQL_FLOAT',
    'SQL_GRAPHIC',
    'SQL_INTEGER',
    'SQL_LONGVARCHAR',
    'SQL_LONGVARBINARY',
    'SQL_LONGVARGRAPHIC',
    'SQL_NUMERIC',
    'SQL_REAL',
    'SQL_SMALLINT',
    'SQL_TYPE_TIME',
    'SQL_TYPE_TIMESTAMP',
    'SQL_VARCHAR',
    'SQL_VARBINARY',
    'SQL_VARGRAPHIC',
    )

BINARYTYPES = (
    'SQL_BLOB', 
    'SQL_BLOB_LOCATOR', 
    'SQL_CLOB', 
    'SQL_CLOB_LOCATOR', 
    'SQL_DBCLOB', 
    'SQL_DBCLOB_LOCATOR', 
    'SQL_GRAPHIC', 
    'SQL_LONGVARBINARY', 
    'SQL_LONGVARGRAPHIC', 
    'SQL_VARBINARY', 
    'SQL_VARGRAPHIC'
    )


def connect(dsn='sample', uid='db2inst1', pwd='ibmdb2', 
            autocommit=True, connecttype=1):
    """Return connection to DB2."""
    conn = DB2.connect(dsn, uid, pwd, autocommit, connecttype)
    return conn

def fp(cursor, sep=' ', null=''):
    """Fetch and print all rows returned by cursor.

    cursor is a database cursor
    sep is the column separator (default is one space)
    null is the representation for a NULL value (default is an empty string)
    """
    rows = cursor.fetchall()
    cprint(cursor, rows, sep, null)

def cprint(cursor, rows, sep=' ', null=None):
    """Print rows returned by cursor in a columnar format.

    cursor is a database cursor
    rows is a list of tuples returned by cursor.fetch*
    sep is the column separator (default is one space)
    null is the representation for a NULL value (default is None)
    """
    columns = cursor.description2
    if not columns:
        return '*** NO QUERY WAS EXECUTED ***'
    headers = getheaders(columns, sep)
    # Format the rows.
    dataformats = getdataformats(columns)
    textformats = gettextformats(columns)
    rows = [formatrow(row, dataformats, textformats, sep, null) for row in rows]
    # Print the final formatted text.
    print "\n".join(headers + rows)

def getheaders(columns, sep):
    """Return list of headers for columnar display."""
    nameheader = getnameheader(columns, sep)
    # Dashes will separate the names from the values.
    dashheader = getdashheader(columns, sep)
    headers = [nameheader, dashheader]
    return headers

def getnameheader(columns, sep):
    """Return name header."""
    names = getnames(columns)
    textformats = gettextformats(columns)
    textformat = sep.join(textformats)
    header = textformat % tuple(names)
    return header

def getdashheader(columns, sep):
    """Return dash header."""
    dashes = getdashes(columns)
    textformats = gettextformats(columns)
    textformat = sep.join(textformats)
    header = textformat % tuple(dashes)
    return header

def getnames(columns):
    """Return list of names for columns"""
    names = [column[0] for column in columns]
    return names

def getdashes(columns):
    """Return list of dashes for columnar display."""
    sizes = getdisplaysizes(columns)
    dashes = ['-' * size for size in sizes]
    return dashes

def getdisplaysizes(columns):
    """Return list of display sizes required for columns."""
    sizes = [getdisplaysize(column) for column in columns]
    return sizes

def getdisplaysize(column):
    """Return display size required for column."""
    name, type, displaysize, internalsize, precision, scale, nullokay = column
    size = max(len(name), displaysize)
    if type in BINARYTYPES:
        if type in ('SQL_DBCLOB', 'SQL_DBCLOB_LOCATOR'):
            size = max(len(name), len('<DBCLOB>'))
        else:
            size = max(len(name), len('<?LOB>'))
    return size

def gettextformats(columns):
    """Return list of text format strings for columns."""
    sizes = getdisplaysizes(columns)
    textformats = ['%%-%ss' % size for size in sizes]
    return textformats

def getdataformats(columns):
    """Return list of data format strings for columns."""
    dataformats = [getdataformat(column) for column in columns]
    return dataformats

def getdataformat(column):
    """Return data format string for column."""
    name, type, displaysize, internalsize, precision, scale, nullokay = column
    size = getdisplaysize(column)
    if type in ('SQL_DECIMAL', 'SQL_DOUBLE', 'SQL_FLOAT', 'SQL_NUMERIC', 'SQL_REAL'):
        format = '%%%s.%sf' % (size, scale)
    elif type in ('SQL_BIGINT', 'SQL_INTEGER', 'SQL_SMALLINT'):
        format = '%%%si' % (size)
    elif type in BINARYTYPES:
        format = '%%-%sr' % (size)
    else:
        format = '%%-%ss' % (size)
    return format

def formatrow(row, dataformats, textformats, sep, null=None):
    """Return row as formatted string, taking into account NULL values."""
    row = list(row)
    formats = []
    for n in range(len(row)):
        if row[n] is None:
            if null is not None:
                row[n] = null
            formats.append(textformats[n])
        else:
            formats.append(dataformats[n])
    format = sep.join(formats)
    row = format % tuple(row)
    return row

 

Connecting


The db2util module provides a connect() function that parallels the connect() constructor in the DB2 module. The only difference is that the db2util version provides keyword arguments with useful default values, which can be quite helpful in a Python shell such as IDLE or PyCrust. Also, since a connection is the entry point to all the other DB2 functionality, you have access to everything you need by simply importing the db2util module. Here is the code for the connect() function:

def connect(dsn='sample', uid='db2inst1', pwd='ibmdb2', 
            autocommit=True, connecttype=1):
    """Return connection to DB2."""
    conn = DB2.connect(dsn, uid, pwd, autocommit, connecttype)
    return conn

 

Using the db2util connect() function is almost identical to the DB2 module's version:

>>> from PyDB2 import db2util
>>> conn = db2util.connect(pwd='******')
>>> curs = conn.cursor()
>>> curs.execute('SELECT * FROM STAFF')
>>>

 

 

Fetching and printing

When running SELECT queries you often want to see the results presented in nicely formatted rows and columns. For this, the db2util module provides the fp() function, which stands for "fetch and print." This function requires a cursor object that has already executed a SELECT query, but hasn't fetched any rows yet. Optionally, you may supply a column separator and a value to represent NULL values. The source code is quite short:

def fp(cursor, sep=' ', null=''):
    """Fetch and print all rows returned by cursor.

    cursor is a database cursor
    sep is the column separator (default is one space)
    null is the representation for a NULL value (default is an empty string)
    """
    rows = cursor.fetchall()
    cprint(cursor, rows, sep, null)

 

Here are the results of running fp() with a few different queries:

>>> from PyDB2 import db2util
>>> conn = db2util.connect(pwd='******')
>>> curs = conn.cursor()
>>> from PyDB2.db2util import fp
>>> curs.execute('SELECT * FROM ORG')
>>> fp(curs)
DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION     
-------- -------------- ------- ---------- -------------
      10 Head Office        160 Corporate  New York     
      15 New England         50 Eastern    Boston       
      20 Mid Atlantic        10 Eastern    Washington   
      38 South Atlantic      30 Eastern    Atlanta      
      42 Great Lakes        100 Midwest    Chicago      
      51 Plains             140 Midwest    Dallas       
      66 Pacific            270 Western    San Francisco
      84 Mountain           290 Western    Denver       
>>> curs.execute('SELECT * FROM STAFF WHERE YEARS > 9')
>>> fp(curs)
ID     NAME      DEPT   JOB   YEARS  SALARY    COMM     
------ --------- ------ ----- ------ --------- ---------
    50 Hanes         15 Mgr       10  20659.80          
   210 Lu            10 Mgr       10  20010.00          
   260 Jones         10 Mgr       12  21234.00          
   290 Quill         84 Mgr       10  19818.00          
   310 Graham        66 Sales     13  21000.00    200.30
>>>

 

Cursor printing

The fp() function described in the previous section had very few lines of source code because it relied heavily on the functionality provided by the cprint() function. The cprint() function also expects to receive a cursor object that has already executed a SELECT query, but it also requires the rows returned by one of the fetch methods. Like fp(), you may supply a separator and NULL representation. Looking at the source code reveals the use of the cursor.description2 to determine the column formatting for the result set:

def cprint(cursor, rows, sep=' ', null=None):
    """Print rows returned by cursor in a columnar format.

    cursor is a database cursor
    rows is a list of tuples returned by cursor.fetch*
    sep is the column separator (default is one space)
    null is the representation for a NULL value (default is None)
    """
    columns = cursor.description2
    if not columns:
        return '*** NO QUERY WAS EXECUTED ***'
    headers = getheaders(columns, sep)
    # Format the rows.
    dataformats = getdataformats(columns)
    textformats = gettextformats(columns)
    rows = [formatrow(row, dataformats, textformats, sep, null) for row in rows]
    # Print the final formatted text.
    print "\n".join(headers + rows)

 

Here is one example of cprint() where we supply a different column separator and NULL representation:

>>> curs.execute('SELECT * FROM STAFF WHERE YEARS > 9')
>>> rows = curs.fetchall()
>>> db2util.cprint(curs, rows, sep=' | ', null='*********')
ID     | NAME      | DEPT   | JOB   | YEARS  | SALARY    | COMM     
------ | --------- | ------ | ----- | ------ | --------- | ---------
    50 | Hanes     |     15 | Mgr   |     10 |  20659.80 | *********
   210 | Lu        |     10 | Mgr   |     10 |  20010.00 | *********
   260 | Jones     |     10 | Mgr   |     12 |  21234.00 | *********
   290 | Quill     |     84 | Mgr   |     10 |  19818.00 | *********
   310 | Graham    |     66 | Sales |     13 |  21000.00 |    200.30
>>>

 

Looking at the source code for cprint(), you can see that it calls upon several other functions defined in the db2util module. While we won't describe the rest of them in this tutorial, their purpose is readily apparent and the code should be relatively easy to follow. These functions illustrate the use of the DB2 module and provide a solid foundation to build upon. For example, the db2util module could be used in the creation of a graphical query tool.
Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls