这次用python连接Sqlite数据库来实现一个简单的学生成绩管理系统，因为我的SQL Server数据库好像有点问题，python成功执行SQL语句后SQL数据库却没有发生变化，没有存储到数据；但是把这段代码放到别人的电脑上运行却没有问题。好了，话不多说，直接开始吧。
一、创建数据库和表
Sqlite数据库是一款轻型的嵌入式数据库，占用资源极低，数据处理速度较快，是默认集成在python系统中的数据库。无需配置，import导入sqlite即可进行操作，也可以下载相关的Sqlite客户端进行操作。
首先我们来创建两个表，一个是Account(账号登录表)，一个是GradeTable(学生成绩表)。
import sqlite3
conn = sqlite3.connect(database="SQLite3.db")
#如果数据库不存在，则会在当前位置自动创建.
# 创建Account表
sql="""create table Account
( username varchar(20) not null,
password varchar(30) not null)
"""
conn.execute(sql)
conn.commit()
conn.close()
import sqlite3
conn = sqlite3.connect(database="SQLite3.db")
# 创建GradeTable表
sql="""create table GradeTable
(Number varchar(20) not null,
Name varchar(10),
Chinese varchar(10),
Math varchar(10),
English varchar(10) )
"""
conn.execute(sql)
conn.commit()
conn.close()
二、编写程序代码
然后就是源代码了，上面的表创建好后，记得注释掉。这里我创建了两个类，并且对源代码进行了简单的封装。
全部源代码：
import sqlite3
import sys
# conn = sqlite3.connect(database="SQLite3.db")
# # 创建Account表
# sql="""create table Account
# ( username varchar(20) not null,
# password varchar(30) not null)
# """
# conn.execute(sql)
# conn.commit()
# conn.close()
# 创建GradeTable表
# sql="""create table GradeTable
# (Number varchar(20) not null,
# Name varchar(10),
# Chinese varchar(10),
# Math varchar(10),
# English varchar(10) )
# """
# conn.execute(sql)
# conn.commit()
# conn.close()
#账号登录
class Account:
def __init__(self):
__conn = sqlite3.connect(database="SQLite3.db")
self.__conn = __conn
def __Login(self):
print("\n-------------- 1.账号登录 --------------")
self.__cur = self.__conn.cursor()
self.__user = input("请输入账号：").strip()
self.__pwd = input("请输入密码：").strip()
self.__sql = """select count(*) from Account where username=?;"""
self.__result = self.__cur.execute(self.__sql, (self.__user,))
self.__st = self.__result.fetchone()
if self.__user == "" or self.__pwd == "":
print("账号或密码不得为空!\n")
self.__Login()
elif self.__st[0] == False:
while True:
self.__num = input("账号不存在!是否选择注册一个新账号?\n1、是；2、否\n")
if self.__num == "1":
self.__Register()
elif self.__num == "2":
self.Main()
else:
self.__num = input("请重新选择：")
else:
self.__VerifyLogin(self.__user, self.__pwd)
def __VerifyLogin(self, user, pwd):
self.__cur = self.__conn.cursor()
self.__sql = """select password from Account where username=?;"""
self.__result = self.__cur.execute(self.__sql, (self.__user,))
self.__st = self.__result.fetchone()
if self.__st[0] == self.__pwd:
print("账号登录成功!\n")
self.__st = GradeTable()
self.__st.Main2()
else:
print("账号密码错误!\n")
self.__Login()
def __Register(self):
print("\n-------------- 2.账号注册 --------------")
self.__cur = self.__conn.cursor()
self.__user = input("请输入账号：").strip()
self.__pwd = input("请输入密码：").strip()
self.__repwd = input("请确认密码：").strip()
self.__sql = "select count(*) from Account where username=?;"
self.__result = self.__cur.execute(self.__sql, (self.__user,))
self.__st = self.__result.fetchone()
while True:
if self.__user == "":
self.__user = input("账号不得为空!\n请输入账号：").strip()
elif self.__pwd == "":
self.__pwd = input("密码不得为空!\n请输入密码：").strip()
elif self.__repwd == "" or self.__pwd != self.__repwd:
repwd = input("请重新确认密码：").strip()
elif self.__st[0] == True:
print("该账号已存在!请重新注册!")
self.__Register()
else:
self.__SaveRegister(self.__user, self.__pwd)
def __SaveRegister(self, user, pwd):
self.__cur = self.__conn.cursor()
self.__sql = "insert into Account (username,password) values (?,?);"
self.__cur.execute(self.__sql, (self.__user, self.__pwd,))
self.__conn.commit()
print("账号注册成功!\n")
self.Main()
def __Change(self):
print("\n-------------- 3.修改密码 --------------")
self.__cur = self.__conn.cursor()
self.__user = input("请输入账号：").strip()
self.__oldpwd = input("请输入密码：").strip()
self.__newpwd = input("请输入新密码：").strip()
sql = """select count(*) from Account where username=?;"""
self.__result = self.__cur.execute(sql, (self.__user,))
self.__st = self.__result.fetchone()
while True:
if self.__user == "":
self.__user = input("账号不得为空!\n请输入账号：").strip()
elif self.__oldpwd == "":
self.__oldpwd = input("密码不得为空!\n请输入密码：").strip()
elif self.__oldpwd == self.__newpwd or self.__newpwd == "":
newpwd = input("请重新输入新密码：").strip()
elif self.__st[0] == False:
print("账号不存在!\n")
self.Main()
else:
self.__VerifyChange(self.__user, self.__oldpwd, self.__newpwd)
def __VerifyChange(self, user, oldpwd, newpwd):
self.__cur = self.__conn.cursor()
self.__sql = """select password from Account where username=?;"""
self.__result = self.__cur.execute(self.__sql, (user,))
self.__st = self.__result.fetchone()
if self.__st[0] == self.__oldpwd:
self.__SaveChange(self.__user, self.__newpwd)
else:
print("账号密码错误!\n")
self.__Change()
def __SaveChange(self, user, newpwd):
try:
print("账号密码修改成功!\n")
self.__cur = self.__conn.cursor()
self.__sql = """update Account set password=? where username=?;"""
self.__cur.execute(self.__sql, (self.__newpwd, self.__user))
self.__conn.commit()
self.Main()
except:
print("账号密码修改失败!\n")
self.Main()
def __Cancel(self):
print("\n-------------- 4.账号注销 --------------")
self.__cur = self.__conn.cursor()
self.__user = input("请输入账号：").strip()
self.__pwd = input("请输入密码：").strip()
self.__sql = """select count(*) from Account where username=?;"""
self.__result = self.__cur.execute(self.__sql, (self.__user,))
self.__st = self.__result.fetchone()
if self.__user == "" or self.__pwd == "":
print("账号或密码不得为空!\n")
self.__Cancel()
elif self.__st[0] == False:
print("账号不存在!\n")
self.Main()
else:
self.__VerifyCancel(self.__user, self.__pwd)
def __VerifyCancel(self, user, pwd):
self.__cur = self.__conn.cursor()
self.__sql = """select password from Account where username=?;"""
self.__result = self.__cur.execute(self.__sql, (self.__user,))
self.__st = self.__result.fetchone()
if self.__st[0] == self.__pwd:
self.__DeleteCancel(user)
else:
print("账号密码错误!\n")
self.Main()
def __DeleteCancel(self, user):
try:
cur = self.__conn.cursor()
sql = """delete from Account where username=?;"""
cur.execute(sql, (self.__user,))
self.__conn.commit()
print("账号注销成功!\n")
self.Main()
except:
print("账号注销失败!\n")
self.Main()
def Main(self):
print("1、账号登录；2、账号注册；3、修改密码；4、账号注销；0、退出.")
self.__num = input("请输入选择：").strip()
if self.__num == "1":
self.__Login()
elif self.__num == "2":
self.__Register()
elif self.__num == "3":
self.__Change()
elif self.__num == "4":
self.__Cancel()
elif self.__num == "0":
print("程序已退出!")
self.__conn.close()
sys.exit(0)
else:
self.Main()
#学生成绩管理类
class GradeTable:
def __init__(self):
__conn = sqlite3.connect(database="SQLite3.db")
self.__conn = __conn
def __GradeInput(self):
print("\n------------------ 1.学生成绩输入 -----------------")
self.__cur = self.__conn.cursor()
self.__number = input("请输入学生学号：").strip()
self.__name = input("请输入学生姓名：").strip()
self.__chinese = input("请输入语文成绩：").strip()
self.__math = input("请输入数学成绩：").strip()
self.__english = input("请输入英语成绩：").strip()
self.__sql = """select count(*) from GradeTable where Number=?;"""
self.__reslut = self.__cur.execute(self.__sql, (self.__number,))
self.__st = self.__reslut.fetchone()
while True:
if self.__name == "":
self.__number = input("学生学号不得为空!\n请输入学生学号：").strip()
elif self.__number == "":
self.__name = input("学生姓名不得为空!\n请输入学生姓名：").strip()
elif self.__chinese == "":
chinese = input("语文成绩不得为空!\n请输入语文成绩：").strip()
elif self.__math == "":
self.__math = input("数学成绩不得为空!\n请输入数学成绩：").strip()
elif self.__english == "":
self.__english = input("英语成绩不得为空!\n请输入英语成绩：").strip()
elif self.__st[0] == True:
print("该学生的成绩信息已输入!\n")
self.__GradeInput()
else:
self.__Input(self.__number, self.__name, self.__chinese, self.__math, self.__english)
def __Input(self, number, name, chinese, math, english):
try:
self.__cur = self.__conn.cursor()
self.__sql = """insert into GradeTable(Number,Name,Chinese,Math,English) values (?,?,?,?,?)"""
self.__cur.execute(self.__sql, (self.__name, self.__number, self.__chinese, self.__math, self.__english))
self.__conn.commit()
print("学生成绩输入成功!\n")
self.__GradeOutput()
except:
print("学生成绩输入失败!\n")
self.Main2()
def __GradeOutput(self):
print("\n************************* 2.学生成绩输出 *****************************")
print("-----------------------------------------------------------------------------")
print(" 学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 ")
print("------------------------------------------------------------------------------")
try:
self.__cur = self.__conn.cursor()
self.__sql = """select* from GradeTable"""
self.__result = self.__cur.execute(self.__sql)
self.__st = self.__result.fetchall()
for i in self.__st:
self.__sum = int(i[2]) + int(i[3]) + int(i[4])
self.__avg = self.__sum / 3.0
print(" " + i[0] + " | " + i[1] + " | " + i[2] + " | " + i[3] + " | " +
i[4] + " | " + str(self.__sum) + " | " + str(round(self.__avg, 2)) + " ")
print("------------------------------------------------------------------------------")
print()
self.Main2()
except:
print("学生成绩信息输出失败!\n")
self.Main2()
def __GradeSearch(self):
print("\n------------------ 3.学生成绩查询 -----------------")
print("1、按学生姓名查询；2、按学生学号查询；0、返回.")
self.__num = input("请输入选择：")
if self.__num == "1":
self.__SelectName()
elif self.__num == "2":
self.__SelectNumber()
elif self.__num == "0":
self.Main2()
else:
self.__GradeSearch()
def __SelectName(self):
self.__cur = self.__conn.cursor()
self.__name = input("请输入学生姓名：").strip()
self.__sql = """select count(*) from GradeTable where Name=?;"""
self.__result = self.__cur.execute(self.__sql, (self.__name,))
self.__st = self.__result.fetchall()
if self.__st[0] == (0,):
print("查无此人!")
self.__GradeSearch()
elif self.__st[0] == (1,):
self.__OutputName(self.__name)
else:
print("该学生姓名有多个!请按学号查询!")
self.__GradeSearch()
def __OutputName(self, name):
print("\n-----------------------------------------------------------------------------")
print(" 学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 ")
print("------------------------------------------------------------------------------")
try:
self.__cur = self.__conn.cursor()
self.__sql = """select * from GradeTable where Name =?;"""
self.__result = self.__cur.execute(self.__sql, (self.__name,))
self.__st = self.__result.fetchall()
for i in self.__st:
self.__sum = int(i[2]) + int(i[3]) + int(i[4])
self.__avg = self.__sum / 3.0
print(" " + i[0] + " | " + i[1] + " | " + i[2] + " | " + i[3] + " | " +
i[4] + " | " + str(self.__sum) + " | " + str(round(self.__avg, 2)) + " ")
print("------------------------------------------------------------------------------")
self.__GradeSearch()
except:
print("学生成绩查询失败!\n")
self.__GradeSearch()
def __SelectNumber(self):
self.__cur = self.__conn.cursor()
self.__number = input("请输入学生学号：").strip()
self.__sql = """select count(*) from GradeTable where Number =?;"""
self.__result = self.__cur.execute(self.__sql, (self.__number,))
self.__st = self.__result.fetchall()
if self.__number == "":
print("学号不得为空!")
self.__SelectNumber()
elif self.__st[0] == True:
self.__OutputNumber(self.__number)
else:
print("该学号不存在!\n")
self.__GradeSearch()
def __OutputNumber(self, number):
print("\n-----------------------------------------------------------------------------")
print(" 学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 ")
print("------------------------------------------------------------------------------")
try:
self.__cur = self.__conn.cursor()
self.__sql = """select* from GradeTable where Number=?;"""
self.__result = self.__cur.execute(self.__sql, (self.__number,))
self.__st = self.__result.fetchall()
for i in self.__st:
self.__sum = int(i[2]) + int(i[3]) + int(i[4])
self.__avg = self.__sum / 3.0
print(" " + i[0] + " | " + i[1] + " | " + i[2] + " | " + i[3] + " | " +
i[4] + " | " + str(self.__sum) + " | " + str(round(self.__avg, 2)) + " ")
print("------------------------------------------------------------------------------")
self.__GradeSearch()
except:
print("学生成绩查询失败!\n")
self.__GradeSearch()
def __GradeChange(self):
self.__cur = self.__conn.cursor()
print("\n------------------ 4.学生成绩修改 -----------------")
self.__number = input("请输入学生学号：").strip()
self.__name = input("请输入学生姓名：").strip()
self.__sql = """select count(*) from GradeTable where Number=? and Name=?;"""
self.__result = self.__cur.execute(self.__sql, (self.__number, self.__name,))
self.__st = self.__result.fetchone()
if self.__number == "" or self.__name == "":
print("学号或姓名不得为空!\n")
self.__GradeChange()
elif self.__st[0] == True:
self.__ChangeNumber(self.__number)
else:
print("查无此人!\n")
self.Main2()
def __ChangeNumber(self, number):
print("\n1、语文；2、数学；3、英语；0、返回.")
self.__num = input("请选择要修改成绩的学科：").strip()
if self.__num == "1":
self.__chinese = input("请输入修改后的语文成绩：").strip()
self.__AlterChineser(self.__num, number, self.__chinese)
elif self.__num == "2":
self.__math = input("请输入修改后的数学成绩：").strip()
self.__AlterMath(self.__num, self.__number, self.__math)
elif self.__num == "3":
self.__english = input("请输入修改后的英语成绩：")
self.__AlterEnglish(self.__num, self.__number, self.__english)
elif self.__num == "0":
self.Main2()
else:
self.__ChangeNumber(self.__number)
def __AlterChineser(self, num, number, chinese):
self.__cur = self.__conn.cursor()
try:
self.__sql = """update GradeTable set Chinese=? where Number =?;"""
self.__cur.execute(self.__sql, (self.__chinese, self.__number))
self.__conn.commit()
print("语文成绩修改成功!\n")
self.__ChangeNumber(number)
except:
print("语文成绩修改失败!\n")
self.__ChangeNumber(self.__number)
finally:
self.__ChangeNumber(self.__number)
def __AlterMath(self, num, number, math):
try:
self.__cur = self.__conn.cursor()
self.__sql = """update GradeTable set Math=? where Number =?;"""
self.__cur.execute(self.__sql, (self.__math, self.__number))
self.__conn.commit()
print("数学成绩修改成功!\n")
self.__ChangeNumber(self.__number)
except:
print("数学成绩修改失败!\n")
self.__ChangeNumber(self.__number)
finally:
self.__ChangeNumber(self.__number)
def __AlterEnglish(self, num, number, english):
try:
self.__cur = self.__conn.cursor()
self.__sql = """update GradeTable set English=? where Number =?;"""
self.__cur.execute(self.__sql, (self.__english, self.__number))
self.__conn.commit()
print("英语成绩修改成功!\n")
self.__ChangeNumber(number)
except:
print("英语成绩修改失败!\n")
self.__ChangeNumber(number)
finally:
self.__ChangeNumber(number)
def __DeleteGrade(self):
print("\n------------------ 5.学生成绩删除 -----------------")
self.__cur = self.__conn.cursor()
self.__number = input("请输入学生学号：").strip()
self.__name = input("请输入学生姓名：").strip()
self.__sql = """select count(*) from GradeTable where Number=? and Name=?;"""
self.__result = self.__cur.execute(self.__sql, (self.__number, self.__name,))
self.__st = self.__result.fetchone()
if self.__number == "":
print("学号或姓名不得为空!\n")
self.__DeleteGrade()
elif self.__st[0] == True:
self.__CancelDelete(self.__number)
else:
print("查无此人!\n")
self.Main2()
def __CancelDelete(self, number):
try:
self.__cur = self.__conn.cursor()
self.__sql = """delete from GradeTable where Number =?;"""
self.__cur.execute(self.__sql, (self.__number,))
self.__conn.commit()
print("学生成绩信息删除成功!\n")
self.Main2()
except:
print("学生成绩信息删除失败!\n")
self.Main2()
def Main2(self):
print("**************************************************************************")
print("1、学生成绩输入；2、学生成绩输出；3、学生成绩查询；4、学生成绩修改；5、学生成绩删除；6、返回登录界面；0、退出当前程序.")
print("**************************************************************************")
self.__num = input("请输入选择：").strip()
if self.__num == "1":
self.__GradeInput()
elif self.__num == "2":
self.__GradeOutput()
elif self.__num == "3":
self.__GradeSearch()
elif self.__num == "4":
self.__GradeChange()
elif self.__num == "5":
self.__DeleteGrade()
elif self.__num == "6":
sd = Account()
sd.Main()
elif self.__num == "0" or self.__num == "":
self.__conn.close()
print("程序已退出!")
sys.exit(0)
else:
self.Main2()
if __name__ == '__main__':
st = Account()
st.Main()
运行效果截图
以上就是全部的操作过程了，仅供大家参考和学习，感兴趣的小伙伴可以来看看。可能还有些不足之处，也欢迎大家多多批评指正。
个人声明：禁止转载！！！严禁抄袭！！如果要引用部分代码，请标明出处！！
916
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
