def delete_grades(stu_no, cou_no):
with db_cursor() as cur :
s = 'SELECT sn, name FROM student WHERE no=%(stu_no)s'
cur.execute(s, dict(stu_no=stu_no))
stu = cur.fetchone()
if stu is None :
print('找不到学生(%s)' % stu_no)
return
s = 'SELECT sn, name FROM course WHERE no=%(cou_no)s'
cur.execute(s, dict(cou_no=cou_no))
cou = cur.fetchone()
if cou is None :
print('找不到课程(%s)' % cou_no)
return
s = """
UPDATE course_grade SET
grade = null
WHERE stu_sn=%(stu_sn)s AND cou_sn= %(cou_sn)s
"""
cur.execute(s,dict(stu_sn=stu[0],
cou_sn=cou[0]))
print('已删除{0}的{1}的成绩'.format(stu[1],cou[1]))
def write_grade(stu_no, cou_no, grade):
"""填写学生stu_no添加课程编号为cou_no的成绩"""
with db_cursor() as cur:
s = 'SELECT sn, name FROM student WHERE no=%(stu_no)s'
cur.execute(s, dict(stu_no=stu_no))
stu = cur.fetchone()
print(stu)
if stu is None:
print('找不到学生(%s)' % stu_no)
return
s = 'SELECT sn, name FROM course WHERE no=%(cou_no)s'
cur.execute(s, dict(cou_no=cou_no))
cou = cur.fetchone()
if cou is None:
print('找不到课程(%s)' % cou_no)
return
s = """
UPDATE course_grade SET
grade = %(grade)s
WHERE stu_sn = %(stu_sn)s AND cou_sn= %(cou_sn)s
"""
cur.execute(s, dict(stu_sn=stu[0], cou_sn=cou[0], grade=grade))
if cur.rowcount == 0:
s = """
INSERT INTO course_grade (stu_sn, cou_sn, grade)
VALUES (%(stu_sn)s, %(cou_sn)s, %(grade)s)
"""
cur.execute(s, dict(stu_sn=stu[0], cou_sn=cou[0], grade=grade))
print('添加%s的%s成绩%.2f' % (stu[1], cou[1], grade))
else:
print('更新%s的%s成绩%.2f' % (stu[1], cou[1], grade))
def init_data():
sqlstr = """
DELETE FROM course_grade;
DELETE FROM student;
DELETE FROM course;
INSERT INTO student (sn, no, name) VALUES (101, 'S001', '张三');
INSERT INTO student (sn, no, name) VALUES
(102, 'S002', '李四'),
(103, 'S003', '王五'),
(104, 'S004', '马六');
INSERT INTO course (sn, no, name) VALUES
(101, 'C01', '高数'),
(102, 'C02', '外语'),
(103, 'C03', '线代');
INSERT INTO course_grade (stu_sn, cou_sn, grade) VALUES
(101, 101, 91),
(102, 101, 89),
(103, 101, 90),
(101, 102, 89);
"""
with db_cursor() as cur :
cur.execute(sqlstr)
def dal_del_course(stu_no):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
DELETE FROM stulist WHERE stu_no=%(stu_no)s
"""
cur.execute(s, dict(stu_no=stu_no))
print('删除%d条记录' % cur.rowcount)
def dal_del_student(stu_sn):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
DELETE FROM student WHERE stu_sn=%(stu_sn)s
"""
cur.execute(s, dict(stu_sn=stu_sn))
print('删除%d条记录' % cur.rowcount)
def dal_del_student(Sno):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
DELETE FROM student WHERE Sno=%(Sno)s
"""
cur.execute(s, dict(Sno=Sno))
print('删除%d条记录' % cur.rowcount)
def init_data():
sqlstr = """
DELETE FROM course_grade;
DELETE FROM student;
DELETE FROM course;
INSERT INTO student (sn, no, name,gender ,enrolled) VALUES (101, 'S001', '张三', '男', '2013-09-08');
INSERT INTO student (sn, no, name,gender,enrolled) VALUES
(102, 'S002', '李四', '女', '2012-09-06'),
(103, 'S003', '王五', '女', '2014-09-05'),
(104, 'S004', '马六', '男', '2011-08-25');
INSERT INTO course (sn, no, name) VALUES
(101, 'C01', '高数'),
(102, 'C02', '外语'),
(103, 'C03', '线代');
INSERT INTO course_grade (stu_sn, cou_sn, grade) VALUES
(101, 101, 91),
(102, 101, 89),
(103, 101, 90),
(101, 102, 89);
"""
with db_cursor() as cur :
cur.execute(sqlstr)
def dal_del_course(cou_sn):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
DELETE FROM course WHERE cou_sn=%(cou_sn)s
"""
cur.execute(s, dict(cou_sn=cou_sn))
print('删除%d条记录' % cur.rowcount)
def dal_get_student(stu_sn):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT stu_sn,stu_id, name, stu_bir, stu_no, gda FROM student WHERE stu_sn=%s
"""
cur.execute(s, (stu_sn, ))
r = cur.fetchone()
if r :
return dict(stu_sn=r[0],stu_id=r[1] ,name=r[2],stu_bir=r[3], stu_no=r[4], gda=r[5])
def dal_get_student(Sno):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT Sno, Sname, Ssex,Sclass, notes FROM student WHERE Sno=%s
"""
cur.execute(s, (Sno, ))
r = cur.fetchone()
if r :
return dict(Sno=r[0], Sname=r[1], Ssex=r[2],Sclass=r[3], notes=r[4])
def dal_get_course(stu_no):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT * FROM stulist WHERE stu_no=%s
"""
cur.execute(s, (stu_no, ))
r = cur.fetchone()
if r :
return dict(stu_no=r[0],name=r[1], birthday=r[2],sex=r[3],stclass=r[4])
def create_db():
sqlstr = """
-- 删除旧表
DROP TABLE IF EXISTS course_grade;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS course;
-- === 学生表
CREATE TABLE IF NOT EXISTS student (
sn INTEGER, --序号
no VARCHAR(10), --学号
name TEXT, --姓名
gender CHAR(1), --性别(F/M/O)
enrolled DATE, --入学时间
PRIMARY KEY(sn)
);
-- 给sn创建一个自增序号
CREATE SEQUENCE seq_student_sn
START 10000 INCREMENT 1 OWNED BY student.sn;
ALTER TABLE student ALTER sn
SET DEFAULT nextval('seq_student_sn');
-- 学号唯一
CREATE UNIQUE INDEX idx_student_no ON student(no);
-- === 课程表
CREATE TABLE IF NOT EXISTS course (
sn INTEGER, --序号
no VARCHAR(10), --课程号
name TEXT, --课程名称
PRIMARY KEY(sn)
);
CREATE SEQUENCE seq_course_sn
START 10000 INCREMENT 1 OWNED BY course.sn;
ALTER TABLE course ALTER sn
SET DEFAULT nextval('seq_course_sn');
CREATE UNIQUE INDEX idx_course_no ON course(no);
-- === 成绩表
CREATE TABLE IF NOT EXISTS course_grade (
stu_sn INTEGER, -- 学生序号
cou_sn INTEGER, -- 课程序号
grade NUMERIC(5,2), -- 最终成绩
PRIMARY KEY(stu_sn, cou_sn)
);
ALTER TABLE course_grade
ADD CONSTRAINT stu_sn_fk FOREIGN KEY (stu_sn)
REFERENCES student(sn);
ALTER TABLE course_grade
ADD CONSTRAINT cou_sn_fk FOREIGN KEY (cou_sn)
REFERENCES course(sn);
"""
with db_cursor() as cur :
cur.execute(sqlstr) # 执行SQL语句
def create_db():
sqlstr = """
-- 删除旧表
DROP TABLE IF EXISTS course_grade;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS course;
-- === 学生表
CREATE TABLE IF NOT EXISTS student (
sn INTEGER, --序号
no VARCHAR(10), --学号
name TEXT, --姓名
gender CHAR(1), --性别(F/M/O)
enrolled DATE, --入学时间
PRIMARY KEY(sn)
);
-- 给sn创建一个自增序号
CREATE SEQUENCE seq_student_sn
START 10000 INCREMENT 1 OWNED BY student.sn;
ALTER TABLE student ALTER sn
SET DEFAULT nextval('seq_student_sn');
-- 学号唯一
CREATE UNIQUE INDEX idx_student_no ON student(no);
-- === 课程表
CREATE TABLE IF NOT EXISTS course (
sn INTEGER, --序号
no VARCHAR(10), --课程号
name TEXT, --课程名称
PRIMARY KEY(sn)
);
CREATE SEQUENCE seq_course_sn
START 10000 INCREMENT 1 OWNED BY course.sn;
ALTER TABLE course ALTER sn
SET DEFAULT nextval('seq_course_sn');
CREATE UNIQUE INDEX idx_course_no ON course(no);
-- === 成绩表
CREATE TABLE IF NOT EXISTS course_grade (
stu_sn INTEGER, -- 学生序号
cou_sn INTEGER, -- 课程序号
grade NUMERIC(5,2), -- 最终成绩
PRIMARY KEY(stu_sn, cou_sn)
);
ALTER TABLE course_grade
ADD CONSTRAINT stu_sn_fk FOREIGN KEY (stu_sn)
REFERENCES student(sn);
ALTER TABLE course_grade
ADD CONSTRAINT cou_sn_fk FOREIGN KEY (cou_sn)
REFERENCES course(sn);
"""
with db_cursor() as cur :
cur.execute(sqlstr) # 执行SQL语句
def dal_get_course(sno):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT * FROM student WHERE sno=%s
"""
cur.execute(s, (sno,))
r = cur.fetchone()
if r :
return dict(sno=r[0], sname=r[1], sage=r[2],ssex=r[3],sspecialty=r[4], sdepart=r[5],sdromitory=r[6],notes=r[7])
def dal_get_student(stu_sn):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT stu_sn, sno, sname, sex,sbir,cla FROM student WHERE stu_sn=%s
"""
cur.execute(s, (stu_sn, ))
r = cur.fetchone()
if r :
return dict(stu_sn=r[0], sno=r[1], sname=r[2], sex=r[3],sbir=r[4],cla=r[5])
def dal_get_course(cou_sn):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT cou_sn, cou_no, name,birth,gender,class0 FROM course WHERE cou_sn=%s
"""
cur.execute(s, (cou_sn, ))
r = cur.fetchone()
if r :
return dict(cou_sn=r[0], cou_no=r[1], name=r[2], birth=r[3], gender=r[4], class0=r[5])
def dal_get_course(cou_sn):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT cou_sn, cou_no, name, pice, sex, notes FROM course WHERE cou_sn=%s
"""
cur.execute(s, (cou_sn, ))
r = cur.fetchone()
if r :
return dict(cou_sn=r[0], cou_no=r[1], name=r[2], pice=r[3], sex=r[4], notes=r[5])
def dal_update_course(cou_sn, cou_no, name, notes):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
UPDATE course SET
cou_no=%(cou_no)s,
name=%(name)s,
notes=%(notes)s
WHERE cou_sn=%(cou_sn)s
"""
cur.execute(s, dict(cou_sn=cou_sn, cou_no=cou_no, name=name, notes=notes))
def dal_list_courses():
data = []
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT * FROM stulist ORDER BY stu_no DESC
"""
cur.execute(s)
for r in cur.fetchall():
cou = dict(stu_no=r[0],name=r[1], birthday=r[2],sex=r[3],stclass=r[4])
data.append(cou)
return data
def dal_list_students():
data = []
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT stu_sn, sno, sname, sex,sbir,cla FROM student ORDER BY stu_sn DESC
"""
cur.execute(s)
for r in cur.fetchall():
stu = dict(stu_sn=r[0], sno=r[1], sname=r[2], sex=r[3],sbir=r[4],cla=r[5])
data.append(stu)
return data
def dal_list_student():
data = []
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT stu_sn, stu_id, name, stu_bir, stu_no, gda FROM student ORDER BY stu_no
"""
cur.execute(s)
for r in cur.fetchall():
stu = dict(stu_sn=r[0],stu_id=r[1] ,name=r[2], stu_bir=r[3],stu_no=r[4], gda=r[5])
data.append(stu)
return data
def init_data():
sqlstr = """
DELETE FROM course;
INSERT INTO course (cou_sn, cou_no, name, birth, sex, grade) VALUES
(101, '1310601', '糖饼', '19950101', '女', '化学1301'),
(102, '1310602', '小梦', '19950102', '女', '化学1302');
"""
with db_cursor() as cur :
cur.execute(sqlstr)
def dal_list_students():
data = []
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT Sno, Snme, Ssex, Sclass, notes FROM student ORDER BY Sno
"""
cur.execute(s)
for r in cur.fetchall():
stu = dict(Sno=r[0], Sname=r[1], Ssex=r[2],Sclass=r[3], notes=r[4])
data.append(stu)
return data
def init_data():
sqlstr = """
DELETE FROM student;
INSERT INTO student (stu_sn,stu_id,stu_bir,stu_no, name,gda) VALUES
(101, '1310201' , '19950101' , 1302 , '张三' , '男'),
(102, '1310401' , '19950202' , 1304 , '李四' , '女'),
(103, '1310301' , '19950303' , 1303 , '王五' , '男');
"""
with db_cursor() as cur :
cur.execute(sqlstr)
def dal_list_courses():
data = []
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT cou_sn, cou_no, name, birth,gender,class0 FROM course ORDER BY class0
"""
cur.execute(s)
for r in cur.fetchall():
cou = dict(cou_sn=r[0], cou_no=r[1], name=r[2], birth=r[3], gender=r[4], class0=r[5])
data.append(cou)
return data
def dal_list_courses():
data = []
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT cou_sn, cou_no, name, pice, sex, notes FROM course ORDER BY cou_sn DESC
"""
cur.execute(s)
for r in cur.fetchall():
cou = dict(cou_sn=r[0], cou_no=r[1], name=r[2], pice=r[3], sex=r[4], notes=r[5])
data.append(cou)
return data
def dal_list_courses():
data = []
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT * FROM student ORDER BY sno DESC
"""
cur.execute(s)
for r in cur.fetchall():
cou = dict(sno=r[0], sname=r[1], sage=r[2],ssex=r[3],sspecialty=r[4], sdepart=r[5],sdromitory=r[6],notes=r[7])
data.append(cou)
return data
def dal_update_student(Sno,Sname,Ssex,Sclass, notes):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
UPDATE student SET
Sname=%(Sname)s,
Ssex=%(Ssex)s,
Sclass=%(Sclass)s,
notes=%(notes)s
WHERE Sno=%(Sno)s
"""
cur.execute(s, dict(Sno=Sno, Sname=Sname,Ssex=Ssex,Sclass=Sclass, notes=notes))
def init_data():
sqlstr = """
DELETE FROM course;
INSERT INTO course (stu_no,cou_no, name,sex,pro,grade,notes) VALUES
(101,'1','邓硕成','男','信息',98,'学委'),
(102,'2','张付东','男','信息',98,'心理委员'),
(103, '3','郭明睿','女','信息',98,'团委');
"""
with db_cursor() as cur :
cur.execute(sqlstr)
def dal_update_student(stu_sn, stu_id,name, stu_bir, stu_no, gda):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
UPDATE student SET
stu_no=%(stu_no)s,
stu_id=%(stu_id)s,
name=%(name)s,
stu_bir=%(stu_bir)s,
gda=%(gda)s
WHERE stu_sn=%(stu_sn)s
"""
cur.execute(s, dict(stu_sn=stu_sn, stu_id=stu_id,name=name, stu_bir=stu_bir, stu_no=stu_no, gda=gda))
def init_data():
sqlstr = """
DELETE FROM student;
INSERT INTO student (stu_sn,stu_id,stu_bir,stu_no, name,gda) VALUES
(101, '1310201' , '19950101' , 1302 , '张三' , '男'),
(102, '1310401' , '19950202' , 1304 , '李四' , '女'),
(103, '1310301' , '19950303' , 1303 , '王五' , '男');
"""
with db_cursor() as cur:
cur.execute(sqlstr)
def dal_list_courses():
data = []
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
SELECT cou_sn, cou_no, name, classroom,sex,birthday FROM course ORDER BY cou_sn DESC
"""
cur.execute(s)
for r in cur.fetchall():
cou = dict(cou_sn=r[0], cou_no=r[1], name=r[2], classroom=r[3],sex=r[4],birthday=r[5])
data.append(cou)
print(data)
return data
def init_data():
sqlstr = """
DELETE FROM course;
INSERT INTO course (cou_sn, cou_no, name, pice, sex, notes) VALUES
(101, '1310650403', '李德辉', '1994-12-27', '男', '信息1304'),
(102, '1310650401', '王浩然', '1994-08-16', '男', '信息1304'),
(103, '1310650410', '赵志鹏', '1994-10-26', '男', '信息1304');
"""
with db_cursor() as cur :
cur.execute(sqlstr)
def dal_update_student(stu_sn, sno, sname, sex,sbir,cla):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
UPDATE student SET
sno=%(sno)s,
sname=%(sname)s,
sex=%(sex)s,
sbir=%(sbir)s,
cla=%(cla)s
WHERE stu_sn=%(stu_sn)s
"""
cur.execute(s, dict(stu_sn=stu_sn, sno=sno, sname=sname, sex=sex, sbir=sbir, cla=cla))
def dal_update_course(stu_no,name, birthday,sex,stclass):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
UPDATE stulist SET
stu_no=%(stu_no)s,
name=%(name)s,
birthday=%(birthday)s,
sex=%(sex)s,
stclass=%(stclass)s
WHERE stu_no=%(stu_no)s
"""
cur.execute(s, dict(stu_no=stu_no,name=name, birthday=birthday,sex=sex,stclass=stclass))
def dal_update_course(cou_sn, cou_no, name, birth,gender,class0):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = """
UPDATE course SET
cou_no=%(cou_no)s,
name=%(name)s,
birth= %(birth)s,
gender= %(gender)s,
class0=%(class0)s
WHERE cou_sn=%(cou_sn)s
"""
cur.execute(s, dict(cou_sn=cou_sn, cou_no=cou_no, name=name, birth =birth,gender=gender,class0=class0))
def init_data():
sqlstr = """
DELETE FROM course;
INSERT INTO course (cou_sn, stu_no, cou_no, name, sex,grade) VALUES
(101, '1310650412', '1995-01-02', '张三', '男','信息1304'),
(102, '1310650411', '1994-03-04', '李四', '女','会计1302'),
(103, '1310650413', '1994-09-12', '王五', '男','工商1301');
"""
with db_cursor() as cur:
cur.execute(sqlstr)
def dal_create_course(name, birthday,sex,stclass):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
cur.execute("SELECT nextval('seq_stu_sn')")
stu_no = cur.fetchone()
assert stu_no is not None
print('新学生学号%d: ' % stu_no)
s = """
INSERT INTO stulist (stu_no,name, birthday,sex,stclass)
VALUES (%(stu_no)s, %(name)s, %(birthday)s, %(sex)s,%(stclass)s)
"""
cur.execute(s, dict(stu_no=stu_no,name=name, birthday=birthday,sex=sex,stclass=stclass))
def dal_create_student(sno, sname, sex,sbir,cla):
with db_cursor() as cur : # 取得操作数据的游标，记为cur
cur.execute("SELECT nextval('seq_stu_sn')")
stu_sn = cur.fetchone()
assert stu_sn is not None
print('新学生内部序号%d: ' % stu_sn)
s = """
INSERT INTO student (stu_sn, sno, sname, sex,sbir,cla)
VALUES (%(stu_sn)s, %(sno)s, %(sname)s, %(sex)s, %(sbir)s, %(cla)s)
"""
cur.execute(s, dict(stu_sn=stu_sn, sno=sno, sname=sname, sex=sex, sbir=sbir, cla=cla))
def delete_grades(stu_no, cou_no):
"""删除学号为stu_no某门课程的成绩，该课程编码为cou_no"""
with db_cursor() as cur :
a=['S002','S003','S004']
b=['C01','C02','C03']
if((stu_no in a) and (cou_no in b)):
s = """DELETE
FROM course_grade
WHERE stu_sn=%(stu_no)s AND cou_sn=%(cou_no)s"""
cur.execute(s, dict(stu_no=stu[0],cou_no=stu1[0]))
print("删除成功")
else:
print("没有该学生号或者课程号")
def list_grades(stu_no):
"""打印出学号为stu_no学生的所有课程成绩，按照课程号从小到大排序"""
s="""
SELECT s.name,s.no,c.name,c.no,g.grade
FROM course_grade as g
INNER JOIN student as s ON g.stu_sn=s.sn
INNER JOIN course as c ON g.cou_sn=c.sn;
ORDER BY s.no,c.no
"""
with db_cursor() as cur :
cur.execute(s)
for row in cur.fetchall():
print('%s %s %.2f' %(row[0],row[2],row[3]))
def list_grades(stu_no):
"""打印出学号为stu_no学生的所有课程成绩，按照课程号从小到大排序"""
with db_cursor() as cur :
b=[101,102,103]
for i in range(0,len(b)):
s = 'SELECT grade FROM course_grade WHERE stu_sn=%(a)s AND cou_sn=%(c)s'
cur.execute(s, dict(a=stu[0],c=b[i]))
stu1 = cur.fetchone()
s = 'SELECT name FROM course WHERE sn=%(d)s'
cur.execute(s, dict(d=b[i]))
stu2 =cur.fetchone()
if stu1==None:
print('%s的%s课的成绩不存在'%(stu[1],stu2[0]))
else:
print('%s的%s成绩是%d'%(stu[1],stu2[0],stu1[0]))
def init_data():
sqlstr = """
DELETE FROM stulist;
INSERT INTO stulist ( stu_no, name,birthday,sex,stclass) VALUES
('1210650301', '边桃子','1993.2.3','男','1203'),
('1310650102', '张葡萄','1994.5.5','男','1301'),
('1310650202', '李苹果','1994.10.25','女','1302'),
('1310650312', '赵栗子','1994.7.7','男','1303'),
('1310650422', '黄杏子','1994.2.5','女','1304');
"""
with db_cursor() as cur:
cur.execute(sqlstr)
def dal_list_student():
data = []
with db_cursor() as cur: # 取得操作数据的游标，记为cur
s = """
SELECT stu_sn, stu_id, name, stu_bir, stu_no, gda FROM student ORDER BY stu_no
"""
cur.execute(s)
for r in cur.fetchall():
stu = dict(stu_sn=r[0],
stu_id=r[1],
name=r[2],
stu_bir=r[3],
stu_no=r[4],
gda=r[5])
data.append(stu)
return data
def list_grades(stu_no):
with db_cursor() as cur :
s = 'SELECT sn, name FROM student WHERE no=%(stu_no)s'
cur.execute(s, dict(stu_no=stu_no))
stu = cur.fetchone()
if stu is None :
print('找不到学生(%s)' % stu_no)
s ='SELECT grade,cou_sn FROM course_grade WHERE stu_sn = %(stu_sn)s ORDER BY cou_sn'
cur.execute(s,dict(stu_sn=stu[0]))
for row in cur.fetchall():
cou_sn=int(row[1])
grade=row[0]
print('{0}的序号为{1}的成绩是{2}'.format(stu[1],cou_sn,grade))
def init_data():
sqlstr = """
DELETE FROM student;
INSERT INTO student (stu_sn, sno, sname,sex,sbir,cla) VALUES
(101, '1310650101', '齐琪','男','1994-1-1','信息1301'),
(102, '1310650304', '张付东','男','1994-2-1','信息1303'),
(103, '1310650106', '潘星','男','1994-3-1','信息1301'),
(104, '1310650116', '蔺珍妮','女','1994-4-1','信息1301'),
(105, '1310650117', '王潇懿','女','1994-5-1','信息1301'),
(106, '1310650119', '张欣怡','女','1994-6-1','信息1301');
"""
with db_cursor() as cur:
cur.execute(sqlstr)
def dal_list_courses(): #将每一个学生数据赋值给date
data = []
with db_cursor() as cur: # 取得操作数据的游标，记为cur
s = """
SELECT cou_sn, cou_no, name, sex, bir, num, notes FROM course ORDER BY cou_sn DESC
"""
cur.execute(s)
for r in cur.fetchall():
cou = dict(cou_sn=r[0],
cou_no=r[1],
name=r[2],
sex=r[3],
bir=[4],
num=r[5])
data.append(cou)
print(data)
return data
def dal_update_student(stu_sn, stu_id, name, stu_bir, stu_no, gda):
with db_cursor() as cur: # 取得操作数据的游标，记为cur
s = """
UPDATE student SET
stu_no=%(stu_no)s,
stu_id=%(stu_id)s,
name=%(name)s,
stu_bir=%(stu_bir)s,
gda=%(gda)s
WHERE stu_sn=%(stu_sn)s
"""
cur.execute(
s,
dict(stu_sn=stu_sn,
stu_id=stu_id,
name=name,
stu_bir=stu_bir,
stu_no=stu_no,
gda=gda))
def write_grade(stu_no, cou_no, grade):
"""填写学生stu_no添加课程编号为cou_no的成绩"""
with db_cursor() as cur : # 取得操作数据的游标，记为cur
s = 'SELECT sn, name FROM student WHERE no=%(stu_no)s'
cur.execute(s, dict(stu_no=stu_no))
stu = cur.fetchone() # 仅读取第一行的数据，有两列(sn, name)
# 如果没有一行数据返回，则返回的是None
if stu is None :
print('找不到学生(%s)' % stu_no)
return
s = 'SELECT sn, name FROM course WHERE no=%(cou_no)s'
cur.execute(s, dict(cou_no=cou_no))
cou = cur.fetchone() # 仅读取第一行的数据
if cou is None :
print('找不到课程(%s)' % cou_no)
return
s = """
UPDATE course_grade SET
grade = %(grade)s
WHERE stu_sn = %(stu_sn)s AND cou_sn= %(cou_sn)s
"""
cur.execute(s, dict(stu_sn=stu[0],
cou_sn=cou[0],
grade=grade))
if cur.rowcount == 0:
# rowcount, 最近一次执行SQL语句后所涉及的行数
# 如果学生和课程都找到，则至少更新一条，
# 反之，没找到可更新的，因此需要插入一条新的
s = """
INSERT INTO course_grade (stu_sn, cou_sn, grade)
VALUES (%(stu_sn)s, %(cou_sn)s, %(grade)s)
"""
cur.execute(s, dict(stu_sn=stu[0],
cou_sn=cou[0],
grade=grade))
print('添加%s的%s成绩%.2f' % (stu[1], cou[1], grade))
else:
print('更新%s的%s成绩%.2f' % (stu[1], cou[1], grade))
def create_db():
sqlstr = """
DROP TABLE IF EXISTS stulist;
CREATE TABLE IF NOT EXISTS stulist (
stu_no VARCHAR(10), --学号
name TEXT, --姓名
birthday DATE, --出生日期
sex CHAR(5), --性别
stclass TEXT, --班级
PRIMARY KEY(stu_no)
);
-- CREATE UNIQUE INDEX idx_stulist_no ON stulist(stu_no);
CREATE SEQUENCE seq_stu_sn
START 10000 INCREMENT 1 OWNED BY stulist.stu_no;
"""
with db_cursor() as cur:
cur.execute(sqlstr) # 执行SQL语句
def dal_create_student(stu_id, name, stu_bir, stu_no, gda):
with db_cursor() as cur: # 取得操作数据的游标，记为cur
cur.execute("SELECT nextval('seq_stu_sn')")
stu_sn = cur.fetchone()
assert stu_sn is not None
print('新学生学号%s: ' % stu_id)
s = """
INSERT INTO student (stu_sn, stu_id,name, stu_bir, stu_no, gda)
VALUES (%(stu_sn)s,%(stu_id)s,%(name)s, %(stu_bir)s, %(stu_no)s, %(gda)s)
"""
cur.execute(
s,
dict(stu_sn=stu_sn,
stu_id=stu_id,
name=name,
stu_bir=stu_bir,
stu_no=stu_no,
gda=gda))
def create_db():
sqlstr = """
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student (
stu_sn INTEGER, --序号
sno TEXT, --学号
sname TEXT, --姓名
sex TEXT, --性别
sbir TEXT, --出生日期
cla TEXT, --班级
PRIMARY KEY(stu_sn)
);
-- CREATE UNIQUE INDEX idx_student_no ON student(sno);
CREATE SEQUENCE seq_stu_sn
START 10000 INCREMENT 1 OWNED BY student.stu_sn;
"""
with db_cursor() as cur:
cur.execute(sqlstr) # 执行SQL语句
def create_db():
sqlstr = """
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student (
stu_sn INTEGER, --内部序号
stu_id INTEGER, --学号
name TEXT, --姓名
stu_bir DATE, --出生日期
stu_no INTEGER, --班级
gda TEXT, --性别
PRIMARY KEY(stu_id)
);
-- CREATE UNIQUE INDEX idx_student_no ON student(stu_no);
CREATE SEQUENCE seq_stu_sn
START 10000 INCREMENT 1 OWNED BY student.stu_sn;
"""
with db_cursor() as cur:
cur.execute(sqlstr) # 执行SQL语句