【pyqt5 QtDesigner SQLserver2017】 Python3.6 Demo

1 使用QtDesigner 设计界面，并设置按钮的click信号，给槽起名字。得到formMain.ui文件。

2 使用QtUIC把ui文件转换为py文件，得到界面文件formMain.py文件。  

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'formMain.ui'
#
# Created by: PyQt5 UI code generator 5.10.1
#
# WARNING! All changes made in this file will be lost!

from PyQt5 import QtCore, QtGui, QtWidgets

class Ui_Main(object):
    def setupUi(self, Main):
        Main.setObjectName("Main")
        Main.setWindowModality(QtCore.Qt.NonModal)
        Main.resize(603, 457)
        self.tableView = QtWidgets.QTableView(Main)
        self.tableView.setGeometry(QtCore.QRect(50, 20, 391, 391))
        self.tableView.setObjectName("tableView")
        self.pushButton = QtWidgets.QPushButton(Main)
        self.pushButton.setGeometry(QtCore.QRect(490, 60, 75, 23))
        self.pushButton.setObjectName("pushButton")
        self.label = QtWidgets.QLabel(Main)
        self.label.setGeometry(QtCore.QRect(160, 420, 231, 16))
        self.label.setObjectName("label")
        self.lineEdit = QtWidgets.QLineEdit(Main)
        self.lineEdit.setGeometry(QtCore.QRect(490, 260, 71, 20))
        self.lineEdit.setObjectName("lineEdit")
        self.pushButton_2 = QtWidgets.QPushButton(Main)
        self.pushButton_2.setGeometry(QtCore.QRect(490, 130, 75, 23))
        self.pushButton_2.setObjectName("pushButton_2")
        self.pushButton_3 = QtWidgets.QPushButton(Main)
        self.pushButton_3.setGeometry(QtCore.QRect(490, 210, 75, 23))
        self.pushButton_3.setObjectName("pushButton_3")
        self.pushButton_4 = QtWidgets.QPushButton(Main)
        self.pushButton_4.setGeometry(QtCore.QRect(490, 170, 75, 23))
        self.pushButton_4.setObjectName("pushButton_4")
        self.pushButton_5 = QtWidgets.QPushButton(Main)
        self.pushButton_5.setGeometry(QtCore.QRect(490, 390, 75, 23))
        self.pushButton_5.setObjectName("pushButton_5")
        self.pushButton_6 = QtWidgets.QPushButton(Main)
        self.pushButton_6.setGeometry(QtCore.QRect(490, 300, 75, 23))
        self.pushButton_6.setObjectName("pushButton_6")
        self.pushButton_7 = QtWidgets.QPushButton(Main)
        self.pushButton_7.setGeometry(QtCore.QRect(490, 20, 75, 23))
        self.pushButton_7.setObjectName("pushButton_7")

        self.retranslateUi(Main)
        self.pushButton.clicked.connect(Main.btnAll)
        self.pushButton_2.clicked.connect(Main.btnInsert)
        self.pushButton_4.clicked.connect(Main.btnDelete)
        self.pushButton_3.clicked.connect(Main.btnUpdate)
        self.pushButton_6.clicked.connect(Main.btnQuery)
        self.pushButton_5.clicked.connect(Main.btnClose)
        self.pushButton_7.clicked.connect(Main.btnCreate)
        QtCore.QMetaObject.connectSlotsByName(Main)

    def retranslateUi(self, Main):
        _translate = QtCore.QCoreApplication.translate
        Main.setWindowTitle(_translate("Main", "SQLserver基本操作"))
        self.pushButton.setText(_translate("Main", "全部数据"))
        self.label.setText(_translate("Main", "Design by David 2018.3.21"))
        self.pushButton_2.setText(_translate("Main", "增加"))
        self.pushButton_3.setText(_translate("Main", "修改"))
        self.pushButton_4.setText(_translate("Main", "删除"))
        self.pushButton_5.setText(_translate("Main", "关闭"))
        self.pushButton_6.setText(_translate("Main", "查询"))
        self.pushButton_7.setText(_translate("Main", "建表"))

3 新建mainForm.py文件，用于写逻辑代码。

3.1 编写代码，显示界面，暂时不写其他逻辑

from PyQt5.QtSql import QSqlQuery, QSqlDatabase
from PyQt5.QtWidgets import QDialog, QMessageBox
from qtpy import QtWidgets

from formMain import Ui_Main


class mainForm(QDialog,Ui_Main):
    def __init__(self):
        QDialog.__init__(self)
        self.setupUi(self)


    def btnCreate(self):
        pass

    def btnAll(self):
        pass

    def btnInsert(self):
        pass

    def btnDelete(self):
        pass

    def btnUpdate(self):
        pass

    def btnQuery(self):
        pass

    def btnClose(self):
        pass


if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    myForm = mainForm()
    myForm.show()
    sys.exit(app.exec_())

写入以上代码后，界面可以正常显示。

3.2 编写函数

 3.2.1 btnCreate（）

写完下方代码，运行，点击“建表”按钮，在SQLserver中可以查看到建好的表和插入的数据。

db.setDatabaseName('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;' % ('.', 'Test', 'sa', 'sql'))
这部分代码中的参数，需要改成自己的SQLserver配置。参数分别为：服务器名字，数据库名，用户名，密码。
    def btnCreate(self):
        db = QSqlDatabase.addDatabase('QODBC')
        db.setDatabaseName('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;' % ('.', 'Test', 'sa', 'sql'))

        if not db.open():
            print('db.open failed')
            QMessageBox.critical(None, ("无法打开数据库"),
                                 ("无法建立到数据库的连接,请检查数据库配置。

"
                                  "点击取消按钮退出应用。"), QMessageBox.Cancel)
            return False

        query = QSqlQuery()
        query.exec_("create table student(sno varchar(10), sname varchar(10))")
        query.exec_("insert into student values('1', 'Tom')")
        query.exec_("insert into student values('2', 'Jerry')")
        query.exec_("insert into student values('3', 'Mike')")
        query.exec_("insert into student values('4', 'Jane')")
        query.exec_("insert into student values('5', 'David')")
        db.close()
        return True

3.2.2 btnAll（）

填入下方代码，点击“全部数据”，左边表格显示数据库中的全部数据。

    def btnAll(self):
        db = QSqlDatabase.addDatabase('QODBC')
        db.setDatabaseName('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;' % ('.', 'Test', 'sa', 'sql'))
        self.model = QSqlTableModel()
        self.model.setTable('student')
        self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.model.select()
        self.model.setHeaderData(0, Qt.Horizontal, "学号")
        self.model.setHeaderData(1, Qt.Horizontal, "姓名")
        self.tableView.setModel(self.model)
        # 下面代码让表格100%填满窗口
        self.tableView.horizontalHeader().setStretchLastSection(True)
        self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

3.2.3 为了使用方便，先把“关闭”按钮的槽函数btnClose（）写了。

    def btnClose(self):
        self.close()

完成以上内容之后，后面主要就是关于数据库的逻辑操作了，mainForm.py全部代码如下：

 1 from PyQt5.QtCore import Qt
 2 from PyQt5.QtSql import QSqlQuery, QSqlDatabase, QSqlTableModel
 3 from PyQt5.QtWidgets import QDialog, QMessageBox, QHeaderView
 4 from qtpy import QtWidgets
 5 
 6 from formMain import Ui_Main
 7 
 8 
 9 class mainForm(QDialog,Ui_Main):
10     def __init__(self):
11         QDialog.__init__(self)
12         self.setupUi(self)
13         # 连接SQLserver数据库
14         self.db = QSqlDatabase.addDatabase('QODBC')
15         self.db.setDatabaseName('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;' % ('.', 'Test', 'sa', 'sql'))
16         self.model = QSqlTableModel()
17         self.model.setTable('student')  # 载入student表
18         self.model.setHeaderData(0, Qt.Horizontal, "学号")
19         self.model.setHeaderData(1, Qt.Horizontal, "姓名")
20         self.tableView.horizontalHeader().setStretchLastSection(True)# 表格宽度充满父控件
21         self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
22         self.query = QSqlQuery()
23         self.btnAll()
24 
25     def btnCreate(self):
26         if not self.db.open():
27             print('db.open failed')
28             QMessageBox.critical(None, ("无法打开数据库"),("无法建立到数据库的连接,请检查数据库配置。

"
29                                   "点击取消按钮退出应用。"), QMessageBox.Cancel)
30             return False
31 
32         self.query.exec_("DROP TABLE  IF EXISTS student")
33         self.query.exec_("create table student(sno varchar(10), sname varchar(10))")
34         self.query.exec_("insert into student values('101', 'Tom')")
35         self.query.exec_("insert into student values('102', 'Jerry')")
36         self.query.exec_("insert into student values('103', 'Mike')")
37         self.query.exec_("insert into student values('104', 'Jane')")
38         self.query.exec_("insert into student values('105', 'David')")
39         self.db.close()
40         return True
41 
42     def btnAll(self):
43         self.model.select()
44         self.tableView.setModel(self.model)
45 
46     def btnInsert(self):
47         self.model.insertRows(self.model.rowCount(), 1)
48 
49     def btnDelete(self):
50         self.model.removeRow(self.tableView.currentIndex().row())
51 
52     def btnUpdate(self):
53         QMessageBox.information(None, ("提示"), ("单击左侧需要修改的单元格。

""在单元格中修改即可。"), QMessageBox.Close)
54 
55     def btnQuery(self):
56         studentid = self.lineEdit.text()
57         self.query.exec_("select * from student where sno = '%s'" %(studentid))
58         self.model.setQuery(self.query)
59         self.tableView.setModel(self.model)
60 
61     def btnClose(self):
62         self.close()
63 
64 
65 if __name__ == "__main__":
66     import sys
67     app = QtWidgets.QApplication(sys.argv)
68     myForm = mainForm()
69     myForm.show()
70     sys.exit(app.exec_())

 “建表”时间需要等待，点击“全部数据”能看到数据后，再做其他部分操作。

 “建表”只需要运行一次，需要初始化数据时候，可以再次点击建表。

源代码：https://github.com/HBU/Jupyter/tree/master/pyqt/09PyQt5SqlServer

 

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/hbuwyg/p/8615985.html