''' Created on Jul 16, 2016 @author: weizhenyuan ''' from mysql.connector import MySQLConnection MySQLConnection.connect() print
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 []
class MySqlAccess:
"""
Wrapper class to access MySQL DB using mysql connector/python
"""
def __init__(self, db_host=default_host, db_port=default_port):
self.__init_consts()
self.__invalid_db = False
self.__order = self.__SQL_ORDER_DESC
try:
self._conn = MySQLConnection(host=db_host,
port=db_port)
except errors.InterfaceError:
self.__invalid_db = True
def __init_consts(self):
# SQL
self.__SQL_SHOW_DB = 'SHOW DATABASES'
self.__SQL_SHOW_TABLES = 'SHOW TABLES IN '
self.__SQL_SHOW_COLUMNS_FMT = 'SHOW COLUMNS IN {0} IN {1}'
self.__SQL_USE_DATABASE = 'USE '
self.__SQL_ORDER_DESC = ' DESC'
self.__SQL_ORDER_ASC = ' ASC'
self.__SQL_SELECT = 'SELECT '
self.__SQL_COLUMN_SEP = ', '
self.__SQL_FROM = ' FROM '
self.__SQL_DOT = '.'
self.__SQL_ORDER_BY = ' ORDER BY '
self.__SQL_UPDATE = 'UPDATE {0} SET '
self.__SQL_WHERE = ' WHERE '
self.__SQL_ASSIGN = '='
self.__SQL_DELETE = 'DELETE FROM {0}'
self.__SQL_INSERT = 'INSERT INTO {0}('
self.__SQL_INSERT_VALUES = ') VALUES ('
self.__SQL_INSERT_END = ')'
self.__SQL_OPEN_BRACKET = '['
self.__SQL_CLOSE_BRACKET = ']'
self.__SQL_VALUE = 'value-'
# SQL templates
self.SQL_TEMPLATE_SELECT = 'SELECT {0} FROM {1} WHERE 1'
def connect(self, user_name=None, user_password=None) -> bool:
if (user_name is None) or (user_password is None) or self.__invalid_db:
return False
try:
self._conn.connect(user=user_name, password=user_password)
except errors.ProgrammingError:
return False
return True
def disconnect(self):
self._conn.close()
def query(self, sql_stmt=None) -> [list]:
cursor = self._conn.cursor()
query = sql_stmt
try:
cursor.execute(query)
l = [row for row in cursor]
except errors.ProgrammingError:
return None
cursor.close()
return l
def get_database(self) -> [list]:
return self.query(self.__SQL_SHOW_DB)
def get_tables(self, db_name) -> [list]:
return self.query(self.__SQL_SHOW_TABLES + db_name)
def get_columns_of_table(self, db_name, table_name) -> [list]:
return self.query(self.__SQL_SHOW_COLUMNS_FMT.format(table_name, db_name))
def flip_order(self):
if self.__order == self.__SQL_ORDER_DESC:
self.__order = self.__SQL_ORDER_ASC
else:
self.__order = self.__SQL_ORDER_DESC
def compose_select(self, db_name, table_name, columns, order_column) -> str:
sql = self.__SQL_SELECT
for v in columns:
sql += v + self.__SQL_COLUMN_SEP
query = self.__remove_trailing_sep(sql)
query += self.__SQL_FROM + db_name + self.__SQL_DOT + table_name
query += self.__SQL_ORDER_BY + order_column + self.__order
return query
def compose_update(self, db_name, table_name, columns) -> str:
sql = self.__SQL_UPDATE.format(db_name + self.__SQL_DOT + table_name)
for (i, v) in zip(range(len(columns)), columns):
sql += v + self.__SQL_ASSIGN
sql += self.__SQL_OPEN_BRACKET + self.__SQL_VALUE + str(i + 1) + self.__SQL_CLOSE_BRACKET
sql += self.__SQL_COLUMN_SEP
query = self.__remove_trailing_sep(sql)
query += self.__SQL_WHERE
return query
def compose_delete(self, db_name, table_name) -> str:
sql = self.__SQL_DELETE.format(db_name + self.__SQL_DOT + table_name)
sql += self.__SQL_WHERE
return sql
def compose_insert(self, db_name, table_name, columns) ->str:
sql = self.__SQL_INSERT.format(db_name + self.__SQL_DOT + table_name)
values = ''
for (i, v) in zip(range(len(columns)), columns):
sql += v + self.__SQL_COLUMN_SEP
values += self.__SQL_OPEN_BRACKET + self.__SQL_VALUE + str(i + 1) + self.__SQL_CLOSE_BRACKET
values += self.__SQL_COLUMN_SEP
query = self.__remove_trailing_sep(sql) + self.__SQL_INSERT_VALUES
values = self.__remove_trailing_sep(values)
query += values + self.__SQL_INSERT_END
return query
def __remove_trailing_sep(self, s) -> str:
if len(s) <= len(self.__SQL_COLUMN_SEP):
return ''
return s[0:len(s) - len(self.__SQL_COLUMN_SEP)]
