python操作SQL Server数据库的多表查询实例——花花幼儿园教务系统的设计Ⅱ

最新推荐文章于 2024-02-04 16:46:57 发布

FoggyProgrammer

最新推荐文章于 2024-02-04 16:46:57 发布

阅读量1.8k

点赞数 4

分类专栏： Python学习文章标签： sql python 数据库

本文链接：https://blog.csdn.net/FoggyProgrammer/article/details/104152920

版权

Python学习专栏收录该内容

6 篇文章 2 订阅

订阅专栏

1.前言

本文介绍了利用python操作数据库，并搭建一个模拟学校教务系统的方法，本文会给出详细的创建过程以及程序代码。建立python以及数据库之间的连接操作以及基本的增删改查操作可参见
利用python实现在SQL Server数据库中的增、删、改、查
本文为实例设计的后半部分，即教务系统教师端的设计。学生端的设计参见
python操作数据库实例——花花幼儿园教务系统的设计Ⅰ

2.任务要求

为了满足花花幼儿园的教学需求，本着科学合理以人为本的思想，为花花幼儿园设计一个学校教务系统。要求该系统面向学生和老师，师生每人都有一个属于自己的账号，登录教务系统时应输入正确的账户名和密码方可登录。
对于学生，登录后可以查看自己本学期的各个课程的课程名、成绩、学分等信息。
对于老师来说，有的老师只负责课程的教学，那么可以查看其选修了其负责课程的同学的考试结果，包括选修了该课程同学的学号，姓名，成绩等；有的老师担任班主任职务，那么他要关心自己所负责班级同学的考试情况，包括其班级同学的学号，姓名，选修的课程以及各个课程的成绩；还有的老师既负责具体课程的教学，又担任班主任，那么以上的两种情况他有权限查看。
根据以上要求利用python设计花花幼儿园的教务系统。

3.涉及到的表

所有数据库和表已经在SQL Server 2012中建立完毕，现给出设计教师端教务系统设计的表，如图所示：
在这里插入图片描述
数据库名为login，其中共7个表

登录（login）表，保存花花幼儿园全校师生的教务系统账号用户名（USERNAME）以及密码（psw），为统一方便管理，园方规定学生的用户名与学号一致，老师的用户名与教师号一致。
在这里插入图片描述
学生（Student）表，保存所有学生的信息，包括学号（S#）,学生姓名（Student）,学生性别（Ssex），学生年龄（Sage）以及所在班级（Class）

课程（Course）表，保存幼儿园开设的课程信息，包括课程号（C#），课程名（Cname）以及课程学分（Credit）
在这里插入图片描述
选课（SC）表，用于存储学生（注意，是每个学生！）的选课情况，包括学号（S#），课程号（C#）以及成绩（Score），比如，学号为98030101的同学选了三门课，课号为1001、1002、1003，这三门课的成绩分别为92、85、88。
在这里插入图片描述
教师（Teacher）表，保存所有教师的信息，包括教师号（T#）,教师姓名（Tname）以及教师职称（Title）

教师班级管理（TeacherClass）表，保存教师担任班主任管理班级的信息，包括教师号（T#）以及班级号（Class）。正如一个学生可以选修多门课程，一个老师可以作为多个班级的班主任，但是允许有的老师不担任班主任职务
在这里插入图片描述
教师任课（TeacherCourse）表，保存教师执教课程的信息，包括教师号（T#）以及课程号（C#）。与TeacherClass表类似，一个老师可以执教多门课程，但是允许有的老师不执教课程

4.程序代码

import pymssql

def output_student(array):
    print('  学号     '+' 分数  '+'课程名  '+'        学分')
    for i in range(len(array)):
        for j in range(len(array[0])):
            print(array[i][j],end='')
            print('   ',end='')
        print(' ')

def teacher(uname,upwd):
    sqlQuery="select * from login where USERNAME ='%s' and psw='%s' "%(uname,upwd)
    results=[]
    conn = pymssql.connect(server='DESKTOP-LYZ', user='sa', password='12345', database='login')
    cursor = conn.cursor()
    cursor.execute(sqlQuery)
    results = cursor.fetchone()
    if results is not None:
        sqlQuery="select Tname,Title FROM Teacher where T# ='%s' "%(uname)
        cursor.execute(sqlQuery)
        results = cursor.fetchone()
        print('您好，'+results[0].rstrip()+results[1].rstrip()+'，您已成功登录花花幼儿园学生成绩管理系统，请选择您要查看的成绩：')
        while(1):
            print('1---查看您所管理的班级的同学成绩')
            print('2---查看您所执教课程的成绩')
            print('3---退出教务系统')
            flag=input()
            if flag=='1':
                Class=input("请输入您管理的班级的班号:")
                sqlQuery="select Class FROM TeacherClass where T# ='%s' and Class = '%s' "%(uname,Class)
                cursor.execute(sqlQuery)
                results = cursor.fetchall()
                if results==[]:
                    print("抱歉，您不是此班级的班主任，暂无成绩可供查询，如有异议请咨询教务处李老师")
                else:
                    sqlQuery="select Student.S#,Sname,Score,Cname,Credit from SC,Course,TeacherClass,Student where T# ='%s' and TeacherClass.Class ='%s' and TeacherClass.Class=Student.Class and Student.S#=SC.S# and SC.C#=Course.C#  "%(uname,Class)
                    cursor.execute(sqlQuery)
                    results = cursor.fetchall()
                    output_teacher(results)                                  
            elif flag=='2':
                Cnum=input("请输入您所执教课程的课程号:")
                sqlQuery="select C# FROM TeacherCourse where T# ='%s'and C#='%s' "%(uname,Cnum)
                cursor.execute(sqlQuery)
                results = cursor.fetchall()
                if results==[]:
                    print("抱歉，您尚未执教此门课程，暂无成绩可供查询，如有异议请咨询教务处李老师")
                else:
                    sqlQuery="select Student.S#,Sname,Score,Cname,Credit from SC,Course,TeacherCourse,Student where T# ='%s' and TeacherCourse.C# ='%s' and TeacherCourse.C#=SC.C# and SC.C#=Course.C# and Student.S#=SC.S#   "%(uname,Cnum)
                    cursor.execute(sqlQuery)
                    results = cursor.fetchall()
                    output_teacher(results)

            else:
                print("您已成功退出教务系统！")
                break
    else:
        print('用户名或密码错误')    
    conn.close()    
       
    
def student(uname,upwd):
    print('')#学生端程序代码见《python操作数据库实例——花花幼儿园教务系统的设计Ⅰ》   
    
print('欢迎登录花花幼儿园教务系统')
print('1---学生登录')
print('2---教师登录')
flag=input('')
uname=input("请输入用户名：")
upwd=input("请输入密码：")
if flag=='1':   
    student(uname,upwd)#学生端程序代码见《python操作数据库实例——花花幼儿园教务系统的设计Ⅰ》
elif flag=='2':
    teacher(uname,upwd)

5.设计流程与思路

与学生登录教务系统所不同，教师登录教务系统时出现的情况将会复杂得多。对于学生来说，在教务系统中，他唯一关心且只能查询到自己的成绩和课程信息，只需判断学生是否输入了正确的用户名和密码并显示成绩课程信息即可。
但是对于教师来说，情况主要分为以下几种：
①仅担任班主任，不承担教学任务的老师主要关心自己所负责班级的同学的成绩课程信息。对于别的班的同学成绩，他不会关心也没有权限访问。除此之外，一个担任班主任的老师还可能兼任多个班级的班主任。
②仅承担教学任务，不担任班主任的老师主要关心选修了自己所执教课程的同学的成绩信息。对于没有选修自己执教课程的同学的成绩或者其他老师执教课程的成绩，他不会关心也没有权限访问。除此之外，一个承担教学任务的老师还可能执教多门课程。
③即担任班主任，又承担教学任务的老师比较特殊，需要考虑上述两种情况，既要关心自己所负责班级的同学的成绩课程信息，又要关心选修了自己所执教课程的同学的成绩信息。
④本教务系统不存在既不承担教学任务，又不担任班主任的老师。这种老师在现实生活中存在，比如主管科研的老师、实验中心的老师等，但本系统不予考虑。
教师访问的几种情况已经清楚了，系统处理教师的访问请求流程如图：
在这里插入图片描述

5.1提示用户输入用户名与密码

进入系统，出现系统提示，为了以老师身份登录系统，输入2：
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200203133815890.png
根据提示输入用户名和密码：

print('欢迎登录花花幼儿园教务系统')
print('1---学生登录')
print('2---教师登录')
flag=input('')
uname=input("请输入用户名：")
upwd=input("请输入密码：")
if flag=='1':   
    student(uname,upwd)#学生端程序代码见《python操作数据库实例——花花幼儿园教务系统的设计Ⅰ》
elif flag=='2':
    teacher(uname,upwd)

flag用于判断是学生登录还是教师登录，uname用于存储用户输入的用户名，upwd用于存储用户输入的密码。因为选择了2，执行teacher()函数。

5.2判断用户名或密码是否正确

在5.1中输入的用户名和密码分别被存储在uname和upwd中，它们作为参数被传递到teacher()函数中。
判断用户名或密码是否正确是进行下一步查询课程与成绩的基础，只有在用户名和密码都正确的情况下，才能继续下一步操作。判断的原理很简单，只需要在存储着用户名（USERNAME）和登录密码（psw）的login表中查询是否存在满足USERNAME=uname，psw=upwd条件的元组。二者缺一不可。如果存在，则判定登录成功，否则登录失败。
暂且抛开python不谈，在SQL Server 2012中，查找满足上述条件的SQL语句为：

USE login
GO
	select * from login
		where USERNAME ='USERNAME' and psw='psw' 
GO

若输入正确的账号密码组合，如（USERNAME，psw）=（t001，happy），则查询成功。
在这里插入图片描述
若输入错误的账号密码组合，如（USERNAME，psw）=（t001，wrongpsw），则查询失败，查询结果为空。

现在回到python代码中，在函数teacher()中

	sqlQuery="select * from login where USERNAME ='%s' and psw='%s' "%(uname,upwd)
    results=[]
    conn = pymssql.connect(server='DESKTOP-LYZ', user='sa', password='12345', database='login')
    cursor = conn.cursor()
    cursor.execute(sqlQuery)
    results = cursor.fetchone()

sqlQurey为SQL查询语句，results用于存储满足条件“USERNAME =’%s’ and psw=’%s’”的记录。results是否为空决定着用户名和密码的正确组合是否查找成功。

5.2.1用户名以及密码正确,提示登录成功并输出欢迎语

	if results is not None:
        	sqlQuery="select Tname,Title FROM Teacher where T# ='%s' "%(uname)
        	cursor.execute(sqlQuery)
        	results = cursor.fetchone()
        	print('您好，'+results[0].rstrip()+results[1].rstrip()+'，您已成功登录花花幼儿园学生成绩管理系统，请选择您要查看的成绩：')

若results不为空，即在login中成功查询到USERNAME=uname，psw=upwd的记录，说明教师用户输入了正确的用户名以及密码，获得了访问教务系统的权限。
在此条件下，设计登录成功提示语，在Teacher表中满足T#为已输入的uname的行中选择教师姓名Tname和职称Title，SQL语句执行后获得的结果存储在results中。（默认老师的用户名与教师号一致）
因为选择的列为Tname,Title两列，因此可以将results看作两个元素的一维数组，他的两个元素分别是results[0],results[1]。
因为Tname,Title的数据类型为nchar(10)，Tname,Title不足10的部分会被补足空格，因此使用.rstrip()将字符串后面的空格去掉。关于使用.rstrip()去掉空格的部分在学生端教务系统的设计中有更加详细的介绍。
提示登录成功并输出欢迎语的样例如下：
在这里插入图片描述

5.2.2用户名或密码错误,输出错误提示

如果在login中没有查找到USERNAME=uname且psw=upwd的记录，则说明用户输入的用户名和密码中至少有一个是错误的。我们不必纠结是哪个错了，总之此时输出错误提示。此处的else等价于if results is None

    else:
        print('用户名或密码错误') 
        conn.close()

5.3提示用户输入自己希望进行的下一步操作

        while(1):
            print('1---查看您所管理的班级的同学成绩')
            print('2---查看您所执教课程的成绩')
            print('3---退出教务系统')
            flag=input()

用flag记录用户的输入，不同的输入代表不同的功能。
while(1)的功能是，只要没有break语句出现，整个循环就会一直进行下去，直到break出现跳出循环为止。即进行完一次查询之后，程序会自动回到while循环开始的地方等待用户输入下一条指令，直到遇到break。这样做的目的是方便老师输入一次用户名及密码，就可以进行多次查询。而不会陷入每进行一次查询就重新输入一次密码的尴尬局面。

5.4对用户的输入进行判断

当用户输入“1”，表示用户站在班主任的角度，希望查看其所管理的班级的同学成绩；当用户输入“2”，表示用户站在任课老师的角度，希望查看选修了其执教课程的同学的成绩；当用户输入“3”，表明查询结束，用户希望退出教务系统，查询工作完毕。

5.4.1用户输入‘1’，进入班级成绩查询模式

            if flag=='1':
                Class=input("请输入您管理的班级的班号:")
                sqlQuery="select Class FROM TeacherClass where T# ='%s' and Class = '%s' "%(uname,Class)
                cursor.execute(sqlQuery)
                results = cursor.fetchall()
                if results==[]:
                    print("抱歉，您不是此班级的班主任，暂无成绩可供查询，如有异议请咨询教务处李老师")
                else:
                    sqlQuery="select Student.S#,Sname,Score,Cname,Credit from SC,Course,TeacherClass,Student where T# ='%s' and TeacherClass.Class ='%s' and TeacherClass.Class=Student.Class and Student.S#=SC.S# and SC.C#=Course.C#  "%(uname,Class)
                    cursor.execute(sqlQuery)
                    results = cursor.fetchall()
                    output_teacher(results)

用户作为班主任，在班级成绩查询模式下，关心自己所负责班级的同学的成绩课程信息。
首先输入班级号，用Class记录，SQL语言：

sqlQuery="select Class FROM TeacherClass where T# ='%s' and Class = '%s' "%(uname,Class)

功能是在TeacherClass表中查询满足T#=uname，Class=Class的记录的Class值，将结果保存在results中。由于一次只能输入一个“Class”，符合条件的记录要么有唯一的一条，要么不存在。
如果results为空，说明这条记录没有被成功查询到，即班主任与班级不匹配，该用户无法访问班号为Class的班级的成绩，输出“您不是此班级的班主任，暂无成绩可供查询”的提示。否则,当成功查询到记录，表明班号为Class的班级的班主任是教工号（用户名）为T#的老师，该老师拥有访问其管理的班级同学成绩信息的权限。
接下来就到了成绩查询的部分：查询班主任是该用户的班级的学生的学号。姓名，选修课程，各科成绩，课程学分。这里使用了多表查询，虽然SQL语言比较复杂，但是连接条件还是比较清楚的。

sqlQuery="select Student.S#,Sname,Score,Cname,Credit from SC,Course,TeacherClass,Student where T# ='%s' and TeacherClass.Class ='%s' and TeacherClass.Class=Student.Class and Student.S#=SC.S# and SC.C#=Course.C#  "%(uname,Class)

5.4.2用户输入‘2’，进入课程成绩查询模式

            elif flag=='2':
                Cnum=input("请输入您所执教课程的课程号:")
                sqlQuery="select C# FROM TeacherCourse where T# ='%s'and C#='%s' "%(uname,Cnum)
                cursor.execute(sqlQuery)
                results = cursor.fetchall()
                if results==[]:
                    print("抱歉，您尚未执教此门课程，暂无成绩可供查询，如有异议请咨询教务处李老师")
                else:
                    sqlQuery="select Student.S#,Sname,Score,Cname,Credit from SC,Course,TeacherCourse,Student where T# ='%s' and TeacherCourse.C# ='%s' and TeacherCourse.C#=SC.C# and SC.C#=Course.C# and Student.S#=SC.S#   "%(uname,Cnum)
                    cursor.execute(sqlQuery)
                    results = cursor.fetchall()
                    output_teacher(results)

用户作为任课教师，在课程成绩查询模式下，关心选修了自己所执教课程的同学的成绩课程信息。
首先输入课程号，用Cnum记录，SQL语言：

sqlQuery="select C# FROM TeacherCourse where T# ='%s'and C#='%s' "%(uname,Cnum)

功能是在TeacherCourse表中查询满足T#=uname，C#=Cnum的记录的C#值，将结果保存在results中。由于一次只能输入一个“Cnum”，符合条件的记录要么有唯一的一条，要么不存在。
如果results为空，说明这条记录没有被成功查询到，即任课教师与执教课程不匹配，该用户无法访问选修了课程号为Cum的课程的同学的成绩，输出“您尚未执教此门课程，暂无成绩可供查询”的提示。否则,当成功查询到记录，表明担任课程号为C#的课程的教学任务的老师是教工号（用户名）为T#的老师，该老师拥有访问选修了其执教课程的同学该课成绩的权限。
接下来就是查询了，同样是多表查询，思路与5.4.1是一样的。

5.4.3用户输入‘3’，退出教务系统

            else:
                print("您已成功退出教务系统！")
                break

退出教务系统，输出退出提示。此处的break的作用是跳出while循环。只要不退出教务系统，while循环就会一直进行。

6.结果演示

输入用户名为t001
输入密码为happy
在这里插入图片描述
选择2，查看执教课程的成绩，课程号为1001，成绩信息显示如下

选择2，查看执教课程的成绩，课程号为1002，成绩信息显示如下

选择2，查看执教课程的成绩，课程号为1003，显示错误信息

选择1，查看所管理班级同学的成绩，课程号为301，成绩信息显示如下
在这里插入图片描述
选择1，查看所管理班级同学的成绩，课程号为402，显示错误信息

选择3，退出教务系统

7.小结

到此为止，花花幼儿园教务系统的设计就完成了。在设计过程中，虽然本人尽量去考虑教务系统中查询成绩时出现的各种情况，比如教师担任的角色不同的情况，一个老师担任不同课程教学任务的情况，一个班主任负责多个班级的情况等。但是整个系统仍然是非常粗浅和简易的，涉及到的python与数据库知识的难度也比较低。

8.学生端与教师端完整代码

import pymssql

def output(array):
    for i in range(len(array)):
        for j in range(len(array[0])):
            print(array[i][j],end='')
            print('   ',end='')
        print(' ')
def output_student(array):
    print('  学号     '+' 分数  '+'课程名  '+'        学分')
    for i in range(len(array)):
        for j in range(len(array[0])):
            print(array[i][j],end='')
            print('   ',end='')
        print(' ')
def output_teacher(array):
    print('  学号     '+'  姓名       '+'   分数 '+' 课程名  '+'       学分')
    for i in range(len(array)):
        for j in range(len(array[0])):
            print(array[i][j],end='')
            print('   ',end='')
        print(' ')
        
def student(uname,upwd):
    sqlQuery="select * from login where USERNAME ='%s' and psw='%s' "%(uname,upwd)
    results=[]
    conn = pymssql.connect(server='DESKTOP-LYZ', user='sa', password='12345', database='login')
    cursor = conn.cursor()
    cursor.execute(sqlQuery)
    results = cursor.fetchone()
    if results is not None:        
        sqlQuery="select Class,Sname FROM Student where S# ='%s' "%(uname)
        cursor.execute(sqlQuery)
        results = cursor.fetchone()
        print('您好，'+results[0].rstrip()+'班的'+results[1].rstrip()+'同学，欢迎登录花花幼儿园教务系统')
        sqlQuery="select S#,Score,Cname,Credit from SC,Course where S# ='%s'and SC.C#=Course.C# "%(uname)
        cursor.execute(sqlQuery)
        results = cursor.fetchall()
        output_student(results)
        conn.close()
        return 1
    else:
        print('用户名或密码错误') 
        conn.close()
        return 0
    
    
def teacher(uname,upwd):
    sqlQuery="select * from login where USERNAME ='%s' and psw='%s' "%(uname,upwd)
    results=[]
    conn = pymssql.connect(server='DESKTOP-LYZ', user='sa', password='12345', database='login')
    cursor = conn.cursor()
    cursor.execute(sqlQuery)
    results = cursor.fetchone()
    if results is not None:
        sqlQuery="select Tname,Title FROM Teacher where T# ='%s' "%(uname)
        cursor.execute(sqlQuery)
        results = cursor.fetchone()
        print('您好，'+results[0].rstrip()+results[1].rstrip()+'，您已成功登录花花幼儿园学生成绩管理系统，请选择您要查看的成绩：')
        while(1):
            print('1---查看您所管理的班级的同学成绩')
            print('2---查看您所执教课程的成绩')
            print('3---退出教务系统')
            flag=input()
            if flag=='1':
                Class=input("请输入您管理的班级的班号:")
                sqlQuery="select Class FROM TeacherClass where T# ='%s' and Class = '%s' "%(uname,Class)
                cursor.execute(sqlQuery)
                results = cursor.fetchall()
                if results==[]:
                    print("抱歉，您不是此班级的班主任，暂无成绩可供查询，如有异议请咨询教务处李老师")
                else:
                    sqlQuery="select Student.S#,Sname,Score,Cname,Credit from SC,Course,TeacherClass,Student where T# ='%s' and TeacherClass.Class ='%s' and TeacherClass.Class=Student.Class and Student.S#=SC.S# and SC.C#=Course.C#  "%(uname,Class)
                    cursor.execute(sqlQuery)
                    results = cursor.fetchall()
                    output_teacher(results)                                  
            elif flag=='2':
                Cnum=input("请输入您所执教课程的课程号:")
                sqlQuery="select C# FROM TeacherCourse where T# ='%s'and C#='%s' "%(uname,Cnum)
                cursor.execute(sqlQuery)
                results = cursor.fetchall()
                if results==[]:
                    print("抱歉，您尚未执教此门课程，暂无成绩可供查询，如有异议请咨询教务处李老师")
                else:
                    sqlQuery="select Student.S#,Sname,Score,Cname,Credit from SC,Course,TeacherCourse,Student where T# ='%s' and TeacherCourse.C# ='%s' and TeacherCourse.C#=SC.C# and SC.C#=Course.C# and Student.S#=SC.S#   "%(uname,Cnum)
                    cursor.execute(sqlQuery)
                    results = cursor.fetchall()
                    output_teacher(results)
            else:
                print("您已成功退出教务系统！")
                break
    else:
        print('用户名或密码错误')    
    conn.close()    
    
print('欢迎登录花花幼儿园教务系统')
print('1---学生登录')
print('2---教师登录')
flag=input('')
uname=input("请输入用户名：")
upwd=input("请输入密码：")
if flag=='1':   
    student(uname,upwd)
elif flag=='2':
    teacher(uname,upwd)

FoggyProgrammer

关注

4
点赞
踩
33

收藏

觉得还不错? 一键收藏
0
评论
python操作SQL Server数据库的多表查询实例——花花幼儿园教务系统的设计Ⅱ

1.前言本文介绍了利用python操作数据库，并搭建一个模拟学校教务系统的方法，本文会给出详细的创建过程以及程序代码。建立python以及数据库之间的连接操作以及基本的增删改查操作可参见利用python实现在SQL Server数据库中的增、删、改、查本文为实例设计的后半部分，即教务系统教师端的设计。学生端的设计参见python操作数据库实例——花花幼儿园教务系统的设计Ⅰ2.任务要求为...
复制链接

扫一扫