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 []
