Database Connectivity in Python
Before starting Database Connectivity first install these packages using CMD prompt window
pip install mysql-connector
pip install mysql-connector-python
pip install pymysql
pip install sqlalchemy
pip install tabulate
pip3 install mysql-connector-python
Project - Gems stone
import mysql.connector
from tabulate import tabulate
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="wecan123",
database="stones"
)
mycursor = mydb.cursor()
def StonesManufacturing():
def inputNumber(message):
while True:
try:
userInput = int(input(message))
except ValueError:
print("Only Integer in Mobile No.")
continue
else:
return userInput
break
CuttingType = input("Enter Cutting Type :")
PolishType = input("Enter Polish Type :")
ManufactureDate = input("Enter Manufacture date(dd-mm-yyyy) :")
#After Getting date from user side we have to convert date in YYYY-MM-DD format because SQL can accept date in YYYY-MM-DD format only
changeFORMAT = ManufactureDate[6:] + "-" + ManufactureDate[3:5] + "-" + ManufactureDate[:2]
ShapeType = input('Enter Shape :')
ColorType = input("Enter Color :")
SizeType = input("Enter Size :")
if CuttingType.isdigit():
print('Integer are not allowed in Cutting Type')
elif PolishType.isdigit() :
print("Integer are not allowed in Polish Type")
elif ShapeType.isdigit():
print("Integers are not allowed in Polish Type")
elif ColorType.isdigit():
print("Integers are not allowed in Color Type")
else:
sql = "INSERT INTO manufacturing(cuttingtype,polishtype,manufacturedate,shapetype,colortype,sizetype)VALUES(%s,%s,%s,%s,%s,%s)"
val = (CuttingType,PolishType,changeFORMAT,ShapeType,ColorType,SizeType)
mycursor.execute(sql, val)
mydb.commit()
print("Data entered successfully")
main()
def ShowStonesManufacturing():
mycursor.execute("SELECT * FROM manufacturing")
myresult = mycursor.fetchall()
"""
#This code is used when we want to show the result not in Tabular format , this code is right
for x in myresult:
print(x)
"""
print(tabulate(myresult, headers=['Manufacture ID','Cutting Type','Polish Type','Manufacture Date','Shape type','Color Type','Shape Type'], tablefmt='psql'))
mydb.commit()
main()
def StonePurchasing():
StoneQuality = input('Enter Stone Quality :')
StoneWeight = input('Enter Stone Weight ;')
PurchaseDate = input("Enter Manufacture date(dd-mm-yyyy) :")
#After Getting date from user side we have to convert date in YYYY-MM-DD format because SQL can accept date in YYYY-MM-DD format only
changeFORMAT = PurchaseDate[6:] + "-" + PurchaseDate[3:5] + "-" + PurchaseDate[:2]
Size = int(input('Enter size '))
Color = input("Enter Color :")
InsertMarks_Query = "INSERT INTO stonepurchasing(stonequality,stoneweight,purchasedate,colortype,sizetype)VALUES(%s,%s,%s,%s,%s)"
InsertMarksval = (StoneQuality,StoneWeight,changeFORMAT,Size,Color)
mycursor.execute(InsertMarks_Query, InsertMarksval)
print('Data Entered Successfully...')
mydb.commit()
main()
def ShowStonesPurchasing():
mycursor.execute("SELECT * FROM stonepurchasing")
myresult = mycursor.fetchall()
"""
#This code is used when we want to show the result not in Tabular format , this code is right
for x in myresult:
print(x)
"""
print(tabulate(myresult, headers=['Stone ID','Stone Quality','Purchase Date','Color Type','Size Type'], tablefmt='psql'))
mydb.commit()
main()
def StoneSelling():
PricePercarrot = input('Enter Price Per Carrot :')
ShapeType = input('Enter Shape Type :')
QualityType = input('Enter Quality :')
SellDate = input("Enter Selling date(dd-mm-yyyy) :")
#After Getting date from user side we have to convert date in YYYY-MM-DD format because SQL can accept date in YYYY-MM-DD format only
changeFORMAT = SellDate[6:] + "-" + SellDate[3:5] + "-" + SellDate[:2]
SizeType = int(input('Enter Size :'))
ColorType = input("Enter Color Type :")
InsertMarks_Query = "INSERT INTO stoneselling(pricepercarot,shape,quality,sellingdate,size,color)VALUES(%s,%s,%s,%s,%s,%s)"
InsertMarksval = (PricePercarrot,ShapeType,QualityType,changeFORMAT,SizeType,ColorType)
mycursor.execute(InsertMarks_Query, InsertMarksval)
print('Date Entered Successfully..')
mydb.commit()
main()
def ShowStoneSelling():
mycursor.execute("SELECT * FROM stoneselling")
myresult = mycursor.fetchall()
"""
#This code is used when we want to show the result not in Tabular format , this code is right
for x in myresult:
print(x)
"""
print(tabulate(myresult, headers=['Stone selling ID','Price per Carrot','Shape','Quality', 'Selling Date','Size','Color'], tablefmt='psql'))
mydb.commit()
main()
def main():
print("""
STONES IMPORT & EXPORT MANAGEMENT SYSTEM
1. STONES MANUFACTURING
2. SHOW STONES MANUFACTURING
3. STONES PURCHASING
4. SHOW STONE PURCHASING
5. STONE SELLING
6. SHOW STONE SELLING
""")
choice=input("Enter Your Choice :")
while True:
if(choice=='1'):
StonesManufacturing()
elif(choice=='2'):
ShowStonesManufacturing()
elif(choice=='3'):
StonePurchasing()
elif(choice=='4'):
ShowStonesPurchasing()
elif(choice=='5'):
StoneSelling()
elif(choice=='6'):
ShowStoneSelling()
else:
print("Invalid Entry.....")
main()
main()
Project - Student Registration
import mysql.connector
from tabulate import tabulate
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="school"
)
mycursor = mydb.cursor()
def StudentRegistration():
def inputNumber(message):
while True:
try:
userInput = int(input(message))
except ValueError:
print("Only Integer in Mobile No.")
continue
else:
return userInput
break
StudentName = input("Enter Student Name :")
FatherName = input("Enter Father Name :")
Class = input("Enter Class :")
DOB = input("Enter Date of Birth(dd-mm-yyyy) :")
#After Getting date from user side we have to convert date in YYYY-MM-DD format because SQL can accept date in YYYY-MM-DD format only
changeFORMAT = DOB[6:] + "-" + DOB[3:5] + "-" + DOB[:2]
Aadharno = input("Enter Aadhar No. :")
MobileNo = inputNumber("Enter Your Mobile Number")
Address = input("Enter Address :")
genderInput = input("Enter 'M' for Male or 'F' for Female\n").upper()
if genderInput == "M":
gender = "Male"
#print(gender)
elif genderInput == "F":
gender= "Female"
if StudentName.isdigit():
print('Integer are not allowed on Student Name')
elif genderInput != "M" and genderInput != "F" :
print("Invalid Entry for Gender")
else:
sql = "INSERT INTO studentregistration(studentname,fathername,class,dateofbirth,aadharno,mobileno,address,gender)VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
val = (StudentName,FatherName,Class,changeFORMAT,Aadharno,MobileNo,Address,gender)
mycursor.execute(sql, val)
mydb.commit()
print("Data entered successfully")
main()
def ShowStudents():
mycursor.execute("SELECT * FROM studentregistration")
myresult = mycursor.fetchall()
"""
This code is used when we want to show the result not in Tabular format , this code is right
for x in myresult:
print(x)
"""
print(tabulate(myresult, headers=['Student ID','Student Name','Father Name','Class','Date of Birth','Aadhar No','Mobile No','Address','Gender'], tablefmt='psql'))
mydb.commit()
main()
def FirstTermMarks():
StudentID = int(input('Enter StudentID :'))
mycursor.execute("SELECT * FROM studentregistration WHERE studentID = %s",(StudentID,))
mycursor.fetchall()
row_count = mycursor.rowcount
#print("number of affected rows: {}".format(row_count))
if row_count == 0:
print("Invalid ID, Please Try Again...")
else:
Economics_MARKS = int(input('Enter Economics Marks :'))
PoliticalScience_MARKS = int(input('Enter Political Science Marks :'))
ComputerScience_MARKS = int(input('Enter Computer Science Marks :'))
Total_MARKS = Economics_MARKS + PoliticalScience_MARKS + ComputerScience_MARKS
Percentage = (Total_MARKS * 100)/300
Division = ""
if (Percentage>=60):
Division = "I Division"
elif (Percentage>=45 and Percentage<=59):
Division = "II Division"
elif (Percentage>=36 and Percentage<=44):
Division = "III Division"
else:
Division = "Fail.."
InsertMarks_Query = "INSERT INTO firsttermmarks(FK_studentid,Economics,polscience,cs,totalmarks,percentage,division)VALUES(%s,%s,%s,%s,%s,%s,%s)"
InsertMarksval = (StudentID,Economics_MARKS,PoliticalScience_MARKS,ComputerScience_MARKS,Total_MARKS,Percentage,Division)
mycursor.execute(InsertMarks_Query, InsertMarksval)
print('Marks Entered Successfully..')
mydb.commit()
main()
def ShowFirstTermMarks():
StudentID = int(input('Enter StudentID :'))
mycursor.execute("SELECT Economics,polscience,cs,totalmarks,percentage,division FROM firsttermmarks WHERE fk_studentID = %s",(StudentID,))
myresult = mycursor.fetchall()
row_count = mycursor.rowcount
#print("number of affected rows: {}".format(row_count))
if row_count == 0:
print("Invalid ID, Please Try Again...")
else:
print(tabulate(myresult, headers=['Economics','Political Science','Computer Science','Total Marks','Percentage','Division'], tablefmt='psql'))
mydb.commit()
main()
def SecondTermMarks():
StudentID = int(input('Enter StudentID :'))
mycursor.execute("SELECT * FROM studentregistration WHERE studentID = %s",(StudentID,))
mycursor.fetchall()
row_count = mycursor.rowcount
#print("number of affected rows: {}".format(row_count))
if row_count == 0:
print("Invalid ID, Please Try Again...")
else:
Economics_MARKS = int(input('Enter Economics Marks :'))
PoliticalScience_MARKS = int(input('Enter Political Science Marks :'))
ComputerScience_MARKS = int(input('Enter Computer Science Marks :'))
Total_MARKS = Economics_MARKS + PoliticalScience_MARKS + ComputerScience_MARKS
Percentage = (Total_MARKS * 100)/300
Division = ""
if (Percentage>=60):
Division = "I Division"
elif (Percentage>=45 and Percentage<=59):
Division = "II Division"
elif (Percentage>=36 and Percentage<=44):
Division = "III Division"
else:
Division = "Fail.."
InsertMarks_Query = "INSERT INTO secondtermmarks(FK_studentid,Economics,polscience,cs,totalmarks,percentage,division)VALUES(%s,%s,%s,%s,%s,%s,%s)"
InsertMarksval = (StudentID,Economics_MARKS,PoliticalScience_MARKS,ComputerScience_MARKS,Total_MARKS,Percentage,Division)
mycursor.execute(InsertMarks_Query, InsertMarksval)
print('Marks Entered Successfully..')
mydb.commit()
main()
def ShowSecondTermMarks():
StudentID = int(input('Enter StudentID :'))
mycursor.execute("SELECT Economics,polscience,cs,totalmarks,percentage,division FROM secondtermmarks WHERE fk_studentID = %s",(StudentID,))
myresult = mycursor.fetchall()
row_count = mycursor.rowcount
#print("number of affected rows: {}".format(row_count))
if row_count == 0:
print("Invalid ID, Please Try Again...")
else:
print(tabulate(myresult, headers=['Economics','Political Science','Computer Science','Total Marks','Percentage','Division'], tablefmt='psql'))
mydb.commit()
main()
def Attendance():
StudentID = int(input('Enter StudentID :'))
mycursor.execute("SELECT * FROM studentregistration WHERE studentID = %s",(StudentID,))
mycursor.fetchall()
row_count = mycursor.rowcount
#print("number of affected rows: {}".format(row_count))
if row_count == 0:
print("Invalid ID, Please Try Again...")
else:
TotalNoofDays = 365
PresentDay = int(input("Enter Number of Present Day :"))
PresentDayInPercentage = (PresentDay *100)/TotalNoofDays
AbsentDayInPercentage = 100 - PresentDayInPercentage
sql = "INSERT INTO attendance(FK_studentID,totalnoofdays,presentday,presentdayinpercentage,absentdayinpercentage)VALUES(%s,%s,%s,%s,%s)"
val = (StudentID,TotalNoofDays,PresentDay,PresentDayInPercentage,AbsentDayInPercentage)
mycursor.execute(sql, val)
mydb.commit()
print("Data entered successfully")
main()
def ShowAttendance():
StudentID = int(input('Enter StudentID :'))
mycursor.execute("SELECT totalnoofdays,presentday,presentdayinpercentage,absentdayinpercentage FROM attendance WHERE fk_studentID = %s",(StudentID,))
myresult = mycursor.fetchall()
row_count = mycursor.rowcount
#print("number of affected rows: {}".format(row_count))
if row_count == 0:
print("Invalid ID, Please Try Again...")
else:
print(tabulate(myresult, headers=['totalnoofdays','presentday','presentdayinpercentage','absentdayinpercentage'], tablefmt='psql'))
mydb.commit()
main()
def main():
print("""
1. STUDENT REGISTRATION
2. ENTER FIRST TERM MARKS
3. SHOW FIRST TERM MARKS
4. ENTER SECOND TERM MARKS
5. SHOW SECOND TERM MARKS
6. ENTER ATTENDANCE
7. SHOW ATTENDANCE
8. SHOW ALL STUDENTS
""")
choice=input("Enter Your Choice :")
while True:
if(choice=='1'):
StudentRegistration()
elif(choice=='2'):
FirstTermMarks()
elif(choice=='3'):
ShowFirstTermMarks()
elif(choice=='4'):
SecondTermMarks()
elif(choice=='5'):
ShowSecondTermMarks()
elif(choice=='6'):
Attendance()
elif(choice=='7'):
ShowAttendance()
elif(choice=='8'):
ShowStudents()
else:
print("Invalid Entry.....")
main()
main()
