Student report card management Python program

Question

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

• Kevin: Why not use object-relational mapping (ORM) ?
• Reinderien: @Kevin It's an option, but it's not crazy to avoid ORMs. Particularly as a beginner learning about SQL syntax.
• Random Person: @Kevin I agree with you. Also, my teachers haven't taught me anything about ORM 😅

Answer 1

Move all of your code into functions.

This stage:

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.")

isn't generally how "real life" works. Applications will generally have a fully-separated setup stage either defined in pure SQL scripts or with some kind of database schema versioning library. It's understandable since this is a student project, but to achieve more realism, fully split away the setup, and write the application to assume that the database has already been created.

Since exam etc. are unambiguous, valid names, there is no need to surround them in backticks and that's just noise. Read keywords for more information.

This:

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 | |
...
'''
 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.")

again is non-realistic as it exposes more implementation details to the user than what's appropriate. The user shouldn't need to see database details to be able to operate the application interface. So, for example, How many rows do you want to add in student table (type number)? should just become How many students do you want to add?

db_cnnt is unprotected and does not receive a guarantee of closure if something goes wrong. To fix that, use context management. Formal documentation for the Python connector is in connector-python-reference, but since I can't find what I'm looking for there, the source code shows that the connection object is a context manager.

This query:

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

being multiply sent to executemany should be reduced to a single insert values statement with one tuple per new row.

Rather than pd.DataFrame(db_cursor.fetchall(), make appropriate use of read_sql.

• Random Person: Well it's been so long since I posted. Anyway, thanks for the suggestions!