第17章课件中，学生管理数据库系统实现例题以提供了数据表创建及数据导入函数create_table、数据表结构查询函数table_struct和数据表记录查询函数table_quer，请根据该题题意，自行设计数据表记录修改函数table_update和数据表记录删除table_del函数,并写出代码。
SQLite数据库和sqlite3模块
- SQLite是一款开源的轻型的数据库
- SQLite支持的数据类型包括：NULL、INTEGER、REAL、TEXT和BLOB，分别对应Python的数据类型：None、int、float、str和bytes
- sqlite3模块提供访问和操作数据库sqlite的各种功能
1、使用sqlite3模块连接和操作SQLite数据库
（1）导入相应的数据库模板
（2）建立数据库连接，返回Connection对象
（3）创建游标对象cur
（4）使用Cursor对象的execute执行SQL命令返回结果
（5）获取游标的查询结果集
（6）数据库的提交和回滚
（7）关闭Cursor对象和Connection对象
2、sqlite3模板
Python的标准库已包含一个PySQLite模块，用于实现SQLite相关操作。因此使用较新版本Python的用户无需再单独安装PySQLite和SQLite就可实现数据库编程。在具体编程中，可以将SQLite作为名为sqlite3的模块导入，然后使用DB-API中相关的工具与方法进行Python数据库编程。
import sqlite3
（1）创建（打开）数据库
sqlite3模块遵循DB-API的一般方法，因此应构建起Python程序与SQLite之间的连接。可通过创建连接对象实现该功能：
import sqlite3
conn = sqlite3.connect('test.db')
以上语句构建了一个名称为conn的连接对象，通过它可实现Python程序与SQLite数据库test.db之间的连接。如果test.db数据库文件已经存在，则会打开该数据库，建立起它与程序之间的连接；如果test.db数据库文件不存在，则在程序根目录下新建该数据库文件，并建立起连接。
（2）创建游标
一旦构建了连接对象，就可以使用DB-API的标准方法：利用cursor()新建一个游标对象；利用execute()执行SQL语句；利用commit()执行事务提交；利用rollback()实现事物回滚；利用close()实现数据库系统关闭。为了进一步的SQL操作，需进一步构建的一个游标对象：
cur = conn.cursor()
(3)创建表
已经有了test.db数据库的连接和游标。在此基础上，应首先构建两个数据表
数据表genre包含两个列g_id和g_name，其中g_id是种类编号，整型，主键；g_name是种类名称，字符型，不为空。
数据表book包含五个列，其中对b_genre应用了外键约束UPDATE CASCADE和DELETE CASCADE，即如果genre中的g_id如果被更新或者删除，则b_genre也会进行相应更新或删除。
如果需要在已构建的表中增加列，则可通过运行以下类似语句实现：
cur.execute('''ALTER TABLE genre ADD COLUMN g_comm text NULL''')
以上语句实现了往表genre插入新列g_comm。
（4）插入数据
构建好表的结构之后，就可以往表中插入数据。
cur.execute('''insert into genre values(1,'History','to know the history of human wolrd')''')
cur.execute('''insert into genre values(2,'Social science','to better understand the society')''')
cur.execute("insert into genre values(?,?,?)",(3,'Fiction','to better understand humanity'))
（5）更新数据
在具体数据库应用中，更新操作是维护数据库系统的重要方法之一。
cur.execute("UPDATE book SET b_price='$45' WHERE b_id == 31")
（6）查询数据
cur.execute('''SELECT book.b_id,book.b_name,book.b_price,genre.g_name
FROM book join genre
ON book.b_genre = genre.g_id
WHERE genre.g_id = 3''')
fetchone(): 该方法获取下一个查询结果集。结果集是一个对象；
fetchall():接收全部的返回结果行；
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
（7）删除数据
cur.execute("DELETE FROM book WHERE b_genre=3")
运行以下语句，可删除表中所有记录：
cur.execute("DELETE FROM book")
学生管理数据库系统的实现
为了减少数据库系统构建过程中代码的重复，应将可能重复执行的代码包装成函数。本系统开发中构建了以下函数：
（1）数据表创建及数据导入函数：create_table；
（2）数据表结构查询函数：table_struct；
（3）数据表记录查询函数：table_quer；
（4）数据表记录修改函数：table_update；
（5）数据表记录删除函数：table_del。
1、数据表创建及数据导入函数：create_table；
# -*- coding: utf-8 -*-
# @Time : 2022/12/12 22:49
# @Author未晞~
# @FileName: new2.py
# @Software: PyCharm
# 构建数据表结构查询函数
import sqlite3
conn = sqlite3.connect('student.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 ='D:\桌面\大三啦\课程\Python\第十七章\专业表.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 = 'D:\桌面\大三啦\课程\Python\第十七章\学生表.txt'
create_table(tab_name_2, col_prop_list_2, txt_path_2)
tab_name_3 = '课程表'
col_prop_list_3 = ['课程号 varchar(7) primary key',
'课程名称 varchar(7) NULL',
'先修课程代码 varchar(7) NULL',
'学时 smallint', '学分 smallint']
txt_path_3 = 'D:\桌面\大三啦\课程\Python\第十七章\课程表.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 = 'D:\桌面\大三啦\课程\Python\第十七章\成绩表.txt'
create_table(tab_name_4, col_prop_list_4, txt_path_4)
注意：他不能重复建立
之后在.py文档的根目录下生成数据库文件student.db
（2）数据表结构查询函数：table_struct；
# 构建数据结构查询函数
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()
（3）数据表记录查询函数：table_quer；
# 构建数据表内容查询函数
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()
（4）数据表记录修改函数：table_update；
# 构建数据表内容更新函数
def table_update(tab_name, col_name1,value1,col_name2,value2,cur=cur):
sql = "update %s set" % tab_name
for i in range(len(col_name1)):
sql = sql+col_name1[i]+'='+"\'"+str(value1[i])+"\'"
if i!=len(col_name1)-1:
sql = sql+","
sql = sql+'where'
for i in range(len(col_name2)):
sql = sql + col_name2[i] + "=" + "\'" + str(value2[i]) + "\'"
if i!=len(col_name2)-1:
sql = sql+","
cur.execute(sql)
print(f"update:{conn.total_changes}")
conn.commit()
（5）数据表记录删除函数：table_del。
# 构建数据表内容删除函数
def table_del(tab_name, col_names='*',value='*', cur=cur):
cur.execute( 'DELETE from %s where %s = %s' % (tab_name, col_names,value))
conn.commit()
print(f"del:{conn.total_changes}")
更新的
总的
# -*- coding: utf-8 -*-
# @Time : 2022/12/12 23:12
# @Author:孙冠楠
# @FileName: new2.py
# @Software: PyCharm
# 构建数据表结构查询函数
import sqlite3
conn = sqlite3.connect('student.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()
# 构建数据表内容更新函数
def table_update(tab_name, col_name1,value1,col_name2,value2,cur=cur):
sql = "update %s set" % tab_name
for i in range(len(col_name1)):
sql = sql+col_name1[i]+'='+"\'"+str(value1[i])+"\'"
if i!=len(col_name1)-1:
sql = sql+","
sql = sql+'where'
for i in range(len(col_name2)):
sql = sql + col_name2[i] + "=" + "\'" + str(value2[i]) + "\'"
if i!=len(col_name2)-1:
sql = sql+","
cur.execute(sql)
print(f"update:{conn.total_changes}")
conn.commit()
# 构建数据表内容删除函数
def table_del(tab_name, col_names='*',value='*', cur=cur):
cur.execute( 'DELETE from %s where %s = %s' % (tab_name, col_names,value))
conn.commit()
print(f"del:{conn.total_changes}")
if __name__ == '__main__':
#（1）创建专业表
tab_name_1 = '专业表'
col_prop_list_1 = ['专业编号 varchar(7) primary key',
'专业名称 varchar(7)']
txt_path_1 ='D:\桌面\大三啦\课程\Python\第十七章\专业表.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 = 'D:\桌面\大三啦\课程\Python\第十七章\学生表.txt'
create_table(tab_name_2, col_prop_list_2, txt_path_2)
tab_name_3 = '课程表'
col_prop_list_3 = ['课程号 varchar(7) primary key',
'课程名称 varchar(7) NULL',
'先修课程代码 varchar(7) NULL',
'学时 smallint', '学分 smallint']
txt_path_3 = 'D:\桌面\大三啦\课程\Python\第十七章\课程表.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 = 'D:\桌面\大三啦\课程\Python\第十七章\成绩表.txt'
create_table(tab_name_4, col_prop_list_4, txt_path_4)
for x in cur.execute("select name from sqlite_master where type = 'table' order by name").fetchall():
print(x[0])
# 查询数据库中所有的数据表
for x in cur.execute("select name from sqlite_master where type = 'table' order by name").fetchall():
print(x[0])
# 数据结构查询函数
print( table_struct('专业表'))
# 构建数据表内容查询函数
print(table_quer('专业表', col_names='*', num_line=10))
# 数据结构查询函数
print( table_struct('学生表'))
col_list = '学号,姓名,专业编号,奖学金'
# 构建数据表内容查询函数
print('数据表内容查询函数')
table_quer('学生表', col_names=col_list, num_line=10)
# 构建数据表内容更新函数
print('数据表内容更新函数')
table_update('专业表',[' 专业名称'],['软件工程'],[' 专业编号'],['01'])
table_quer('专业表')
# 构建数据表内容删除函数
print("数据表内容删除函数")
table_del('学生表','学号','0305362')
table_quer('学生表')
2、数据库操作
for x in cur.execute("select name from sqlite_master where type = 'table' order by name").fetchall():
print(x[0])
2)查询学生表的结构及前10行内容
3)查询课程表的结构及前10行内容。
4)查询成绩表的结构及前10行内容。
5）综合查询。以下查询语句的执行将返回国际贸易法课程成绩小于60的学生的学号、姓名、课程名称和成绩，并按学号的升序排列。
4540
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
