#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()
class_sqlite3
最新推荐文章于 2021-03-21 01:50:34 发布