链接数据库的学员管理系统

#coding:Utf-8
# 学员管理系统

import sqlite3

# 管理器
class StudentManager(object):

    @classmethod
    def sqlite3_operation(cls,type, *args):
        '''
        :param type: -1 0 1     2查询  3创建表
        :param args: 学员信息
        :return:
        '''
        connect = sqlite3.connect('student.db')
        cursor = connect.cursor()
        if type == 2:
            # 查询
            students = cls.query_stu(cursor)
            return students
        elif type == 3:
            cls.create_table(cursor)
        else:
            # 增删改
            cls.operation_stu(type,connect,cursor,*args)

        cursor.close()
        connect.close()

    @classmethod
    def create_table(cls,cursor):

        try:
            cursor.execute('CREATE TABLE Student(id INTEGER PRIMARY KEY ,name TEXT, age INTEGER, phone TEXT)')
        except Exception as e:
            print(e)


    # 添加学员
    @classmethod
    def add_stu(cls):
        ID = int(input('请输入学员学号：'))
        name = input('请输入学员姓名：')
        age = int(input('请输入学员年龄'))
        phone = input('请输入学员电话：')

        # 执行添加学员的sqlite操作
        cls.sqlite3_operation(1,ID,name,age,phone)

    # 查询学员
    @classmethod
    def query_stu(cls,cursor):
        rs = cursor.execute("SELECT * FROM Student")
        result_list = []
        for stu in rs:
            print('学号：{} 姓名：{} 年龄：{} 电话：{}'.format(stu[0],stu[1],stu[2],stu[3]))
            result_list.append(stu)
        # 返回列表
        return result_list

    # 修改学员
    @classmethod
    def update_stu(cls):
        students = cls.sqlite3_operation(2)
        if len(students) ==0:
            print('没有学员信息，请先添加学员！')
            return
        ID = int(input('请选择要修改的学员学号：'))

        while ID not in [s[0] for s in students]:
            ID = int(input('没有该学号，请重选：'))

        stu = [s for s in students if s[0]==ID]

        name = input('请输入修改后的姓名(%s)：'%stu[0][1])
        age = int(input('请输入修改后的年龄(%s)：'%stu[0][2]))
        phone = input('请输入修改后的电话(%s)：'%stu[0][3])

        # 执行修改操作
        cls.sqlite3_operation(0,ID,name,age,phone)

    # 删除学员
    @classmethod
    def delete_stu(cls):
        students = cls.sqlite3_operation(2)
        if len(students) == 0:
            print('没有学员信息，请先添加学员！')
            return
        ID = int(input('请选择要删除的学员学号：'))

        while ID not in [s[0] for s in students]:
            ID = int(input('没有该学号，请重选：'))

        # 执行删除操作
        cls.sqlite3_operation(-1,ID)

    # 传入操作类型，然后根据学员信息，做出对应的操作
    @classmethod
    def operation_stu(cls, type,connect,cursor,*args):
        '''
        :param type: 操作类型 1表示添加学员  0表示修改学员  -1删除学员
        :param args: 学员信息
        :return: None
        '''
        if type not in range(-1,2):
            raise TypeError('type must be int, 1 or 0 or -1 !')

        sql = ''
        # 根据不同的操作类型，拼接sql语句
        if type == 1:
            # 添加学员
            sql = "INSERT INTO Student(ID,name,age,phone)VALUES({},'{}',{},'{}')".format(args[0],args[1],args[2],args[3])
        elif type == 0:
            # 修改学员
            sql = "UPDATE Student SET name='{}',age={},phone='{}' WHERE id={}".format(args[1],args[2],args[3],args[0])
        else:
            # 删除学员
            sql = "DELETE FROM Student WHERE id={}".format(args[0])

        try:
            cursor.execute(sql)
            connect.commit()
        except Exception as e:
            print(e)




if __name__ == '__main__':
    # 创建表
    StudentManager.sqlite3_operation(3)
    while True:
        print("""
        1.添加学员
        2.修改学员
        3.查询学员
        4.删除学员
        0.退出程序
        """)
        num = int(input('请选择您的操作：'))
        while num not in range(0,5):
            num = int(input('选项错误，请重选：'))
        if num == 1:
            # 执行添加学员的操作
            StudentManager.add_stu()
        elif num == 2:
            StudentManager.update_stu()
        elif num == 3:
            StudentManager.sqlite3_operation(2)
        elif num == 4:
            StudentManager.delete_stu()
        else:
            break