wxh_39.sql
    student表属性.png

    1. # @Time    : 2022/9/2 9:22
    2. # @Author  : Andrew
    3. import time
    4. import datetime
    6. import pymysql
    9. ###   学生信息的增删改查
    11. #  数据库的读写操作
    12. def open_DB(host, user, password, port, db):
    13.     db_object = pymysql.Connect(host=host, user=user, password=password, port=port, db=db, charset='utf8')
    14.     sheet_object = db_object.cursor()
    15.     return db_object, sheet_object
    18. def read_DB(sheet_object, sql):
    19.     sheet_object.execute(sql)
    20.     result = sheet_object.fetchall()
    21.     return result
    24. def write_DB(db_object, sheet_object, sql):
    25.     try:
    26.         sheet_object.execute(sql)
    27.         db_object.commit()
    28.     except:
    29.         db_object.rollback()
    32. def close_DB(db_object, sheet_object):
    33.     db_object.close()
    34.     sheet_object.close()
    37. #  前段交互操作 函数
    39. #   得到需要搜索的学生信息
    40. def getStuSearchInfo():
    41.     while True:
    42.         name, sex, cid, race = input("请输入学生信息(以','隔开):\nname sex cid race\n").split(',')
    43.         if sex not in ('男', '女'):
    44.             print('输入用户性别非法,请重新输入!')
    45.         else:
    46.             return name, sex, cid, race
    49. #    得到需要添加的学生的信息
    50. def getStuAddInfo():
    51.     while True:
    52.         Sname, birth, firstDay, race, sex, height, CID, hobbies, remarks = input("请输入学生信息(以','隔开)\n"
    53.                                                                                  "Sname,birth,firstDay,race,sex,height,CID,hobbies,remarks:\n").split(
    54.             ',')
    55.         if Sname == '':
    56.             print('学生姓名不能为空,请重新输入!')
    57.         elif birth == '':
    58.             print('学生生日不能为空,请重新输入!')
    59.             try:
    60.                 timeArray = time.strptime(birth, "%Y-%m-%d")
    61.             except:
    62.                 print('请输入正确的出生日期')
    63.         elif firstDay == '':
    64.             print('入学时间不能为空,请重新输入!')
    65.             try:
    66.                 timeArray = time.strptime(firstDay, "%Y-%m-%d")
    67.             except:
    68.                 print('请输入正确的入学时间')
    69.         elif sex == '':
    70.             print('学生性别不能为空,请重新输入!')
    71.         elif sex not in ('男', '女'):
    72.             print('输入用户性别非法,请重新输入!')
    73.         elif CID == '':
    74.             print('班级编号不能为空,请重新输入!')
    75.         elif int(CID) not in (31, 33, 35, 37, 38, 39, 41):
    76.             print('请输入正确的班级')
    77.         else:
    78.             return Sname, birth, firstDay, race, sex, height, CID, hobbies, remarks
    81. #   得到需要修改的学生信息
    82. def getStuUpdataInfo():
    83.     while True:
    84.         name = input('请输入学生姓名:')
    85.         result, stu_info = searchStu(name, sex='', cid='', race='')
    86.         # print(stu_info,type(stu_info))
    87.         if result == 0:
    88.             sid = stu_info[0][0]
    89.             # stu_info1.extend(stu_info[0])
    90.             Sname, birth, firstDay, race, sex, height, CID, hobbies, remarks = input("请输入学生信息(以','隔开)\n"
    91.                                                                                      "Sname,birth,firstDay,race,sex,height,CID,hobbies,remarks:\n").split(
    92.                 ',')
    93.             if Sname == '':
    94.                 print('学生姓名不能为空,请重新输入!')
    95.             elif birth == '':
    96.                 print('学生生日不能为空,请重新输入!')
    97.                 try:
    98.                     timeArray = time.strptime(birth, "%Y-%m-%d")
    99.                 except:
    100.                     print('请输入正确的出生日期')
    101.             elif firstDay == '':
    102.                 print('入学时间不能为空,请重新输入!')
    103.                 try:
    104.                     timeArray = time.strptime(firstDay, "%Y-%m-%d")
    105.                 except:
    106.                     print('请输入正确的入学时间')
    107.             elif sex == '':
    108.                 print('学生性别不能为空,请重新输入!')
    109.             elif sex not in ('男', '女'):
    110.                 print('输入用户性别非法,请重新输入!')
    111.             elif CID == '':
    112.                 print('班级编号不能为空,请重新输入!')
    113.             elif int(CID) not in (31, 33, 35, 37, 38, 39, 41):
    114.                 print('请输入正确的班级')
    115.             else:
    116.                 if height == '':
    117.                     height = 0
    118.             return name, Sname, birth, firstDay, sex, CID, race, height, hobbies, remarks
    121. def stuDelectInfo():
    122.     while True:
    123.         stu_name = input('请输入需要删除的学生姓名:')
    124.         result1, stu_info2 = searchStu(stu_name, sex='', cid='', race='')
    125.         if result1 == -1:
    126.             print('没有该同学')
    127.         else:
    128.             # print(stu_info2)
    129.             # choice3 = input('请输入需要删除的学生学号:')
    130.             result3 = delectstu(stu_name)
    131.             if result3 == -1:
    132.                 print('删除失败')
    133.             else:
    134.                 print('删除成功')
    136. # 后端操作
    137. #   查询学生信息并返回result结果 0.-1
    138. def searchStu(name, sex, cid, race):
    139.     sql = "select * from student where "
    140.     if name == '':
    141.         sql = sql + 'Sname = Sname '
    142.     else:
    143.         sql = sql + f'Sname = \'{name}\' '
    144.     if sex == '':
    145.         sql = sql + 'and sex = sex '
    146.     else:
    147.         sql = sql + f'and sex = \'{sex}\' '
    148.     if cid == '':
    149.         sql = sql + 'and CID = CID '
    150.     else:
    151.         sql = sql + f'and CID = \'{cid}\' '
    152.     if race == '':
    153.         sql = sql + 'and race = race '
    154.     else:
    155.         sql = sql + f'and race = \'{race}\' '
    156.     info1 = read_DB(sheet_object, sql)
    157.     if len(info1) == 0:
    158.         result = -1
    159.     else:
    160.         result = 0
    161.     return result, info1
    164. #   添加学生的信息到数据库
    165. def addStu(name, birth, firstDay, race, sex, height, cid, hobbies, remarks):
    166.     if height == '':
    167.         height = 0
    168.     sql = "insert into student(Sname,birth,firstDay,race,sex,height,CID,hobbies,remarks) values" \
    169.           f"(\'{name}\',\'{birth}\',\'{firstDay}\',\'{race}\',\'{sex}\',\'{height}\',\'{cid}\',\'{hobbies}\',\'{remarks}\')"
    170.     write_DB(db_object, sheet_object, sql)
    171.     result = searchStu(name, sex, cid, race)
    172.     return result
    175. def updateStu(name, Sname, birth, firstDay, sex, CID, race, height, hobbies, remarks):
    176.     sql = "update student set "
    177.     if Sname == '':
    178.         sql = sql + "sname = sname"
    179.     else:
    180.         sql = sql + f"sname = '{Sname}'"
    181.     if birth == '':
    182.         sql = sql + ", birth = birth"
    183.     else:
    184.         sql = sql + f", birth ='{birth}'"
    185.     if firstDay == '':
    186.         sql = sql + ", firstDay = firstDay"
    187.     else:
    188.         sql = sql + f", firstDay ='{firstDay}'"
    189.     if sex == '':
    190.         sql = sql + ", sex = sex"
    191.     else:
    192.         sql = sql + f", sex ='{sex}'"
    193.     if CID == '':
    194.         sql = sql + ", cid = cid"
    195.     else:
    196.         sql = sql + f", cid ={CID}"
    197.     if race == '':
    198.         sql = sql + ", race = race"
    199.     else:
    200.         sql = sql + f", race ='{race}'"
    201.     if height == '':
    202.         sql = sql + ", height = height"
    203.     else:
    204.         sql = sql + f", height ={height}"
    205.     if hobbies == '':
    206.         sql = sql + ", hobbies = hobbies "
    207.     else:
    208.         sql = sql + f", hobbies ='{hobbies}'"
    209.     if remarks == '':
    210.         sql = sql + f", remarks = remarks where Sname = '{name}'"
    211.     else:
    212.         sql = sql + f", remarks = {remarks} where Sname = '{name}'"
    213.     write_DB(db_object, sheet_object, sql)
    214.     result, stu_info = searchStu(Sname, sex, CID, race)
    215.     return result
    218. def delectstu(num):
    219.     sql = "delete from student where " + f"Sname = '{num}'"
    220.     print(sql)
    221.     result3 = write_DB(db_object, sheet_object, sql)
    222.     return result3
    225. db_object, sheet_object = open_DB('localhost', 'root', '514623', 3307, 'practice')
    226. # choice = input('1.add\n2.search\n3.updata\n4.delete\n请输入您的选择:')
    227. while True:
    228.     choice = input('1.add\n2.search\n3.updata\n4.delete\n请输入您的选择:')
    229.     ## 增加学生信息
    230.     if choice == '1':
    231.         add_stu_info = getStuAddInfo()
    232.         result = addStu(*add_stu_info)
    233.         if result == -1:
    234.             print('添加失败!')
    235.         else:
    236.             print('新增成功!')
    237.     #  查询功能
    238.     elif choice == '2':
    239.         search_info = getStuSearchInfo()
    240.         result, stu_info = searchStu(*search_info)
    241.         if result == -1:
    242.             print('没有该同学')
    243.         else:
    244.             print(stu_info)
    246.     elif choice == '3':
    247.         name, Sname, birth, firstDay, race, sex, height, CID, hobbies, remarks = getStuUpdataInfo()
    248.         result1, check_info = updateStu(name, Sname, birth, firstDay, race, sex, height, CID, hobbies, remarks)
    249.         if result1 == -1:
    250.             print('修改失败!')
    251.         else:
    252.             print('修改成功!')
    253.     elif choice == '4':
    254.         stuDelectInfo()
    255.     elif choice == 'q':
    256.         break
    257.     else:
    258.         print('请输入正确的选项!')
    259. close_DB(db_object, sheet_object)
    260. # '1.[name] 2.[birth] 3.[firstDay] 4.[race] 5.[sex] 6.[height] 7.[CID] 8 [hobbies] 9[remarks]

    /