PyQt provides us UI features which can be useful in number of ways to build our applications using all the features and widgets of PyQt. Moreover PyQt provides us the facility to integrate our database in our application. We can integrate any database through it some of them are- MySQL, SQLITE etc.
To link SQL database with PyQt5 application QtSql module is used.The SQL classes are divided n three layers:
1. Driver Layer: It have QSqlDriver, QSqlDriverCreatorBase, and QSqlResult classes.
2. SQL API Layer: These provide access to databases. For connection QSqlDatabase is used, Interaction with database is done by QSqlQuery class.
3. User Interface Layer: These work with Qt’s model framework. Some of them are QSqlQueryModel, QSqlTableModel etc.
To import the module following command is used:
from PyQt5 import QtSql
For connection to databases
self.QSqlDatabase.addDatabase("QMYSQL")
self.db.setHostName("neveropen")
self.db.setDatabaseName("gfgdb")
self.db.setUserName("Lazyroar")
self.db.setPassword("gfg")
First argument QSqlDatabase.addDatabase is used to add drivers(eg. QPSQL, QMYSQL, QOCI, QODBC, QSQLITE etc). All next four commands setHostName(), setDatabaseName(), setUserName(), and setPassword() initializes the database connection. QSqlDatabase.open() is called to open the database and to access it once it is initialized.
Executing MySQL Query
self.qry = QString("SELECT * FROM employee")
self.query = QSqlQuery()
self.query.prepare(self.qry)
self.query.exec()
QSqlQuery class provide exec() method to execute the query.
Now, to fetch the results in the form of table then following sequence of code is used:
for row_number, row_data in enumerate(self.query.result()):
for column_number, data in enumerate(row_data):
self.tableWidget.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(data)
Below only one type of query is shown but in the same way Insert, Delete, Update queries can also be executed, you need to replace only the query in self.qry variable. Database naming gfgdb should be there having one table naming employee.
Example:
# Write Python3 code here import sys from PyQt5 import QtCore, QtGui, QtWidgets, QtSql class Ui_MainWindow(object): have def setupUi(self, MainWindow): # Setting mainwindow MainWindow.setObjectName("MainWindow") MainWindow.resize(432, 813) MainWindow.setMinimumSize(QtCore.QSize(432, 813)) MainWindow.setMaximumSize(QtCore.QSize(432, 813)) self.centralwidget = QtWidgets.QWidget(MainWindow) self.centralwidget.setObjectName("centralwidget") self.frame = QtWidgets.QFrame(self.centralwidget) self.frame.setGeometry(QtCore.QRect(0, 0, 781, 821)) self.frame.setFrameShape(QtWidgets.QFrame.StyledPanel) self.frame.setFrameShadow(QtWidgets.QFrame.Raised) self.frame.setObjectName("frame") # setting up the output table self.tableWidget = QtWidgets.QTableWidget(self.frame) self.tableWidget.setGeometry(QtCore.QRect(0, 10, 431, 731)) self.tableWidget.setRowCount(10) self.tableWidget.setColumnCount(2) self.tableWidget.setObjectName("tableWidget") # initializing items to be added in the table item = QtWidgets.QTableWidgetItem() item1 = QtWidgets.QTableWidgetItem() # inserting above items to the table self.tableWidget.setHorizontalHeaderItem(0, item) self.tableWidget.setHorizontalHeaderItem(1, item1) self.tableWidget.horizontalHeader().setDefaultSectionSize(185) self.tableWidget.verticalHeader().setMinimumSectionSize(50) MainWindow.setCentralWidget(self.centralwidget) self.retranslateUi(MainWindow) QtCore.QMetaObject.connectSlotsByName(MainWindow) # connection to the database self.QSqlDatabase.addDatabase("QMYSQL") self.db.setHostName("neveropen") self.db.setDatabaseName("gfgdb") self.db.setUserName("Lazyroar") self.db.setPassword("gfg") # executing MySql query self.qry = QString("SELECT * FROM employee") self.query = QSqlQuery() self.query.prepare(self.qry) self.query.exec() # displaying output of query in the table for row_number, row_data in enumerate(self.query.result()): for column_number, data in enumerate(row_data): self.tableWidget.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(data) def retranslateUi(self, MainWindow): _translate = QtCore.QCoreApplication.translate MainWindow.setWindowTitle(_translate("MainWindow", "List of All Employee(GFGdb)")) item = self.tableWidget.horizontalHeaderItem(0) item.setText(_translate("MainWindow", "NAME")) item1 = self.tableWidget.horizontalHeaderItem(1) item1.setText(_translate("MainWindow", "SALARY")) if __name__ == "__main__": import sys app = QtWidgets.QApplication(sys.argv) MainWindow = QtWidgets.QMainWindow() ui = Ui_MainWindow() ui.setupUi(MainWindow) MainWindow.show() sys.exit(app.exec_()) |
Output:
