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

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/TI01-discovery/branches/ingestAutomation-upgrade/OAIBatch/db_funcs.py@3852
Revision 3852, 1.6 KB checked in by cbyrom, 13 years ago (diff)

Add port to db_connect and adjust set up of connection to allow this to
be specified (connection string doesn't seem to allow the inclusion of this).
Also, remove default connection settings, to avoid security issues.

Line 
1#!/usr/bin/env python
2
3# functions for use with NDG discovery postgres db
4
5import pgdb, logging, sys
6
7def db_connect(databaseName, hostName, userName, password, port='5432'):
8        '''
9        Open a Postgres database connection
10        @param databaseName: Name of DB to connect to
11        @param hostName: Name of machine where DB is located
12        @param userName: Name of user to connect to DB as
13        @param password: Password for user
14        @param port: Port to use - defaults to 5432
15        '''
16        logging.info("Setting up connection to DB: " + databaseName + " on " + hostName)
17        connection = pgdb.connect(host = hostName + ":" + port, database= databaseName, \
18                                                          user = userName, password = password)
19        logging.info("DB connection established")
20        return connection
21
22
23def runSQLCommand(connection, sqlCmd):
24        '''
25        Run a SQL command against a specified DB connection
26        @param connection: a postgres DB connection
27        @param sqlCmd: a SQL command to execute with the postgres connection 
28        '''
29        logging.info("Running SQL command")
30        logging.debug("Actual command: %s" %sqlCmd)
31        cursor = connection.cursor()
32        try:
33            cursor.execute(sqlCmd)
34        except:
35            sys.exit("Error: database error %s %s" %(sys.exc_type, sys.exc_value))
36        connection.commit()
37        logging.info("SQL command completed successfully")
38
39        # work out if there's anything to return; if so, return it
40        try:
41                data = cursor.fetchall()
42                logging.debug("Command returned data: %s" %data)
43               
44                # test for empty array
45                if data[0]:
46                        return data
47                else:
48                        logging.debug("No actual data returned in array")
49                        return
50        except:
51                logging.debug("Command returned no data")
52
53        return
Note: See TracBrowser for help on using the repository browser.