1 | #!/usr/bin/env python |
---|
2 | |
---|
3 | # functions for use with NDG discovery postgres db |
---|
4 | |
---|
5 | import pgdb, logging, sys |
---|
6 | |
---|
7 | def db_connect(databaseName='discoverydb', hostName='localhost', userName='postgres', password='pass01word'): |
---|
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 | ''' |
---|
15 | logging.info("Setting up connection to DB: " + databaseName + " on " + hostName) |
---|
16 | connection_string = hostName + ':' + databaseName + ':' + userName + ':' + password |
---|
17 | connection = pgdb.connect(connection_string) |
---|
18 | logging.info("DB connection established") |
---|
19 | return connection |
---|
20 | |
---|
21 | |
---|
22 | def runSQLCommand(connection, sqlCmd): |
---|
23 | ''' |
---|
24 | Run a SQL command against a specified DB connection |
---|
25 | @param connection: a postgres DB connection |
---|
26 | @param sqlCmd: a SQL command to execute with the postgres connection |
---|
27 | ''' |
---|
28 | logging.info("Running SQL command") |
---|
29 | logging.debug("Actual command: %s" %sqlCmd) |
---|
30 | cursor = connection.cursor() |
---|
31 | try: |
---|
32 | cursor.execute(sqlCmd) |
---|
33 | except: |
---|
34 | sys.exit("Error: database error %s %s" %(sys.exc_type, sys.exc_value)) |
---|
35 | connection.commit() |
---|
36 | logging.info("SQL command completed successfully") |
---|
37 | |
---|
38 | # work out if there's anything to return; if so, return it |
---|
39 | try: |
---|
40 | data = cursor.fetchall() |
---|
41 | logging.debug("Command returned data: %s" %data) |
---|
42 | |
---|
43 | # test for empty array |
---|
44 | if data[0]: |
---|
45 | return data |
---|
46 | else: |
---|
47 | logging.debug("No actual data returned in array") |
---|
48 | return |
---|
49 | except: |
---|
50 | logging.debug("Command returned no data") |
---|
51 | |
---|
52 | return |
---|