# coding=gbk
import pymysql
def createTable():
try:
con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
password="", db="students",charset="utf8")
print("连接成功")
cursor = con.cursor(pymysql.cursors.DictCursor)
try:
sql = "create table students (sNo varchar(12) primary key,sName varchar(8),sChinese int,sMath int,sEnglish int)"
cursor.execute(sql)
except:
print("表单已存在")
con.close()
except Exception as err:
print(err)
def inputStudent():
sNo = input("sNo=")
sName = input("sName=")
sChinese = input("sChinese=")
sChinese = int(sChinese)
sMath = input("sMath=")
sMath = int(sMath)
sEnglish = input("sEnglish=")
sEnglish = int(sEnglish)
return {"sNo":sNo,"sName":sName,"sChinese":sChinese,"sMath":sMath,"sEnglish":sEnglish}
def insertDateToTable(sNo,sName,sChinese,sMath,sEnglish):
try:
con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
password="", db="students", charset="utf8")
cursor = con.cursor(pymysql.cursors.DictCursor)
try:
sql = "insert into students (sNo,sName,sChinese,sMath,sEnglish) values (%s,%s,%s,%s,%s)"
cursor.execute(sql,[sNo,sName,sChinese,sMath,sEnglish])
print("添加成功")
except Exception as err:
print(err)
con.commit()
con.close()
except Exception as err:
print(err)
def updateDate(sNo,sName,sChinese,sMath,sEnglish):
try:
con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
password="", db="students", charset="utf8")
cursor = con.cursor(pymysql.cursors.DictCursor)
try:
sql = "update students set sName=%s,sChinese=%s,sMath=%s,sEnglish=%s where sNo=%s"
cursor.execute(sql,[sName,sChinese,sMath,sEnglish,sNo])
print("修改成功")
except Exception as err:
print(err)
con.commit()
con.close()
except Exception as err:
print(err)
def DeleteDate(sNo):
try:
con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
password="", db="students", charset="utf8")
cursor = con.cursor(pymysql.cursors.DictCursor)
try:
# cursor.execute("delete from students"):删除表单所有信息
sql = "delete from students where sNo=%s"
cursor.execute(sql,[sNo])
print("删除成功")
except Exception as err:
print(err)
con.commit()
con.close()
except Exception as err:
print(err)
def selectDate():
try:
con = pymysql.connect(host="127.0.0.1", port=3306, user="root",
password="", db="students", charset="utf8")
cursor = con.cursor(pymysql.cursors.DictCursor)
try:
sql = "select * from students order by sNo"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print("%-12s%-12s%-4d%-4d%-4d" %(row["sNo"],row["sName"],row["sChinese"],row["sMath"],row["sEnglish"]))
print("Total:",len(rows))
except Exception as err:
print(err)
con.close()
except Exception as err:
print(err)
while True:
createTable()
print("1.显示记录" )
print("2.增加记录" )
print("3.修改记录" )
print("4.删除记录" )
print("5.退出程序")
s=input("请选择1-5:")
if s=="1":
selectDate()
elif s=="2":
row= inputStudent()
insertDateToTable(row["sNo"], row["sName"], row["sChinese"], row["sMath"], row["sEnglish"])
elif s=="3":
row= inputStudent()
updateDate(row["sNo"],row["sName"],row["sChinese"], row["sMath"], row["sEnglish"])
elif s=="4":
sNo= input("学号:").strip()
DeleteDate(sNo)
elif s=="5":
break
python学生成绩管理系统(基于MySQL数据库)
最新推荐文章于 2023-02-09 08:32:46 发布
这个Python脚本演示了如何使用pymysql库连接到MySQL数据库,执行基本的CRUD操作,包括创建表格、输入学生信息、更新记录、删除记录以及查询所有学生信息。用户可以选择执行不同的操作,例如添加、修改或删除学生数据。
摘要由CSDN通过智能技术生成