class Connector(object): def __init__(self): self.configPath = os.path.join( os.path.dirname(__file__) + "/config/db_config_inner.ini") config = cu.read_db_config(self.configPath) dbConfig = dict({"connection_timeout": 3600}, **config) self.connector = MySQLConnection(charset='utf8', **dbConfig) def cursor(self): if self.connector.is_connected(): return self.connector.cursor() else: try: self.connector.reconnect(attempts=10, delay=1) return self.cursor() except Exception: return None pass def commit(self): if self.connector: if self.connector.is_connected(): self.connector.commit() else: try: self.connector.reconnect(attempts=10, delay=1) self.commit() except Exception: pass
class Connector(object): def __init__(self, isLocalDB=False): """ :param isLocalDB: 是否是本地的数据库,默认是远程 """ db_config_file = "/config/db_config_local.ini" if isLocalDB else "/config/db_config_inner.ini" self.configPath = os.path.join( os.path.dirname(__file__) + db_config_file) config = cu.read_db_config(self.configPath) self.dbConfig = dict({"connection_timeout": 3600}, **config) self.connector = None self.getConnector() def getConnector(self, attempts=10, delay=4): if self.connector: return self.connector counter = 0 while counter != attempts: counter += 1 try: self.connector = MySQLConnection(charset='utf8mb4', **self.dbConfig) break except Exception as err: if counter == attempts: break if delay > 0: time.sleep(delay) return self.connector def cursor(self): if not self.getConnector(): # 如果不存在 则代表没有连接成功(尝试了10次之后还没传成功,就代表此次不进行操作) return None if self.connector.is_connected(): return self.connector.cursor() else: try: self.connector.reconnect(attempts=10, delay=1) return self.cursor() except Exception: return None def commit(self): if self.connector: if self.connector.is_connected(): self.connector.commit() else: try: self.connector.reconnect(attempts=10, delay=1) self.commit() except Exception: pass def close(self): if self.connector: self.connector.close()
try: cur.execute(command) except Exception, e: logging.error(e, exc_info=True) if coords == "mlt": command = "SELECT vel, mag_glatc, mag_gltc, mag_gazmc, " +\ "datetime, rad FROM {tb1} ORDER By datetime ASC" elif coords == "geo": command = "SELECT vel, geo_glatc, geo_gltc, geo_gazmc, " +\ "datetime, rad FROM {tb1} ORDER By datetime ASC" command = command.format(tb1=input_table) # check the db connection before fetching if not conn_ten.is_connected(): conn_ten.reconnect() # fetch the data try: cur_ten.execute(command) except Exception, e: logging.error(e, exc_info=True) rows = cur_ten.fetchall() # insert the data into a table if rows: if coords == "mlt": command = "INSERT IGNORE INTO {tb2} (vel, mag_glatc, mag_gltc, " +\ "mag_gazmc, datetime, season, rad) VALUES (%s, %s, %s, %s, %s, %s, %s)" elif coords == "geo": command = "INSERT IGNORE INTO {tb2} (vel, geo_glatc, geo_gltc, " +\ "geo_gazmc, datetime, season, rad) VALUES (%s, %s, %s, %s, %s, %s, %s)"
while edtm <= etm: # bin_vel stores the velocity data as {glatc-glonc-gazmc: [velocites]} bin_vel = {} for tbl in tbl_names: # select column variables from iscat for a ten-minute interval if coords == "mlt": command = "SELECT vel, mag_glatc, mag_gltc, mag_gazmc " +\ "FROM {tb} WHERE datetime BETWEEN '{sdtm}' AND '{edtm}'" elif coords == "geo": command = "SELECT vel, geo_glatc, geo_gltc, geo_gazmc " +\ "FROM {tb} WHERE datetime BETWEEN '{sdtm}' AND '{edtm}'" command = command.format(tb=tbl, sdtm=sdtm, edtm=edtm) # check the db connection before select if not conn_iscat.is_connected(): conn_iscat.reconnect() try: cur_iscat.execute(command) except Exception, e: logging.error(e, exc_info=True) rows_tmp = cur_iscat.fetchall() if rows_tmp: # loop through each row for row in rows_tmp: vel, lat, lon, az = row if None not in row: # convert from string to float vel = [float(x) for x in vel.split(",")] lat = [float(x) for x in lat.split(",")]
# logging.error(e, exc_info=True) batch_size = 1000 niter = len(dtms) / batch_size + 1 for n in range(niter): dtms_n = dtms[n * batch_size:batch_size * (n + 1)] command = "SELECT * FROM {tb} " +\ "WHERE (datetime BETWEEN '{stm}' AND '{etm}') AND " +\ "(datetime IN {dtms_n})" command = command.format(tb=input_table, stm=stm, etm=etm, dtms_n=tuple([str(x) for x in dtms_n])) # check the db connection before fetching if not conn_input.is_connected(): conn_input.reconnect() # fetch the data try: print("Fetching data from " + input_table + " for interval between " +\ str(dtms_n[0]) + " and " + str(dtms_n[-1])) cur_input.execute(command) rows = cur_input.fetchall() except Exception, e: logging.error(e, exc_info=True) # insert the data into the output table if rows: print("Inserting data to " + output_table + " for interval between " +\ str(dtms_n[0]) + " and " + str(dtms_n[-1])) for rw in rows:
class MySQL(MySQLBase): def __init__(self, host: str, port: int, user: str, password: str): super().__init__() self.__db = MySQLConnection() self.__devices_data_fields = [ 'id', 'time', 'temperature', 'air_humidity', 'count', 'bbox', 'img' ] self.__users_data_fields = [ 'id', 'time', 'count', 'bbox', 'img', 'coordinates' ] self.__connect(host, port, user, password) def __connect(self, host: str, port: int, user: str, password: str) -> bool: try: self.__db.connect(host=host, port=port, user=user, passwd=password) return True except sql.errors.InterfaceError: return False def __reconnect(self) -> bool: try: self.__db.reconnect() return True except sql.errors.InterfaceError: return False def user_auth_check(self, login, password) -> bool: if not self.__db.is_connected(): self.__reconnect() if self.__db.is_connected(): if self.sql_check(login): query = MySQLQueries.USER_AUTH_CHECK answer = self.execute( self.__db, query, [login, hashlib.md5(bytes(password, 'utf-8')).hexdigest()])[0][0] return bool(answer) else: return False else: return False def device_in_db(self, device_token) -> bool: if not self.__db.is_connected(): self.__reconnect() if self.__db.is_connected(): if self.sql_check(device_token): query = MySQLQueries.DEVICE_IN_DB answer = self.execute(self.__db, query, [device_token])[0][0] return bool(answer) else: return False else: return False def camera_in_db(self, camera_token) -> bool: if not self.__db.is_connected(): self.__reconnect() if self.__db.is_connected(): if self.sql_check(camera_token): query = MySQLQueries.CAMERA_IN_DB answer = self.execute(self.__db, query, [camera_token])[0][0] return bool(answer) else: return False else: return False def save_device_package(self, data: IcicleSpyPackage, influx_db: Influx) -> bool: if not self.__db.is_connected(): self.__reconnect() if self.__db.is_connected(): query = MySQLQueries.DEVICE_ID_BY_TOKEN device_id = self.execute(self.__db, query, [data.device_token])[0][0] query = MySQLQueries.CAMERA_ID_BY_TOKEN camera_id = self.execute(self.__db, query, [data.camera_token])[0][0] query = MySQLQueries.DEVICE_RECORD_ID_EXISTS record_id = self.execute(self.__db, query, [device_id, camera_id]) if len(record_id) == 0: query = MySQLQueries.SAVE_DEVICE_PACKAGE self.execute(self.__db, query, [ data.time, data.temperature, data.air_humidity, data.count, str(data.bbox), data.img, device_id, camera_id ], set=True) elif len(record_id) == 1: query = MySQLQueries.UPDATE_DEVICE_RECORD self.execute(self.__db, query, [ data.time, data.temperature, data.air_humidity, data.count, str(data.bbox), data.img, record_id[0][0], device_id, camera_id ], set=True) else: return False query = MySQLQueries.DEVICE_RECORD_ID_EXISTS record_id = self.execute(self.__db, query, [device_id, camera_id])[0][0] query = MySQLQueries.ADDRESS_INFO_BY_IDX country, region_state, city, street, building, index = self.execute( self.__db, query, [record_id])[0] influx_db.save_device_package(device_token=data.device_token, country=country, region_state=region_state, city=city, street=street, building=building, index=index, temperature=data.temperature, air_humidity=data.air_humidity) return True else: return False # def save_mobile_package(self, data: IcicleSpyPackageMobile) -> bool: # if not self.__db.is_connected(): # self.__reconnect() # # if self.__db.is_connected(): # query: str = MySQLQueries.SAVE_MOBILE_PACKAGE # self.execute(self.__db, # query, # [data.time, # data.count, # data.bbox, # data.latitude, # data.longitude, # data.users_id], # set=True) # return True # else: # return False def get_icicle_count_by_token(self, token: str) -> [int, int, int]: if not self.__db.is_connected(): self.__reconnect() if self.__db.is_connected(): query: str = MySQLQueries.GET_ICICLE_COUNT_BY_TOKEN min_count, max_count, avg = self.execute(self.__db, query, [token])[0] if int(min_count) is not None and int( max_count) is not None and int(avg) is not None: return int(min_count), int(max_count), int(avg) else: return -1, -1, -1 else: return -1, -1, -1 def get_data_by_id(self, id: int, request: list) -> list: if not self.__db.is_connected(): self.__reconnect() if self.__db.is_connected(): for r in request: if r not in self.__devices_data_fields and self.sql_check(r): return [] query: str = MySQLQueries.DEVICE_RECORD_ID_BY_ID query: str = query % (', '.join(request), '%s') data = self.execute(self.__db, query, [id]) return_data = [] for d in data: tmp = {} for r, dd in zip(request, d): tmp[r] = dd return_data.append(tmp) return return_data else: return []