Issues in offlinemeter.py (master) - Issues in master - myems/myems-normalization - Measure and Improve Code Quality continuously with Scrutinizer

Issues (8)

offlinemeter.py (2 issues)

Severity

Unknown 2

import time
from datetime import datetime, timedelta
import mysql.connector
from openpyxl import load_workbook
import config

################################################################################################################
# PROCEDURES:
# STEP 1: get all 'new' offline meter files
# STEP 2: for each new files, iterate all rows and read cell's value and store data to energy data list
# STEP 3: insert or update energy data to table offline meter hourly in energy database
# STEP 4: update file status to 'done' or 'error'
################################################################################################################


def calculate_hourly(logger):
    while True:
        # outer loop to reconnect server if there is a connection error
        ################################################################################################################
        # STEP 1: get all 'new' offline meter files
        ################################################################################################################
        cnx = None
        cursor = None
        try:
            cnx = mysql.connector.connect(**config.myems_historical_db)
            cursor = cnx.cursor()
        except Exception as e:
            logger.error("Error in step 1.1 of offline meter.calculate_hourly " + str(e))
            if cursor:
                cursor.close()
            if cnx:
                cnx.close()
            # sleep several minutes and continue the outer loop to reconnect the database
            print("Could not connect the MyEMS Historical Database, and go to sleep 60 seconds...")
            time.sleep(60)
            continue

        print("Connected to MyEMS Historical Database")

        print("Getting all new offline meter files")
        try:
            query = (" SELECT id, file_name, file_object "
                     " FROM tbl_offline_meter_files "
                     " WHERE status = 'new' "
                     " ORDER BY id ")

            cursor.execute(query, )
            rows_files = cursor.fetchall()
        except Exception as e:
            logger.error("Error in step 1.2 of offline meter.calculate_hourly " + str(e))
            time.sleep(60)
            continue
        finally:
            if cursor:
                cursor.close()
            if cnx:
                cnx.close()

        excel_file_list = list()
        if rows_files is not None and len(rows_files) > 0:

            for row_file in rows_files:
                excel_file_list.append({"id": row_file[0],
                                        "name": row_file[1],
                                        "file_object": row_file[2]})
        else:
            print("there isn't any new files found, and go to sleep 60 seconds...")
            time.sleep(60)
            continue

        ################################################################################################################
        # STEP 2: for each new files, dump file object to local file and then load workbook from the local file
        ################################################################################################################
        for excel_file in excel_file_list:
            print("read data from each offline meter file" + excel_file['name'])
            is_valid_file = True
            fw = None
            try:
                fw = open("myems-normalization.blob", 'wb')
                fw.write(excel_file['file_object'])
                fw.close()
            except Exception as e:
                logger.error("Error in step 2.1 of offline meter.calculate_hourly " + str(e))
                if fw:
                    fw.close()
                # mark as invalid file
                is_valid_file = False

            fr = None
            wb = None
            try:
                fr = open("myems-normalization.blob", 'rb')
                wb = load_workbook(fr, data_only=True)
                fr.close()
            except Exception as e:
                logger.error("Error in step 2.2 of offline meter.calculate_hourly " + str(e))
                if fr:
                    fr.close()
                # mark as invalid file
                is_valid_file = False

            energy_data_list = list()
            # grab the active worksheet

            if is_valid_file:
                ws = wb.active

                # get timezone offset in minutes, this value will be returned to client
                timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
                if config.utc_offset[0] == '-':
                    timezone_offset = -timezone_offset

                for row in ws.iter_rows(min_row=3, max_row=1024, min_col=1, max_col=34):
                    offline_meter_data = dict()
                    offline_meter_data['offline_meter_id'] = None
                    offline_meter_data['offline_meter_name'] = None
                    offline_meter_data['data'] = dict()
                    col_num = 0

                    for cell in row:
                        col_num += 1
                        print(cell.value)
                        if col_num == 1:
                            # get offline meter ID
                            if cell.value is not None:
                                offline_meter_data['offline_meter_id'] = cell.value
                            else:
                                break
                        elif col_num == 2:
                            # get offline meter name
                            if cell.value is None:
                                break
                            else:
                                offline_meter_data['offline_meter_name'] = cell.value
                        elif col_num > 3:
                            # get date of the cell
                            try:
                                offline_datetime = datetime(year=ws['A2'].value,
                                                            month=ws['B2'].value,
                                                            day=col_num - 3)
                            except ValueError:
                                # invalid date and go to next cell in this row until reach max_col
                                continue

                            offline_datetime_utc = offline_datetime - timedelta(minutes=timezone_offset)

                            if cell.value is None:
                                # if the cell is empty then stop at that day
                                break
                            else:
                                offline_meter_data['data'][offline_datetime_utc] = cell.value

                    if len(offline_meter_data['data']) > 0:
                        print("offline_meter_data:" + str(offline_meter_data))
                        energy_data_list.append(offline_meter_data)

            ############################################################################################################
            # STEP 3: insert or update energy data to table offline meter hourly in energy database
            ############################################################################################################
            print("to valid offline meter id in excel file...")
            if len(energy_data_list) == 0:
                print("Could not find any offline meters in the file...")
                print("and go to process the next file...")
                is_valid_file = False
            else:
                try:
                    cnx = mysql.connector.connect(**config.myems_system_db)
                    cursor = cnx.cursor()
                except Exception as e:
                    logger.error("Error in step 3.1 of offlinemeter.calculate_hourly " + str(e))
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()
                    time.sleep(60)
                    continue

                try:
                    cursor.execute(" SELECT id, name "
                                   " FROM tbl_offline_meters ")
                    rows_offline_meters = cursor.fetchall()
                except Exception as e:
                    logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
                    time.sleep(60)
                    continue
                finally:
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()

                if rows_offline_meters is None or len(rows_offline_meters) == 0:

                    print("Could not find any offline meters in the MyEMS System Database...")
                    time.sleep(60)
                    continue
                else:
                    offline_meter_id_set = set()
                    for row_offline_meter in rows_offline_meters:
                        # valid offline meter id in excel file
                        offline_meter_id_set.add(row_offline_meter[0])

                    for energy_data_item in energy_data_list:
                        if energy_data_item['offline_meter_id'] not in offline_meter_id_set:
                            is_valid_file = False
                            break

                if is_valid_file:
                    ####################################################################################################
                    # delete possibly exists offline meter hourly data in myems energy database,
                    # and then insert new offline meter hourly data
                    ####################################################################################################
                    try:
                        cnx = mysql.connector.connect(**config.myems_energy_db)
                        cursor = cnx.cursor()
                    except Exception as e:
                        logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
                        if cursor:
                            cursor.close()
                        if cnx:
                            cnx.close()
                        time.sleep(60)
                        continue

                    try:
                        for energy_data_item in energy_data_list:
                            offline_meter_id = energy_data_item['offline_meter_id']
                            print(energy_data_item['data'].items())
                            for k, v in energy_data_item['data'].items():
                                start_datetime_utc = k
                                end_datetime_utc = start_datetime_utc + timedelta(hours=24)
                                actual_value = v / (24 * 60 / config.minutes_to_count)
                                cursor.execute(" DELETE FROM tbl_offline_meter_hourly "
                                               " WHERE offline_meter_id = %s "
                                               "       AND start_datetime_utc >= %s "
                                               "       AND start_datetime_utc < %s ",
                                               (offline_meter_id,
                                                start_datetime_utc.isoformat()[0:19],
                                                end_datetime_utc.isoformat()[0:19]))
                                cnx.commit()
                                # todo: check with hourly low limit and hourly high limit
                                add_values = (" INSERT INTO tbl_offline_meter_hourly "
                                              "             (offline_meter_id, start_datetime_utc, actual_value) "
                                              " VALUES  ")

                                while start_datetime_utc < end_datetime_utc:
                                    add_values += " (" + str(offline_meter_id) + ","
                                    add_values += "'" + start_datetime_utc.isoformat()[0:19] + "',"
                                    add_values += str(actual_value) + "), "
                                    start_datetime_utc += timedelta(minutes=config.minutes_to_count)

                                print("add_values:" + add_values)
                                # trim ", " at the end of string and then execute
                                cursor.execute(add_values[:-2])
                                cnx.commit()
                    except Exception as e:
                        logger.error("Error in step 3.3 of offlinemeter.calculate_hourly " + str(e))
                        time.sleep(60)
                        continue
                    finally:
                        if cursor:
                            cursor.close()
                        if cnx:
                            cnx.close()

            ############################################################################################################
            # STEP 4: update file status to 'done' or 'error'
            ############################################################################################################
            print("to update offline meter file status to done...")
            try:
                cnx = mysql.connector.connect(**config.myems_historical_db)
                cursor = cnx.cursor()
            except Exception as e:
                logger.error("Error in step 4.1 of offlinemeter.calculate_hourly " + str(e))
                if cursor:
                    cursor.close()
                if cnx:
                    cnx.close()
                time.sleep(60)
                continue

            try:
                update_row = (" UPDATE tbl_offline_meter_files "
                              " SET status = %s "
                              " WHERE id = %s ")
                cursor.execute(update_row, ('done' if is_valid_file else 'error', excel_file['id'],))
                cnx.commit()
            except Exception as e:
                logger.error("Error in step 4.2 of offlinemeter.calculate_hourly " + str(e))
                time.sleep(60)
                continue
            finally:
                if cursor:
                    cursor.close()
                if cnx:
                    cnx.close()

        # end of for excel_file in excel_file_list

        print("go to sleep ...")
        time.sleep(300)
        print("wake from sleep, and go to work...")
    # end of outer while



1			import time
2			from datetime import datetime, timedelta
3			import mysql.connector
4			from openpyxl import load_workbook
5			import config
6
7			################################################################################################################
8			# PROCEDURES:
9			# STEP 1: get all 'new' offline meter files
10			# STEP 2: for each new files, iterate all rows and read cell's value and store data to energy data list
11			# STEP 3: insert or update energy data to table offline meter hourly in energy database
12			# STEP 4: update file status to 'done' or 'error'
13			################################################################################################################
14
15
16			def calculate_hourly(logger):
17			while True:
18			# outer loop to reconnect server if there is a connection error
19			################################################################################################################
20			# STEP 1: get all 'new' offline meter files
21			################################################################################################################
22			cnx = None
23			cursor = None
24			try:
25			cnx = mysql.connector.connect(**config.myems_historical_db)
26			cursor = cnx.cursor()
27			except Exception as e:
28			logger.error("Error in step 1.1 of offline meter.calculate_hourly " + str(e))
29			if cursor:
30			cursor.close()
31			if cnx:
32			cnx.close()
33			# sleep several minutes and continue the outer loop to reconnect the database
34			print("Could not connect the MyEMS Historical Database, and go to sleep 60 seconds...")
35			time.sleep(60)
36			continue
37
38			print("Connected to MyEMS Historical Database")
39
40			print("Getting all new offline meter files")
41			try:
42			query = (" SELECT id, file_name, file_object "
43			" FROM tbl_offline_meter_files "
44			" WHERE status = 'new' "
45			" ORDER BY id ")
46
47			cursor.execute(query, )
48			rows_files = cursor.fetchall()
49			except Exception as e:
50			logger.error("Error in step 1.2 of offline meter.calculate_hourly " + str(e))
51			time.sleep(60)
52			continue
53			finally:
54			if cursor:
55			cursor.close()
56			if cnx:
57			cnx.close()
58
59			excel_file_list = list()
60			if rows_files is not None and len(rows_files) > 0:
			0 ignored issues – show introduced 2020-11-25 02:42 UTC by Report Bug Copy Issue Report Show Similar Issues like this The variable `rows_files` does not seem to be defined for all execution paths. Loading history...
61			for row_file in rows_files:
62			excel_file_list.append({"id": row_file[0],
63			"name": row_file[1],
64			"file_object": row_file[2]})
65			else:
66			print("there isn't any new files found, and go to sleep 60 seconds...")
67			time.sleep(60)
68			continue
69
70			################################################################################################################
71			# STEP 2: for each new files, dump file object to local file and then load workbook from the local file
72			################################################################################################################
73			for excel_file in excel_file_list:
74			print("read data from each offline meter file" + excel_file['name'])
75			is_valid_file = True
76			fw = None
77			try:
78			fw = open("myems-normalization.blob", 'wb')
79			fw.write(excel_file['file_object'])
80			fw.close()
81			except Exception as e:
82			logger.error("Error in step 2.1 of offline meter.calculate_hourly " + str(e))
83			if fw:
84			fw.close()
85			# mark as invalid file
86			is_valid_file = False
87
88			fr = None
89			wb = None
90			try:
91			fr = open("myems-normalization.blob", 'rb')
92			wb = load_workbook(fr, data_only=True)
93			fr.close()
94			except Exception as e:
95			logger.error("Error in step 2.2 of offline meter.calculate_hourly " + str(e))
96			if fr:
97			fr.close()
98			# mark as invalid file
99			is_valid_file = False
100
101			energy_data_list = list()
102			# grab the active worksheet
103
104			if is_valid_file:
105			ws = wb.active
106
107			# get timezone offset in minutes, this value will be returned to client
108			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
109			if config.utc_offset[0] == '-':
110			timezone_offset = -timezone_offset
111
112			for row in ws.iter_rows(min_row=3, max_row=1024, min_col=1, max_col=34):
113			offline_meter_data = dict()
114			offline_meter_data['offline_meter_id'] = None
115			offline_meter_data['offline_meter_name'] = None
116			offline_meter_data['data'] = dict()
117			col_num = 0
118
119			for cell in row:
120			col_num += 1
121			print(cell.value)
122			if col_num == 1:
123			# get offline meter ID
124			if cell.value is not None:
125			offline_meter_data['offline_meter_id'] = cell.value
126			else:
127			break
128			elif col_num == 2:
129			# get offline meter name
130			if cell.value is None:
131			break
132			else:
133			offline_meter_data['offline_meter_name'] = cell.value
134			elif col_num > 3:
135			# get date of the cell
136			try:
137			offline_datetime = datetime(year=ws['A2'].value,
138			month=ws['B2'].value,
139			day=col_num - 3)
140			except ValueError:
141			# invalid date and go to next cell in this row until reach max_col
142			continue
143
144			offline_datetime_utc = offline_datetime - timedelta(minutes=timezone_offset)
145
146			if cell.value is None:
147			# if the cell is empty then stop at that day
148			break
149			else:
150			offline_meter_data['data'][offline_datetime_utc] = cell.value
151
152			if len(offline_meter_data['data']) > 0:
153			print("offline_meter_data:" + str(offline_meter_data))
154			energy_data_list.append(offline_meter_data)
155
156			############################################################################################################
157			# STEP 3: insert or update energy data to table offline meter hourly in energy database
158			############################################################################################################
159			print("to valid offline meter id in excel file...")
160			if len(energy_data_list) == 0:
161			print("Could not find any offline meters in the file...")
162			print("and go to process the next file...")
163			is_valid_file = False
164			else:
165			try:
166			cnx = mysql.connector.connect(**config.myems_system_db)
167			cursor = cnx.cursor()
168			except Exception as e:
169			logger.error("Error in step 3.1 of offlinemeter.calculate_hourly " + str(e))
170			if cursor:
171			cursor.close()
172			if cnx:
173			cnx.close()
174			time.sleep(60)
175			continue
176
177			try:
178			cursor.execute(" SELECT id, name "
179			" FROM tbl_offline_meters ")
180			rows_offline_meters = cursor.fetchall()
181			except Exception as e:
182			logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
183			time.sleep(60)
184			continue
185			finally:
186			if cursor:
187			cursor.close()
188			if cnx:
189			cnx.close()
190
191			if rows_offline_meters is None or len(rows_offline_meters) == 0:
			0 ignored issues – show introduced 2020-11-25 02:42 UTC by Report Bug Copy Issue Report Show Similar Issues like this The variable `rows_offline_meters` does not seem to be defined for all execution paths. Loading history...
192			print("Could not find any offline meters in the MyEMS System Database...")
193			time.sleep(60)
194			continue
195			else:
196			offline_meter_id_set = set()
197			for row_offline_meter in rows_offline_meters:
198			# valid offline meter id in excel file
199			offline_meter_id_set.add(row_offline_meter[0])
200
201			for energy_data_item in energy_data_list:
202			if energy_data_item['offline_meter_id'] not in offline_meter_id_set:
203			is_valid_file = False
204			break
205
206			if is_valid_file:
207			####################################################################################################
208			# delete possibly exists offline meter hourly data in myems energy database,
209			# and then insert new offline meter hourly data
210			####################################################################################################
211			try:
212			cnx = mysql.connector.connect(**config.myems_energy_db)
213			cursor = cnx.cursor()
214			except Exception as e:
215			logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
216			if cursor:
217			cursor.close()
218			if cnx:
219			cnx.close()
220			time.sleep(60)
221			continue
222
223			try:
224			for energy_data_item in energy_data_list:
225			offline_meter_id = energy_data_item['offline_meter_id']
226			print(energy_data_item['data'].items())
227			for k, v in energy_data_item['data'].items():
228			start_datetime_utc = k
229			end_datetime_utc = start_datetime_utc + timedelta(hours=24)
230			actual_value = v / (24 * 60 / config.minutes_to_count)
231			cursor.execute(" DELETE FROM tbl_offline_meter_hourly "
232			" WHERE offline_meter_id = %s "
233			" AND start_datetime_utc >= %s "
234			" AND start_datetime_utc < %s ",
235			(offline_meter_id,
236			start_datetime_utc.isoformat()[0:19],
237			end_datetime_utc.isoformat()[0:19]))
238			cnx.commit()
239			# todo: check with hourly low limit and hourly high limit
240			add_values = (" INSERT INTO tbl_offline_meter_hourly "
241			" (offline_meter_id, start_datetime_utc, actual_value) "
242			" VALUES ")
243
244			while start_datetime_utc < end_datetime_utc:
245			add_values += " (" + str(offline_meter_id) + ","
246			add_values += "'" + start_datetime_utc.isoformat()[0:19] + "',"
247			add_values += str(actual_value) + "), "
248			start_datetime_utc += timedelta(minutes=config.minutes_to_count)
249
250			print("add_values:" + add_values)
251			# trim ", " at the end of string and then execute
252			cursor.execute(add_values[:-2])
253			cnx.commit()
254			except Exception as e:
255			logger.error("Error in step 3.3 of offlinemeter.calculate_hourly " + str(e))
256			time.sleep(60)
257			continue
258			finally:
259			if cursor:
260			cursor.close()
261			if cnx:
262			cnx.close()
263
264			############################################################################################################
265			# STEP 4: update file status to 'done' or 'error'
266			############################################################################################################
267			print("to update offline meter file status to done...")
268			try:
269			cnx = mysql.connector.connect(**config.myems_historical_db)
270			cursor = cnx.cursor()
271			except Exception as e:
272			logger.error("Error in step 4.1 of offlinemeter.calculate_hourly " + str(e))
273			if cursor:
274			cursor.close()
275			if cnx:
276			cnx.close()
277			time.sleep(60)
278			continue
279
280			try:
281			update_row = (" UPDATE tbl_offline_meter_files "
282			" SET status = %s "
283			" WHERE id = %s ")
284			cursor.execute(update_row, ('done' if is_valid_file else 'error', excel_file['id'],))
285			cnx.commit()
286			except Exception as e:
287			logger.error("Error in step 4.2 of offlinemeter.calculate_hourly " + str(e))
288			time.sleep(60)
289			continue
290			finally:
291			if cursor:
292			cursor.close()
293			if cnx:
294			cnx.close()
295
296			# end of for excel_file in excel_file_list
297
298			print("go to sleep ...")
299			time.sleep(300)
300			print("wake from sleep, and go to work...")
301			# end of outer while
302
303

myems / myems-normalization

Issues (8)

offlinemeter.py (2 issues)

Severity

Introduced By

Duplication Side-by-Side

Filter issues like