def get_existing_tables(self):
with DBConnection() as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM sqlite_master WHERE type='table'")
records = cursor.fetchall()
return [single_row["tbl_name"] for single_row in records]
def insert_a_subject(self, stu_id, subject, score):
command = "INSERT INTO subject_info (stu_id, subject, score) VALUES ('{}', '{}', {});".format(stu_id, subject, score)
with DBConnection() as connection:
cursor = connection.cursor()
cursor.execute(command)
connection.commit()
def update_a_subject(self, stu_id, subject, score):
command = "UPDATE subject_info SET score='{}' WHERE stu_id='{}' AND subject='{}';".format(score, stu_id, subject)
with DBConnection() as connection:
cursor = connection.cursor()
cursor.execute(command)
connection.commit()
def delete_a_subject(self, stu_id):
command = "DELETE FROM subject_info WHERE stu_id='{}';".format(stu_id)
with DBConnection() as connection:
cursor = connection.cursor()
cursor.execute(command)
connection.commit()
def select_a_subject(self, stu_id, subject):
command = "SELECT * FROM subject_info WHERE stu_id = '{}' AND subject='{}';".format(stu_id, subject)
with DBConnection() as connection:
cursor = connection.cursor()
cursor.execute(command)
record_from_db = cursor.fetchall()
return [row['score'] for row in record_from_db]
def show_all_subjects(self, stu_id) :
command = "SELECT DISTINCT subject FROM subject_info WHERE stu_id='{}';".format(stu_id)
with DBConnection() as connection:
cursor = connection.cursor()
cursor.execute(command)
connection.commit()
record_from_db = cursor.fetchall()
return [row['subject'] for row in record_from_db]
def create_table_with_specefied_command(self, command):
with DBConnection() as connection:
cursor = connection.cursor()
cursor.execute(command)
connection.commit()