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("geeksforgeeks")
self.db.setDatabaseName("gfgdb")
self.db.setUserName("geeks")
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:
Python3
# 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("geeksforgeeks")
self.db.setDatabaseName("gfgdb")
self.db.setUserName("geeks")
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_())
Similar Reads
PyQt5 QListWidget | Python
In PyQt5, QListWidget is a convenience class that provides a list view with a classic item-based interface for adding and removing items. QListWidget uses an internal model to manage each QListWidgetItem in the list. Syntax: listWidget = QListWidget() There are two ways to add items to the list. Th
1 min read
Python GUI - PyQt VS TKinter
A GUI toolkit contains widgets that are used to create a graphical interface. Python includes a wide range of Interface implementations available, from TkInter (it comes with Python, ) to a variety of various cross-platform solutions, such as PyQt5, which is known for its more sophisticated widgets
5 min read
Python | Introduction to PyQt5
There are so many options provided by Python to develop GUI application and PyQt5 is one of them. PyQt5 is cross-platform GUI toolkit, a set of python bindings for Qt v5. One can develop an interactive desktop application with so much ease because of the tools and simplicity provided by this library
3 min read
Python MySQL - Select Query
Python Database API ( Application Program Interface ) is the Database interface for the standard Python. This standard is adhered to by most Python Database interfaces. There are various Database servers supported by Python Database such as MySQL, GadFly, mySQL, PostgreSQL, Microsoft SQL Server 2000
2 min read
Python MySQL
Python MySQL Connector is a Python driver that helps to integrate Python and MySQL. This Python MySQL library allows the conversion between Python and MySQL data types. MySQL Connector API is implemented using pure Python and does not require any third-party library. This Python MySQL tutorial will
9 min read
PyQt5 - QAction
QAction : In PyQt5 applications many common commands can be invoked via menus, toolbar buttons, and keyboard shortcuts, since the user expects each command to be performed in the same way, regardless of the user interface used, QAction is useful to represent each command as an action. Actions can be
3 min read
PyQt5 QPushButton
QPushButton is a simple button in PyQt, when clicked by a user some associated action gets performed. For adding this button into the application, QPushButton class is used. Example: A window having a Push Button, when clicked a message will appear "You clicked Push Button". Below is the code: Pytho
1 min read
PyQt5 - QApplication
The QApplication class manages the GUI application's control flow and main settings. It specializes in the QGuiApplication with some functionality needed for QWidget based applications. It handles widget specific initialization, finalization. For any GUI application using Qt, there is precisely one
3 min read
PyQt5 Input Dialog | Python
PyQt5 provides a class named QInputDialog which is used to take input from the user. In most of the application, there comes a situation where some data is required to be entered by the user and hence input dialog is needed. Input can be of type String or Text, Integer, Double and item.Used methods:
2 min read
PyQt5 - QDial
QDial is a class in PyQt5 which provide user to select the value within a program-definable range, and the range either wraps around (for example, with angles measured from 0 to 359 degrees). Also, it can be used to show the current value in a similar way of speedometer. Below is how the QDial looks
2 min read