class MECODBReader(object): """ Read records from a database. """ def __init__(self, testing = False): """ Constructor. :param testing: True if in testing mode. """ self.connector = MSGDBConnector() self.conn = MSGDBConnector(testing).connectDB() self.dbUtil = MSGDBUtil() self.dbName = self.dbUtil.getDBName(self.connector.dictCur) def selectRecord(self, conn, table, keyName, keyValue): """ Read a record in the database given a table name, primary key name, and value for the key. :param conn DB connection :param table DB table name :param keyName DB column name for primary key :param keyValue Value to be matched :returns: Row containing record data. """ print "selectRecord:" sql = """SELECT * FROM "%s" WHERE %s = %s""" % ( table, keyName, keyValue) dcur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor) self.dbUtil.executeSQL(dcur, sql) row = dcur.fetchone() return row def readingAndMeterCounts(self): """ Retrieve the reading and meter counts. :returns: Multiple lists containing the retrieved data. """ sql = """SELECT "Day", "Reading Count", "Meter Count" FROM count_of_readings_and_meters_by_day""" dcur = self.conn.cursor(cursor_factory = psycopg2.extras.DictCursor) self.dbUtil.executeSQL(dcur, sql) rows = dcur.fetchall() dates = [] meterCounts = [] readingCounts = [] for row in rows: dates.append(row[0]) readingCounts.append(row[1] / row[2]) meterCounts.append(row[2]) return dates, readingCounts, meterCounts
class MSGDBUtilTester(unittest.TestCase): """ Unit tests for MECO DB Utils. """ def setUp(self): self.i = MECODBInserter() # Connect to the testing database. self.connector = MSGDBConnector(testing = True) self.conn = self.connector.connectDB() self.lastSeqVal = None # Does this work having the dictCur be in another class? self.dictCur = self.connector.dictCur self.cursor = self.conn.cursor() self.deleter = MECODBDeleter() self.tableName = 'MeterData' self.columnName = 'meter_data_id' self.configer = MSGConfiger() self.logger = MSGLogger(__name__, 'debug') self.dbUtil = MSGDBUtil() def testMECODBUtilCanBeInited(self): self.assertIsNotNone(self.dbUtil) def testLastSequenceNumberIsCorrect(self): """ Test if last sequence ID value is generated correctly. Do this by inserting and deleting a DB record. """ # Insert some values. sampleDict = {'MeterName': '100001', 'UtilDeviceID': '100001', 'MacID': '00:00:00:00:00:00:00:00'} self.i.insertData(self.conn, self.tableName, sampleDict) self.lastSeqVal = self.dbUtil.getLastSequenceID(self.conn, self.tableName, self.columnName) print "lastSeqVal = %s" % self.lastSeqVal sql = """SELECT * FROM "%s" WHERE %s = %s""" % ( self.tableName, self.columnName, self.lastSeqVal) dictCur = self.connector.dictCur self.dbUtil.executeSQL(dictCur, sql) row = dictCur.fetchone() meterDataID = row[self.columnName] self.assertEqual(self.lastSeqVal, meterDataID) def testGetDBName(self): dbName = self.dbUtil.getDBName(self.cursor)[0] self.logger.log("DB name is %s" % dbName, 'info') self.assertEqual(dbName, "test_meco", "Testing DB name should be set correctly.") def testEraseTestingDatabase(self): """ Test that calls to eraseTestMeco() work correctly. """ dbName = self.dbUtil.getDBName(self.cursor)[0] self.logger.log("DB name is %s" % dbName, 'info') self.assertEqual(dbName, "test_meco", "Testing DB name should be set correctly.") self.dbUtil.eraseTestMeco() # Check all of the tables for the presence of records. for table in self.configer.insertTables: sql = """select count(*) from "%s";""" % table self.dbUtil.executeSQL(self.dictCur, sql) row = self.dictCur.fetchone() self.assertEqual(row[0], 0, "No records should be present in the %s table." % table) def testColumns(self): """ Test the ability to retrieve the column names from a database. """ print self.dbUtil.columns(self.cursor, 'Event') def tearDown(self): """ Delete the record that was inserted. """ if self.lastSeqVal != None: self.deleter.deleteRecord(self.conn, self.tableName, self.columnName, self.lastSeqVal) self.connector.closeDB(self.conn)
class MSGDBUtilTester(unittest.TestCase): """ Unit tests for MECO DB Utils. """ def setUp(self): self.i = MECODBInserter() # Connect to the testing database. self.connector = MSGDBConnector(testing=True) self.conn = self.connector.connectDB() self.lastSeqVal = None # Does this work having the dictCur be in another class? self.dictCur = self.connector.dictCur self.cursor = self.conn.cursor() self.deleter = MECODBDeleter() self.tableName = 'MeterData' self.columnName = 'meter_data_id' self.configer = MSGConfiger() self.logger = SEKLogger(__name__, 'debug') self.dbUtil = MSGDBUtil() def testMECODBUtilCanBeInited(self): self.assertIsNotNone(self.dbUtil) def testLastSequenceNumberIsCorrect(self): """ Test if last sequence ID value is generated correctly. Do this by inserting and deleting a DB record. """ # Insert some values. sampleDict = { 'MeterName': '100001', 'UtilDeviceID': '100001', 'MacID': '00:00:00:00:00:00:00:00' } self.i.insertData(self.conn, self.tableName, sampleDict) self.lastSeqVal = self.dbUtil.getLastSequenceID( self.conn, self.tableName, self.columnName) print "lastSeqVal = %s" % self.lastSeqVal sql = """SELECT * FROM "%s" WHERE %s = %s""" % ( self.tableName, self.columnName, self.lastSeqVal) dictCur = self.connector.dictCur self.dbUtil.executeSQL(dictCur, sql) row = dictCur.fetchone() meterDataID = row[self.columnName] self.assertEqual(self.lastSeqVal, meterDataID) def testGetDBName(self): dbName = self.dbUtil.getDBName(self.cursor)[0] self.logger.log("DB name is %s" % dbName, 'info') self.assertEqual(dbName, "test_meco", "Testing DB name should be set correctly.") def testEraseTestingDatabase(self): """ Test that calls to eraseTestMeco() work correctly. """ dbName = self.dbUtil.getDBName(self.cursor)[0] self.logger.log("DB name is %s" % dbName, 'info') self.assertEqual(dbName, "test_meco", "Testing DB name should be set correctly.") self.dbUtil.eraseTestMeco() # Check all of the tables for the presence of records. for table in self.configer.insertTables: sql = """select count(*) from "%s";""" % table self.dbUtil.executeSQL(self.dictCur, sql) row = self.dictCur.fetchone() self.assertEqual( row[0], 0, "No records should be present in the %s table." % table) def testColumns(self): """ Test the ability to retrieve the column names from a database. """ print self.dbUtil.columns(self.cursor, 'Event') def tearDown(self): """ Delete the record that was inserted. """ if self.lastSeqVal != None: self.deleter.deleteRecord(self.conn, self.tableName, self.columnName, self.lastSeqVal) self.connector.closeDB(self.conn)