datarepair.do() - Code Metrics - Inspection of "Merge branch 'develop'" - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( d6c50d...b54273 )

by Guangyu

created 2022-07-24 04:30 UTC

datarepair.do() F

↳ Parent: datarepair

Complexity

Conditions

Size

Total Lines	306
Code Lines	233

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	233
dl	0
loc	306
rs	0
c	0
b	0
f	0
cc	67
nop	1

How to fix Long Method Complexity

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

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


def do(logger):
    while True:
        # the outermost while loop to reconnect server if there is a connection error
        ################################################################################################################
        # STEP 1: get all 'new' data repair 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 datarepair.do " + 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 data repair files")
        try:
            query = (" SELECT id, file_name, file_object "
                     " FROM tbl_data_repair_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 datarepair.do " + 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 data repair files found, and go to sleep 60 seconds")
            time.sleep(60)
            continue

        ################################################################################################################
        # STEP 2: for each new files, iterate all rows, read cell value and store data to point data list
        ################################################################################################################
        for excel_file in excel_file_list:
            print("reading data from data repair file " + excel_file['name'])
            is_valid_file = True
            fw = None
            try:
                fw = open("myems-data-repair.blob", 'wb')
                fw.write(excel_file['file_object'])
                fw.close()
            except Exception as e:
                logger.error("Error in step 2.1 of datarepair.do " + str(e))
                if fw:
                    fw.close()
                # mark as invalid file
                is_valid_file = False

            fr = None
            wb = None
            try:
                fr = open("myems-data-repair.blob", 'rb')
                wb = load_workbook(fr, data_only=True)
                fr.close()
            except Exception as e:
                logger.error("Error in step 2.2 of datarepair.do " + 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=2, max_row=1024, min_col=1, max_col=4):
                    repair_file_data = dict()
                    repair_file_data['point_id'] = None
                    repair_file_data['point_name'] = None
                    repair_file_data['date_time_utc'] = None
                    repair_file_data['actual_value'] = None
                    col_num = 0

                    for cell in row:
                        col_num += 1
                        if not isinstance(cell.value, type(None)):
                            print(cell.value)
                        if col_num == 1:
                            # get point ID (should exist in myems_system_db.tbl_points)
                            if cell.value is not None:
                                repair_file_data['point_id'] = cell.value
                            else:
                                break
                        elif col_num == 2:
                            # get point name
                            if cell.value is None:
                                is_valid_file = False
                                break
                            else:
                                repair_file_data['point_name'] = cell.value
                        elif col_num == 3:
                            # get date of the cell
                            if cell.value is None:
                                is_valid_file = False
                                break
                            else:
                                try:
                                    start_datetime_local = datetime.strptime(cell.value, '%Y-%m-%d %H:%M:%S')
                                    start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
                                    repair_file_data['date_time_utc'] = start_datetime_utc
                                except Exception as e:
                                    print("invalid datetime " + str(e))
                                    is_valid_file = False
                                    break
                        elif col_num == 4:
                            if cell.value is None:
                                is_valid_file = False
                                break
                            else:
                                repair_file_data['actual_value'] = Decimal(cell.value)

                    if not isinstance(repair_file_data['point_id'], type(None)):
                        print("repair_file_data:" + str(repair_file_data))
                        energy_data_list.append(repair_file_data)

            ############################################################################################################
            # STEP 3: insert or update point data to table tbl_energy_value in historical database
            ############################################################################################################
            print("check point id in excel file")
            if len(energy_data_list) == 0:
                print("Could not find any repair data in the file")
                print("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 datarepair.do " + str(e))
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()
                    time.sleep(60)
                    continue

                try:
                    cursor.execute(" SELECT id, name, low_limit, high_limit"
                                   " FROM tbl_points ")
                    rows_points = cursor.fetchall()
                except Exception as e:
                    logger.error("Error in step 3.2 of datarepair.do " + str(e))
                    time.sleep(60)
                    continue
                finally:
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()

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

                    print("Could not find any points in the myems system database")
                    time.sleep(60)
                    continue
                elif is_valid_file:
                    system_point_id_set = set()
                    for rows_point in rows_points:
                        # valid point id in excel file
                        system_point_id_set.add(rows_point[0])

                    file_point_id_set = set()
                    for energy_data_item in energy_data_list:
                        file_point_id_set.add(energy_data_item['point_id'])

                    # limit one point id in one file
                    if len(file_point_id_set) != 1:
                        is_valid_file = False

                    for energy_data_item in energy_data_list:
                        if energy_data_item['point_id'] not in system_point_id_set:
                            is_valid_file = False
                            break
                        # check actual value with point high limit and low limit
                        for rows_point in rows_points:
                            if energy_data_item['point_id'] == rows_point[0]:
                                if energy_data_item['actual_value'] < rows_point[2]:
                                    is_valid_file = False
                                    break
                                elif energy_data_item['actual_value'] > rows_point[3]:
                                    is_valid_file = False
                                    break
                                break

                if is_valid_file:
                    ####################################################################################################
                    # delete possibly exists point value data in myems historical database,
                    # and then insert new point data
                    ####################################################################################################
                    try:
                        cnx = mysql.connector.connect(**config.myems_historical_db)
                        cursor = cnx.cursor()
                    except Exception as e:
                        logger.error("Error in step 3.2 of datarepair.do " + str(e))
                        if cursor:
                            cursor.close()
                        if cnx:
                            cnx.close()
                        time.sleep(60)
                        continue

                    try:
                        date_time_utc_list = list()
                        for i in range(len(energy_data_list)):
                            for item in (energy_data_list[i]['date_time_utc'],):
                                date_time_utc_list.append(item)

                        start_date_time_utc = min(date_time_utc_list)
                        end_date_time_utc = max(date_time_utc_list)
                        point_id = energy_data_list[0]['point_id']
                        print("deleted data from %s to %s in table myems_historical_db.tbl_energy_value",
                              start_date_time_utc, end_date_time_utc)
                        cursor.execute(" DELETE FROM tbl_energy_value "
                                       " WHERE point_id = %s "
                                       "       AND utc_date_time >= %s "
                                       "       AND utc_date_time <= %s ",
                                       (str(point_id),
                                        start_date_time_utc.isoformat()[0:19],
                                        end_date_time_utc.isoformat()[0:19]))
                        cnx.commit()

                        for energy_data_item in energy_data_list:
                            add_values = (" INSERT INTO tbl_energy_value "
                                          "             (point_id, utc_date_time, actual_value, is_bad) "
                                          " VALUES  ")
                            add_values += " (" + str(point_id) + ","
                            add_values += "'" + energy_data_item['date_time_utc'].isoformat()[0:19] + "',"
                            add_values += "'" + str(energy_data_item['actual_value']) + "',"
                            add_values += "0" + "), "
                            print("add_values:" + add_values)
                            cursor.execute(add_values[:-2])
                            cnx.commit()
                    except Exception as e:
                        logger.error("Error in step 3.3 of datarepair.do " + 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("updating data repair file status")
            try:
                cnx = mysql.connector.connect(**config.myems_historical_db)
                cursor = cnx.cursor()
            except Exception as e:
                logger.error("Error in step 4.1 of datarepair.do " + str(e))
                if cursor:
                    cursor.close()
                if cnx:
                    cnx.close()
                time.sleep(60)
                continue

            try:
                update_row = (" UPDATE tbl_data_repair_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 datarepair.do " + 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			from decimal import Decimal
6			import config
7
8			################################################################################################################
9			# PROCEDURES:
10			# STEP 1: get all 'new' data repair files
11			# STEP 2: for each new files, iterate all rows, read cell value and store data to point data list
12			# STEP 3: insert or update point data to table tbl_energy_value in historical database
13			# STEP 4: update file status to 'done' or 'error'
14			################################################################################################################
15
16
17			def do(logger):
18			while True:
19			# the outermost while loop to reconnect server if there is a connection error
20			################################################################################################################
21			# STEP 1: get all 'new' data repair files
22			################################################################################################################
23			cnx = None
24			cursor = None
25			try:
26			cnx = mysql.connector.connect(**config.myems_historical_db)
27			cursor = cnx.cursor()
28			except Exception as e:
29			logger.error("Error in step 1.1 of datarepair.do " + str(e))
30			if cursor:
31			cursor.close()
32			if cnx:
33			cnx.close()
34			# sleep several minutes and continue the outer loop to reconnect the database
35			print("Could not connect the myems historical database, and go to sleep 60 seconds")
36			time.sleep(60)
37			continue
38
39			print("Connected to myems historical database")
40			print("Getting all new data repair files")
41			try:
42			query = (" SELECT id, file_name, file_object "
43			" FROM tbl_data_repair_files "
44			" WHERE status = 'new' "
45			" ORDER BY id ")
46			cursor.execute(query, )
47			rows_files = cursor.fetchall()
48			except Exception as e:
49			logger.error("Error in step 1.2 of datarepair.do " + str(e))
50			time.sleep(60)
51			continue
52			finally:
53			if cursor:
54			cursor.close()
55			if cnx:
56			cnx.close()
57
58			excel_file_list = list()
59			if rows_files is not None and len(rows_files) > 0:
			0 ignored issues – show introduced 2022-07-24 04:35 UTC by Report Bug Copy Issue Report The variable `rows_files` does not seem to be defined for all execution paths. Loading history...
60			for row_file in rows_files:
61			excel_file_list.append({"id": row_file[0],
62			"name": row_file[1],
63			"file_object": row_file[2]})
64			else:
65			print("there isn't any new data repair files found, and go to sleep 60 seconds")
66			time.sleep(60)
67			continue
68
69			################################################################################################################
70			# STEP 2: for each new files, iterate all rows, read cell value and store data to point data list
71			################################################################################################################
72			for excel_file in excel_file_list:
73			print("reading data from data repair file " + excel_file['name'])
74			is_valid_file = True
75			fw = None
76			try:
77			fw = open("myems-data-repair.blob", 'wb')
78			fw.write(excel_file['file_object'])
79			fw.close()
80			except Exception as e:
81			logger.error("Error in step 2.1 of datarepair.do " + str(e))
82			if fw:
83			fw.close()
84			# mark as invalid file
85			is_valid_file = False
86
87			fr = None
88			wb = None
89			try:
90			fr = open("myems-data-repair.blob", 'rb')
91			wb = load_workbook(fr, data_only=True)
92			fr.close()
93			except Exception as e:
94			logger.error("Error in step 2.2 of datarepair.do " + str(e))
95			if fr:
96			fr.close()
97			# mark as invalid file
98			is_valid_file = False
99
100			energy_data_list = list()
101			# grab the active worksheet
102			if is_valid_file:
103			ws = wb.active
104
105			# get timezone offset in minutes, this value will be returned to client
106			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
107			if config.utc_offset[0] == '-':
108			timezone_offset = -timezone_offset
109
110			for row in ws.iter_rows(min_row=2, max_row=1024, min_col=1, max_col=4):
111			repair_file_data = dict()
112			repair_file_data['point_id'] = None
113			repair_file_data['point_name'] = None
114			repair_file_data['date_time_utc'] = None
115			repair_file_data['actual_value'] = None
116			col_num = 0
117
118			for cell in row:
119			col_num += 1
120			if not isinstance(cell.value, type(None)):
121			print(cell.value)
122			if col_num == 1:
123			# get point ID (should exist in myems_system_db.tbl_points)
124			if cell.value is not None:
125			repair_file_data['point_id'] = cell.value
126			else:
127			break
128			elif col_num == 2:
129			# get point name
130			if cell.value is None:
131			is_valid_file = False
132			break
133			else:
134			repair_file_data['point_name'] = cell.value
135			elif col_num == 3:
136			# get date of the cell
137			if cell.value is None:
138			is_valid_file = False
139			break
140			else:
141			try:
142			start_datetime_local = datetime.strptime(cell.value, '%Y-%m-%d %H:%M:%S')
143			start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
144			repair_file_data['date_time_utc'] = start_datetime_utc
145			except Exception as e:
146			print("invalid datetime " + str(e))
147			is_valid_file = False
148			break
149			elif col_num == 4:
150			if cell.value is None:
151			is_valid_file = False
152			break
153			else:
154			repair_file_data['actual_value'] = Decimal(cell.value)
155
156			if not isinstance(repair_file_data['point_id'], type(None)):
157			print("repair_file_data:" + str(repair_file_data))
158			energy_data_list.append(repair_file_data)
159
160			############################################################################################################
161			# STEP 3: insert or update point data to table tbl_energy_value in historical database
162			############################################################################################################
163			print("check point id in excel file")
164			if len(energy_data_list) == 0:
165			print("Could not find any repair data in the file")
166			print("go to process the next file")
167			is_valid_file = False
168			else:
169			try:
170			cnx = mysql.connector.connect(**config.myems_system_db)
171			cursor = cnx.cursor()
172			except Exception as e:
173			logger.error("Error in step 3.1 of datarepair.do " + str(e))
174			if cursor:
175			cursor.close()
176			if cnx:
177			cnx.close()
178			time.sleep(60)
179			continue
180
181			try:
182			cursor.execute(" SELECT id, name, low_limit, high_limit"
183			" FROM tbl_points ")
184			rows_points = cursor.fetchall()
185			except Exception as e:
186			logger.error("Error in step 3.2 of datarepair.do " + str(e))
187			time.sleep(60)
188			continue
189			finally:
190			if cursor:
191			cursor.close()
192			if cnx:
193			cnx.close()
194
195			if rows_points is None or len(rows_points) == 0:
			0 ignored issues – show introduced 2022-07-24 04:35 UTC by Report Bug Copy Issue Report The variable `rows_points` does not seem to be defined for all execution paths. Loading history...
196			print("Could not find any points in the myems system database")
197			time.sleep(60)
198			continue
199			elif is_valid_file:
200			system_point_id_set = set()
201			for rows_point in rows_points:
202			# valid point id in excel file
203			system_point_id_set.add(rows_point[0])
204
205			file_point_id_set = set()
206			for energy_data_item in energy_data_list:
207			file_point_id_set.add(energy_data_item['point_id'])
208
209			# limit one point id in one file
210			if len(file_point_id_set) != 1:
211			is_valid_file = False
212
213			for energy_data_item in energy_data_list:
214			if energy_data_item['point_id'] not in system_point_id_set:
215			is_valid_file = False
216			break
217			# check actual value with point high limit and low limit
218			for rows_point in rows_points:
219			if energy_data_item['point_id'] == rows_point[0]:
220			if energy_data_item['actual_value'] < rows_point[2]:
221			is_valid_file = False
222			break
223			elif energy_data_item['actual_value'] > rows_point[3]:
224			is_valid_file = False
225			break
226			break
227
228			if is_valid_file:
229			####################################################################################################
230			# delete possibly exists point value data in myems historical database,
231			# and then insert new point data
232			####################################################################################################
233			try:
234			cnx = mysql.connector.connect(**config.myems_historical_db)
235			cursor = cnx.cursor()
236			except Exception as e:
237			logger.error("Error in step 3.2 of datarepair.do " + str(e))
238			if cursor:
239			cursor.close()
240			if cnx:
241			cnx.close()
242			time.sleep(60)
243			continue
244
245			try:
246			date_time_utc_list = list()
247			for i in range(len(energy_data_list)):
248			for item in (energy_data_list[i]['date_time_utc'],):
249			date_time_utc_list.append(item)
250
251			start_date_time_utc = min(date_time_utc_list)
252			end_date_time_utc = max(date_time_utc_list)
253			point_id = energy_data_list[0]['point_id']
254			print("deleted data from %s to %s in table myems_historical_db.tbl_energy_value",
255			start_date_time_utc, end_date_time_utc)
256			cursor.execute(" DELETE FROM tbl_energy_value "
257			" WHERE point_id = %s "
258			" AND utc_date_time >= %s "
259			" AND utc_date_time <= %s ",
260			(str(point_id),
261			start_date_time_utc.isoformat()[0:19],
262			end_date_time_utc.isoformat()[0:19]))
263			cnx.commit()
264
265			for energy_data_item in energy_data_list:
266			add_values = (" INSERT INTO tbl_energy_value "
267			" (point_id, utc_date_time, actual_value, is_bad) "
268			" VALUES ")
269			add_values += " (" + str(point_id) + ","
270			add_values += "'" + energy_data_item['date_time_utc'].isoformat()[0:19] + "',"
271			add_values += "'" + str(energy_data_item['actual_value']) + "',"
272			add_values += "0" + "), "
273			print("add_values:" + add_values)
274			cursor.execute(add_values[:-2])
275			cnx.commit()
276			except Exception as e:
277			logger.error("Error in step 3.3 of datarepair.do " + str(e))
278			time.sleep(60)
279			continue
280			finally:
281			if cursor:
282			cursor.close()
283			if cnx:
284			cnx.close()
285
286			############################################################################################################
287			# STEP 4: update file status to 'done' or 'error'
288			############################################################################################################
289			print("updating data repair file status")
290			try:
291			cnx = mysql.connector.connect(**config.myems_historical_db)
292			cursor = cnx.cursor()
293			except Exception as e:
294			logger.error("Error in step 4.1 of datarepair.do " + str(e))
295			if cursor:
296			cursor.close()
297			if cnx:
298			cnx.close()
299			time.sleep(60)
300			continue
301
302			try:
303			update_row = (" UPDATE tbl_data_repair_files "
304			" SET status = %s "
305			" WHERE id = %s ")
306			cursor.execute(update_row, ('done' if is_valid_file else 'error', excel_file['id'],))
307			cnx.commit()
308			except Exception as e:
309			logger.error("Error in step 4.2 of datarepair.do " + str(e))
310			time.sleep(60)
311			continue
312			finally:
313			if cursor:
314			cursor.close()
315			if cnx:
316			cnx.close()
317
318			# end of for excel_file in excel_file_list
319
320			print("go to sleep")
321			time.sleep(300)
322			print("wake from sleep, and go to work")
323			# end of outer while
324

MyEMS / myems

Push — master ( d6c50d...b54273 )

datarepair.do() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like