#!/usr/bin/env python
# @Time  : 2018/3/30 17:37
# @Author : KiritoLiu
# @Contact : kiritoliuyhsky@gmail.com
# @Site  :
# @File  : 學生信息管理系統(tǒng).py
# @Software: PyCharm
import pymysql
import datetime
import re
 
def CalAge(Date):
  #生日（年月日（數(shù)據(jù)庫中的））轉換為年齡
  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():
  #查看學生表數(shù)據(jù)庫
  db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
  # 創(chuàng)建游標對象
  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):
  #根據(jù)學號，查看某一條數(shù)據(jù)
  db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
  # 創(chuàng)建游標對象
  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("您的輸入有誤，將會退出系統(tǒng)")
      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():
  #添加一條數(shù)據(jù)
  db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
  # 創(chuàng)建游標對象
  cursor = db.cursor()
  sql = "select s.sno from stu s"
  cursor.execute(sql)
  alist = cursor.fetchall()    #此處讀取數(shù)據(jù)庫中的所有學號
  blist = ()           #建立一個空的元組用于存放學號
  print("以下學號已被占用，不可使用：")
  for i in alist:
    blist += i         #存放所有的學號
    print(i[0], end=" ")    #輸出已經被使用過的學號
  print()
  sno = int(input("請輸入添加的學員的學號:\n"))
  if sno in blist:        #判斷學號是否被使用過，學號不可以重復
    print("您輸入的學號已被占用！系統(tǒng)即將退出！")
    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("成功添加條數(shù)：", m)
    print("您添加的信息為:")
    seeone(sno)
  except Exception as err:
    db.rollback()
    print("SQL添加失敗！錯誤：", err)
  db.close()
 
def updatasql():
  #更新修改某條數(shù)據(jù)
  db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
  # 創(chuàng)建游標對象
  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("您的輸入有誤，將會退出！") # 此處退出防止某些誤操作導致的數(shù)據(jù)庫數(shù)據(jù)泄露
      quit()
    cursor.execute(sql)
    db.commit()
    print("修改后的該學員信息為：")
    seeone(stuid)
  except Exception as err:
    db.rollback()
    print("SQL修改失?。″e誤：", err)
  db.close()
 
def delsql():
  #刪除某條學生數(shù)據(jù)
  db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
  # 創(chuàng)建游標對象
  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("取消學員信息刪除，即將退出系統(tǒng)")
      quit()
    db.commit()
    print("該學員信息已刪除")
  except Exception as err:
    db.rollback()
    print("SQL刪除失?。″e誤：", err)
  db.close()
 
def mainstu():
  while True:
    # 輸出初始界面
    print("=" * 12, "學員信息管理系統(tǒng)", "=" * 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.退出系統(tǒng)"))
    print("=" * 45)
    key = int(input("請輸入對應的選擇:\n"))
    # 根據(jù)鍵盤值判斷并進行操作
    if key == 1:
      print("=" * 12, "學員信息瀏覽", "=" * 15)
      seesql()
      input("按回車繼續(xù)")
    elif key == 2:
      print("=" * 12, "學員信息添加", "=" * 15)
      addmql()
      input("按回車繼續(xù)")
    elif key == 3:
      print("=" * 12, "學員信息修改", "=" * 15)
      seesql()
      updatasql()
      input("按回車繼續(xù)")
    elif key == 4:
      print("=" * 12, "學員信息刪除", "=" * 15)
      seesql()
      delsql()
      input("按回車繼續(xù)")
    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