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

最新推荐文章于 2022-07-28 09:36:35 发布

FoggyProgrammer

最新推荐文章于 2022-07-28 09:36:35 发布

阅读量1.6k

点赞数 3

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

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

版权

Python学习专栏收录该内容

6 篇文章 2 订阅

订阅专栏

1.前言

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

2.任务要求

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

3.涉及到的表

所有数据库和表已经在SQL Server 2012中建立完毕，现给出设计学生端教务系统设计的表，如图所示：
在这里插入图片描述
数据库名为login，其中共7个表，本文只涉及到前4个表，后3个表在教务系统教师端设计时再给出

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

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

4.程序代码

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 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()
    else:
        print('用户名或密码错误') 
        conn.close()
    
    
def teacher(uname,upwd):
    #教师端程序代码见《python操作数据库实例——花花幼儿园教务系统的设计Ⅱ》
    
print('欢迎登录花花幼儿园教务系统')
print('1---学生登录')
print('2---教师登录')
flag=input('')
uname=input("请输入用户名：")
upwd=input("请输入密码：")
if flag=='1':   
    student(uname,upwd)
elif flag=='2':#教师端程序代码见下文
    teacher(uname,upwd)

5.设计流程与思路

对于学生来说，登录教务系统的目的是查询自己的成绩，系统处理学生的访问请求流程如图：
在这里插入图片描述

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

进入系统，出现系统提示，为了以学生身份登录系统，输入1：
在这里插入图片描述
根据提示输入用户名和密码：

print('欢迎登录花花幼儿园教务系统')
print('1---学生登录')
print('2---教师登录')
flag=input('')
uname=input("请输入用户名：")
upwd=input("请输入密码：")
if flag=='1':   
    student(uname,upwd)
elif flag=='2':#教师端程序代码见下文
    teacher(uname,upwd)

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

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

在5.1中输入的用户名和密码分别被存储在uname和upwd中，它们作为参数被传递到student()函数中。
判断用户名或密码是否正确是进行下一步查询课程与成绩的基础，只有在用户名和密码都正确的情况下，才能继续下一步操作。判断的原理很简单，只需要在存储着用户名（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）=（98030101，123456），则查询成功。
在这里插入图片描述
若输入错误的账号密码组合，如（USERNAME，psw）=（98030101，wrongpsw），则查询失败，查询结果为空。

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

	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 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()

若results不为空，即在login中成功查询到USERNAME=uname，psw=upwd的记录，说明学生用户输入了正确的用户名以及密码，获得了访问自己课程与成绩的权限。
在此条件下，设计登录成功提示语，在Student表中满足S#为已输入的uname的行中选择Class，Sname，SQL语句执行后获得的结果存储在results中。
因为选择的列为Class，Sname两列，因此可以将results看作两个元素的一维数组，他的两个元素分别是results[0],results[1]。
在这里插入图片描述
值得注意的是，在表的创建过程中，Sname、Class的数据类型为nchar(10)，Sname和Class不足10的部分会被补足空格。如：
301在表中实际为“301_______”（后面补足7个空格’_’）

 print('您好，'+results[0]+'班的'+results[1]+'同学，欢迎登录花花幼儿园教务系统')

如果按照上面的方式输出欢迎语，则欢迎语中会将表中默认的空格一并输出，看起来很不美观。
在这里插入图片描述
解决的方法是通过.rstrip()将空格去掉

print('您好，'+results[0].rstrip()+'班的'+results[1].rstrip()+'同学，欢迎登录花花幼儿园教务系统')

现在看起来很紧凑了
在这里插入图片描述
.rstrip()的使用绝不仅仅是为了看起来美观。我们知道，在字符串的比较中，‘301’和‘301_______’肯定是不一样的，因此在比较两个字符串是否相等时，.rstrip()将扮演至关重要的作用。
接下来输出学生课程与成绩信息，查找S#，Score,Cname,Credit的SQL语句为：

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

这里的SQL语句用到了多表联合查询，涉及到多个表。SQL语句的编写属于数据库知识，这里不赘述。在教师端教务系统的设计中，多表联合查询的使用将更加普遍与复杂。
将SQL语句的查询结果放在results中，并通过output_student()函数进行输出。

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(' ')

output_student()函数的功能是将各个列值单独输出，而不是以行为单位输出。

sqlQuery="select S#,Score,Cname,Credit from SC,Course where S# ='%s'and SC.C#=Course.C# "%(uname)
        cursor.execute(sqlQuery)
        results = cursor.fetchall()
        print(results)

若不使用output_student()函数，而是直接利用print()输出，会出现以下结果
在这里插入图片描述
点击链接可以了解更多：print()和output_student()的区别

5.2.2输出错误提示

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

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

QQ登录的错误提示样式，提示“账户名或密码不正确”：
在这里插入图片描述

6.结果演示

输入用户名为98030101
输入密码为123456
在这里插入图片描述
输入用户名为98030102
输入密码为100000

输入用户名为98030302
输入密码为100000

至此，教务系统的学生端设计就告一段落了。实例设计的后半部分，即教务系统教师端的设计《参见python操作数据库实例——花花幼儿园教务系统的设计Ⅱ》

FoggyProgrammer

关注

3
点赞
踩
24

收藏

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

1.前言本文介绍了利用python操作数据库，并搭建一个模拟学校教务系统的方法，本文会给出详细的创建过程以及程序代码。建立python以及数据库之间的连接操作以及基本的增删改查操作可参见https://blog.csdn.net/FoggyProgrammer/article/details/104108678本文为实例设计的前半部分，即教务系统学生端的设计。教师端的设计参见python操作数...
复制链接

扫一扫