python创建一个数据库test.db,该数据库中包含一张student表,该数据表含有五个字段学号sid、姓名sname、性别sgender、年龄sage、班级sclass。 以test.db为基础,编写Python代码分别完成数据添加、删除、修改和查询操作。 (1)通过SQL语句insert添加如下数据, image.png (2)通过SQL语句delete其中1人, (3)通过SQL语句update其中1人的sage和sclass, (4)通过SQL语句select查询显示所有数据,查询显示姓名、年龄和班级数据。
时间: 2024-02-03 20:15:07 浏览: 122
下面是Python代码实现,需要使用sqlite3模块:
```python
import sqlite3
# 创建数据库连接
conn = sqlite3.connect('test.db')
# 创建数据表
conn.execute('''CREATE TABLE IF NOT EXISTS student
(sid INT PRIMARY KEY NOT NULL,
sname TEXT NOT NULL,
sgender TEXT NOT NULL,
sage INT NOT NULL,
sclass TEXT NOT NULL);''')
# 插入数据
conn.execute("INSERT INTO student (sid, sname, sgender, sage, sclass) \
VALUES (1, 'Tom', 'Male', 18, 'Class 1')")
conn.execute("INSERT INTO student (sid, sname, sgender, sage, sclass) \
VALUES (2, 'Mary', 'Female', 19, 'Class 2')")
conn.execute("INSERT INTO student (sid, sname, sgender, sage, sclass) \
VALUES (3, 'John', 'Male', 20, 'Class 1')")
conn.execute("INSERT INTO student (sid, sname, sgender, sage, sclass) \
VALUES (4, 'Cindy', 'Female', 21, 'Class 3')")
conn.execute("INSERT INTO student (sid, sname, sgender, sage, sclass) \
VALUES (5, 'David', 'Male', 22, 'Class 2')")
# 提交更改
conn.commit()
# 删除数据
conn.execute("DELETE FROM student WHERE sid=5")
# 提交更改
conn.commit()
# 修改数据
conn.execute("UPDATE student SET sage=23, sclass='Class 3' WHERE sid=4")
# 提交更改
conn.commit()
# 查询数据
cursor = conn.execute("SELECT sname, sage, sclass FROM student")
for row in cursor:
print("Name = ", row[0])
print("Age = ", row[1])
print("Class = ", row[2])
# 关闭连接
conn.close()
```
输出结果为:
```
Name = Tom
Age = 18
Class = Class 1
Name = Mary
Age = 19
Class = Class 2
Name = John
Age = 20
Class = Class 1
Name = Cindy
Age = 23
Class = Class 3
```
阅读全文