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

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

Add rollback to db funcs - to allow processing to continue correctly if
an error is experienced during a DB operation.

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                connection.rollback()
36                raise SystemError, "Error: database error %s %s" %(sys.exc_type, sys.exc_value)
37           
38        connection.commit()
39        logging.info("SQL command completed successfully")
40
41        # work out if there's anything to return; if so, return it
42        try:
43                data = cursor.fetchall()
44                logging.debug("Command returned data: %s" %data)
45               
46                # test for empty array
47                if data[0]:
48                        return data
49                else:
50                        logging.debug("No actual data returned in array")
51                        return
52        except:
53                logging.debug("Command returned no data")
54
55        return
Note: See TracBrowser for help on using the repository browser.