4
\$\begingroup\$

The program is designed to take data from a MySQL database, using mysql-connector-python, and print it as a table in Python using the texttable module. The program also plots charts based on the data from MySQL database using the matplotlib library.

For those who don’t have any data in MySQL, the program will assist a bit to insert data into a few tables.

Relevant MySQL information:

mysql> use ip_project;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_ip_project |
+----------------------+
| exam                 |
| student              |
| subject              |
+----------------------+
3 rows in set (0.05 sec)

mysql> desc exam;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| sub_code | varchar(3) | NO   |     | NULL    |       |
| roll_no  | int        | NO   |     | NULL    |       |
| marks    | int        | YES  |     | NULL    |       |
| test     | varchar(3) | NO   |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll_no | int         | NO   | PRI | NULL    |       |
| name    | varchar(20) | NO   |     | NULL    |       |
| stream  | varchar(5)  | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc subject;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sub_code | varchar(3)  | NO   | PRI | NULL    |       |
| name     | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

The coding was done in a conda environment with Python 3.7.11. The following packages/modules/libraries were used:

  • spyder
  • pandas
  • numpy
  • matplotlib
  • mysql-connector-python
  • texttable

Python code:

# Importing
from getpass import getpass
from mysql.connector import connect
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import texttable

#################################
#Defining/Assigning

create_db_query = """CREATE DATABASE IF NOT EXISTS ip_project;"""

use_db_query = "USE ip_project;"

crt_examtbl_query = """CREATE TABLE IF NOT EXISTS `exam` (
  `sub_code` varchar(3) NOT NULL,
  `roll_no` int NOT NULL,
  `marks` int DEFAULT NULL,
  `test` varchar(3) NOT NULL
);"""

crt_stutbl_query = """CREATE TABLE IF NOT EXISTS `student` (
  `roll_no` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `stream` varchar(5) NOT NULL,
  PRIMARY KEY (`roll_no`)
);"""

crt_subtbl_query = """CREATE TABLE IF NOT EXISTS `subject` (
  `sub_code` varchar(3) NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`sub_code`)
);
"""

tbl_structure = '''You can now see the table structures to get a better idea:
 
'exam' table:
============
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| sub_code | varchar(3) | NO   |     | NULL    |       |
| roll_no  | int        | NO   |     | NULL    |       |
| marks    | int        | YES  |     | NULL    |       |
| test     | varchar(3) | NO   |     | NULL    |       |
+----------+------------+------+-----+---------+-------+

'student' table:
===============
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll_no | int         | NO   | PRI | NULL    |       |
| name    | varchar(20) | NO   |     | NULL    |       |
| stream  | varchar(5)  | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

'subject' table:
===============
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sub_code | varchar(3)  | NO   | PRI | NULL    |       |
| name     | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
'''

insert_stu_query = '''INSERT INTO student(roll_no, name, stream) VALUES(%s, %s, %s);'''

stream_human_lst = '''
1. MPC
2. BiPC
3. MEC
4. CEIP
'''
stream_list = ["MPC","BiPC","MEC","CEIP"]

stream_inp_stat = "Choose one of the these streams (by typing the number): "

select_stu_query = "SELECT * FROM student;"

insert_sub_query = '''INSERT INTO subject
VALUES
("301","English"),
("041","Maths"),
("042","Physics"),
("043","Chemistry"),
("065","Informatics Practices"),
("055","Accountancy"),
("030","Economics"),
("054","Business Studies"),
("044","Biology");
'''

select_sub_query = "SELECT * FROM subject;"

exam_tbl = "We request you to insert data in 'exam' table using MySQL."

#######################################################################

menu = '''Choose one of the options by typing the number:
1. See the marks of the student
2. See the total marks and percentage of each test of the student
3. See graphs/charts made using the marks of the student
'''

Q1 = "SELECT * FROM student;"

marks_query = """
              SELECT
                exam.test Test,
                exam.sub_code 'Code',
                subject.name Subject,
                exam.marks Marks
              FROM exam
              LEFT JOIN subject ON exam.sub_code = subject.sub_code
              WHERE roll_no=%s
              ORDER BY exam.test,exam.sub_code ASC;
              """

total_query = '''
SELECT test Test, SUM(marks) Total, SUM(marks)/250*100 "Percentage (%)"
FROM exam
WHERE roll_no=%s
GROUP BY test
ORDER BY test ASC;
'''

plot_menu = '''Choose one of the options by typing the number:
1. Bar graph
2. Line graph
'''

mpc_query = '''SELECT * FROM student WHERE stream="MPC";'''

bipc_query = '''SELECT * FROM student WHERE stream="BiPC";'''

mec_query = '''SELECT * FROM student WHERE stream="MEC";'''

ceip_query = '''SELECT * FROM student WHERE stream="CEIP";'''

bar_query = """
            SELECT exam.test, exam.sub_code, subject.name, exam.marks
            FROM exam
            LEFT JOIN subject ON exam.sub_code = subject.sub_code
            WHERE exam.roll_no=%s
            ORDER BY exam.test,exam.sub_code ASC;
            """
line_query = """
            SELECT exam.test, exam.sub_code, subject.name, exam.marks
            FROM exam
            LEFT JOIN subject ON exam.sub_code = subject.sub_code
            WHERE exam.roll_no=%s
            ORDER BY exam.sub_code,exam.test ASC;
            """
            
uni_subnames = """
              SELECT DISTINCT subject.name
              FROM exam
              LEFT JOIN subject ON exam.sub_code = subject.sub_code
              WHERE exam.roll_no=%s
              ORDER BY exam.sub_code,exam.test ASC;
              """

########################################################################

# Welcoming user
print("Hello! Welcome to the report card management software.")
print()
print("""Do you have a MySQL database which has the marks data of students?
       
Press y or n.""")

print()
database_inp = input()
print()

if database_inp == "n":
    print()
    print("OK. Let's create a database for you.")
    print()
    print("Firstly, let's connect to MySQL.")
    db_cnnt = connect(
                  host="localhost",
                  user=input("Enter username: "),
                  password=getpass("Enter password: "))

    #Cursor
    db_cursor = db_cnnt.cursor()
    
    db_cursor.execute(create_db_query)
    
    print()
    print("We have created a database named 'ip_project' for you.")
    print()
    
    db_cursor.execute(use_db_query)
    
    db_cursor.execute(crt_examtbl_query)
    db_cursor.execute(crt_stutbl_query)
    db_cursor.execute(crt_subtbl_query)
    
    print("We have also created three tables for you. The tables are 'exam', 'student' and 'subject'.")
    print()
    print(tbl_structure)
    print()
    print("Now let's insert data in 'student' table.")
    stu_no = int(input("How many rows do you want to add in student table (type number)? "))
    main_l = []

    for i in range(stu_no):
        c1 = int(input("Enter roll number of the student: "))
        c2 = input("Enter name of the student: ")
        print(stream_inp_stat, stream_human_lst)
        print("For example: Type 1 for MPC.")
        print()
        stream_choice = int(input())

        if stream_choice == 1:
            c3 = stream_list[0]
        elif stream_choice == 2:
            c3 = stream_list[1]
        elif stream_choice == 3:
            c3 = stream_list[2]
        elif stream_choice == 4:
            c3 = stream_list[3]
        
        l1 = [c1,c2,c3]
        t1 = tuple(l1)

        main_l.append(t1)
    
    db_cursor.executemany(insert_stu_query,main_l)
    db_cnnt.commit()
    
    print()
    db_cursor.execute(select_stu_query)
    stu_sql_tbl = pd.DataFrame(db_cursor.fetchall(),
                        columns=[item[0] for item in db_cursor.description])
    print("This is the 'student' table:")
    print()
    print(stu_sql_tbl.to_markdown(index = False, tablefmt="grid"))
    
    db_cursor.execute(insert_sub_query)
    db_cnnt.commit()
    
    db_cursor.execute(select_sub_query)
    sub_sql_tbl = pd.DataFrame(db_cursor.fetchall(),
                        columns=[item[0] for item in db_cursor.description])
    print()
    print("We have inserted the data in 'subject' table. This is the subject table:")
    print()
    print(sub_sql_tbl.to_markdown(index = False, tablefmt="grid"))
    
    print()
    print(exam_tbl)
    print("After inserting data in 'exam' table through MySQL, you can see other things like total and graphs in this program.")
    
    db_cursor.close()
    db_cnnt.close()

elif database_inp == "y":
    print("Let's connect to the MySQL database!")
    db_cnnt = connect(
                  host="localhost",
                  user=input("Enter username: "),
                  password=getpass("Enter password: "),
                  database="ip_project")

    #print(db_cnnt)

    #Cursor
    db_cursor = db_cnnt.cursor()

    #Getting basic details of all students
    db_cursor.execute(Q1)
    
    stu_table = pd.DataFrame(db_cursor.fetchall(),
                             columns=[item[0] for item in db_cursor.description],
                             index = range(1,21))
    #Taking input
    while True:
        try:
            inp = int(input("Please type the roll number of the student whose details you would like to see: "))
        except ValueError:
            print("Please type a valid input.")
            continue
        else:
            break

    print()
    print(menu)
    menu_inp = int(input())
    
    if menu_inp == 1:
    
        print()   
        db_cursor.execute(marks_query, (inp,))
    
        marks_df = pd.DataFrame(db_cursor.fetchall(),
                                columns=[item[0] for item in db_cursor.description])
        marks_df1 = marks_df
        marks_df1.loc[marks_df1['Test'].duplicated(), 'Test'] = ''

        if marks_df1["Marks"].isnull().values.any() == True:
            marks_df1["Marks"] = marks_df1["Marks"].fillna("Absent")

        marks_col = marks_df1.columns
        marks_val = marks_df1.values.tolist()

        tableObj = texttable.Texttable()
        tableObj.set_cols_align(["l", "l", "l", "r"])
        tableObj.set_cols_dtype(["t", "t", "t", "a"])
        tableObj.set_cols_valign(["m", "m", "m", "m"])

        tableObj.header(marks_col)
        tableObj.add_rows(marks_val,header=False)

        print("Name of student:", stu_table.loc[inp,"name"])
        print("Grade: XII")
        print("Stream:", stu_table.loc[inp,"stream"])
        print()
        print(tableObj.draw())
        print()


    ##############################################################################
    
    elif menu_inp==2:
        db_cursor.execute(total_query, (inp,))
    
        total_df = pd.DataFrame(db_cursor.fetchall(),
                                columns=[item[0] for item in db_cursor.description])
        total_col = total_df.columns
        total_val = total_df.values.tolist()
    
        tableObj = texttable.Texttable()
        tableObj.set_cols_align(["l", "l", "l"])
        tableObj.set_cols_dtype(["t", "i", "f"])
        tableObj.set_cols_valign(["l", "m", "m"])
    
        tableObj.header(total_col)
        tableObj.add_rows(total_val,header=False)
        print(tableObj.draw())

    ####################################################### 
    
    elif menu_inp==3:
        print(plot_menu)
        plot_menu_inp = int(input())
        
        if plot_menu_inp == 1:
            db_cursor.execute(bar_query, (inp,))
        
            bar_df = pd.DataFrame(db_cursor.fetchall(),
                                  columns=[item[0] for item in db_cursor.description])
        
            #print(bar_df)
        
            ########################################################
        
            m1_values = bar_df.loc[0:4, 'marks'].to_list()
            m2_values = bar_df.loc[5:9, 'marks'].to_list()
            m3_values = bar_df.loc[10:14, 'marks'].to_list()
            sub_list = bar_df.loc[0:4, 'name'].to_list()
        
            #########################################################
            
            db_cursor.execute(mpc_query)
            mpc_df = pd.DataFrame(db_cursor.fetchall(),
                                  columns=[item[0] for item in db_cursor.description])
            mpc_list = mpc_df.roll_no.to_list()
        
            db_cursor.execute(bipc_query)
            bipc_df = pd.DataFrame(db_cursor.fetchall(),
                                  columns=[item[0] for item in db_cursor.description])
            bipc_list = bipc_df.roll_no.to_list()
        
            db_cursor.execute(mec_query)
            mec_df = pd.DataFrame(db_cursor.fetchall(),
                                  columns=[item[0] for item in db_cursor.description])
            mec_list = mec_df.roll_no.to_list()
        
            db_cursor.execute(ceip_query)
            ceip_df = pd.DataFrame(db_cursor.fetchall(),
                                  columns=[item[0] for item in db_cursor.description])
            ceip_list = ceip_df.roll_no.to_list()
        
        
            if inp in mpc_list or inp in bipc_list:
                sub_list[3]="IP"
            elif inp in mec_list:
                sub_list[2]="B.St."
                sub_list[3]="Acct."
            elif inp in ceip_list:
                sub_list[1]="B.St."  
                sub_list[2]="Acct."
                sub_list[3]="IP"
        
            #print(sub_list)
            #########################################################

            N = len(sub_list)
            ind = np.arange(N) 
            width = 0.25
        
            plt.grid(axis='y', zorder=0)
        
            plt.bar(ind, m1_values, width, label="MT1", color='xkcd:pink', zorder=3)
            plt.bar(ind+width, m2_values, width, label="MT2", color='xkcd:light blue', zorder=3)
            plt.bar(ind+width*2, m3_values, width, label="MT3", color='xkcd:mint', zorder=3)
        
            plt.xticks(ind+width,sub_list)
            plt.yticks(np.arange(0, 51, 5))
        
            plt.legend(loc=(1.05, 0.5))
            plt.tight_layout()
            plt.show() 
    
        #####################################################################
        elif plot_menu_inp == 2:
            db_cursor.execute(line_query, (inp,))
        
            line_df = pd.DataFrame(db_cursor.fetchall(),
                                  columns=[item[0] for item in db_cursor.description])
        
            #print(line_df)
        
            db_cursor.execute(uni_subnames, (inp,))
        
            uni_subnames_df = pd.DataFrame(db_cursor.fetchall(),
                                           columns=[item[0] for item in db_cursor.description])
        
            #print(uni_subnames_df)
        
            uni_sub_list = uni_subnames_df.name.to_list()
        
            mt_list = ["MT1","MT2","MT3"]
        
            ########################################################
        
            sub1_marks = line_df.loc[0:2, 'marks'].to_list()
            sub2_marks = line_df.loc[3:5, 'marks'].to_list()
            sub3_marks = line_df.loc[6:8, 'marks'].to_list()
            sub4_marks = line_df.loc[9:11, 'marks'].to_list()
            sub5_marks = line_df.loc[12:14, 'marks'].to_list()
        
            plt.grid()
            plt.plot(sub1_marks, label=uni_sub_list[0], marker="s")
            plt.plot(sub2_marks, label=uni_sub_list[1], marker="s")
            plt.plot(sub3_marks, label=uni_sub_list[2], marker="s")
            plt.plot(sub4_marks, label=uni_sub_list[3], marker="s")
            plt.plot(sub5_marks, label=uni_sub_list[4], marker="s")
        
            plt.xticks(np.arange(0,3),mt_list)
            plt.legend(loc=(1.05, 0.5))
            plt.xlabel(r"Monthly Test $\rightarrow$")
            plt.ylabel(r"Marks $\rightarrow$")
            plt.tight_layout()
            plt.show()

    #######################################################

    db_cursor.close()
    db_cnnt.close()

input("Program complete. Press any key to exit.")

SQL queries used to create the database and insert data:

-- Creating database

CREATE DATABASE IF NOT EXISTS ip_project;
USE ip_project;

-- Creating tables:
CREATE TABLE IF NOT EXISTS `exam` (
  `sub_code` varchar(3) NOT NULL,
  `roll_no` int NOT NULL,
  `marks` int DEFAULT NULL,
  `test` varchar(3) NOT NULL
);

CREATE TABLE IF NOT EXISTS `student` (
  `roll_no` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `stream` varchar(5) NOT NULL,
  PRIMARY KEY (`roll_no`)
);

CREATE TABLE IF NOT EXISTS `subject` (
  `sub_code` varchar(3) NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`sub_code`)
);



-- Inserting data

-- student table

INSERT INTO Student
VALUES (1,"Abhinash","MPC"),
(2,"Aditi","MEC"),
(3,"Alekhya","BiPC"),
(4,"Amit","CEIP"),
(5,"Anuhitha","BiPC"),
(6,"Hari","MPC"),
(7,"Jay","MEC"),
(8,"Kiran","CEIP"),
(9,"Madhav","CEIP"),
(10,"Manohar","MPC"),
(11,"Manoj","CEIP"),
(12,"Neha","MEC"),
(13,"Pawan","MEC"),
(14,"Ravi","MPC"),
(15,"Ritwik","CEIP"),
(16,"Samraddhi","BiPC"),
(17,"Smita","MPC"),
(18,"Swathi","MEC"),
(19,"Veena","MPC"),
(20,"Yogesh","MPC");

-- subject table
INSERT INTO subject
VALUES
("301", "English"),
("041","Maths"),
("042","Physics"),
("043","Chemistry"),
("065","Informatics Practices"),
("055","Accountancy"),
("030","Economics"),
("054","Business Studies"),
("044","Biology");


-- exam table
-- MT1

-- Accountancy:
INSERT INTO exam
VALUES
("055", 2, 32, "MT1"),
("055", 4, 39, "MT1"),
("055", 7, 42, "MT1"),
("055", 8, 40, "MT1"),
("055", 9, NULL, "MT1"),
("055", 11, 40, "MT1"),
("055", 12, 39, "MT1"),
("055", 13, 29, "MT1"),
("055", 15, 42, "MT1"),
("055", 18, 45, "MT1");

-- Maths:
INSERT INTO exam
VALUES
("041", 1 , 29, "MT1"),
("041", 2, 43, "MT1" ),
("041", 6, 37, "MT1"),
("041", 7, 33, "MT1"),
("041", 10, 40, "MT1"),
("041", 12, 44, "MT1"),
("041", 13, 23, "MT1"),
("041", 14, 25, "MT1"),
("041", 17, 30, "MT1"),
("041", 18, 39, "MT1"),
("041", 19, 27, "MT1"),
("041", 20, 28, "MT1");

-- IP:
INSERT INTO exam
VALUES
("065", 1, 46, "MT1"),
("065" ,3, 39, "MT1"),
("065", 4, 39, "MT1"),
("065", 5, 35, "MT1"),
("065", 6, 44, "MT1"),
("065", 8, 41, "MT1"),
("065", 9, 49, "MT1"),
("065", 10, 46, "MT1"),
("065", 11, 40, "MT1"),
("065", 14, 29, "MT1"),
("065", 15, 35, "MT1"),
("065", 16, 38, "MT1"),
("065", 17, 44, "MT1"),
("065", 19, 30, "MT1"),
("065", 20, 30, "MT1");

-- Eco:
INSERT INTO exam
VALUES
("030", 2, 37, "MT1"),
("030", 4, 44 , "MT1"),
("030", 7, 35, "MT1"),
("030", 8, 38, "MT1"),
("030", 9, 34, "MT1"),
("030", 11, 39, "MT1"),
("030", 12, 43, "MT1"),
("030", 13, 35, "MT1"),
("030", 15, 40, "MT1"),
("030", 18, 35, "MT1");

-- BST:
INSERT INTO exam
VALUES
("054", 2, 34, "MT1"),
("054", 4, 47, "MT1"), 
("054", 7, 26, "MT1"),
("054", 8, 37, "MT1"),
("054", 9 , 34, "MT1"),
("054", 11, 37, "MT1"),
("054", 12, 41, "MT1"),
("054", 13, 38, "MT1"),
("054",15 , 35, "MT1"),
("054",18 , 29, "MT1");

-- Chem:
INSERT INTO exam
VALUES
("043", 1, 42, "MT1"),
("043", 3, 37, "MT1"),
("043", 5, 42, "MT1"),
("043", 6, 42, "MT1"),
("043", 10, 30, "MT1"),
("043", 14, NULL, "MT1"),
("043", 16, 35, "MT1"),
("043", 17, 29, "MT1"),
("043", 19, 28, "MT1"),
("043", 20, 30, "MT1");

-- Bio:
INSERT INTO exam
VALUES
("044",3,29,"MT1"),
("044",5,39,"MT1"),
("044",16,43,"MT1");

-- Physics:
INSERT INTO exam
VALUES
("042",1,36,"MT1"),
("042",3,34,"MT1"),
("042",5,40,"MT1"),
("042",6,39,"MT1"),
("042",10,37,"MT1"),
("042",14,32,"MT1"),
("042",16,39,"MT1"),
("042",17,38,"MT1"),
("042",19,45,"MT1"),
("042",20,34,"MT1");

-- English:
INSERT INTO exam
VALUES
("301", 1, 38, "MT1"),
("301", 2, 45, "MT1"),
("301", 3, 40, "MT1"),
("301", 4, 44, "MT1"),
("301", 5, 44, "MT1"),
("301", 6, 40, "MT1"),
("301", 7, 37, "MT1"),
("301", 8, 39, "MT1"),
("301", 9, 29, "MT1"),
("301", 10, 43, "MT1"),
("301", 11, 44, "MT1"),
("301", 12, 40, "MT1"),
("301", 13, 28, "MT1"),
("301", 14, 21, "MT1"),
("301", 15, 40, "MT1"),
("301", 16, 39, "MT1"),
("301", 17, 47, "MT1"),
("301", 18, 35, "MT1"),
("301", 19, 39, "MT1"),
("301", 20, 37, "MT1");


-- MT2

-- Maths:
INSERT INTO exam
VALUES
("041", 1 , 37, "MT2"),
("041", 2, 44, "MT2" ),
("041", 6, 41, "MT2"),
("041", 7, 38, "MT2"),
("041", 10, 43, "MT2"),
("041", 12, 44, "MT2"),
("041", 13, 30, "MT2"),
("041", 14, 28, "MT2"),
("041", 17, 35, "MT2"),
("041", 18, 40, "MT2"),
("041", 19, 39, "MT2"),
("041", 20, 29, "MT2");

-- Chem:
INSERT INTO exam
VALUES
("043", 1, 45, "MT2"),
("043", 3, 43, "MT2"),
("043", 5, 39, "MT2"),
("043", 6, 39, "MT2"),
("043", 10, 25, "MT2"),
("043", 14, 38, "MT2"),
("043", 16, 44, "MT2"),
("043", 17, 37, "MT2"),
("043", 19, 37, "MT2"),
("043", 20, 35, "MT2");

 -- BST:
INSERT INTO exam
VALUES
("054", 2, 40 , "MT2"),
("054", 4, 45, "MT2"), 
("054", 7, 38, "MT2"),
("054", 8, 43, "MT2"),
("054", 9 , 32, "MT2"),
("054", 11, 39, "MT2"),
("054", 12, 38, "MT2"),
("054", 13, 33, "MT2"),
("054", 15 , 42, "MT2"),
("054", 18 , 38, "MT2");

-- Eco:
INSERT INTO exam
VALUES
("030", 2, 38, "MT2"),
("030", 4, 39 , "MT2"),
("030", 7, 30, "MT2"),
("030", 8, 40, "MT2"),
("030", 9, 44, "MT2"),
("030", 11, 40, "MT2"),
("030", 12, 40, "MT2"),
("030", 13, 35, "MT2"),
("030", 15, 38, "MT2"),
("030", 18, 37, "MT2");

-- IP
INSERT INTO exam
VALUES
("065", 1, 48, "MT2"),
("065" ,3, 42, "MT2"),
("065", 4, 44, "MT2"),
("065", 5, 40, "MT2"),
("065", 6, 38, "MT2"),
("065", 8, 38, "MT2"),
("065", 9, 42, "MT2"),
("065",10, 33, "MT2"),
("065", 11, 43, "MT2"),
("065", 14, 33, "MT2"),
("065", 15, 40, "MT2"),
("065", 16, 42, "MT2"),
("065", 17, 40, "MT2"),
("065", 19, 39, "MT2"),
("065", 20, 37, "MT2");

-- Phy:
INSERT INTO exam
VALUES
("042", 1, 43, "MT2"),
("042", 3, 35, "MT2"),
("042", 5, 42, "MT2"),
("042", 6, 42, "MT2"),
("042", 10, 19, "MT2"),
("042", 14, 22, "MT2"),
("042", 16, 41, "MT2"),
("042", 17, 43, "MT2"),
("042", 19, 28, "MT2"),
("042", 20, 39, "MT2");


-- Accountancy
INSERT INTO exam
VALUES
("055", 2, 29, "MT2"),
("055", 4, 41, "MT2"),
("055", 7, 40, "MT2"),
("055", 8, 39, "MT2"),
("055", 9, 43, "MT2"),
("055", 11, 37, "MT2"),
("055", 12, 41, "MT2"),
("055", 13, 28, "MT2"),
("055", 15, 47, "MT2"),
("055", 18, 37, "MT2");

-- English:
INSERT INTO exam
VALUES
("301", 1, 40, "MT2"),
("301", 2, 45, "MT2"),
("301", 3, 49, "MT2"),
("301", 4, 45, "MT2"),
("301", 5, 41, "MT2"),
("301", 6, 44, "MT2"),
("301", 7, 40, "MT2"),
("301", 8, 44, "MT2"),
("301", 9, 33, "MT2"),
("301", 10, 40, "MT2"),
("301", 11, 40, "MT2"),
("301", 12, 37, "MT2"),
("301", 13, 31, "MT2"),
("301", 14, 26, "MT2"),
("301", 15, 33, "MT2"),
("301", 16, 42, "MT2"),
("301", 17, 39, "MT2"),
("301", 18, 41, "MT2"),
("301", 19, 41, "MT2"),
("301", 20, NULL, "MT2");

-- Bio
INSERT INTO exam
VALUES
("044",3,33,"MT2"),
("044",5,40,"MT2"),
("044",16,NULL,"MT2");

-- MT3

-- Maths:
INSERT INTO exam
VALUES
("041", 1 , 42, "MT3"),
("041", 2, 37, "MT3" ),
("041", 6, 43, "MT3"),
("041", 7, 42, "MT3"),
("041", 10, 36, "MT3"),
("041", 12, 40, "MT3"),
("041", 13, 45, "MT3"),
("041", 14, 40, "MT3"),
("041", 17, 40, "MT3"),
("041", 18, 44, "MT3"),
("041", 19, 42, "MT3"),
("041", 20, 35, "MT3");

-- Chem:
INSERT INTO exam
VALUES
("043", 1, 37, "MT3"),
("043", 3, 44, "MT3"),
("043", 5, 43, "MT3"),
("043", 6, 41, "MT3"),
("043", 10, 28, "MT3"),
("043", 14, 40, "MT3"),
("043", 16, 38, "MT3"),
("043", 17, 43, "MT3"),
("043", 19, 42, "MT3"),
("043", 20, 39, "MT3");

-- Eco:
INSERT INTO exam
VALUES
("030", 2, 33, "MT3"),
("030", 4, 40 , "MT3"),
("030", 7, NULL, "MT3"),
("030", 8, 43, "MT3"),
("030", 9, 45, "MT3"),
("030", 11, 42, "MT3"),
("030", 12, 39, "MT3"),
("030", 13, 43, "MT3"),
("030", 15, 45, "MT3"),
("030", 18, 42, "MT3");

-- IP
INSERT INTO exam
VALUES
("065", 1, 45, "MT3"),
("065" ,3, 47, "MT3"),
("065", 4, 42, "MT3"),
("065", 5, 39, "MT3"),
("065", 6, 40, "MT3"),
("065", 8, 40, "MT3"),
("065", 9, 48, "MT3"),
("065",10, 30, "MT3"),
("065", 11, NULL, "MT3"),
("065", 14, 42, "MT3"),
("065", 15, 42, "MT3"),
("065", 16, 39, "MT3"),
("065", 17, 39, "MT3"),
("065", 19, 43, "MT3"),
("065", 20, 40, "MT3");

-- BST
INSERT INTO exam
VALUES
("054", 2, 42 , "MT3"),
("054", 4, 44, "MT3"),
("054", 7, 40, "MT3"),
("054", 8, 36, "MT3"),
("054", 9 , 42, "MT3"),
("054", 11, 45, "MT3"),
("054", 12, 45, "MT3"),
("054", 13, 40, "MT3"),
("054", 15 , 46, "MT3"),
("054", 18 , 42, "MT3");

-- Eng:
INSERT INTO exam
VALUES
("301", 1, 45, "MT3"),
("301", 2, 43, "MT3"),
("301", 3, 46, "MT3"),
("301", 4, 40, "MT3"),
("301", 5, 45, "MT3"),
("301", 6, 45, "MT3"),
("301", 7, 44, "MT3"),
("301", 8, 47, "MT3"),
("301", 9, 42, "MT3"),
("301", 10, 30, "MT3"),
("301", 11, 47, "MT3"),
("301", 12, 42, "MT3"),
("301", 13, 42, "MT3"),
("301", 14, 38, "MT3"),
("301", 15, 45, "MT3"),
("301", 16, 44, "MT3"),
("301", 17, 42, "MT3"),
("301", 18, 45, "MT3"),
("301", 19, 45, "MT3"),
("301", 20, 44, "MT3");

-- Bio:
INSERT INTO exam
VALUES
("044", 3, 40, "MT3"),
("044", 5, 42,"MT3"),
("044", 16, 40,"MT3");

-- Accountancy
INSERT INTO exam
VALUES
("055", 2, 41, "MT3"),
("055", 4, 45, "MT3"),
("055", 7, 45, "MT3"),
("055", 8, 43, "MT3"),
("055", 9, 47, "MT3"),
("055", 11, 42, "MT3"),
("055", 12, 44, "MT3"),
("055", 13, 36, "MT3"),
("055", 15, 45, "MT3"),
("055", 18, 41, "MT3");

-- Physics:
INSERT INTO exam
VALUES
("042", 1, 40, "MT3"),
("042", 3, 40, "MT3"),
("042", 5, 39, "MT3"),
("042", 6, 40, "MT3"),
("042", 10, 27, "MT3"),
("042", 14, 37, "MT3"),
("042", 16, 40, "MT3"),
("042", 17, 37, "MT3"),
("042", 19, 41, "MT3"),
("042", 20, 43, "MT3");

The Python program was made for my project. I am a Python and MySQL beginner.

Are there any chances of SQL injections?

Is my method of calculating sum directly in MySQL better than importing it to pandas and finding the total? Any opinion is welcome :)

\$\endgroup\$
3
  • \$\begingroup\$ Why not use object-relational mapping (ORM) ? \$\endgroup\$
    – Kevin
    Nov 28, 2021 at 19:34
  • 1
    \$\begingroup\$ @Kevin It's an option, but it's not crazy to avoid ORMs. Particularly as a beginner learning about SQL syntax. \$\endgroup\$
    – Reinderien
    Nov 29, 2021 at 14:34
  • \$\begingroup\$ @Kevin I agree with you. Also, my teachers haven't taught me anything about ORM 😅 \$\endgroup\$ Nov 29, 2021 at 20:10

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.