学生信息管理系统python版

本文实例为大家分享了python学生信息管理系统的具体代码，供大家参考，具体内容如下

#!/usr/bin/env python
# @time  : 2018/3/30 17:37
# @author : kiritoliu
# @contact : [e-mail]
# @site  :
# @file  : 学生信息管理系统.py
# @software: pycharm
import pymysql
import datetime
import re
 
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):
        nextyear = 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 "错误"
 
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()
 
def seeone(a):
  #根据学号，查看某一条数据
  db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
  # 创建游标对象
  cursor = db.cursor()
  stuid =int(a)
  sql = "select s.sno,s.name,s.sex,s.cla,s.tel,s.birthday from stu s where s.sno = '%d'" % stuid
  try:
    m = cursor.execute(sql)
    b = cursor.fetchone()
    if b == none:
      print("您的输入有误，将会退出系统")
      quit()
    else:
      print("{:>3}|\t{:<4}\t|{}|\t{:<3}\t\t| {:<8}|{}| {}".format("学号", "姓名", "性别", "班级", "电话", "年龄", "出生日期"))
      birth = b[5]
      bir = birth.strftime("%y-%m-%d")
      if bir == "1949-10-01":
        bir = "null"
      print("{:>5}|\t{:<4}\t| {} |\t{:<10}\t|{:<11}| {:<2} | {}".format(b[0], b[1], b[2], b[3], b[4], calage(bir), bir))
    db.commit()
  except exception as err:
    db.rollback()
    print("sql查询失败！错误：", err)
  db.close()
 
def addmql():
  #添加一条数据
  db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
  # 创建游标对象
  cursor = db.cursor()
  sql = "select s.sno from stu s"
  cursor.execute(sql)
  alist = cursor.fetchall()    #此处读取数据库中的所有学号
  blist = ()           #建立一个空的元组用于存放学号
  print("以下学号已被占用，不可使用：")
  for i in alist:
    blist += i         #存放所有的学号
    print(i[0], end=" ")    #输出已经被使用过的学号
  print()
  sno = int(input("请输入添加的学员的学号:\n"))
  if sno in blist:        #判断学号是否被使用过，学号不可以重复
    print("您输入的学号已被占用！系统即将退出！")
    quit()
  sname = input("请输入添加的学员的姓名:\n")
  sex = input("请输入添加的学员的性别(男or女):\n")
  if sex == "男" or sex == "女":
    sex = sex
  else:
    sex = "男"
    print("性别输入有误,已默认为男")
  cla = input("请输入添加的学员的班级(例：python01):\n")
  tel = input("请输入添加的学员的电话:\n")
  if tel == re.search(r"(1[3456789]\d{9})", tel):
    tel = tel
    print("电话输入错误，已重置为空")
  else:
    tel = ""
  sbir = input("请输入添加的学员的出生日期(例:2001-1-1):\n")
  if sbir == re.search(r"(\d{4}-\d{1,2}-\d{1,2})", sbir):
    sbir = sbir
  else:
    sbir = "1949-10-01"
    print("出生日期输入错误，已重置为初始值")
  sql = "insert into stu(sno,name,sex,cla,tel,birthday) values('%d', '%s', '%s', '%s', '%s', '%s')"%(sno, sname, sex, cla, tel, sbir)
  try:
    m = cursor.execute(sql)
    # 事务提交
    db.commit()
    print("成功添加条数：", m)
    print("您添加的信息为:")
    seeone(sno)
  except exception as err:
    db.rollback()
    print("sql添加失败！错误：", err)
  db.close()
 
def updatasql():
  #更新修改某条数据
  db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
  # 创建游标对象
  cursor = db.cursor()
  stuid = int(input("请输入要修改的学员的学号：\n"))    # 一个班不超过100人，以stuid作为索引
  try:
    seeone(stuid)
    print("======可修改的学员信息的名称======")
    print("{0:2}{1:5}{2:5}{3:5}".format(" ", "1.姓名", "2.性别", "3.班级"))
    print("{0:2}{1:5}{2}".format(" ", "4.电话", "5.出生日期"))
    a = int(input("请选择要修改的学员信息的名称(学号不可修改):\n"))
    if a == 1:
      xm = input("请输入修改后的姓名：\n")
      sql = "update stu s set s.name = '%s' where s.sno = '%d'" % (xm, stuid)
    elif a == 2:
      xb = input("请输入修改后的性别(男or女):\n")
      if xb == "男" or xb == "女":
        xb = xb
      else:
        xb = "男"
        print("性别输入有误,已默认为男")
      sql = "update stu s set s.sex = '%s' where s.sno = '%d'" % (xb, stuid)
    elif a == 3:
      bj = input("请输入修改后的班级:\n")
      sql = "update stu s set s.cla = '%s' where s.sno = '%d'" % (bj, stuid)
    elif a == 4:
      dh = input("请输入修改后的电话:\n")
      sql = "update stu s set s.tel = '%s' where s.sno = '%d'" % (dh, stuid)
      if dh == re.search(r"(1[3456789]\d{9})", dh):
        '''正则表达式匹配判断输入是否合格'''
        dh = dh
      else:
        dh = ""
        print("电话输入错误，已重置为空")
    elif a == 5:
      birday = input("请输入修改后的出生日期(格式：2000-1-1):")
      if birday == 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 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()
 
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()

配套的数据库文件，内含数据

-- mysql dump 10.13 distrib 5.7.12, for win64 (x86_64)
--
-- host: localhost  database: stu
-- ------------------------------------------------------
-- server version 5.7.17-log
 
/*!40101 set @old_character_set_client=@@character_set_client */;
/*!40101 set @old_character_set_results=@@character_set_results */;
/*!40101 set @old_collation_connection=@@collation_connection */;
/*!40101 set names utf8 */;
/*!40103 set @old_time_zone=@@time_zone */;
/*!40103 set time_zone='+00:00' */;
/*!40014 set @old_unique_checks=@@unique_checks, unique_checks=0 */;
/*!40014 set @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0 */;
/*!40101 set @old_sql_mode=@@sql_mode, sql_mode='no_auto_value_on_zero' */;
/*!40111 set @old_sql_notes=@@sql_notes, sql_notes=0 */;
 
--
-- table structure for table `stu`
--
 
drop table if exists `stu`;
/*!40101 set @saved_cs_client   = @@character_set_client */;
/*!40101 set character_set_client = utf8 */;
create table `stu` (
 `id` int(3) not null auto_increment,
 `sno` int(3) not null,
 `name` varchar(20) not null,
 `sex` varchar(1) not null,
 `cla` varchar(10) not null,
 `tel` varchar(11) default null,
 `birthday` datetime default null,
 primary key (`id`),
 unique key `stu_no_unique` (`sno`)
) engine=innodb auto_increment=32 default charset=utf8;
/*!40101 set character_set_client = @saved_cs_client */;
 
--
-- dumping data for table `stu`
--
 
lock tables `stu` write;
/*!40000 alter table `stu` disable keys */;
insert into `stu` values (1,1,'张三','男','python01','12345678910','1999-01-01 00:00:00'),(2,2,'李四','男','python01','18866668888','1996-12-06 00:00:00'),(3,3,'王五','男','python02','12345665410','1996-11-27 00:00:00'),(4,4,'赵六','女','python02','12332233210','1997-10-24 00:00:00'),(5,5,'qq01','女','python03','13322223322','1990-01-31 00:00:00'),(6,6,'qq02','男','python03','12288886666','1992-02-20 00:00:00'),(7,7,'qq03','女','python03','13579264801','2000-10-30 00:00:00'),(8,8,'uu01','男','python01','18898084886','1998-08-08 00:00:00'),(9,9,'uu02','女','python02','12022000022','1994-04-01 00:00:00'),(10,10,'aa','女','python02','18899998888','2004-04-04 00:00:00'),(11,11,'bb','男','python03','19264664234','1995-05-15 00:00:00'),(25,12,'uu10','男','python04','17788992332','1996-12-06 00:00:00'),(28,13,'uu10','女','python04','13571854999','1996-12-06 00:00:00');
/*!40000 alter table `stu` enable keys */;
unlock tables;
 
--
-- dumping events for database 'stu'
--
 
--
-- dumping routines for database 'stu'
--
/*!40103 set time_zone=@old_time_zone */;
 
/*!40101 set sql_mode=@old_sql_mode */;
/*!40014 set foreign_key_checks=@old_foreign_key_checks */;
/*!40014 set unique_checks=@old_unique_checks */;
/*!40101 set character_set_client=@old_character_set_client */;
/*!40101 set character_set_results=@old_character_set_results */;
/*!40101 set collation_connection=@old_collation_connection */;
/*!40111 set sql_notes=@old_sql_notes */;
 

-- dump completed on 2018-03-31 15:10:58

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。