Python 通过pymssql访问查询操作 SQL Server数据库
作者:mmseoamin日期:2023-12-13

在企业应用开发中,经常用到应用程序访问数据库的开发模式,中小企业使用的数据库中,以ms SQL Server居多。本文就以一个简单的实例模型,简单介绍一下python访问ms sql sever数据库的方法。

一、数据库模型

本文中以下面的本地SQL Server数据库为例进行数据库连接,数据表的查询、增加、删除、修改等操作

主机地址:localhost(我的是本地服务器,使用localhost,在linx系统下测试时使用了远程的数据库)

端口:默认的1433

用户名:sa

密码:sa1234

数据库名:myTestDb

数据库myTestDb中预设一个表tab_product,表结构如下:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第1张

Tab_product中已经包括以下5行试验数据:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第2张

二、pymssql库及简介

python访问ms SQL Server 需要用到Pymssql库,如没有安装,可以在命令行模式下输入:

pip install pymssql

进行下载安装。

1、使用pymssql创建数据连接,需要将SQL Server服务器的参数传给pymssql的connect方法,成功后建立连接。如下:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第3张

2、查询数据方法:查询数据使用标准的SQL查询语句为作cursor对象execute方法的参数,注意返回值results为一个数据集合,result为集合中的每一行,res为每一行中的相关字段。

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第4张

3、数据操作:包括数据的增加、删除、修改等。也是使用标准的SQL语句。如下为增加一行数据:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第5张

注意所有操作完成后,需要使用conn.close()关闭和数据库的连接。

三、实例演示

演示界面使用pyqt5开发。界面如下:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第6张

界面上部为数据连接的主要参数,包括主机名,端口,数据库名称,用户名和密码等参数,设置完成后点击连接,连接成功后。才能进行下面的其它操作。

界面中部为两个文本框,用于输入SQL数据查询命令和SQL操作执行命令(如删除、新增、更新等)

界面下部为一个多行文本框,用于显示、记录查询结果或记录。

三、完整代码

实现的完整代码如下:代码中的注释比较清楚,不再赘述。

需要说明的一点就是关于python和SQL Server 使用的字符集不同,处理不当会出现中文乱码的情况,这个在需要注意和进行转换的代码中注释的非常清楚。

#encoding:utf8
'''
code writing:hnkkfan
本代码用于演示pyqt5访问查询和操作sql sever数据库
布局采用一个盒式布局,一个垂直布局里面包括五个水平布局
第一行为连接参数,包括主机名(地址),端口、数据库名
第二行为:用户名和密码,再加一个连接按钮
第三行为一个文本框,用于输入数据查询命令,后面跟一个查询按询
第四行为一个文本框,用于输入数据执行命令,后面跟一个执行按询
第五行为一个多行文本框,用于显示执行效果和错误信息
'''
import sys
from PyQt5.Qt import (QApplication, QWidget, QPushButton,QThread,pyqtSignal)
import pymssql
from PyQt5.QtWidgets import QTextEdit, QHBoxLayout, QVBoxLayout, QLineEdit, QLabel
from PyQt5.QtWidgets import QMessageBox

class MyWin(QWidget):
    conn=False
    def __init__(self):
        super().__init__()
        self.btn_conn = QPushButton('连接', self)#定义一个按钮
        self.btn_conn.clicked.connect(self.btnClick_conn)  # 给按钮绑定连接函数
        hbox_line1 = QHBoxLayout()
        hbox_line1.addWidget(QLabel("服务器主机名(IP地址):"))
        self.txt_host = QLineEdit("localhost")#数据库服务器主机本地为localhost
        hbox_line1.addWidget(self.txt_host)
        hbox_line1.addWidget(QLabel("端口:"))
        self.txt_port = QLineEdit("1433")#数据库服务器端口,默认为1433
        self.txt_port.setMaximumWidth(100)
        self.txt_port.setMinimumWidth(100)
        hbox_line1.addWidget(self.txt_port)
        hbox_line2 = QHBoxLayout()
        hbox_line2.addWidget(QLabel("数据库:"))
        self.txt_dbname = QLineEdit("myTestDb")#数据库名
        hbox_line2.addWidget(self.txt_dbname)
        hbox_line2.addWidget(QLabel("用户名:"))
        self.txt_user = QLineEdit("sa")#连接数据库的用户名
        self.txt_user.setMaximumWidth(160)
        self.txt_user.setMinimumWidth(160)
        hbox_line2.addWidget(self.txt_user)
        hbox_line2.addWidget(QLabel("密码:"))
        self.txt_pass = QLineEdit("SQL2008r2")#连接数据库的用户密码
        self.txt_pass.setMaximumWidth(160)
        self.txt_pass.setMinimumWidth(160)
        self.txt_pass.setEchoMode(QLineEdit.Password)#设置密码内容用点代替
        hbox_line2.addWidget(self.txt_pass)
        hbox_line2.addWidget(self.btn_conn)
        hbox_line3=QHBoxLayout()
        hbox_line3.addWidget(QLabel("查询语句:"))
        self.txt_selelct=QLineEdit("SELECT * FROM tab_product")#示例的查询语句
        self.btn_select = QPushButton('查询', self)  # 查询按钮
        self.btn_select.clicked.connect(self.btnClick_select)  # 给查询绑定函数
        self.btn_select.setEnabled(False)
        hbox_line3.addWidget(self.txt_selelct)
        hbox_line3.addWidget(self.btn_select)
        hbox_line4 = QHBoxLayout()
        hbox_line4.addWidget(QLabel("操作语句:"))
        #示例的操作语句,可以使用标准的SLQ 删除、更新及插入语句
        self.txt_do = QLineEdit("update tab_product set p_name='' where p_id=")
        self.btn_do = QPushButton('执行', self)  # 执行按钮
        self.btn_do.clicked.connect(self.btnClick_do)  # 给执行按钮绑定函数
        self.btn_do.setEnabled(False)
        hbox_line4.addWidget(self.txt_do)
        hbox_line4.addWidget(self.btn_do)
        hbox_line5=QHBoxLayout()
        self.txt_PressInfo = QTextEdit()#定义一个显示操作信息的多行文本框
        hbox_line5.addWidget(self.txt_PressInfo)
        vbox = QVBoxLayout()
        vbox.addLayout(hbox_line1)
        vbox.addLayout(hbox_line2)
        vbox.addLayout(hbox_line3)
        vbox.addLayout(hbox_line4)
        vbox.addLayout(hbox_line5)
        self.setLayout(vbox)  # 把垂直布局器添加到窗口
    def btnClick_conn(self):#连接按钮的点击事件
        #建立与数据库的连接,参数分别为主机名,端口,用户名,用户密码和连接的数据库
        # 注意:这里字符集使用的python文本常用的utf8(也可不加,因为默认就是utf8)
        try:
            self.conn = pymssql.connect(host=self.txt_host.text(),
                                    port=self.txt_port.text(),
                                    user=self.txt_user.text(),
                                    password=self.txt_pass.text(),
                                    database=self.txt_dbname.text(),
                                    charset='utf8')
            self.btn_select.setEnabled(True)
            self.btn_do.setEnabled(True)
            self.txt_PressInfo.append("数据库已经连接!")
        except:#如不能连接上则让查询和执行按钮不可以用并进行提示
            self.txt_PressInfo.append("数据库连接失败!")
    def btnClick_select(self):#查询按钮的点击事件
        cursor=self.conn.cursor()#建立游标
        try:#用一个try来进行查询,成功则返回查询结果,不成功则提示查询失败
            cursor.execute(self.txt_selelct.text())
            results=cursor.fetchall()
            for result in results:#对数据集合中的每行进行操作
                showTxt=""
                for col in range(len(result)):#对每行中每列数据进行操作
                    # 因为SQL Server返回的是GBK字符串,在uft8下中文会出现乱码,
                    # 通过.encode('latin-1').decode('gbk')转码解决问题
                    # strip()用于清除字符串前后的空格,
                    # ljust()用于将所有字段长度固定(不足时补齐)
                    showTxt=showTxt+str(result[col]).strip().ljust(20).encode('latin-1').decode('gbk')
                self.txt_PressInfo.append(showTxt)#把本行查询结果追加到多行文本框中显示
        except:
            self.txt_PressInfo.append("查询数据记录时发生错误!")
    def btnClick_do(self):#数据操作按钮的点击事件
        cursor = self.conn.cursor()
        try:#通过一个try对数据进行操作,成功则提示操作成功,否则提示操作错误。
            sqldo=self.txt_do.text()
            cursor.execute(sqldo)
            self.conn.commit()
            self.txt_PressInfo.append("执行数据操作完成!")
        except:
            self.txt_PressInfo.append("执行数据操作时发生错误!")
            
    #定义一个关闭窗口时的对话框,如确定退出,则关闭conn的连接。
    def closeEvent(self, event):  # 关闭窗口触发以下事件
        a = QMessageBox.question(self, '退出', '你确定要退出吗?', QMessageBox.Yes | QMessageBox.No,
                                 QMessageBox.No)  # "退出"代表的是弹出框的标题,"你确认退出.."表示弹出框的内容
        if a == QMessageBox.Yes:
            if self.conn:
                self.conn.close()
            event.accept()  # 接受关闭事件
        else:
            event.ignore()  # 忽略关闭事件
if __name__ == "__main__":
    app = QApplication(sys.argv)
    myshow = MyWin()
    myshow.setWindowTitle("Pyqt5连接SQL Server 演示")
    myshow.setMinimumHeight(640)
    myshow.setMinimumWidth(720)
    myshow.show()
    sys.exit(app.exec_())

四、运行效果:

1、数据库连接成功时:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第7张

2、数据库连接失败时:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第8张

3、查询数据成功时:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第9张

4、查询数据失败时:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第10张

5、执行数据操作成功时:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第11张

6、执行数据操作失败时:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第12张

7 、关闭窗口时提示:

Python 通过pymssql访问查询操作 SQL Server数据库,在这里插入图片描述,第13张

五、测试环境:

这段代码在windows和linux下都测试通过。

1、windows下:windows11家庭版+python3.11.1+PyCharm2022.3.2(Community Edition)

2、linux下:deepin os 20.8+python 3.7.1+PyCharm Community 2022.3。(数据库采用公司服务器上远程的SQL Server进行测试)。