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()