此次管理系统与mysql数据库连接,实现了增删改查等基本功能
其中增加了模糊查询的功能,更加便捷查询信息
import pymysql
conn = pymysql.connect(host="localhost",user="root",password="",
database="zuoye",port=3306,autocommit=False)
cursor=conn.cursor()
def select():
con = "yes"
while (con == "yes"):
conn = pymysql.connect(host="localhost", user="root", password="",
database="zuoye", port=3306,autocommit=False)
l = conn.cursor()
a = input("输入信息类型(学生信息,社团信息,教师信息,加入社团信息):\n")
if (a == "学生信息"):
column=input("输入你要查寻的属性,并用逗号隔开:\n")
c=input("输入条件(如果你的条件值为不为数字请将你的条件值加上' '):\n")
if c:
sql = "select "+column+" from student " +"where "+c
else:
sql = "select "+column+" from student"
l.execute(sql)
row = l.fetchone()
print("%r" % (row,))
for row in l:
print("%r" % (row,))
conn.close()
elif (a == "社团信息"):
column=input("输入你要查寻的属性,并用逗号隔开:\n")
c = input("输入条件(如果你的条件值为不为数字请将你的条件值加上' '):\n")
if c:
sql = "select "+column+" from depart " +"where "+c
else:
sql = "select "+column+" from depart"
print(sql)
l.execute(sql)
row = l.fetchone()
print("%r" % (row,))
for row in l:
print("%r" % (row,))
conn.close()
elif (a == "教师信息"):
column=input("输入你要查寻的属性,并用逗号隔开:\n")
c = input("输入条件(如果你的条件值为不为数字请将你的条件值加上' '):\n")
if c:
sql = "select "+column+" from depart " +"where "+c
else:
sql = "select "+column+" from depart"
print(sql)
l.execute(sql)
row = l.fetchone()
print("%r" % (row,))
for row in l:
print("%r" % (row,))
conn.close()
elif (a == "加入社团信息"):
column=input("输入你要查寻的属性,并用逗号隔开:\n")
c = input("输入条件(如果你的条件值为不为数字请将你的条件值加上' '):\n")
if c:
sql = "select "+column+" from joindepart " +"where "+c
else:
sql = "select "+column+" from joindepart"
print(sql)
l.execute(sql)
row = l.fetchone()
print("%r" % (row,))
for row in l:
print("%r" % (row,))
conn.close()
else:
print("您输入的信息有误\n")
con = input("您还要继续查询吗(yes/no):\n")
def insert():
con="yes"
while(con=="yes"):
conn = pymysql.connect(host="localhost", user="root", password="",
database="zuoye", port=3306, autocommit=False)
l = conn.cursor()
a = input("输入信息类型(学生信息,社团信息,教师信息,加入社团信息):\n")
column=""
value=""
con="yes"
if (a == "学生信息"):
l = ["学号", "姓名", "性别", "年龄", "学院编号"]
for i in l:
b = input("输入你要添加的" + i + ":\n")
if (i==l[-1]):
column += ("%s" % i)
value += ("%r" %b)
else:
column += ("%s," % i)
value += ("%r," % b)
sql = "insert into student" + "(" + (column) + ")" + " " + "values" + "(" + (value) + ")"
l = conn.cursor()
l.execute(sql)
conn.close()
print(column)
print(value)
elif(a == "社团信息"):
l=["社团编号","社团名称","成立时间","活动地点"]
for i in l:
b = input("输入你要添加的" + i + ":\n")
if (i==l[-1]):
column += ("%s" % i)
value += ("%r" %b)
else:
column += ("%s," % i)
value += ("%r," % b)
sql="insert into depart"+"("+(column)+")"+" "+"values"+"("+(value)+")"
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print(column)
print(value)
elif(a == "教师信息"):
l=["教师姓名","教师编号","教师人数"]
for i in l:
b = input("输入你要添加的" + i + ":\n")
if (i==l[-1]):
column += ("%s" % i)
value += ("%r" %b)
else:
column += ("%s," % i)
value += ("%r," % b)
sql="insert into teacher"+"("+(column)+")"+" "+"values"+"("+(value)+")"
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print(column)
print(value)
elif (a == "加入社团信息"):
l = ["学生学号","社团编号","加入时间"]
for i in l:
b = input("输入你要添加的" + i + ":\n")
if (i == l[-1]):
column += ("%s" % i)
value += ("%r" % b)
else:
column += ("%s," % i)
value += ("%r," % b)
sql = "insert into joindepart" + "(" + (column) + ")" + " " + "values" + "(" + (value) + ")"
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print(column)
print(value)
else:
print("您输入的信息有误")
con=input("您还要继续添加信息吗:(yes/no)\n")
def delete():
con="yes"
while(con == "yes"):
conn = pymysql.connect(host="localhost", user="root", password="",
database="zuoye", port=3306,autocommit=False)
l = conn.cursor()
a=input("输入删除信息类型(学生信息,社团信息,教师信息,加入社团信息):\n")
if (a == "学生信息"):
sno=input("输入你要删除学生的条件(如果你的条件值为不为数字请将你的条件值加上' '):\n")
sql="delete from student where "+sno
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print("删除成功")
elif(a == "社团信息"):
sno=input("输入你要删除的社团的条件(如果你的条件值为不为数字请将你的条件值加上' '):\n")
sql="delete from depart where "+sno
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print("删除成功")
elif (a == "教师信息"):
sno = input("输入你要删除的教师的条件(如果你的条件值为不为数字请将你的条件值加上' '):\n")
sql = "delete from teacher where " + sno
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print("删除成功")
elif (a == "加入社团信息"):
sno = input("输入你要删除的社团的编号:\n")
sql = "delete from joindepart where " + sno
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print("删除成功")
else:
print("您输入的信息有误\n")
con = input("你还要继续删除吗(yes/no):\n")
def update():
con="yes"
while(con == "yes"):
conn = pymysql.connect(host="localhost", user="root", password="",
database="zuoye", port=3306,autocommit=False)
l = conn.cursor()
a=input("输入修改信息类型(学生信息,社团信息,教师信息,加入社团信息):\n")
if (a == "学生信息"):
column=input("输入你要修改学生的属性:\n")
rol_value=input("输入条件(如果你的条件值为不为数字请将你的条件值加上' '):\n")
value=input("输入您要修改的值:\n")
sql="update student set "+(column)+"="+(value)+" where "+(rol_value)
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print("修改成功")
elif(a == "社团信息"):
column=input("输入你要修改的俱乐部的属性:\n")
rol_value=input("输入条件(如果你的条件值为
不为数字请将你的条件值加上' ')\n")
value=input("输入您要修改的值\n")
sql="update depart set "+(column)+"="+(value)+" where "+(rol_value)
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print("修改成功")
elif (a == "教师信息"):
column = input("输入你要修改的学院的属性:\n")
rol_value=input("输入条件(如果你的条件值为
不为数字请将你的条件值加上' ')\n")
value=input("输入您要修改的值\n")
sql = "update teacher set "+(column)+"="+(value)+" where "+(rol_value)
print(sql)
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print("修改成功")
elif (a == "加入社团信息"):
column = input("输入你要修改的加入俱乐部的属性:\n")
rol_value=input("输入条件(如果你的条件值为不为数字请将你的条件值加上' ')\n")
value=input("输入您要修改的值\n")
sql = "update joindepart set "+(column)+"="+(value)+" where "+(rol_value)
l = conn.cursor()
l.execute(sql)
conn.commit()
conn.close()
print("修改成功")
else:
print("您输入的信息有误\n")
con = input("你还要继续修改吗(yes/no):\n")
cont="yes"
while(cont == "yes"):
beg=input("你想执行什么操作(查询信息,修改信息,添加信息,删除信息):\n")
if(beg == "查询信息"):
select()
elif(beg =="修改信息"):
update()
elif(beg == "添加信息"):
insert()
elif(beg == "删除信息"):
delete()
else:print("您输入信息有误:\n")
cont=input("您还要继续操作吗(yes/no):\n")
conn.close()