Student Information Management System python edition
- 2020-12-20 03:41:20
- OfStack
The example of this paper shares the specific code of python student information management system for your reference. The specific content is as follows
#!/usr/bin/env python
# @Time : 2018/3/30 17:37
# @Author : KiritoLiu
# @Contact : kiritoliuyhsky@gmail.com
# @Site :
# @File : Student information management system .py
# @Software: PyCharm
import pymysql
import datetime
import re
def CalAge(Date):
# Birthdays (days and years (in the database)) are converted to ages
if Date == "NULL":
return " There is no "
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: # If today is the birthday
return "%d" % (Age)
else:
DaysLeft = NextYear - Today
return "%d" % (Age)
except:
return " error "
def seesql():
# View the student table database
db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
# Create a cursor object
cursor = db.cursor()
sql = "select s.sno,s.name,s.sex,s.cla,s.tel,s.birthday from stu s order by sno"
# with sno Sort to see the list of students
try:
m = cursor.execute(sql)
alist = cursor.fetchall()
print("{:>3}|\t{:<4}\t|{}|\t{:<3}\t\t| {:<8}|{}| {}".format(" Student id ", " The name ", " gender ", " The class ", " The phone ", " age ", " Date of birth "))
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 View failed! Error: ", err)
db.close()
def seeone(a):
# According to the student number, view a 1 The data
db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
# Create a cursor object
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(" Your input is incorrect and you will exit the system ")
quit()
else:
print("{:>3}|\t{:<4}\t|{}|\t{:<3}\t\t| {:<8}|{}| {}".format(" Student id ", " The name ", " gender ", " The class ", " The phone ", " age ", " Date of birth "))
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 Query failed! Error: ", err)
db.close()
def addmql():
# add 1 The data
db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
# Create a cursor object
cursor = db.cursor()
sql = "select s.sno from stu s"
cursor.execute(sql)
alist = cursor.fetchall() # All student numbers in the database are read here
blist = () # To establish 1 An empty tuple is used to store a student number
print(" The following student numbers have been occupied and cannot be used: ")
for i in alist:
blist += i # Store all student numbers
print(i[0], end=" ") # Output the student number that has been used
print()
sno = int(input(" Please enter the student number of the added student :\n"))
if sno in blist: # Determine whether the student number has been used. The student number should not be repeated
print(" The student number you entered has been occupied! The system is about to quit! ")
quit()
sname = input(" Please enter the name of the student you added :\n")
sex = input(" Please enter the gender of the student you added ( male or female ):\n")
if sex == " male " or sex == " female ":
sex = sex
else:
sex = " male "
print(" Gender input error , Default is male ")
cla = input(" Please enter the class of the added student ( Ex. : Python01):\n")
tel = input(" Please enter the number of the added student :\n")
if tel == re.search(r"(1[3456789]\d{9})", tel):
tel = tel
print(" Incorrect telephone input. Reset to null ")
else:
tel = ""
sbir = input(" Please enter the date of birth of the added student ( case :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(" Incorrect birth date input. Reset to original value ")
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)
# Transaction commit
db.commit()
print(" Number of bars added successfully: ", m)
print(" The information you add is :")
seeone(sno)
except Exception as err:
db.rollback()
print("SQL Add failed! Error: ", err)
db.close()
def updatasql():
# Update to modify a piece of data
db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
# Create a cursor object
cursor = db.cursor()
stuid = int(input(" Please enter the student id of the student to be modified: \n")) # 1 No more than one class 100 People, in order to stuid As the index
try:
seeone(stuid)
print("====== The name of the student information that can be modified ======")
print("{0:2}{1:5}{2:5}{3:5}".format(" ", "1. The name ", "2. gender ", "3. The class "))
print("{0:2}{1:5}{2}".format(" ", "4. The phone ", "5. Date of birth "))
a = int(input(" Please select the name of the student information you want to modify ( Student id cannot be modified ):\n"))
if a == 1:
xm = input(" Please enter the modified name: \n")
sql = "UPDATE stu s SET s.name = '%s' WHERE s.sno = '%d'" % (xm, stuid)
elif a == 2:
xb = input(" Please enter the modified gender ( male or female ):\n")
if xb == " male " or xb == " female ":
xb = xb
else:
xb = " male "
print(" Gender input error , Default is male ")
sql = "UPDATE stu s SET s.sex = '%s' WHERE s.sno = '%d'" % (xb, stuid)
elif a == 3:
bj = input(" Please enter the revised class :\n")
sql = "UPDATE stu s SET s.cla = '%s' WHERE s.sno = '%d'" % (bj, stuid)
elif a == 4:
dh = input(" Please enter the modified phone number :\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):
''' A regular expression match determines whether the input qualifies '''
dh = dh
else:
dh = ""
print(" Incorrect telephone input. Reset to null ")
elif a == 5:
birday = input(" Please enter your modified date of birth ( Format: 2000-1-1):")
if birday == re.search(r"(\d{4}-\d{1,2}-\d{1,2})", birday):
''' A regular expression match determines whether the input qualifies '''
birday = birday
else:
birday = "1949-10-01"
print(" Incorrect birth date input. Reset to original value ")
sql = "UPDATE stu s SET s.birthday = '%s' WHERE s.sno = '%d'" % (birday, stuid)
else:
print(" Your input error, will exit! ") # Exit here to prevent database data leakage due to some misoperation
quit()
cursor.execute(sql)
db.commit()
print(" The revised information of the student is as follows: ")
seeone(stuid)
except Exception as err:
db.rollback()
print("SQL Modification failed! Error: ", err)
db.close()
def delsql():
# Delete some student data
db = pymysql.connect(host="localhost", user="root", password="123456", db="stu", charset="utf8")
# Create a cursor object
cursor = db.cursor()
stuid = int(input(" Please enter the student id of the student to be deleted: \n")) # 1 No more than one class 100 People, in order to stuid As the index
try:
print("====== The name of the student information that will be deleted ======")
seeone(stuid)
a = input(" Please confirm whether to delete the student information (y/n):\n")
if a == 'y' or a == 'Y':
sql = "delete from stu where sno = '%d'"%(stuid)
cursor.execute(sql)
else:
print(" Cancel student information deletion, and you will soon quit the system ")
quit()
db.commit()
print(" The student information has been deleted ")
except Exception as err:
db.rollback()
print("SQL Delete failed! Error: ", err)
db.close()
def mainstu():
while True:
# Output initial interface
print("=" * 12, " Student information management system ", "=" * 15)
print("{0:2}{1:13}{2:15}".format(" ", "1. View student information ", "2. Add Student information "))
print("{0:2}{1:13}{2:15}".format(" ", "3. Modify student information ", "4. Delete student information "))
print("{0:2}{1:13}".format(" ", "5. Log out "))
print("=" * 45)
key = int(input(" Please enter the corresponding selection :\n"))
# Judge and operate according to the keyboard value
if key == 1:
print("=" * 12, " Student information browsing ", "=" * 15)
seesql()
input(" Press Enter to continue ")
elif key == 2:
print("=" * 12, " Student information addition ", "=" * 15)
addmql()
input(" Press Enter to continue ")
elif key == 3:
print("=" * 12, " Student information modification ", "=" * 15)
seesql()
updatasql()
input(" Press Enter to continue ")
elif key == 4:
print("=" * 12, " Student information deletion ", "=" * 15)
seesql()
delsql()
input(" Press Enter to continue ")
elif key == 5:
print("=" * 12, " goodbye ", "=" * 12)
quit()
else:
print("=" * 12, " Your input is incorrect, please re-enter ", "=" * 12)
mainstu()
Supporting database files containing data
-- 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,' zhang 3',' male ','Python01','12345678910','1999-01-01 00:00:00'),(2,2,' li 4',' male ','Python01','18866668888','1996-12-06 00:00:00'),(3,3,' The king 5',' male ','Python02','12345665410','1996-11-27 00:00:00'),(4,4,' zhao 6',' female ','Python02','12332233210','1997-10-24 00:00:00'),(5,5,'qq01',' female ','Python03','13322223322','1990-01-31 00:00:00'),(6,6,'qq02',' male ','Python03','12288886666','1992-02-20 00:00:00'),(7,7,'qq03',' female ','Python03','13579264801','2000-10-30 00:00:00'),(8,8,'uu01',' male ','Python01','18898084886','1998-08-08 00:00:00'),(9,9,'uu02',' female ','Python02','12022000022','1994-04-01 00:00:00'),(10,10,'aa',' female ','Python02','18899998888','2004-04-04 00:00:00'),(11,11,'bb',' male ','Python03','19264664234','1995-05-15 00:00:00'),(25,12,'uu10',' male ','Python04','17788992332','1996-12-06 00:00:00'),(28,13,'uu10',' female ','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