Python MSGDBUtil.executeSQL方法代码示例

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
    def test_log_successful_export(self):
        """
        Test logging of export results to the export history table.
        """
        # @REVIEWED
        self.assertTrue(self.exporter.logSuccessfulExport(name = 'test_export',
                                                          url =
                                                          'http://test_url',
                                                          datetime = 0,
                                                          size = 100))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()

        self.assertTrue(
            dbUtil.executeSQL(cursor, 'select * from "ExportHistory" where '
                                      'timestamp = '
                                      'to_timestamp(0)'))

        self.assertEqual(len(cursor.fetchall()), 1,
                         "There should only be one result row.")

        self.assertTrue(
            dbUtil.executeSQL(cursor, 'delete from "ExportHistory" where '
                                      'timestamp = to_timestamp(0)'))
        conn.commit()

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
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

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
class MECODBDeleter(object):
    """
    Provide delete routines for MECO DB.
    """

    def __init__(self):
        """
        Constructor.
        """
        self.dbUtil = MSGDBUtil()


    def deleteRecord(self, conn, tableName, idText, idValue):
        """
        Delete record from DB where record has an int-based serial number.

        param: tableName
        param: idText DB column name for record ID
        param: idValue Value of the ID to be deleted
        """

        sql = """DELETE FROM "{}" where {} = {}""".format(tableName, idText,
                                                          idValue)
        dictCur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
        self.dbUtil.executeSQL(dictCur, sql)
        conn.commit()

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
class MSGWeatherDataDupeChecker(object):
    """
    Determine if a duplicate record exists based on the tuple

    (WBAN, Date, Time, StationType).
    """

    def __init__(self, testing = False):
        """
        Constructor.

        :param testing: Flag for testing mode.
        """

        self.logger = SEKLogger(__name__, 'debug')
        self.dbUtil = MSGDBUtil()


    def duplicateExists(self, dbCursor, wban, datetime, recordType):
        """
        Check for the existence of a duplicate record.

        :param dbCursor
        :param wban
        :param datetime
        :param recordType
        :returns: True if a duplicate record exists, otherwise False.
        """

        tableName = "WeatherNOAA"
        sql = """SELECT wban, datetime, record_type FROM \"%s\" WHERE
                 wban = '%s' AND datetime = '%s' AND record_type = '%s'""" % (
            tableName, wban, datetime, recordType)

        self.logger.log("sql=%s" % sql, 'debug')
        self.logger.log("wban=%s, datetime=%s, record_type=%s" % (
            wban, datetime, recordType), 'debug')

        self.dbUtil.executeSQL(dbCursor, sql)
        rows = dbCursor.fetchall()

        if len(rows) > 0:
            return True
        else:
            return False

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
def insertData(files, table, cols, testing = False):
	"""
	Insert aggregated data generated by this script into a database table.

	:param files: A list of the filenames to be processed.
	:param table: The name of the table in the DB.
	:param cols: A list of the columns (as strings) in the table.
	:param testing: Specify whether to use test (false by default).
	"""
	connector = MSGDBConnector()
	conn = connector.connectDB()
	dbUtil = MSGDBUtil()
	cursor = conn.cursor()

	cnt = 0

	for file in files:

		with open(file, 'r') as csvfile:
			reader = csv.reader(csvfile, delimiter = ',')
			# Skip the header line.
			reader.next()
			for row in reader:
				sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % (
					table, ','.join(cols),
					','.join("'" + item.strip() + "'" for item in row))

				sql = sql.replace("'NULL'", 'NULL')

				dbUtil.executeSQL(cursor, sql)

				cnt += 1
				if cnt % 10000 == 0:
					conn.commit()

		conn.commit()
		cnt = 0

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
    def countOfDBExports(self, since = None):
        """
        :param since: datetime indicating last export datetime.
        :return: Int of count of exports.
        """
        myDatetime = lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%S')
        if not since:
            since = myDatetime('1900-01-01 00:00')
        self.logger.log(since.strftime('%Y-%m-%d %H:%M'), 'DEBUG')

        sql = 'SELECT COUNT("public"."ExportHistory"."timestamp") FROM ' \
              '"public"."ExportHistory" WHERE "timestamp" > \'{}\''.format(
            since.strftime('%Y-%m-%d %H:%M'))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        rows = None
        if dbUtil.executeSQL(cursor, sql, exitOnFail = False):
            rows = cursor.fetchall()
        assert len(rows) == 1, 'Invalid return value.'
        return rows[0][0]

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
    def logSuccessfulExport(self, name = '', url = '', datetime = 0, size = 0):
        """
        When an export has been successful, log information about the export
        to the database.

        The items to log include:
        * filename
        * URL
        * timestamp
        * filesize

        :param name: String
        :param url: String
        :param datetime:
        :param size: Int
        :return: True if no errors occurred, else False.
        """

        def exportHistoryColumns():
            return ['name', 'url', 'timestamp', 'size']

        timestamp = lambda \
                datetime: 'to_timestamp(0)' if datetime == 0 else "timestamp " \
                                                                  "'{}'".format(
            datetime)

        sql = 'INSERT INTO "{0}" ({1}) VALUES ({2}, {3}, {4}, {5})'.format(
            self.configer.configOptionValue('Export', 'export_history_table'),
            ','.join(exportHistoryColumns()), "'" + name + "'", "'" + url + "'",
            timestamp(datetime), size)

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        result = dbUtil.executeSQL(cursor, sql, exitOnFail = False)
        conn.commit()
        return result

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
class MSGNOAAWeatherDataInserter(object):
    """
    Performs weather data insertion to a database.
    """

    def __init__(self, testing = False):
        """
        Constructor.
        :param testing: True if testing mode is being used.
        """

        self.logger = SEKLogger(__name__, 'info')
        self.dbUtil = MSGDBUtil()
        self.dupeChecker = MSGWeatherDataDupeChecker()

    def insertDataDict(self, conn, tableName, listOfDataDicts, commit = False):
        """
        Given a table name and a dictionary of column names and values,
        insert them to the db.

        :param conn: A database connection.
        :param tableName: Name of the DB table to be inserted to.
        :param columnsAndValues: Dictionary of columns and values to be
        inserted to the DB.
        :param (optional) commit: A flag indicated that DB transactions will
        be committed.
        :returns: Set of datetimes processed.
        """

        cur = conn.cursor()
        processedDateTimes = set()

        for row in listOfDataDicts:

            # Add a creation timestamp using the SQL function.
            row['created'] = 'NOW()'

            cols = []
            vals = []

            for col in row.keys():
                # Prepare the columns and values for insertion via SQL.

                cols.append(col)
                if (row[col] != 'NULL'):
                    # Surround each value with single quotes...
                    vals.append("'%s'" % row[col])
                else:
                    # Except for NULL values.
                    vals.append("%s" % row[col])

            sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % (
                tableName, ','.join(cols), ','.join(vals))

            if self.dupeChecker.duplicateExists(cur, row['wban'],
                                                row['datetime'],
                                                row['record_type']):
                self.logger.log("Dupe found, dropping dupe.", 'info')
            else:
                processedDateTimes.add(
                    dt.datetime.strptime(row['datetime'], "%Y-%m-%d %H:%M"))
                if self.dbUtil.executeSQL(cur, sql,
                                          exitOnFail = False) is False:
                    # An error occurred.
                    for col in sorted(row.keys()):
                        print "%s: %s" % (col, row[col])
                    sys.exit(-1)

        if commit:
            try:
                conn.commit()
            except:
                self.logger.log("ERROR: Commit failed.", 'debug')

        return processedDateTimes

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
class MSGEgaugeNewDataChecker(object):
    """
    Provide notification of newly loaded MSG eGauge data.

    This uses notification type MSG_EGAUGE_SERVICE.
    """

    def __init__(self):
        """
        Constructor.
        """

        print __name__
        self.logger = SEKLogger(__name__)
        self.connector = MSGDBConnector()
        self.dbUtil = MSGDBUtil()
        self.notifier = MSGNotifier()
        self.configer = MSGConfiger()


    def newDataCount(self):
        """
        Measure the amount of new data that is present since the last time
        new data was reported.
        """

        cursor = self.connector.conn.cursor()
        tableName = 'EgaugeEnergyAutoload'
        lastTime = self.lastReportDate('MSG_EGAUGE_SERVICE')
        if lastTime is None:
            lastTime = '1900-01-01'
        sql = """SELECT COUNT(*) FROM "%s" WHERE datetime > '%s'""" % (
            tableName, lastTime)

        success = self.dbUtil.executeSQL(cursor, sql)
        if success:
            rows = cursor.fetchall()

            if not rows[0][0]:
                return 0
            else:
                return rows[0][0]
        else:
            # @todo Raise an exception.
            return None


    def lastReportDate(self, notificationType):
        """
        Get the last time a notification was reported.

        :param notificationType: A string indicating the type of the
        notification. It is stored in the event history.
        :returns: datetime of last report date.
        """

        cursor = self.connector.conn.cursor()
        sql = """SELECT MAX("notificationTime") FROM "%s" WHERE
        "notificationType" = '%s'""" % (
            NOTIFICATION_HISTORY_TABLE, notificationType)

        success = self.dbUtil.executeSQL(cursor, sql)
        if success:
            rows = cursor.fetchall()

            if not rows[0][0]:
                return None
            else:
                return rows[0][0]
        else:
            # @todo Raise an exception.
            return None


    def saveNotificationTime(self):
        """
        Save the notification event to the notification history.
        """

        cursor = self.connector.conn.cursor()
        sql = """INSERT INTO "%s" ("notificationType", "notificationTime")
        VALUES ('MSG_EGAUGE_SERVICE', NOW())""" % NOTIFICATION_HISTORY_TABLE
        success = self.dbUtil.executeSQL(cursor, sql)
        self.connector.conn.commit()
        if not success:
            # @todo Raise an exception.
            self.logger.log(
                'An error occurred while saving the notification time.')


    def sendNewDataNotification(self, testing = False):
        """
        Sending notification reporting on new data being available since the
        last time new data was reported.

        :param testing: Use testing mode when True.
        """

        lastReportDate = self.lastReportDate('MSG_EGAUGE_SERVICE')

#.........这里部分代码省略.........

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
class MSGDataVerifier(object):
    """
    Perform verification procedures related to data integrity.
    """

    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'DEBUG')
        self.cursor = MSGDBConnector().connectDB().cursor()
        self.dbUtil = MSGDBUtil()

    def mecoReadingsDupeCount(self):
        """
        Generate counts of MECO dupe readings.
        """

        dupes = 0
        startDate = lambda y, m: '%d-%02d-%02d' % (y, m, 1)
        endDate = lambda y, m: '%d-%02d-%02d' % (
            y, m, calendar.monthrange(y, m)[1])

        for y in YEARS:
            startDates = [startDate(y, m) for m in
                          map(lambda x: x + 1, range(12))]
            endDates = [endDate(y, m) for m in map(lambda x: x + 1, range(12))]

            for start in startDates:
                cnt = self.__mecoReadingsDupeCount(start, endDates[
                    startDates.index(start)])
                self.logger.log('start: %s, dupe cnt: %s' % (start, cnt),
                                'INFO')
                dupes += cnt

        return dupes


    def __mecoReadingsDupeCount(self, startDate, endDate):
        """

        :param startDate:
        :param endDate:
        :returns: DB row count.
        """

        self.dbUtil.executeSQL(self.cursor, """SELECT "Interval".end_time,
                            "MeterData".meter_name,
                            "Reading".channel
                     FROM "MeterData"
                     INNER JOIN "IntervalReadData" ON "MeterData"
                     .meter_data_id = "IntervalReadData".meter_data_id
                     INNER JOIN "Interval" ON "IntervalReadData"
                     .interval_read_data_id = "Interval".interval_read_data_id
                     INNER JOIN "Reading" ON "Interval".interval_id = "Reading"
                     .interval_id
                     WHERE "Interval".end_time BETWEEN '%s' and '%s'
                     GROUP BY "MeterData".meter_name,
                     "Interval".end_time,
                     "Reading".channel
                     HAVING (COUNT(*) > 1)""" % (startDate, endDate))
        return len(self.cursor.fetchall())


    def egaugeAggregationCount(self):
        """
        There should not be more than 96 15-min interval endpoints within a
        single calendar day for a given sub ID.
        :return:
        """
        pass

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
class MSGWeatherDataUtil(object):
    """
    Utility methods for working with weather data.
    """

    def __init__(self):
        """
        Constructor.

        A database connection is not maintained here to keep this class
        lightweight. This results in the class not having a parameter for
        TESTING MODE.
        """

        self.logger = MSGLogger(__name__, 'info')
        self.configer = MSGConfiger()
        self.url = self.configer.configOptionValue('Weather Data',
                                                   'weather_data_url')
        self.pattern = self.configer.configOptionValue('Weather Data',
                                                       'weather_data_pattern')
        self.fileList = []
        self.dateList = [] # List of dates corresponding weather data files.
        self.fillFileListAndDateList()
        self.dbUtil = MSGDBUtil()


    def fillFileListAndDateList(self):
        """
        Return a list of weather files obtained from the remote server used
        in processing weather data.
        """

        response = urllib2.urlopen(self.url).read()

        for filename in re.findall(self.pattern, response):
            self.fileList.append(filename[0])
            self.dateList.append(self.datePart(filename = filename[0]))


    def datePart(self, filename = None, datetime = None):
        """
        Return the date part of a NOAA weather data filename.

        :param: The filename.
        :param: A datetime object.
        :returns: The date part of the given parameter.
        """

        assert filename == None or datetime == None, "One argument is allowed."
        if filename:
            newName = filename.replace("QCLCD", '')
            newName = newName.replace(".zip", '')
            return newName
        if datetime:
            return datetime.strftime('%Y-%m-%d')

    def getLastDateLoaded(self, cursor):
        """
        Return the last date of loaded weather data.

        :returns: Last date.
        """

        sql = """select wban, datetime, record_type from "%s"
                 ORDER BY datetime desc limit 1""" % WEATHER_DATA_TABLE

        self.dbUtil.executeSQL(cursor, sql)
        row = cursor.fetchone()
        # self.logger.log('Date last loaded = %s' % row[1], 'info')
        return row[1]


    def getKeepList(self, fileList, cursor):
        """
        The Keep List is the list of filenames of files containing data that are
        *within* the month of the last loaded date or are beyond the last loaded
        date.

        :param: fileList: A list of files containing weather data.
        :param: DB cursor.
        :returns: List of weather data filenames to process.
        """

        keepList = []
        i = 0
        for date in fileList:
            self.logger.log('Examining date %s.' % date)

            # The list date should be the last day of the month.
            # It is the date that is compared against the last retrieved date.

            listDate = dt.datetime.strptime(self.datePart(filename = date),
                                            "%Y%m")
            lastDay = calendar.monthrange(listDate.year, listDate.month)[1]
            listDate = dt.datetime.strptime(
                '%s-%s-%s' % (listDate.year, listDate.month, lastDay),
                "%Y-%m-%d")
            self.logger.log('List date = %s.' % listDate)
            lastDate = self.getLastDateLoaded(cursor)

#.........这里部分代码省略.........

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
class MECODupeChecker(object):
    """
    Check for duplicate data in the database.
    """

    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'debug')
        self.mecoConfig = MSGConfiger()
        self.currentReadingID = 0
        self.dbUtil = MSGDBUtil()


    def getLastElement(self, rows):
        """
        Get the last element in a collection.

        Example:
            rows = (element1, element2, element3)
            getLastElement(rows) # return element3

        :param rows Result froms from a query
        :return last element in the collection
        """

        for i, var in enumerate(rows):
            if i == len(rows) - 1:
                return var

    def eventBranchDupeExists(self, conn, meterName, eventTime):
        """

        :param conn: Database connection.
        :param meterName: Meter name in MeterData table.
        :param eventTime: Timestamp of event.
        :return: True if tuple exists, False if not.
        """

        dbCursor = conn.cursor()

        sql = """SELECT "Event".event_time,
                        "MeterData".meter_data_id,
                        "EventData".event_data_id
                 FROM ( ( "MeterData" JOIN "EventData" ON (
                        ( "MeterData".meter_data_id = "EventData"
                        .meter_data_id ) ) )
                 JOIN "Event" ON ( ( "EventData".event_data_id = "Event"
                 .event_data_id ) ) )
                 WHERE "MeterData".meter_name = '%s'
                 AND "Event".event_time = '%s' """ % (meterName, eventTime)

        self.dbUtil.executeSQL(dbCursor, sql)
        rows = dbCursor.fetchall()

        if len(rows) > 0:
            return True
        else:
            return False


    def registerBranchDupeExists(self, conn, meterName, readTime,
                                 registerNumber, DEBUG = False):
        """
        Determine if a register branch duplicate exists for a given meter
        name, read time, number tuple.

        :param conn: Database connection.
        :param meterName: Meter name in MeterData table.
        :param readTime: Read time in RegisterRead table.
        :param registerNumber: Corresponds to DB column "number".
        :return: True if tuple exists, False if not.
        """

        dbCursor = conn.cursor()

        sql = """SELECT "public"."MeterData".meter_name,
                        "public"."RegisterRead".read_time,
                        "public"."Register"."number"
                 FROM "public"."MeterData"
                 INNER JOIN "public"."RegisterData" ON
                      "public" ."MeterData".meter_data_id = "public"
                      ."RegisterData".meter_data_id
                 INNER JOIN "public"."RegisterRead" ON
                      "public"."RegisterData" .register_data_id = "public"
                      ."RegisterRead".register_data_id
                 INNER JOIN "public"."Tier" ON "public"."RegisterRead"
                 .register_read_id = "public"."Tier" .register_read_id
                 INNER JOIN "public"."Register" ON "public"."Tier".tier_id =
                 "public"."Register".tier_id
                 WHERE "public"."MeterData".meter_name = '%s'
                 AND "public"."RegisterRead".read_time = '%s'
                 AND "public"."Register".number = '%s'
                 """ % (meterName, readTime, registerNumber)

        self.dbUtil.executeSQL(dbCursor, sql)
        rows = dbCursor.fetchall()

#.........这里部分代码省略.........

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
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)

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]
            if lineCnt != 0: # Skip header.
                data = line[0:len(cols)] # Overshoot columns to get the last column.

                for i in range(0, len(cols)):
                    if len(data[i]) == 0:
                        data[i] = 'NULL'
                    else:
                        # Escape single quotes with double single quotes in
                        # PostgreSQL.
                        data[i] = data[i].replace("'", "\'\'")
                        data[i] = "'" + data[i] + "'"

                sql = """INSERT INTO "MeterLocationHistory" (%s) VALUES (%s)""" % (
                    ','.join(cols), ','.join(data))
                logger.log("SQL: %s" % sql, 'debug')
                success = dbUtil.executeSQL(cur, sql)
                if not success:
                    anyFailure = True

            lineCnt += 1

    conn.commit()

    msg = ("Processed %s lines.\n" % lineCnt)
    sys.stderr.write(msg)
    msgBody += msg

    if not anyFailure:
        msg = "Finished inserting Meter Location History records.\n"
        sys.stderr.write(msg)
        msgBody += msg

# 需要导入模块: from msg_db_util import MSGDBUtil [as 别名]
# 或者: from msg_db_util.MSGDBUtil import executeSQL [as 别名]

#.........这里部分代码省略.........
                self.logger.log("minute crossed at #1.", "debug")
                return True
            elif self.nextMinuteCrossing[subkey] == first and minute >= first and minute <= intervalSize:
                self.nextMinuteCrossing[subkey] = intervalSize
                self.logger.log("minute crossed at #2.", "debug")
                return True
            return False
        else:
            if (
                minute >= self.nextMinuteCrossingWithoutSubkeys
                and minute <= last
                and self.nextMinuteCrossingWithoutSubkeys != first
            ):
                self.nextMinuteCrossingWithoutSubkeys += intervalSize
                if self.nextMinuteCrossingWithoutSubkeys >= last:
                    self.nextMinuteCrossingWithoutSubkeys = first
                self.logger.log("minute crossed at #3.", "debug")
                return True
            elif self.nextMinuteCrossingWithoutSubkeys == first and minute >= first and minute <= intervalSize:
                self.nextMinuteCrossingWithoutSubkeys = intervalSize
                self.logger.log("minute crossed at #4.", "debug")
                return True
            return False

    def rows(self, sql):
        """
        Rows from a SQL fetch.

        :param sql: Command to be executed.
        :returns: DB result set.
        """

        self.logger.log("sql: {}".format(sql), "debug")
        self.dbUtil.executeSQL(self.cursor, sql)
        return self.cursor.fetchall()

    def rawData(self, dataType="", orderBy=None, timestampCol="", startDate="", endDate=""):
        """
        Raw data to be aggregated.

        :param dataType: string
        :param orderBy: list
        :param timestampCol: string
        :param startDate: string
        :param endDate: string
        :returns: DB rows.
        """

        # @todo Validate args.

        orderBy = filter(None, orderBy)

        return self.rows(
            """SELECT {} FROM "{}" WHERE {} BETWEEN '{}' AND
        '{}' ORDER BY {}""".format(
                self.columns[dataType], self.tables[dataType], timestampCol, startDate, endDate, ",".join(orderBy)
            )
        )

    def subkeys(self, dataType="", timestampCol="", subkeyCol="", startDate="", endDate=""):
        """
        The distinct subkeys for a given data type within a time range.

        Subkeys are fields such as egauge_id in eGauge data or sensor_id in
        irradiance data.