''' 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)]