python考核

连接数据库

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '10011122'
DBNAME = 'dbtest2'

db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
cur = db.cursor()  # 声明一个游标

managerSystem.py

from student import *
import threading
import time
import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '10011122'
DBNAME = 'dbtest2'

db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
cur = db.cursor()  # 声明一个游标


class StudentManager(object):


    # 一. 程序入口函数
    def run(self):
        # 1. 加载文件里面的学员数据

        while True:
            # 2. 显示功能菜单
            self.show_menu()
            # 3. 用户输入目标功能序号
            menu_num = int(input('请输入您需要的功能序号：'))

            # 4. 根据用户输入的序号执行不同的功能 -- 如果用户输入1，执行添加
            if menu_num == 1:
                # 添加学员
                self.add_student()
            elif menu_num == 2:
                # 删除学员
                self.del_student()
            elif menu_num == 3:
                # 修改学员信息
                self.modify_student()
            elif menu_num == 4:
                # 查询学员信息
                self.search_student()
            elif menu_num == 5:
                # 显示所有学员信息
                self.show_student()
            elif menu_num == 6:
                # 保存学员信息
                self.save_student()
            elif menu_num == 7:
                # 退出系统 -- 退出循环
                break

    # 二. 系统功能函数
    # 2.1 显示功能菜单 -- 打印序号的功能对应关系 -- 静态
    @staticmethod
    def show_menu():
        print('请选择如下功能：')
        print('1:添加学员')
        print('2:删除学员')
        print('3:修改学员信息')
        print('4:查询学员信息')
        print('5:显示所有学员信息')
        print('6:保存学员信息')
        print('7:退出系统')

    # 2.2 添加学员
    def add_student(self):
        # 1. 用户输入姓名、性别、手机号
        name = input('请输入您的姓名：')
        gender = input('请输入您的性别：')
        tel = input('请输入您的手机号：')

        sql = "insert into student(name,gender,tel)values(%s,%s,%s)"
        value = (name, gender, tel)
        try:
            cur.execute(sql, value)
            db.commit()
            print('添加成功')
        except:
            db.rollback()

    # 2.3 删除学员
    def del_student(self):
        # 1. 用户输入目标学员姓名
        del_name = input('请输入要删除的学员姓名：')

        sql =f"" f"DELETE FROM student where name='{del_name}'"""

        try:
            cur.execute(sql)
            db.commit()
            print('删除成功')

        except:
            db.rollback()

    # 2.4 修改学员信息
    def modify_student(self):
        # 1. 用户输入目标学员姓名
        modify_name = input('请输入要修改的学员姓名：')

        sql = f"""
                SELECT 1 FROM student WHERE name = "{modify_name}";
                """

        cur.execute(sql)
        student = cur.fetchall()
        if student:
            new_name = input("请输入姓名：")
            new_gender = input("请输入性别：")
            new_tel = input("请输入手机号：")
            sql2 = f"""
                    UPDATE stu SET name = "{new_name}",gender = "{new_gender},tel = "{new_tel}"WHERE name="{new_name}";
                    """
            cur.execute(sql2)
            db.commit()
        else:
            print('查无此人')
        print('修改成功')

    # 2.5 查询学员信息
    def search_student(self):
        # 1. 用户输入目标学员姓名
        search_name = input('请输入您要搜索的学员姓名：')

        sql = 'SELECT * FROM student WHERE name = %s'
        value = (search_name,)
        try:
            cur.execute(sql, value)
            results = cur.fetchall()
            print('查询成功！')
            for row in results:
                name = row[0]
                gender = row[1]
                tel = row[2]
                print('姓名:%s,性别:%s, 手机号:%s' % (name, gender, tel))
        except:
            print('查无此人！')

    # 2.6 显示所有学员信息
    def show_student(self):
        # 1. 打印表头
        print('姓名\t性别\t手机号')

        sql = "SELECT * FROM Student"
        try:
            cur.execute(sql)
            results = cur.fetchall()
            for row in results:
                name = row[0]
                gender = row[1]
                tel = row[2]
                print('姓名:%s,性别:%s, 手机号:%s' % (name, gender, tel))
        except pymysql.Error as e:
            print('数据显示失败：' + str(e))
        db.close()

    # 2.7 保存学员信息
    def save_student(self):
        print('保存成功！')
        t2 = threading.Timer(1, function=self.save_student)
        t2.start()
        t2.cancel()

    # 2.8 加载学员信息
    def load_student(self):
        # 1. 打开文件：尝试r打开，如果有异常w
        try:
            f = open('student.data', 'r')
        except:
            f = open('student.data', 'w')
        else:
            # 2. 读取数据：文件读取出的数据是字符串还原列表类型；[{}] 转换 [学员对象]
            data = f.readline()  # 字符串
            new_list = eval(data)
            for i in new_list:
                print(i['name'], i['gender'], i['tel'])
                name = i['name']
                gender = i['gender']
                tel = i['tel']
                try:
                    cur=db.cursor()
                    sql = "insert into student(name,gender,tel)values(%s,%s,%s)"
                    value = (name, gender, tel)
                    cur.execute(sql, value)
                    db.commit()
                    print('保存成功')
                    time.sleep(1)
                except:
                    print('保存失败')


        finally:
            # 3. 关闭文件
            f.close()

main.py

# 1. 导入管理系统模块
from managerSystem import *

# 2. 启动管理系统
# 保证是当前文件运行才启动管理系统：if --创建对象并调用run方法
if __name__ == '__main__':
    student_manager = StudentManager()
    student_manager.run()

student.py

class Student(object):
    def __init__(self, name, gender, tel):
        # 姓名、性别、手机号
        self.name = name
        self.gender = gender
        self.tel = tel

    def __str__(self):
        return f'{self.name}, {self.gender}, {self.tel}'