def copy_data(data_file, table, mycursor):
"""
Copy the data contents of DATA_FILE to temporary table TABLE.
Returns TRUE if successful, FALSE otherwise. Uses cursor MYCURSOR.
"""
sql = """
COPY %s
(
time_stamp_utc,
reading
) FROM '%s'
WITH DELIMITER ','
NULL AS 'Null'
CSV HEADER
""" % (table, data_file)
print("Copying data to temporary table '%s' ..." % (table)),
try:
exec_and_commit(mycursor, sql)
util.done()
return True
except pyodbc.Error, copy_err:
util.fail()
print(copy_err)
return False
def load_ids(id_list, mycursor):
"""
Insert ID list ID_LIST into the 'meter' table and return the meter ID
created by the insertion. Returns -1 in case of failure.
The order of ID_LIST is as follows:
[ description, unit_id, commodity_id, source_system_id, reading_type_id ]
Uses cursor MYCURSOR.
"""
sql = """
INSERT INTO meter
(
description,
unit_id,
commodity_id,
source_system_id,
reading_type_id
)
VALUES ('%s', %d, %d, %d, %d)
RETURNING id
""" % (id_list[0], id_list[1], id_list[2], id_list[3], id_list[4])
print("Inserting ID's into 'meter' table ..."),
try:
exec_and_commit(mycursor, sql)
result = mycursor.fetchone()
util.done()
return result.id
except pyodbc.Error, get_meter_id_err:
util.fail()
print(get_meter_id_err)
return -1
def copy_data(data_file, table, mycursor):
"""
Copy the data contents of DATA_FILE to temporary table TABLE.
Returns TRUE if successful, FALSE otherwise. Uses cursor MYCURSOR.
"""
sql = """
COPY %s
(
time_stamp_utc,
reading
) FROM '%s'
WITH DELIMITER ','
NULL AS 'Null'
CSV HEADER
""" % (table, data_file)
print("Copying data to temporary table '%s' ..." % (table)),
try:
exec_and_commit(mycursor, sql)
util.done()
return True
except pyodbc.Error, copy_err:
util.fail()
print(copy_err)
return False
def load_ids(id_list, mycursor):
"""
Insert ID list ID_LIST into the 'meter' table and return the meter ID
created by the insertion. Returns -1 in case of failure.
The order of ID_LIST is as follows:
[ description, unit_id, commodity_id, source_system_id, reading_type_id ]
Uses cursor MYCURSOR.
"""
sql = """
INSERT INTO meter
(
description,
unit_id,
commodity_id,
source_system_id,
reading_type_id
)
VALUES ('%s', %d, %d, %d, %d)
RETURNING id
""" % (id_list[0], id_list[1], id_list[2], id_list[3], id_list[4])
print("Inserting ID's into 'meter' table ..."),
try:
exec_and_commit(mycursor, sql)
result = mycursor.fetchone()
util.done()
return result.id
except pyodbc.Error, get_meter_id_err:
util.fail()
print(get_meter_id_err)
return -1
def drop_tmp_table(my_cursor, table_name):
"""
Drop temporary table TABLE_NAME using cursor MY_CURSOR.
"""
print("Deleting temporary table '%s' ..." % (table_name)),
dele_sql = "DROP TABLE IF EXISTS %s" % (table_name)
exec_and_commit(my_cursor, dele_sql)
util.done()
def drop_tmp_table(my_cursor, table_name):
"""
Drop temporary table TABLE_NAME using cursor MY_CURSOR.
"""
print("Deleting temporary table '%s' ..." % (table_name)),
dele_sql = "DROP TABLE IF EXISTS %s" % (table_name)
exec_and_commit(my_cursor, dele_sql)
util.done()
def add_id_col(m_id, table, mycursor):
"""
Add a 'id' column with default value M_ID to table TABLE using cursor
MYCURSOR. Returns TRUE if successful, FALSE otherwise.
"""
sql = "ALTER TABLE %s ADD COLUMN id INTEGER DEFAULT %d" % (table, m_id)
print("Adding column to table '%s' with id value %d ..." % (table, m_id)),
try:
exec_and_commit(mycursor, sql)
util.done()
return True
except pyodbc.Error, add_col_err:
util.fail()
print(add_col_err)
return False
def extract_meter_data(extract_file):
"""
Extract meter reading data from the database as .csv files. The meters are
from the EXTRACT_FILE.
Also collects and writes meter metadata to a meter information file that
will be used to import the data into the central database.
"""
try:
print("Using database '%s':" % (DB.upper()))
print("Connecting to database ..."),
cnxn_str = "DSN=%s;UID=%s;PWD=%s" % (DB, USER, PASSWD)
cnxn = pyodbc.connect(cnxn_str)
util.done()
except pyodbc.Error, connect_err:
util.fail()
print(connect_err)
exit()
def extract_meter_data(extract_file):
"""
Extract meter reading data from the database as .csv files. The meters are
from the EXTRACT_FILE.
Also collects and writes meter metadata to a meter information file that
will be used to import the data into the central database.
"""
try:
print("Using database '%s':" % (DB.upper()))
print("Connecting to database ..."),
cnxn_str = "DSN=%s;UID=%s;PWD=%s" % (DB, USER, PASSWD)
cnxn = pyodbc.connect(cnxn_str)
util.done()
except pyodbc.Error, connect_err:
util.fail()
print(connect_err)
exit()
def get_id(item, table, field, mycursor):
"""
Returns the ID from table TABLE whose FIELD is "ilike" to ITEM. Uses
cursor MYCURSOR. If no ID is found, returns -1.
"""
sql = "SELECT id FROM %s WHERE %s ILIKE '%s' LIMIT 1" % (table, field, item)
print("Getting %s ID ..." % (table)),
mycursor.execute(sql)
result = mycursor.fetchone()
if not result:
util.fail()
return -1
else:
util.done()
return result.id
def add_id_col(m_id, table, mycursor):
"""
Add a 'id' column with default value M_ID to table TABLE using cursor
MYCURSOR. Returns TRUE if successful, FALSE otherwise.
"""
sql = "ALTER TABLE %s ADD COLUMN id INTEGER DEFAULT %d" % (table, m_id)
print("Adding column to table '%s' with id value %d ..." % (table, m_id)),
try:
exec_and_commit(mycursor, sql)
util.done()
return True
except pyodbc.Error, add_col_err:
util.fail()
print(add_col_err)
return False
def get_id(item, table, field, mycursor):
"""
Returns the ID from table TABLE whose FIELD is "ilike" to ITEM. Uses
cursor MYCURSOR. If no ID is found, returns -1.
"""
sql = "SELECT id FROM %s WHERE %s ILIKE '%s' LIMIT 1" % (table, field,
item)
print("Getting %s ID ..." % (table)),
mycursor.execute(sql)
result = mycursor.fetchone()
if not result:
util.fail()
return -1
else:
util.done()
return result.id
def insert_table(table, mycursor):
"""
Insert the contents of table TABLE into 'meter_value' using cursor MYCURSOR.
Returns TRUE if successful, FALSE otherwise.
"""
sql = """
INSERT INTO meter_value (meter_id, time_stamp_utc, reading)
SELECT id, time_stamp_utc, reading FROM %s
""" % (table)
print("Inserting readings into 'meter_value' table ..."),
try:
exec_and_commit(mycursor, sql)
util.done()
return True
except pyodbc.Error, insert_table_err:
util.fail()
print(insert_table_err)
return False
def insert_table(table, mycursor):
"""
Insert the contents of table TABLE into 'meter_value' using cursor MYCURSOR.
Returns TRUE if successful, FALSE otherwise.
"""
sql = """
INSERT INTO meter_value (meter_id, time_stamp_utc, reading)
SELECT id, time_stamp_utc, reading FROM %s
""" % (table)
print("Inserting readings into 'meter_value' table ..."),
try:
exec_and_commit(mycursor, sql)
util.done()
return True
except pyodbc.Error, insert_table_err:
util.fail()
print(insert_table_err)
return False
def get_readings(meter_id, start_date, end_date, my_cursor):
"""
Run the SQL to get readings for meter METER_ID between START_DATE and
END_DATE. Return TRUE if successful, FALSE otherwise. The readings are
stored in MY_CURSOR for later iteration.
"""
get_data_sql = """
SELECT UTCDateTime, ActualValue
FROM tblActualValueFloat
WHERE UTCDateTime >= CAST('%s' AS datetime)
AND UTCDateTime < CAST('%s' AS datetime)
AND PointSliceID = %d
ORDER BY UTCDateTime ASC
""" % (start_date, end_date, meter_id)
print("Getting meter readings ..."),
try:
my_cursor.execute(get_data_sql)
util.done()
return True
except pyodbc.Error, get_data_err:
util.fail()
print(get_data_err)
return False
def get_readings(meter_id, start_date, end_date, my_cursor):
"""
Run the SQL to get readings for meter METER_ID between START_DATE and
END_DATE. Return TRUE if successful, FALSE otherwise. The readings are
stored in MY_CURSOR for later iteration.
"""
get_data_sql = """
SELECT UTCDateTime, ActualValue
FROM tblActualValueFloat
WHERE UTCDateTime >= CAST('%s' AS datetime)
AND UTCDateTime < CAST('%s' AS datetime)
AND PointSliceID = %d
ORDER BY UTCDateTime ASC
""" % (start_date, end_date, meter_id)
print("Getting meter readings ..."),
try:
my_cursor.execute(get_data_sql)
util.done()
return True
except pyodbc.Error, get_data_err:
util.fail()
print(get_data_err)
return False
def get_commodity(orig_unit, desc):
"""
Return the commodity type (Water, Gas, Electricity) based on ORIG_UNIT and
description DESC. Return None if commodity cannot be found.
"""
print("Getting commodity ..."),
unit = orig_unit.lower()
output = ""
if (unit == "kw" or unit == "kwh"):
output = "Electricity"
elif (unit == "cu ft" or unit == "btu"):
output = "Gas"
elif (unit == "gal"):
output = "Water"
elif (unit == "unknown"):
if ("btu" in desc.lower()):
output = "Gas"
if (not output):
util.fail()
print("ERROR: No commodity found for unit '%s'" % (orig_unit))
return None
else:
util.done()
return output
def get_commodity(orig_unit, desc):
"""
Return the commodity type (Water, Gas, Electricity) based on ORIG_UNIT and
description DESC. Return None if commodity cannot be found.
"""
print("Getting commodity ..."),
unit = orig_unit.lower()
output = ""
if (unit == "kw" or unit == "kwh"):
output = "Electricity"
elif (unit == "cu ft" or unit == "btu"):
output = "Gas"
elif (unit == "gal"):
output = "Water"
elif (unit == "unknown"):
if ("btu" in desc.lower()):
output = "Gas"
if (not output):
util.fail()
print("ERROR: No commodity found for unit '%s'" % (orig_unit))
return None
else:
util.done()
return output
def isMonotonic(my_cursor):
"""
Given a list of values stored in cursor MY_CURSOR, return TRUE if values
are totalized (i.e. monotonic), FALSE if values are in interval form.
"""
first = my_cursor.fetchone()
if not first:
util.fail()
raise ValueError("ERROR: No meter values found in input date range")
else:
count = 0
prev = first.ActualValue
for v in my_cursor:
curr = v.ActualValue
diff = curr - prev
if (diff < 0):
util.done()
return False
elif (count >= 100):
break
count += 1
prev = curr
util.done()
return True
def create_temp_table(table_name, mycursor):
"""
Create temporary table with name TABLE_NAME to hold timestamp, reading data
in the data file currently being processed.
Returns TRUE if successful, FALSE otherwise. Uses cursor MYCURSOR.
"""
sql = """
CREATE TABLE IF NOT EXISTS %s
(
time_stamp_utc TIMESTAMP,
reading NUMERIC
)
""" % (table_name)
print("Creating temporary table '%s' ..." % (table_name)),
try:
exec_and_commit(mycursor, sql)
util.done()
return True
except pyodbc.Error, create_tbl_err:
util.fail()
print(create_tbl_err)
return False
def main():
arg_len = len(sys.argv)
if (arg_len > 3 or arg_len == 2):
usage()
exit()
elif (arg_len == 3):
data_dir = sys.argv[1]
processed_dir = sys.argv[2]
elif (arg_len == 1):
data_dir = DEFAULT_DATA_DIR
processed_dir = DEFAULT_PROCESSED_DIR
if (not os.path.isdir(data_dir) or not os.path.isdir(processed_dir)):
print("ERROR: directory '%s' does not exist!" % (data_dir))
exit()
data_files = get_data_files(data_dir)
try:
cnxn_str = "DSN=%s;UID=%s;PWD=%s" % (DB, USER, PWD)
print("Connecting to database ..."),
cnxn = pyodbc.connect(cnxn_str)
util.done()
except pyodbc.Error, conn_err:
util.fail()
print(conn_err)
exit()
def create_temp_table(table_name, mycursor):
"""
Create temporary table with name TABLE_NAME to hold timestamp, reading data
in the data file currently being processed.
Returns TRUE if successful, FALSE otherwise. Uses cursor MYCURSOR.
"""
sql = """
CREATE TABLE IF NOT EXISTS %s
(
time_stamp_utc TIMESTAMP,
reading NUMERIC
)
""" % (table_name)
print("Creating temporary table '%s' ..." % (table_name)),
try:
exec_and_commit(mycursor, sql)
util.done()
return True
except pyodbc.Error, create_tbl_err:
util.fail()
print(create_tbl_err)
return False
def main():
arg_len = len(sys.argv)
if (arg_len > 3 or arg_len == 2):
usage()
exit()
elif (arg_len == 3):
data_dir = sys.argv[1]
processed_dir = sys.argv[2]
elif (arg_len == 1):
data_dir = DEFAULT_DATA_DIR
processed_dir = DEFAULT_PROCESSED_DIR
if (not os.path.isdir(data_dir) or not os.path.isdir(processed_dir)):
print("ERROR: directory '%s' does not exist!" % (data_dir))
exit()
data_files = get_data_files(data_dir)
try:
cnxn_str = "DSN=%s;UID=%s;PWD=%s" % (DB, USER, PWD)
print("Connecting to database ..."),
cnxn = pyodbc.connect(cnxn_str)
util.done()
except pyodbc.Error, conn_err:
util.fail()
print(conn_err)
exit()
def isMonotonic(my_cursor):
"""
Given a list of values stored in cursor MY_CURSOR, return TRUE if values
are totalized (i.e. monotonic), FALSE if values are in interval form.
"""
first = my_cursor.fetchone()
if not first:
util.fail()
raise ValueError("ERROR: No meter values found in input date range")
else:
count = 0
prev = first.ActualValue
for v in my_cursor:
curr = v.ActualValue
diff = curr - prev
if (diff < 0):
util.done()
return False
elif (count >= 100):
break
count += 1
prev = curr
util.done()
return True
def get_readings(meter_id, quantity_id, start_date, end_date):
"""
Run the SQL to get readings for meter METER_ID with QUANTITY_ID between
START_DATE and END_DATE. Return TRUE if successful, FALSE otherwise.
The readings are stored in MY_CURSOR for later iteration.
"""
get_data_sql = """
SELECT TimestampUTC, Value
FROM DataLog2
WHERE TimestampUTC >= CAST('%s' AS datetime2)
AND TimestampUTC < CAST('%s' AS datetime2)
AND SourceID = %d
AND QuantityID = %d
ORDER BY TimestampUTC ASC
""" % (start_date, end_date, meter_id, quantity_id)
print("Getting meter readings ..."),
try:
my_cursor.execute(get_data_sql)
util.done()
return True
except pyodbc.Error, get_data_err:
util.fail()
print(get_data_err)
return False
print("Getting meter name ..."),
try:
my_cursor.execute(get_description_sql)
except pyodbc.Error, meter_name_err:
util.fail()
print(meter_name_err)
return None
description_result = my_cursor.fetchone()
if (not description_result):
util.fail()
print("ERROR! Description not found!")
return None
else:
util.done()
return description_result.PointName
def get_reading_type(meter_id, my_cursor, start_date, end_date):
"""
Returns the reading type of meter METER_ID (Totalization, Interval), using
cursor MY_CURSOR. If reading type cannot be determined, return NONE.
Readings are limited to those between START_DATE and END_DATE
"""
check_sql = """
SELECT TOP 100 ActualValue FROM tblActualValueFloat
WHERE PointSliceID = %d
AND UTCDateTime >= CAST('%s' AS datetime)
AND UTCDateTime < CAST('%s' AS datetime)
ORDER BY UTCDateTime ASC
SELECT TOP 1 Name FROM Source WHERE ID = %d
""" % (meter_id)
print("Getting meter name ..."),
try:
my_cursor.execute(get_description_sql)
except pyodbc.Error, meter_name_err:
util.fail()
print(meter_name_err)
return None
description_result = my_cursor.fetchone()
if (not description_result):
util.fail()
print("[ERROR] Description not found!")
return None
else:
util.done()
return description_result.Name
def get_quantity_name(quantity_id, my_cursor):
"""
Returns the string containing the quantity name for QUANTITY_ID, or None if
the name cannot be found. Uses cursor MY_CURSOR.
"""
get_quantity_name_sql = """
SELECT TOP 1 Name FROM Quantity WHERE ID = %d
""" % (quantity_id)
try:
my_cursor.execute(get_quantity_name_sql)
except pyodbc.Error, get_qty_name_err:
print(get_qty_name_err)
return None