source: ndgCommon/trunk/ndg/common/src/clients/reldb/postgres/postgresclient.py @ 5007

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/ndgCommon/trunk/ndg/common/src/clients/reldb/postgres/postgresclient.py@5007
Revision 5007, 3.8 KB checked in by cbyrom, 11 years ago (diff)

Add interface for relational DB clients to follow - and use this for
the existing postgres client + tidy up this client slightly.

Line 
1'''
2Client for accessing and running SQL statements against a postgres DB
3
4@author: C Byrom, Jan 09, Tessella
5'''
6
7import pgdb, logging, sys
8from ndg.common.src.clients.reldb.interfacereldbclient import InterfaceRelDBClient
9
10DEFAULT_CONFIG_FILE = 'pg.config'
11
12class PostgresClient(InterfaceRelDBClient):
13   
14    def __init__(self, configFile = None):
15        '''
16        Constructor to set up postgres client
17        @keyword configFile: config file to use to set up postgres connection
18        '''
19        logging.info("Instantiating PostgresClient")
20       
21        self.conn = None
22       
23        # set up the db connection, if a config file is specified
24        if configFile:
25            self.setupDBConnection(configFile)
26           
27        logging.info("- client instantiated")
28   
29           
30    def setupDBConnection(self, configFile):
31        '''
32        Get the default DB connection - by reading in data from the db config file
33        @param configFile: filename of config file to use
34        @raise ValueError: if config file has incorrect data
35        @return: pgdb.connection object with connection to DB set up
36        '''
37        logging.info("Setting up connection to postgres DB")
38        dbinfo_file=open(configFile, "r")
39        dbinfo = dbinfo_file.read().split()
40        if len(dbinfo) < 4:
41                raise ValueError, 'Incorrect data in config file'
42       
43        dbConnection = None
44        # if port specified in file, use this, otherwise use default
45        if len(dbinfo) > 4:
46                dbConnection = self.__dbConnect(dbinfo[0], dbinfo[1], dbinfo[2], dbinfo[3], port = dbinfo[4])
47        else:
48                dbConnection = self.__dbConnect(dbinfo[0], dbinfo[1], dbinfo[2], dbinfo[3])
49        logging.info("Postgres DB connection now set up")
50
51        self.conn = dbConnection
52   
53   
54    def __dbConnect(self, databaseName, hostName, userName, password, port='5432'):
55        '''
56        Open a Postgres database connection
57        @param databaseName: Name of DB to connect to
58        @param hostName: Name of machine where DB is located
59        @param userName: Name of user to connect to DB as
60        @param password: Password for user
61        @param port: Port to use - defaults to 5432
62        '''
63        logging.info("Setting up connection to DB: " + databaseName + " on " + hostName)
64        connection = pgdb.connect(host = hostName + ":" + port, database= databaseName, \
65                                                          user = userName, password = password)
66   
67        logging.info("DB connection established")
68        return connection
69   
70   
71    def runSQLCommand(self, sqlCmd):
72        '''
73        Run a SQL command against a specified DB connection
74        @param sqlCmd: a SQL command to execute with the postgres connection
75        @return: array of results data, or None if no data found 
76        '''
77        logging.info("Running SQL command")
78        logging.debug("Actual command: %s" %sqlCmd)
79       
80        if not self.conn:
81            # attempt to run with default password file
82            self.setupDBConnection(DEFAULT_CONFIG_FILE)
83           
84            if not self.conn:
85                logging.error("DB connection not yet set up - run setupDBConnection(configFile) before trying to run a SQL command")
86                return None
87       
88        cursor = self.conn.cursor()
89        try:
90            cursor.execute(sqlCmd)
91        except:
92                self.conn.rollback()
93                raise SystemError, "Error: database error %s %s" %(sys.exc_type, sys.exc_value)
94           
95        self.conn.commit()
96        logging.info("SQL command completed successfully")
97   
98        # work out if there's anything to return; if so, return it
99        try:
100                data = cursor.fetchall()
101                logging.debug("Command returned data: %s" %data)
102               
103                # test for empty array
104                if data[0]:
105                        return data
106                else:
107                        logging.debug("No actual data returned in array")
108                        return None
109        except:
110                logging.debug("Command returned no data")
111   
112        return None
Note: See TracBrowser for help on using the repository browser.