source: TI01-discovery/branches/ingestAutomation-upgrade/OAIBatch/db_funcs.py @ 3844

#!/usr/bin/env python

# functions for use with NDG discovery postgres db

import pgdb, logging, sys

def db_connect(databaseName='discoverydb', hostName='localhost', userName='postgres', password='pass01word'):
        '''
        Open a Postgres database connection
        @param databaseName: Name of DB to connect to
        @param hostName: Name of machine where DB is located
        @param userName: Name of user to connect to DB as
        @param password: Password for user 
        '''
        logging.info("Setting up connection to DB: " + databaseName + " on " + hostName)
        connection_string = hostName + ':' + databaseName + ':' + userName + ':' + password
        connection = pgdb.connect(connection_string)
        logging.info("DB connection established")
        return connection


def runSQLCommand(connection, sqlCmd):
        '''
        Run a SQL command against a specified DB connection
        @param connection: a postgres DB connection
        @param sqlCmd: a SQL command to execute with the postgres connection  
        '''
        logging.info("Running SQL command")
        logging.debug("Actual command: %s" %sqlCmd)
        cursor = connection.cursor()
        try:
            cursor.execute(sqlCmd)
        except:
            sys.exit("Error: database error %s %s" %(sys.exc_type, sys.exc_value))
        connection.commit()
        logging.info("SQL command completed successfully")

        # work out if there's anything to return; if so, return it
        try:
                data = cursor.fetchall()
                logging.debug("Command returned data: %s" %data)
                
                # test for empty array
                if data[0]:
                        return data
                else:
                        logging.debug("No actual data returned in array")
                        return
        except:
                logging.debug("Command returned no data")

        return