def test_delete_where(self):
_database = DbConnection('localhost', 'KartonBot', 'root', 'zxc123', 3306)
query_connector = _database.select_all_table('users')
# TODO WTF Sized != Iterable
_last_len = len(query_connector)
_database.delete_where('users', {'id': 5})
self.assertEqual(len(_database.select_all_table('users')), _last_len - 1)
def test_select_all_columns(self):
_database = DbConnection('localhost', 'KartonBot', 'root', 'zxc123', 3306)
query_connector = _database.select_all_table(table_name='users', column_names=['id', 'vk_id'])
_driver = mysql.connector.connect(host='localhost', database='mysql', user='******', password='******')
_cur = _driver.cursor()
_cur.execute("select id, vk_id from Kartonbot.users limit 2")
query_driver = _cur.fetchall()
for dr_item, conn_item in zip(query_driver, query_connector):
print(dr_item, " | ", conn_item)
self.assertEqual(dr_item, conn_item)
def test_select_all(self):
_osu_wr = OsuDbWorker()
data_from_worker = _osu_wr.select_all()
_database = DbConnection('localhost', 'KartonBot', 'root', 'zxc123', 3306)
_pure_data = _database.select_all_table('osu', ['vk_id', 'nickname', 'mode'])
for taken_item, action in zip(_pure_data, data_from_worker):
print(taken_item, " | ", action)
self.assertEqual(taken_item[0], action['vk_id'])
self.assertEqual(taken_item[1], action['nickname'])
self.assertEqual(taken_item[2], action['mode'])
def test_select_all(self):
command_worker = CommandDbWorker()
data_from_worker = command_worker.select_all()
_database = DbConnection('localhost', 'KartonBot', 'root', 'zxc123', 3306)
_pure_data = _database.select_all_table('categories', ['access_level', 'name', 'value', 'attachment'])
for taken_item, action in zip(_pure_data, data_from_worker):
print(taken_item, " | ", action)
self.assertEqual(taken_item[0], action['access_level'])
self.assertEqual(taken_item[1], action['name'])
self.assertEqual(taken_item[2], action['value'])
self.assertEqual(taken_item[3], action['attachment'])
def test_select_all(self):
users_worker = UserDbWorker()
data_from_worker = users_worker.select_all()
_database = DbConnection('localhost', 'KartonBot', 'root', 'zxc123', 3306)
_pure_data = _database.select_all_table('users', ['access_level', 'vk_id', 'association', 'lvl_exp'])
for taken_item, action in zip(_pure_data, data_from_worker):
print(taken_item, " | ", action)
self.assertEqual(taken_item[0], action['access_level'])
self.assertEqual(taken_item[1], action['vk_id'])
self.assertEqual(taken_item[2], action['association'])
self.assertEqual(taken_item[3], action['lvl_exp'])
class CommandDbWorker:
def __init__(self):
self.db = DbConnection('localhost', 'KartonBot', 'root', 'zxc123', 3306, DbConnVersion.SYNC)
self.table_name = 'categories'
def select_all(self):
data = self.db.select_all_table(self.table_name, ['access_level', 'name', 'value', 'attachment'])
items = []
for item in data:
items.append({
'access_level': item[0],
'name': item[1],
'value': item[2],
'attachment': item[3]})
return items
def insert(self, access_lvl: int, comm_name: str, comm_value: str = '', comm_attachment: str = None):
if comm_attachment is not None:
return self.db.insert_into(self.table_name, {'access_level': access_lvl,
'name': comm_name,
'value': comm_value,
'attachment': comm_attachment})
else:
return self.db.insert_into(self.table_name, {'access_level': access_lvl,
'name': comm_name,
'value': comm_value})
def delete(self, comm_name: str):
return self.db.delete_where(self.table_name, {'name': comm_name})
def update(self, comm_name: str, access_lvl: int = None, value: str = None, attachment: str = None) -> bool:
args = locals()
if any(args.values()) is not None:
dict_of_updates = {}
if access_lvl is not None:
dict_of_updates.update({'access_level': access_lvl})
if value is not None:
dict_of_updates.update({'value': value})
if attachment is not None:
dict_of_updates.update({'attachment': attachment})
return self.db.update_where(self.table_name, {'name': comm_name}, dict_of_updates)
else:
logging.warning('taken zero params and cannot be any update..')
return False
class OsuDbWorker:
def __init__(self):
self.db = DbConnection('localhost', 'KartonBot', 'root', 'zxc123',
3306, DbConnVersion.SYNC)
self.table_name = 'osu'
def select_all(self) -> Iterable:
data = self.db.select_all_table(self.table_name,
['vk_id', 'nickname', 'mode'])
items = []
for item in data:
items.append({
'vk_id': item[0],
'nickname': item[1],
'mode': item[2]
})
return items
def select_one(self, osu_vk_id: int) -> object:
return self.db.select_where(self.table_name, {'vk_id': osu_vk_id})
def insert(self, osu_vk_id: int, osu_nickname: str, mode: int = 1):
return self.db.insert_into(self.table_name, {
'vk_id': osu_vk_id,
'nickname': osu_nickname,
'mode': mode
})
def delete(self, osu_vk_id: int) -> bool:
return self.db.delete_where(self.table_name, {'vk_id': osu_vk_id})
def update(self, vk_id, nickname: str = None, mode: int = None) -> bool:
args = locals()
if any(args.values()) is not None:
dict_of_updates = {}
if nickname is not None:
dict_of_updates.update({'nickname': nickname})
if mode is not None:
dict_of_updates.update({'mode': mode})
return self.db.update_where(self.table_name, {'vk_id': vk_id},
dict_of_updates)
else:
logging.warning('taken zero params and cannot be any update..')
return False
class UserDbWorker:
def __init__(self):
self.table_name = 'users'
self.db = DbConnection('localhost', 'KartonBot', 'root', 'zxc123',
3306, DbConnVersion.SYNC)
def select_all(self) -> Iterable:
data = self.db.select_all_table(
self.table_name,
['access_level', 'vk_id', 'association', 'lvl_exp'])
items = []
for item in data:
items.append({
'access_level': item[0],
'vk_id': item[1],
'association': item[2],
'lvl_exp': item[3]
})
return items
def first_or_default(self, vk_id: int):
query = self.db.select_where(self.table_name, {'vk_id': vk_id})
return query[0] if len(query) > 0 else None
def insert(self,
access_level: int,
vk_id: int,
association: str,
level_exp=0) -> bool:
return self.db.insert_into(
self.table_name, {
'access_level': access_level,
'vk_id': vk_id,
'association': association,
'lvl_exp': level_exp
})
def delete(self, vk_id: int) -> bool:
return self.db.delete_where(self.table_name, {'vk_id': vk_id})
def update(self,
vk_id,
association: str = None,
level: int = None,
exp: float = None) -> bool:
args = locals()
if any(args.values()) is not None:
dict_of_updates = {}
if association is not None:
dict_of_updates.update({'association': association})
if level is not None:
dict_of_updates.update({'access_level': level})
if exp is not None:
dict_of_updates.update({'lvl_exp': exp})
return self.db.update_where(self.table_name, {'vk_id': vk_id},
dict_of_updates)
else:
logging.warning('taken zero params and cannot be any update..')
return False
def contains(self, vk_id: int) -> bool:
return len(self.db.select_where(self.table_name,
{'vk_id': vk_id})) != 0