学生信息管理系统python版


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





#!/usr/bin/env python

# @Time  : 2018/3/30 17:37

# @Author : KiritoLiu

# @Contact : kiritoliuyhsky@gmail.com

# @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







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