【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, ("无法打开数据库"), ("无法建立到数据库的连接,请检查数据库配置。\n\n" "点击取消按钮退出应用。"), 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, ("无法打开数据库"),("无法建立到数据库的连接,请检查数据库配置。\n\n" 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, ("提示"), ("单击左侧需要修改的单元格。\n\n""在单元格中修改即可。"), 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
