Python 通过pymssql访问查询操作 SQL Server数据库

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

一、数据库模型

本文中以下面的本地SQL Server数据库为例进行数据库连接，数据表的查询、增加、删除、修改等操作。
主机地址：localhost（我的是本地服务器，使用localhost,在linx系统下测试时使用了远程的数据库）
端口：默认的1433
用户名：sa
密码：sa1234
数据库名：myTestDb
数据库myTestDb中预设一个表tab_product，表结构如下：

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

二、pymssql库及简介

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

pip install pymssql

进行下载安装。
1、使用pymssql创建数据连接，需要将SQL Server服务器的参数传给pymssql的connect方法，成功后建立连接。如下：

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

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

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

三、实例演示

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

界面上部为数据连接的主要参数，包括主机名，端口，数据库名称，用户名和密码等参数，设置完成后点击连接，连接成功后。才能进行下面的其它操作。
界面中部为两个文本框，用于输入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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

四、运行效果：

1、数据库连接成功时：

2、数据库连接失败时：

3、查询数据成功时：

4、查询数据失败时：

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

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

7 、关闭窗口时提示：

五、测试环境：
这段代码在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进行测试）。