Python编程六级 题库
使用Python的sqlite3库完成以下操作:
1.创建一个名为cpu的数据库文件,并创建一张Rate的表(表有三个字段:ID、Rate、updatetime)
2.记录下十秒钟cpu相关数据,并删除第id为1的数据。
import sqlite3
import datetime
import psutil #获取cpu当前占比
conn = sqlite3.connect(" ① ")
creatsql = "create table Rate(ID integer primary key, Rate float,updatetime time)"
②
cur.execute(creatsql)
conn.commit()
insertsql = "insert into Rate(ID,Rate,updatetime) values(%d,%f,'%s')"
checksql = "select * from Rate"
for x in range(0,10):
nowtime = datetime.datetime.now()
nowtime = nowtime.strftime('%Y-%m-%d %H:%M:%S')
cpu_per = float(psutil.cpu_percent(1))
cur. ③ (insertsql % (x,cpu_per,nowtime))
conn.commit()
cur.execute(checksql)
data = cur.fetchall()
delsql="delete from Rate where ID=%d"
cur.execute(delsql %1)
conn.commit()
④
conn.close()
#参考程序:
import sqlite3
import datetime
import psutil
conn = sqlite3.connect("cpu.db")
creatsql = "create table Rate(ID integer primary key, Rate float,updatetime time)"
cur = conn.cursor()
cur.execute(creatsql)
conn.commit()
insertsql = "insert into Rate(ID,Rate,updatetime) values(%d,%f,'%s')"
checksql = "select * from Rate"
for x in range(0,10):
nowtime = datetime.datetime.now()
nowtime = nowtime.strftime('%Y-%m-%d %H:%M:%S')
cpu_per = float(psutil.cpu_percent(1))
cur.execute(insertsql % (x,cpu_per,nowtime))
conn.commit()
cur.execute(checksql)
data = cur.fetchall()
delsql="delete from Rate where ID=%d"
cur.execute(delsql %1)
conn.commit()
cur.close()
conn.close()评分标准:
(1)cpu.db;(2分)
(2)cur = conn.cursor();(3分)
(3)execute;(2分)
(4)cur.close()。(3分)
小强建立了一个简易的学生成绩管理信息系统,使用SQlite进行数据的插入、查询和删除操作,每位学生的记录存储在data.db文件的数据表STUDENT中,该表包含NAME,NUMBER和GRADE 3个字段。程序运行时,输出操作选择,用户输入1表示插入记录,输入姓名,学号和成绩后,记录将增加到数据库中。用户输入2表示查询记录,输出所有学生的姓名,用户输入姓名后可查询详细的姓名,学号和成绩。用户输入3,再输入要删除的学生的姓名,根据姓名对数据库中的记录进行删除。程序运行界面如图所示。

程序代码如下,请在划线处填入合适的代码。(本题无需运行通过,写入完整代码即可)
import sqlite3
while True:
code = input("1代表插入,2代表查询, 3代表删除,4代表退出\n请输入操作代号:")
if code == '1':
# 执行插入操作,代码略
if ① :
print('所有学生姓名如下:')
conn2 = sqlite3.connect('data.db')
c2 = conn2.cursor()
cursor = c2.execute("SELECT NAME from ② ")
for row in cursor:
print(row)
conn2.close()
select_name = input("请输入需要查询的姓名:")
conn3 = sqlite3.connect('data.db')
c3 = conn3.cursor()
cursor = c3.execute("SELECT * from STUDENT WHERE NAME =? ", ③ )
for row in cursor:
print(* row)
print('select successful!')
conn3.close()
if code == '3':
delete_name = input("请输入需要删除的姓名:")
conn4 = sqlite3.connect('data.db')
c4 = conn4.cursor()
cursor = c4.execute("DELETE from STUDENT WHERE NAME =?",(delete_name,))
④
print('delete successful!')
conn4.close()
if code == '4':
⑤import sqlite3
while True:
code = input("1代表插入,2代表查询, 3代表删除,4代表退出\n请输入操作代号:")
if code == '1':
# 执行插入操作,代码略
if code == '2':
print('所有学生姓名如下:')
conn2 = sqlite3.connect('data.db')
c2 = conn2.cursor()
cursor = c2.execute("SELECT NAME from STUDENT")
for row in cursor:
print(row)
conn2.close()
select_name = input("请输入需要查询的姓名:")
conn3 = sqlite3.connect('data.db')
c3 = conn3.cursor()
cursor = c3.execute("SELECT * from STUDENT WHERE NAME =? ",(select_name,))
for row in cursor:
print(* row)
print('select successful!')
conn3.close()
if code == '3':
delete_name = input("请输入需要删除的姓名:")
conn4 = sqlite3.connect('data.db')
c4 = conn4.cursor()
cursor = c4.execute("DELETE from STUDENT WHERE NAME =?",(delete_name,))
conn4.commit()
print('delete successful!')
conn4.close()
if code == '4':
break评分标准:
(1)code == '2';(2分)
(2)STUDENT;(2分)
(3)(select_name,);(2分)
(4)conn4.commit();(2分)
(5)break。(2分)
创建学生库
使用Python的sqlite3库完成以下问题。
(1)创建一个名为students的数据库;
(2)在这个数据库中,创建一个名为students_table的表,包含以下字段:id(主键),name(学生的名字),age(学生的年龄),grade(学生的年级);
(3)向students_table中插入至少5个学生的数据;
(4)查询年龄大于18岁的所有学生,并打印结果;
(5)将名字为"Alice"的学生的年龄增加1岁;
(6)删除名字为"Bob"的学生。
(本题无需运行通过,写入代码即可)
import sqlite3
conn = sqlite3.connect('students.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE students_table(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,age INTEGER,grade TEXT)''')
students = [ ('Alice', 17, '10th'), ('Bob', 18, '11th'), ('Charlie', 16, '10th'), ('David', 19, '12th'),('Eve', 17, '11th')]
cursor.executemany('''INSERT INTO students_table (name, age, grade) VALUES (?, ?, ?)''', students)
conn.commit()
cursor.execute('SELECT * FROM students_table WHERE age > 18')
print("年龄大于18岁的学生:")
print(cursor.fetchall())
cursor.execute('UPDATE students_table SET age = age + 1 WHERE name = "Alice"')
cursor.execute('DELETE FROM students_table WHERE name = "Bob"')
conn.commit()
conn.close()评分标准:
(1)students.db 或等效答案;(2分)
(2)CREATE TABLE 或等效答案; (2分)
(3)WHERE age > 18 或等效答案;(3分)
(4)fetchall() 或等效答案。(3分)
成绩统计
(1)在D盘根目录下建立一个空数据库Cjdata.db,按照如图所示的表结构,创建学生基本情况表cj;

(2)为表cj添加数据,内容是”学号—— 20210919、班级——4、姓名——赵雨乐、性别——女、成绩——98“;
(3)查询并输出表cj中成绩低于100的数据。
请根据要求,补全代码。(本题无需运行通过,写入代码即可)
import sqlite3
conn = ①
cursor = conn.cursor()
cursor.execute('''CREATE TABLE cj
( ② ,
班级 INTEGER,
姓名 TEXT(10) NOT NULL,
性别 TEXT(1) NOT NULL,
成绩 REAL);''')
cursor.execute(" ③ ('20210919',4,'赵雨乐','女',98)")
conn.commit()
cursor.execute(" ④ ")
for i in ⑤ :
print(i)
conn.close()
import sqlite3
conn =sqlite3.connect("d:/Cjdata.db")
cursor = conn.cursor()
cursor.execute('''CREATE TABLE cj
(学号 TEXT(10) PRIMARY KEY NOT NULL,
班级 INTEGER,
姓名 TEXT(10) NOT NULL,
性别 TEXT(1) NOT NULL,
成绩 REAL);''')
cursor.execute("INSERT INTO CJ VALUES('20210919',4,'赵雨乐','女',98)")
conn.commit()
cursor.execute("SELECT * from cj where 成绩<100")
for i in cursor.fetchall():
print(i)
conn.close()评分标准:
(1)sqlite3.connect("d:/Cjdata.db") 或等效答案;(2分)
(2)学号 TEXT(10) PRIMARY KEY NOT NULL 或等效答案; (2分)
(3)INSERT INTO CJ VALUES 或等效答案;(2分)
(4)SELECT * from cj where 成绩<100 或等效答案;(2分)
(5)cursor.fetchall() 或等效答案。(2分)
数据库操作
某健身房要开发一个会员管理系统,需要程序完成以下操作:
(1)连接数据库gym.db
(2)创建会员信息表member,包括:编号(id,文本型,主键)、姓名(name,文本型,不能为空)、剩余天数(days,整数型)
(3)插入一条会员数据
(4)查询所有剩余天数小于30天的会员,并输出提醒
请根据代码框架补全代码。(本题无需运行通过,写入代码即可)
import sqlite3
# 连接数据库
conn = sqlite3.connect('gym.db')
# 创建表
conn.execute('''CREATE TABLE IF NOT EXISTS member
(id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
days INTEGER);''')
# 插入一条数据
conn.execute("INSERT INTO member VALUES('M001', '小红', 25)")
# 查询剩余天数小于30天的会员
cur = conn.cursor()
cur.execute(" ① ")
for row in ② :
print("会员", row[1], "剩余天数不足,请尽快续费!(剩余", ③ , "天)")
# 提交并关闭
conn.commit()
④
import sqlite3
# 连接数据库
conn = sqlite3.connect('gym.db')
# 创建表
conn.execute('''CREATE TABLE IF NOT EXISTS member
(id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
days INTEGER);''')
# 插入一条数据
conn.execute("INSERT INTO member VALUES('M001', '小红', 25)")
# 查询剩余天数小于30天的会员
cur = conn.cursor()
cur.execute("SELECT * FROM member WHERE days < 30 ")
for row in cur.fetchall():
print("会员", row[1], "剩余天数不足,请尽快续费!(剩余",row[2], "天)")
# 提交并关闭
conn.commit()
conn.close()管理电话号码
编写一个使用SQLite管理电话号码的程序,功能有:显示所有电话号码、添加、删除和退出,请补全程序。
(本题无需运行通过,写入代码即可)
import sqlite3
def init_db():
conn = sqlite3.connect('phonebook.db')
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS contacts")
cursor.execute('''CREATE TABLE contacts (name TEXT NOT NULL,phone TEXT PRIMARY KEY)''')
conn.commit()
return conn
def show_all_contacts(conn):
cursor = conn.cursor()
cursor.execute("SELECT name, phone FROM contacts ORDER BY name")
contacts = cursor. ①
if not contacts:
print("电话簿为空")
return
for name, phone in contacts:
print(f"{name}: {phone}")
def add_contact(conn):
name = input("请输入姓名: ").strip()
phone = input("请输入电话号码: ").strip()
if not name or not phone:
print("错误: 姓名和电话都不能为空")
return
try:
cursor = conn.cursor()
cursor.execute("INSERT INTO contacts (name, phone) VALUES (?, ?)", ( ② , ③ ))
conn.commit()
print(f"成功添加联系人: {name} - {phone}")
except sqlite3.IntegrityError:
print("错误: 该电话号码已存在")
def delete_contact(conn):
phone = input("请输入要删除的电话号码: ").strip()
if not phone:
print("错误: 请输入有效的电话号码")
return
cursor = conn.cursor()
cursor.execute("SELECT name FROM contacts WHERE phone=?", (phone,))
result = cursor. ④
if not result:
print("错误: 未找到该电话号码")
return
confirm = input(f"确定要删除 {result[0]}({phone}) 吗? (y/n): ").lower()
if confirm == 'y':
cursor.execute("DELETE FROM contacts WHERE phone=?", (phone,))
conn.commit()
print("联系人已删除")
else:
print("取消删除")
# 主程序
def main():
conn = init_db()
while True:
print("----------------")
show_all_contacts(conn)
print("\n请选择操作:")
print("1. 添加联系人")
print("2. 删除联系人")
print("0. 退出程序")
choice = input("请输入选项(0-2): ").strip()
if choice == "1":
add_contact(conn)
elif choice == "2":
delete_contact(conn)
elif choice == "0":
print("\n感谢使用电话簿管理系统,再见!")
conn.close()
break
else:
print("无效输入,请重新选择!")
if __name__ == "__main__":
main()import sqlite3
def init_db():
conn = sqlite3.connect('phonebook.db')
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS contacts")
cursor.execute('''CREATE TABLE contacts (name TEXT NOT NULL,phone TEXT PRIMARY KEY)''')
conn.commit()
return conn
def show_all_contacts(conn):
cursor = conn.cursor()
cursor.execute("SELECT name, phone FROM contacts ORDER BY name")
contacts = cursor.fetchall()
if not contacts:
print("电话簿为空")
return
for name, phone in contacts:
print(f"{name}: {phone}")
def add_contact(conn):
name = input("请输入姓名: ").strip()
phone = input("请输入电话号码: ").strip()
if not name or not phone:
print("错误: 姓名和电话都不能为空")
return
try:
cursor = conn.cursor()
cursor.execute("INSERT INTO contacts (name, phone) VALUES (?, ?)", (name, phone))
conn.commit()
print(f"成功添加联系人: {name} - {phone}")
except sqlite3.IntegrityError:
print("错误: 该电话号码已存在")
def delete_contact(conn):
phone = input("请输入要删除的电话号码: ").strip()
if not phone:
print("错误: 请输入有效的电话号码")
return
cursor = conn.cursor()
cursor.execute("SELECT name FROM contacts WHERE phone=?", (phone,))
result = cursor.fetchone()
if not result:
print("错误: 未找到该电话号码")
return
confirm = input(f"确定要删除 {result[0]}({phone}) 吗? (y/n): ").lower()
if confirm == 'y':
cursor.execute("DELETE FROM contacts WHERE phone=?", (phone,))
conn.commit()
print("联系人已删除")
else:
print("取消删除")
# 主程序
def main():
conn = init_db()
while True:
print("----------------")
show_all_contacts(conn)
print("\n请选择操作:")
print("1. 添加联系人")
print("2. 删除联系人")
print("0. 退出程序")
choice = input("请输入选项(0-2): ").strip()
if choice == "1":
add_contact(conn)
elif choice == "2":
delete_contact(conn)
elif choice == "0":
print("\n感谢使用电话簿管理系统,再见!")
conn.close()
break
else:
print("无效输入,请重新选择!")
if __name__ == "__main__":
main()评分标准:
(1)fetchall()(或等效答案);(3分)
(2)name(或等效答案); (2分)
(3)phone(或等效答案); (2分)
(4)fetchone()(或等效答案)。(3分)
IP地址记录
小明的IP地址记录在文件ip_list.txt中,但其中有些IP属于内网(局域网IP段如192.开头),小明想要:
(1)读取所有IP地址
(2)删除每行末尾换行符
(3)筛选出不是以192.开头的IP地址
(4)将这些IP地址逆序排列后保存到新文件ip_result.txt
(5)并在屏幕上显示每一条被保存的IP
请根据下面代码框架补充完整(共4个填空)。
# 读取ip_list.txt
with open('/data/ip_list.txt', 'r') as f:
ip_list = ①
# 处理IP列表
clean_list = []
for ip in ip_list:
ip = ip.strip('\n')
if not ip.startswith( ② ):
clean_list.append(ip)
# 逆序排列
clean_list.reverse()
# 保存到新文件并输出
with open('ip_result.txt', 'w') as f:
for ip in clean_list:
f.write(ip + "\n")
print( ③ )
a= ④ ("按p退出程序")
if a=="p":
exit()# 读取ip_list.txt
with open('/data/ip_list.txt', 'r') as f:
ip_list = f.readlines()
# 处理IP列表
clean_list = []
for ip in ip_list:
ip = ip.strip('\n')
if not ip.startswith('192.' ):
clean_list.append(ip)
# 逆序排列
clean_list.reverse()
# 保存到新文件并输出
with open('ip_result.txt', 'w') as f:
for ip in clean_list:
f.write(ip + "\n")
print(ip)
a=input("按p退出程序")
if a=="p":
exit()评分标准:
(1)f.readlines()(或等效答案);(3分)
(2)'192.'(或等效答案);(3分)
(3)ip(或等效答案);(2分)
(4)input(或等效答案)。(2分)
学生数据库
编写程序操作SQLite数据库,并读出表中的数据。
具体要求如下:
(1)打开数据库连接;
(2)清除已存在的表 -students;
(3)创建一个表students;
(4)向新表插入数据;
(5)读取表students中数据。
(本题无需运行通过,写入代码即可)
import sqlite3
#打开数据库连接
conn = sqlite3. ① ('test.db')
print("Opend database successfully")
#清除已存在的表 -students
conn. ② ('''DROP TABLE students''');
conn. ③
#创建一个表students
conn.execute(''' ④ students
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL);''')
print("Table created successfully");
conn.commit()
#插入数据
conn.execute("INSERT INTO students(ID,NAME,AGE) VALUES(1,'Allen',25)");
conn.execute("INSERT INTO students(ID,NAME,AGE) VALUES(2,'Maxsu',20)");
conn.execute("INSERT INTO students(ID,NAME,AGE) VALUES(3,'Teddy',24)");
conn.commit()
print("Records Insert successfully");
print("-------------------");
#读取表students
⑤ =conn.execute("SELECT * from students")
print ("ID NAME AGE")
for it in cursor:
for i in range(len(it)):
print(it[i])
print ('\n')
conn.close()
import sqlite3
#打开数据库连接
conn = sqlite3.connect('test.db')
print("Opend database successfully")
#清除已存在的表 -students
conn.execute('''DROP TABLE students''');
conn.commit()
#创建一个表students
conn.execute('''CREATE TABLE students
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL);''')
print("Table created successfully");
conn.commit()
#插入数据
conn.execute("INSERT INTO students(ID,NAME,AGE) VALUES(1,'Allen',25)");
conn.execute("INSERT INTO students(ID,NAME,AGE) VALUES(2,'Maxsu',20)");
conn.execute("INSERT INTO students(ID,NAME,AGE) VALUES(3,'Teddy',24)");
conn.commit()
print("Records Insert successfully");
print("-------------------");
#读取表students
cursor = conn.execute("SELECT * from students")
print ("ID NAME AGE")
for it in cursor:
for i in range(len(it)):
print(it[i])
print ('\n')
conn.close()评分标准:
(1)connect 或等效答案;(2分)
(2)execute 或等效答案;(2分)
(3)commit() 或等效答案;(2分)
(4)CREATE TABLE 或等效答案;(2分)
(5)cursor 或等效答案。(2分)