#coding:utf-8 import sqlite3 # 定义一个创建数据库文件并添加Student表的函数 def create_db_and_table(): # 先连上数据库文件 connect = sqlite3.connect('student.db') cursor = connect.cursor() # AUTOINCREMENT约束:表示该字段自动增长,一般用于INTEGER类型。 # UNIQUE约束:表示该字段值是唯一的。 # NOT NULL约束:表示该字段值不允许为空。 # IF NOT EXISTS:当Student表不存在时,再执行创建的sql语句,如果表已经存在, 则sql语句不再执行。可以避免异常。 create_sql = 'CREATE TABLE IF NOT EXISTS Student (id INTEGER PRIMARY KEY UNIQUE, name TEXT NOT NULL, age INTEGER, score FLOAT)' cursor.execute(create_sql) # 执行一个或多个事务 connect.commit() cursor.close() connect.close() # 添加学员信息 def add_student(): connect = sqlite3.connect('student.db') cursor = connect.cursor() name = raw_input('请输入姓名:') age = input('请输入年龄:') score = input('请输入成绩:') # sql语句中%s如果代表的是字符串类型,需要加上引号。 insert_sql = 'INSERT INTO Student (name, age, score) VALUES ("%s", %d, %f)'%(name,age,score) cursor.execute(insert_sql) connect.commit() cursor.close() connect.close() def select_all_student_info(is_total_number): connect = sqlite3.connect('student.db') cursor = connect.cursor() # select_sql = 'SELECT count(*) FROM Student' 查询表中一共多少条 数据记录,返回整数。 # select_sql = 'SELECT (name, age) FROM Student' 只查询name及age 字段的值。 if is_total_number == True: # 是在获取数据的总数量 select_sql = 'SELECT count(*) FROM Student' res = cursor.execute(select_sql) # fetchone()是Cursor类中的一个方法,用于匹配一条数据 # fetchall()是Cursor类中的一个方法,用于匹配多条数据 count = res.fetchone()[0] return count else: # 查询所有学员数据 select_sql = 'SELECT * FROM Student' result_list = cursor.execute(select_sql) for id, name, age, score in result_list: print id,'. ',name,age,score connect.commit() cursor.close() connect.close() def update_student_info(): # 修改之前,先查询所有学员的信息 select_all_student_info(False) select_number = input('请选择要修改的学员编号:') # 需要动态从数据库的表中查询所有学员的数量 while select_number < 1 or select_number > select_all_student_info (True): select_number = input('学员编号错误,请重新选择要修改的学员编号:') # 获取修改过后的学员信息 name = raw_input('请输入修改后的姓名:') age = input('请输入修改后的年龄:') score = input('请输入修改后的成绩:') update_sql = 'UPDATE Student SET name="%s", age=%d, score=%f WHERE id=%d'%(name,age,score,select_number) connect = sqlite3.connect('student.db') cursor = connect.cursor() cursor.execute(update_sql) connect.commit() cursor.close() connect.close() def delete_student_info(): print '1-删除指定的学员信息' print '2-删除所有的学员信息' connect = sqlite3.connect('student.db') cursor = connect.cursor() select_number = input('请选择操作编号:') # 声明变量,用于记录最终执行的是1中的sql语句还是2中的sql语句 delete_sql = "" if select_number == 1: select_all_student_info(False) number = input('请选择要删除的学员编号:') while number < 1 or number > select_all_student_info(True): number = input('学员编号错误,请重新选择要删除的学员编号:') delete_sql = 'DELETE FROM Student WHERE id=%d'%number else: # 默认是清空表中的所有数据 delete_sql = 'DELETE FROM Student' cursor.execute(delete_sql) connect.commit() cursor.close() connect.close() if __name__ == '__main__': create_db_and_table() # select_all_student_info(True) while True: print ''' 1. 添加学员信息 2. 修改学员信息 3. 查询学员信息 4. 删除学员信息 0. 退出程序 ''' select_number = input('请选择操作:') if select_number == 1: add_student() print '\n添加学员成功!\n' elif select_number == 2: update_student_info() print '\n修改学员信息成功!\n' elif select_number == 3: select_all_student_info(False) elif select_number == 4: delete_student_info() print '\n删除学员信息成功!\n' else: break
student_sqlite3
最新推荐文章于 2022-09-17 14:42:39 发布