学生管理数据库系统开发实例分析
数据说明
通过一个学生管理数据库系统的开发过程来展示如何系统地基于Python实现SQLite数据库编程。首先提出数据库系统的基本结构，以及如何通过SQLite逐步达到这些要求并进行常规操作。
- 该学生管理数据库系统包括四张表：专业表、学生表、课程表和成绩表；
- 实现对学生信息、专业信息、课程信息和成绩的综合管理。
（1）专业表 专业表包括专业编号和专业名称两个列，具体设置见如下表。
（2）学生表 学生表包括学号、姓名、性别、生日、专业编号、奖学金、党员、照片和备注等列，具体设置见如下表。其中，学生表中的专业编号以专业表中的专业编号作为外键，实施参照完整性。
（3）课程表 课程表包括课程号、课程名称、先修课程代码、学时和学分等列，具体设置如下表。
（4）成绩表 成绩表包含三列：学号、课程号和成绩，具体设置如下表。其中，学号和课程号共同构建为主键。同时，该表中的学号以学生表的学号作为外键，课程号以课程表中的课程号作为外键，实施参照完整性。
2．数据准备
为了规范数据输入，分别用4个txt文档存储4张表原始数据。文档中的数据组织形式为：列1值,列2值,…。以专业表为例，在对应的txt文档中，数据组织形式如下: 01,国际经济与贸易 02,工商管理 … 16,第二学位班 因此，在构建好相应的数据表结构之后，可方便地编写函数统一将txt文档中的数据导入到对应数据表中。
3．关键函数
为了减少数据库系统构建过程中代码的重复，应将可能重复执行的代码包装成函数。本系统开发中构建了以下函数： （1）数据表创建及数据导入函数：create_table； （2）数据表结构查询函数：table_struct； （3）数据表记录查询函数：table_quer。
4．数据库系统构建代码实现
# coding=utf-8 import sqlite3 conn = sqlite3.connect('Shift_MIS.db') cur = conn.cursor() cur.execute("PRAGMA foreign_keys=ON") # 构建数据表创建及文本数据导入函数 def create_table(tab_name, col_prop_list, txt_path, conn=conn, cur=cur): col_name_props = ','.join(col_prop_list) cur.execute('CREATE TABLE IF NOT EXISTS %s(%s)'%(tab_name,col_name_props)) f = open(txt_path,'r') for x in f: x = x.rstrip().split(',') a = [ "'%s'" % x[i] for i in range(len(x))] x = ','.join(a) cur.execute('INSERT INTO %s values(%s)'%(tab_name,x)) f.close() print('%s创建成功' % tab_name) print(' %s导入成功' % txt_path) conn.commit() # 构建数据表结构查询函数 def table_struct(tab_name, cur=cur): cur.execute("PRAGMA table_info(%s)" % tab_name) t_struct = cur.fetchall() for item in t_struct: for x in item: x = str(x) print(x, sep='\t', end=' ') print() # 构建数据表内容查询函数 def table_quer(tab_name, col_names='*', num_line=None, cur=cur): cur.execute('select %s from %s' % (col_names, tab_name)) Li = cur.fetchall() for line in Li[:num_line]: for item in line: print(item, sep='\t', end=' ') print() if __name__ == '__main__': #（1）创建专业表 tab_name_1 = '专业表' col_prop_list_1 = ['专业编号 varchar(7) primary key', '专业名称 varchar(7)'] txt_path_1 ='专业表.txt' create_table(tab_name_1,col_prop_list_1,txt_path_1) #（2）创建学生表 tab_name_2 = '学生表' col_prop_list_2 = ['学号 varchar(7) primary key', '姓名 varchar(7)', '性别 tinyint', '生日 text NULL', '专业编号 varchar(7) REFERENCES 专业表(专业编号) ON UPDATE CASCADE ON DELETE CASCADE', '奖学金 numeric NULL', '党员 tinyint NULL', '照片 blob NULL', '备注 text NULL'] txt_path_2 = '学生表.txt' create_table(tab_name_2, col_prop_list_2, txt_path_2) # （3） 创建课程表 tab_name_3 = '课程表' col_prop_list_3 = ['课程号 varchar(7) primary key', '课程名称 varchar(7) NULL', '先修课程代码 varchar(7) NULL', '学时 smallint', '学分 smallint'] txt_path_3 = '课程表.txt' create_table(tab_name_3, col_prop_list_3, txt_path_3) # （4）创建成绩表 tab_name_4 = '成绩表' col_prop_list_4 = ['学号 varchar(7) REFERENCES 学生表(学号) ON UPDATE CASCADE ON DELETE CASCADE', '课程号 varchar(7) REFERENCES 课程表(课程号) ON UPDATE CASCADE ON DELETE CASCADE', '成绩 smallint NULL', 'PRIMARY KEY (学号,课程号)'] txt_path_4 = '成绩表.txt' create_table(tab_name_4,col_prop_list_4,txt_path_4)
5．数据库系统构建结果
6．数据库操作
首先，查询数据库中所有的数据表。
#首先，查询数据库中所有的数据表。 for x in cur.execute("select name from sqlite_master where type = 'table' order by name").fetchall(): print(x[0])
#查询专业表的结构及前10行内容。 table_struct('专业表') table_quer('专业表', col_names='*', num_line=10)=
#综合查询。以下查询语句的执行将返回国际贸易法课程成绩小于60的学生的学号、姓名、课程名称和成绩，并按学号的升序排列。 >>> cur.execute('''SELECT 学生表.学号,学生表.姓名,课程表.课程名称,成绩表.成绩 FROM 学生表 JOIN 成绩表 JOIN 课程表 ON 学生表.学号=成绩表.学号 AND 课程表.课程号=成绩表.课程号 WHERE 成绩表.成绩 < 60 and 课程表.课程名称=’国际贸易法’ ORDER BY 学生表.学号 ASC''') >>> for line in cur.fetchall(): for x in line: print(x, sep='\t', end= ' ') print
