Python增加学生信息 python学生信息表

要求：使用python连接mysql数据库，用pymysql第三方库实现对学生信息的增删查改功能。学生信息应该包含：学号、姓名、性别、班级、电话、年龄、出生日期。

使用模块：

pymysql datetime re

运行效果截图：

从截图中，可以看到，难点是需要根据时间来判断年龄

实现步骤：应该设计一张数据库的学生表，表包含如上要求的信息，创建数据库后，生成该表：

菜单功能选择函数：

def mainstu():
    while True:
        # 输出初始界面
        print("=" * 12, "学生信息管理系统", "=" * 15)
        print("{0:2}{1:13}{2:15}".format(" ", "1.查看学员信息", "2.添加学员信息"))
        print("{0:2}{1:13}{2:15}".format(" ", "3.修改学员信息", "4.删除学员信息"))
        print("{0:2}{1:13}".format(" ", "5.退出系统"))
        print("=" * 45)
        key = int(input("请输入对应的选择:\n"))
        # 根据键盘值判断并进行操作
        if key == 1:
            print("=" * 12, "学员信息浏览", "=" * 15)
            seesql()
            input("按回车继续")
        elif key == 2:
            print("=" * 12, "学员信息添加", "=" * 15)
            addmql()
            input("按回车继续")
        elif key == 3:
            print("=" * 12, "学员信息修改", "=" * 15)
            seesql()
            updatasql()
            input("按回车继续")
        elif key == 4:
            print("=" * 12, "学员信息删除", "=" * 15)
            seesql()
            delsql()
            input("按回车继续")
        elif key == 5:
            print("=" * 12, "再见", "=" * 12)
            quit()
        else:
            print("=" * 12, "您的输入有误，请重新输入", "=" * 12)


mainstu()

 查询所有学生：

def seesql():
    # 查看学生表数据库
    db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
    # 创建游标对象
    cursor = db.cursor()
    sql = "select s.sno,s.name,s.sex,s.cla,s.tel,s.birthday from stu s order by sno"
    # 用sno（学号）排序查看学生名单
    try:
        m = cursor.execute(sql)
        alist = cursor.fetchall()
        print("{:>3}|\t{:<4}\t|{}|\t{:<3}\t\t| {:<8}|{}| {}".format("学号", "姓名", "性别", "班级", "电话", "年龄", "出生日期"))
        for vo in alist:
            birth = vo[5]
            bir = birth.strftime("%Y-%m-%d")
            if bir == "1949-10-01":
                bir = "NULL"
            print(
                "{:>5}|\t{:<4}\t| {} |\t{:<10}\t|{:<11}| {} | {}".format(vo[0], vo[1], vo[2], vo[3], vo[4], CalAge(bir),
                                                                         bir))
        db.commit()
    except Exception as err:
        db.rollback()
        print("SQL查看失败！错误：", err)
    db.close()

修改出生年份，自动计算年龄：

 代码实现：

elif a == 5:
            birday = input("请输入修改后的出生日期(格式：2000-1-1):")
            if re.search(r"(\d{4}-\d{1,2}-\d{1,2})", birday):
                '''正则表达式匹配判断输入是否合格'''
                birday = birday
            else:
                birday = "1949-10-01"
                print("出生日期输入错误，已重置为初始值")
            sql = "UPDATE stu s SET s.birthday = '%s' WHERE s.sno = '%d'" % (birday, stuid)
        else:
            print("您的输入有误，将会退出！")  # 此处退出防止某些误操作导致的数据库数据泄露
            quit()
        cursor.execute(sql)
        db.commit()
        print("修改后的该学员信息为：")
        seeone(stuid)
    except Exception as err:
        db.rollback()
        print("SQL修改失败！错误：", err)
    db.close()

def CalAge(Date):
    # 生日（年月日（数据库中的））转换为年龄
    if Date == "NULL":
        return "无"
    try:
        Date = Date.split('-')
        Birth = datetime.date(int(Date[0]), int(Date[1]), int(Date[2]))
        Today = datetime.date.today()
        if (Today.month > Birth.month):
            NextYear = datetime.date(Today.year + 1, Birth.month, Birth.day)
        elif (Today.month < Birth.month):
            NextYear = datetime.date(Today.year, Today.month + (Birth.month - Today.month), Birth.day)
        elif (Today.month == Birth.month):
            if (Today.day > Birth.day):
                date = datetime.date(Today.year + 1, Birth.month, Birth.day)
            elif (Today.day < Birth.day):
                NextYear = datetime.date(Today.year, Birth.month, Today.day + (Birth.day - Today.day))
            elif (Today.day == Birth.day):
                NextYear = 0
        Age = Today.year - Birth.year
        if NextYear == 0:  # 如果今天就是生日
            return "%d" % (Age)
        else:
            DaysLeft = NextYear - Today
            return "%d" % (Age)
    except:
        return "错误"

删除学生：

 14号学生不存在：

 实现代码：

def delsql():
    # 删除某条学生数据
    db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
    # 创建游标对象
    cursor = db.cursor()
    stuid = int(input("请输入要删除的学员的学号：\n"))  # 一个班不超过100人，以stuid作为索引
    try:
        print("======即将删除的学员信息的名称======")
        seeone(stuid)
        a = input("请确认是否删除该学员信息(y/n):\n")
        if a == 'y' or a == 'Y':
            sql = "delete from stu where sno = '%d'" % (stuid)
            cursor.execute(sql)
        else:
            print("取消学员信息删除，即将退出系统")
            quit()
        db.commit()
        print("该学员信息已删除")
    except Exception as err:
        db.rollback()
        print("SQL删除失败！错误：", err)
    db.close()