class_sqlite

coding:utf-8

import sqlite3

声明数据模型类，数据模型类中只包含属性，而不包含操作数据的函数。

class Student(object):
    """
    数据模型类设置的属性需要和数据库中设置的字段保持一致。
    """
    def __init__(self, name, age, score, id=0):
        super(Student, self).__init__()
        self.id = id
        self.name = name
        self.age = age
        self.score = score

声明数据库工具类，工具类中一般只包含操作函数，而不包含属性。

class DBManager(object):
    """
    实现数据库的增删改查的功能。
    args:
        db_path: 保存要操作的数据库路径
        table_name: 保存数据库中的表名
        connect: 保存数据库链接对象
        cursor: 保存游标对象
    """
    def __init__(self, db_path, table_name):
        super(DBManager, self).__init__()
        self.db_path = db_path
        self.table_name = table_name
        self.connect = None
        self.cursor = None

    # 定义获取数据库链接对象和游标对象的函数
    def get_connect_and_cursor(self):
        self.connect = sqlite3.connect(self.db_path)
        self.cursor = self.connect.cursor()
        # return connect,cursor

    # 定义关闭数据库及游标的函数
    # def close_and_commit(self, connect, cursor):
    #     connect.commit()
    #     cursor.close()
    #     connect.close()
    def close_and_commit(self):
        self.connect.commit()
        self.cursor.close()
        self.connect.close()

    # 定义添加数据的函数
    def insert_student_info(self, stu_obj):
        insert_sql = 'insert into ' + self.table_name + '(name, age, score) values ("%s", %d, %f)'%(stu_obj.name, stu_obj.age, stu_obj.score)
        self.cursor.execute(insert_sql)
    def update_student_info(self, number, stu_obj):
        update_sql = 'update ' + self.table_name + ' set name="%s",age=%d,score=%f where id=%d'%(stu_obj.name, stu_obj.age, stu_obj.score, number)
        self.cursor.execute(update_sql)
    def select_student_info(self):
        select_sql = 'select * from '+self.table_name
        result = self.cursor.execute(select_sql)
        for id,name,age,score in result:
            print id,'. ',name,age,score

    def delete_student_info(self):
        pass

    # 定义创建表的函数
    def create_table(self):
        create_sql = 'CREATE TABLE IF NOT EXISTS ' + self.table_name + '(id INTEGER PRIMARY KEY UNIQUE, name TEXT, age INTEGER, score FLOAT)'
        self.cursor.execute(create_sql)

if __name__ == '__main__':
    # 创建工具类对象
    db_handler = DBManager('database.db', 'Student')
    # 创建数据库链接对象及游标对象
    db_handler.get_connect_and_cursor()
    # 创建表
    db_handler.create_table()
    # 提交并关闭数据库及游标
    db_handler.close_and_commit()

    while True:
        print '''
        1. 添加学员信息
        2. 修改学员信息
        3. 查询学员信息
        4. 删除学员信息
        0. 退出程序
        '''
        # 创建工具类对象
        db_handler = DBManager('database.db', 'Student')
        # 创建数据库链接对象及游标对象
        db_handler.get_connect_and_cursor()
        select_number = input('选择操作：')
        if select_number == 1:
            name = raw_input('请输入姓名：')
            age = input('请输入年龄：')
            score = input('请输入成绩：')
            # 将三个数据保存到对象的属性中
            stu = Student(name,age,score)
            db_handler.insert_student_info(stu)
        elif select_number == 2:
            db_handler.select_student_info()
            number = input('请选择修改学员的编号：')
            name = raw_input('请输入修改后的姓名：')
            age = input('请输入修改后的年龄：')
            score = input('请输入修改后的成绩：')
            stu = Student(name,age,score)
            db_handler.update_student_info(number,stu)
        elif select_number == 3:
            db_handler.select_student_info()
        elif select_number == 4:
            pass
        else:
            break
        db_handler.close_and_commit()