/
check.py
101 lines (84 loc) · 2.84 KB
/
check.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
"""
This is the script that prints the earliest and latest reading timestamp for
each meter in the meter csv file.
If a meter in the file does not exist in the ION database, 'None' will be
displayed.
"""
import os
import pyodbc
from LbnlIon7350Interface.utils import utils
from LbnlIon7350Interface.utils import defaults
from LbnlIon7350Interface.utils import Cursor
def get_query_str(asc):
"""
Return the SQL query string that will get the earliest or latest timestamp
that exists for a given meter name and QuantityID.
If asc is True, get earliest timestamp. Otherwise, get latest timestamp.
To use the query string, the user needs to specify two (2) parameters
when passing the string to cursor.execute():
name string
qid integer
Params:
asc boolean
"""
option = 'ASC'
if not asc:
option = 'DESC'
query = '''SELECT TOP 1 D.TimestampUTC
FROM ION_Data.dbo.DataLog2 D
INNER JOIN ION_Data.dbo.Source S
ON S.ID = D.SourceID
AND S.Name = ?
AND D.QuantityID = ?
ORDER BY D.TimestampUTC %s''' % (option)
return query
def get_timestamps(cursor, meters, query):
"""
For each meter in meters list, execute SQL query using existing cursor
object.
Returns a dictionary where keys are meter ION names and values are
earliest or latest timestamps, depending on query's contents.
Params:
cursor: Cursor object
meters: list of meter rows
query: string
"""
output = {}
for m in meters:
ion_name = utils.get_ion_name(m)
qid = utils.get_ion_qid(m)
try:
cursor.execute(query, ion_name, qid)
except pyodbc.Error:
pass
if cursor.rowcount == 0:
output[ion_name] = None
continue
row = cursor.fetchone()
ts = row.TimestampUTC.split(".")[0]
output[ion_name] = ts
return output
def run(root):
creds_file = defaults.creds(root)
cnxn_str = utils.get_cnxn_str(creds_file)
meter_file = defaults.meter_file(root)
earliest = {}
latest = {}
print("Running check.py...")
with Cursor.Cursor(cnxn_str) as cursor:
earliest_query = get_query_str(True)
latest_query = get_query_str(False)
meter_generator = utils.read_meter_file(meter_file)
meters = [m for m in meter_generator]
earliest = get_timestamps(cursor, meters, earliest_query)
latest = get_timestamps(cursor, meters, latest_query)
for i, m in enumerate(meters):
ion_name = utils.get_ion_name(m)
earliest_ts = earliest[ion_name]
latest_ts = latest[ion_name]
print(" %d)\t%s\tStart: %s\tEnd: %s" % (i, ion_name, earliest_ts, latest_ts))
def main():
root = os.path.dirname(os.path.realpath(__file__))
run(root)
if __name__ == '__main__':
main()