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()