🔴 换一位老师,两边各要改几行
数学课换老师。分别在宽表和拆开后的课程表上改一次。输出两个数:宽表改了几行、课程表改了几行(用 / 隔开)。
enroll_flat(一张宽表,什么都塞在里面) student phone course teacher room 阿岚 111 数学 王老师 A101 阿岚 111 英语 李老师 B202 小满 222 数学 王老师 A101 阿泰 333 英语 李老师 B202 小满 222 物理 张老师 C303 南风 444 数学 王老师 A101 拆开之后的三张 student(id, name, phone) 4 行 course (id, name, teacher, room) 3 行 enroll (student_id, course_id) 6 行
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE enroll_flat (student TEXT, phone TEXT, course TEXT, teacher TEXT, room TEXT);
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, phone TEXT NOT NULL);
CREATE TABLE course (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,
teacher TEXT NOT NULL, room TEXT NOT NULL);
CREATE TABLE enroll (student_id INTEGER, course_id INTEGER,
PRIMARY KEY (student_id, course_id));
""")
db.executemany("INSERT INTO enroll_flat VALUES (?,?,?,?,?)", [
("阿岚", "111", "数学", "王老师", "A101"),
("阿岚", "111", "英语", "李老师", "B202"),
("小满", "222", "数学", "王老师", "A101"),
("阿泰", "333", "英语", "李老师", "B202"),
("小满", "222", "物理", "张老师", "C303"),
("南风", "444", "数学", "王老师", "A101")])
db.executemany("INSERT INTO student VALUES (?,?,?)", [
(1, "阿岚", "111"), (2, "小满", "222"), (3, "阿泰", "333"), (4, "南风", "444")])
db.executemany("INSERT INTO course VALUES (?,?,?,?)", [
(1, "数学", "王老师", "A101"), (2, "英语", "李老师", "B202"),
(3, "物理", "张老师", "C303")])
db.executemany("INSERT INTO enroll VALUES (?,?)", [
(1, 1), (1, 2), (2, 1), (3, 2), (2, 3), (4, 1)])
a = db.execute("UPDATE enroll_flat SET teacher = '赵老师' "
"WHERE course = '数学'").rowcount
b = db.execute("UPDATE course SET teacher = '赵老师' "
"WHERE name = '数学'").rowcount
print(str(a) + "/" + str(b))
全部评论