def db_get_user_info(name, telegram_id): read_user = None enroll_id = None list_user_groups = [] list_user_enrolls = [] already_learn = [] enroll_to_group = [] db = db_connect(name) cursor = db.cursor() command = f'SELECT * FROM users WHERE(telegramID = %s);' cursor.execute(command, (telegram_id,)) try: records = cursor.fetchall() row = records[0] already_learn = str_to_list(row[5]) enroll_to_group = str_to_list(row[6]) for group in already_learn: al_group, al_course, course_id = db_get_group_info(name, group) list_user_groups.append([al_course, al_group]) for enroll_id in enroll_to_group: en_group, en_course, course_id = db_get_group_info(name, enroll_id) list_user_enrolls.append([en_course, en_group]) read_user = [row[2], row[3], row[4], list_user_groups, list_user_enrolls, row[7]] except Error: print("Error reading data from MySQL table", Error) finally: if db.is_connected(): db.close() cursor.close() return read_user, enroll_id, already_learn, enroll_to_group
def db_accept_enroll(name, telegram_id, enroll): db = db_connect(name) cursor = db.cursor() command = f'SELECT enroll FROM users WHERE(telegramID = {telegram_id});' cursor.execute(command) try: enroll = int(enroll) db_enroll = (cursor.fetchall()[0][0]) db_enroll = str_to_list(db_enroll) if enroll in db_enroll: db_enroll.remove(enroll) else: print(f'deleting enroll error: this ({enroll})enroll is not found') return new_enroll = str(db_enroll) if len(db_enroll) == 0: new_enroll = '[]' command = f"UPDATE users SET enroll = %s WHERE (telegramID = {telegram_id});;" cursor.execute(command, (new_enroll,)) command = f"SELECT group_id FROM users WHERE (telegramID = {telegram_id});" cursor.execute(command) records = cursor.fetchone()[0] groups = str_to_list(records) if len(groups) == 0: groups = [enroll] new_groups = str(groups) command = f"UPDATE users SET group_id = %s WHERE (telegramID = {telegram_id});" cursor.execute(command, (new_groups,)) db.commit() elif enroll in groups: print('db_accept_enroll : enroll are the same with one of student groups') return else: groups.append(enroll) new_groups = str(groups) command = f"UPDATE users SET group_id = %s WHERE (telegramID = {telegram_id});" cursor.execute(command, (new_groups,)) db.commit() except mysql.connector.Error as err: print('add db_accept_enroll: ', err.msg) finally: if db.is_connected(): db.close() cursor.close()
def db_get_group_students(name, group_id): db = db_connect(name) cursor = db.cursor() command = f'SELECT telegramID, group_id FROM users' cursor.execute(command) matched_users = [] users_names = [] try: records = cursor.fetchall() for row in records: user_groups = str_to_list(row[1]) if group_id in user_groups: matched_users.append(row[0]) except mysql.connector.Error as err: print('db_get_group_students read id err:', err.msg) for user in matched_users: command = f'SELECT name FROM users WHERE telegramID = {user};' try: cursor.execute(command) user_in_group = cursor.fetchone() users_names.append((user, user_in_group[0])) except mysql.connector.Error as err: print(f'db_get_group_students read name of {user} err:', err.msg) if db.is_connected(): db.close() cursor.close() return users_names
def db_delete_group(name, group_id): db = db_connect(name) cursor = db.cursor() command = 'DELETE FROM groups WHERE id = %s' try: cursor.execute(command, (group_id,)) command = 'SELECT telegramID,group_id FROM users' cursor.execute(command) record = cursor.fetchall() for row in record: groups_list = str_to_list(row[1]) if group_id not in groups_list: continue # print('deleting group info: this group not found in current user groups') else: groups_list.remove(group_id) new_groups = str(groups_list) if len(groups_list) == 0: new_groups = '[]' db_save_var(name, row[0], 'group_id', new_groups) command = 'SELECT telegramID,enroll FROM users' cursor.execute(command) record = cursor.fetchall() for row in record: enroll_list = str_to_list(row[1]) if group_id not in enroll_list: continue # print('deleting enroll from group_delete info: enroll not found in current user groups') else: enroll_list.remove(group_id) new_enrolls = str(enroll_list) if len(enroll_list) == 0: new_enrolls = '[]' db_save_var(name, row[0], 'enroll', new_enrolls) db.commit() except mysql.connector.Error as error: print("Failed to delete record from table: {}".format(error)) finally: if db.is_connected(): db.close() cursor.close()
def db_delete_student_from_group(name, telegram_id, group_id): db = db_connect(name) cursor = db.cursor() command = f'SELECT group_id FROM users WHERE telegramID = {telegram_id}' try: cursor.execute(command) user_groups = cursor.fetchone() user_groups = str_to_list(user_groups[0]) print('T_ID :', telegram_id, 'User Groups :', user_groups, type(user_groups), 'Delete from:', group_id,) user_groups.remove(int(group_id)) db_save_var(name, telegram_id, 'group_id', str(user_groups)) db.commit() except mysql.connector.Error as err: print(f'db_delete_student_from_group err:', err.msg) finally: if db.is_connected(): db.close() cursor.close()
def db_delete_enroll(name, telegram_id, enroll): db = db_connect(name) cursor = db.cursor() command = f'SELECT enroll FROM users WHERE telegramID = {telegram_id}' cursor.execute(command) try: records = cursor.fetchone()[0] cur_enroll = str_to_list(records) if enroll in cur_enroll: cur_enroll.remove(enroll) else: print('db_delete_enroll : enroll are not found in db row') return db_save_var(name, telegram_id, 'enroll', str(cur_enroll)) db.commit() except mysql.connector.Error as err: print('db_delete_enroll: ', err.msg) finally: if db.is_connected(): db.close() cursor.close()