Getting and Setting Database Information

This code sample shows you how to use Python to access a database. For related information, see Database Object and Database Result Set Object.

db = environment.get_default_database()

def get_control_id(control_key):
    '''Look up the control ID for the given key
    '''
    try:
        # submit a database query to retrieve the control ID
        query = 'SELECT * FROM control_table WHERE control_key = ' + control_key
	results = db.execute_select(query)

	# return the control ID if one is found
        if results.count_of_row() > 0:
            return str(results.value(0,'control_id'))

    except Exception, error_message:
        # Log the error message.
        print 'error:', error_message
    return ''

def set_control_id(control_key, value, overwrite=False):
    '''Set the control ID for the given key
    '''
    try:
        # first check to see if control ID is already defined
        query = 'SELECT * FROM control_table WHERE control_key = ' + control_key
        results = db.execute_select(query)

        if results.count_of_row() == 0:
	    # if control ID not previously defined, set the control ID
            query = 'INSERT INTO control_table (control_key, control_id) VALUES (' + \
	        control_key + ', ' + value + ')'
   	    db.execute(query)
	elif overwrite:
  	    # if control ID previously defined and overwriting permitted,
	    # update the existing control ID
            query = 'UPDATE control_table SET control_id = ' + value + \
	        ' WHERE control_key = ', control_key
	    db.execute(query)

    except Exception, error_message:
        print error_message

Here, the get_control_id function searches for all rows in the control table that match the specified control key. If the number of results found is non-zero, the function returns the control ID, which is the value of the control_id column in the first returned row.


	In get_control_id, if the database returns more than result, only the first result is used. This is not normally a problem, as the control key is likely to be a primary key for the table.

The set_control_id function expects a control key, a value for the control ID, and a flag indicating whether to allow overwriting of any existing control ID. The function does the following:

It searches for all rows in the control table that match the specified control key.
If no result is found, it inserts the new control key and its value into the table.
If a control key already exists, and overwrite is set to True, the function overwrites the existing control key, setting it to the new value.

Both functions define exception handlers to trap any errors that occur.

iNTERFACEWARE™ Inc.

Getting and Setting Database Information