In this PyQt5 Tutorial we are going to learn how to Retrieve Data from MySQL in QTableWidget, for the database connection we want to use mysql connector and for GUI design we are going to use QT Designer. before this we have learned how you can create mysql database in pyqt5 and how you can insert data in mysql database using pyqt5.
What is MySQL Database ?
MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and manipulating data. MySQL is one of the most popular databases in the world, and it is widely used by web developers, businesses and individuals to store and manage data.
MySQL was originally developed by MySQL AB, it was Swedish company, but it is now owned and maintained by Oracle Corporation. It is compatible with different operating systems including Linux, Windows and macOS, and it is often used in conjunction with popular web programming languages such as PHP, Python and Ruby.
MySQL is known for its scalability, reliability and flexibility, and it can handle large amounts of data. It supports many advanced features, such as transactional processing, stored procedures, triggers, and views. MySQL also has a strong security model that allows administrators to control access to data and protect against unauthorized access.
As i have already said we want to use MySQL Connector, you need to install this library.
Installation
You can install mysql connector using pip .
|
1 |
pip install mysql-connector-python |
Make sure that you have already installed Wamp Server, and you have the database and table in your Wamp Server, you need to read the two previous articles that i have already added the links at the top.
Open your Qt Designer, you can just write pyqt5designer in your terminal, after opening the Qt Designer you need to create Widget window. now we add widgets in Qt Designer.
- Add a QHBoxLayout in QHBoxLayout add a QLabel and QLineEdit
- Add another QHBoxLayout, you need to also add a Qlabel and QLineEdit in this layout
- Add a QVBoxLayout, in this layout add a QTableWidget with a QPushButton
- In the QTableWidget select number of rows and columns, in our case it is 8 rows and 3 columns
- At the end click on the main window and select layout vertically for all widgets
This is the design that we want
After completing the design you need to save the your design, the extension will be .ui file. now it is time to convert our .ui file in to .py file. there are two ways that you can do, first way is loading the ui file, the second way is converting the .ui file to .py file, we want to use the second way. for converting of the ui file to python file we need to use pyuic5 module, this module is located in the Scripts folder of your python installation, you need to copy your ui file in the Scripts folder of your Python installation. after that open the terminal in the Scripts folder, and run this command.
|
1 |
pyuic5 selectd.ui -o selectd.py -x |
And this is the converted file, also we have added a method for retrieving the data from mysql database in qtablewidget.
|
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 |
# -*- coding: utf-8 -*- # Form implementation generated from reading ui file 'selectd.ui' # # Created by: PyQt5 UI code generator 5.15.1 # # WARNING: Any manual changes made to this file will be lost when pyuic5 is # run again. Do not edit this file unless you know what you are doing. from PyQt5 import QtCore, QtGui, QtWidgets import mysql.connector as mc from PyQt5.QtWidgets import QTableWidgetItem class Ui_Form(object): def setupUi(self, Form): Form.setObjectName("Form") Form.resize(482, 451) self.verticalLayout_2 = QtWidgets.QVBoxLayout(Form) self.verticalLayout_2.setObjectName("verticalLayout_2") self.horizontalLayout = QtWidgets.QHBoxLayout() self.horizontalLayout.setObjectName("horizontalLayout") self.label = QtWidgets.QLabel(Form) self.label.setObjectName("label") self.horizontalLayout.addWidget(self.label) self.lineEditDb = QtWidgets.QLineEdit(Form) self.lineEditDb.setObjectName("lineEditDb") self.horizontalLayout.addWidget(self.lineEditDb) self.verticalLayout_2.addLayout(self.horizontalLayout) self.horizontalLayout_2 = QtWidgets.QHBoxLayout() self.horizontalLayout_2.setObjectName("horizontalLayout_2") self.label_2 = QtWidgets.QLabel(Form) self.label_2.setObjectName("label_2") self.horizontalLayout_2.addWidget(self.label_2) self.lineEditTable = QtWidgets.QLineEdit(Form) self.lineEditTable.setObjectName("lineEditTable") self.horizontalLayout_2.addWidget(self.lineEditTable) self.verticalLayout_2.addLayout(self.horizontalLayout_2) self.verticalLayout = QtWidgets.QVBoxLayout() self.verticalLayout.setObjectName("verticalLayout") self.tableWidget = QtWidgets.QTableWidget(Form) sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Expanding) sizePolicy.setHorizontalStretch(0) sizePolicy.setVerticalStretch(0) sizePolicy.setHeightForWidth(self.tableWidget.sizePolicy().hasHeightForWidth()) self.tableWidget.setSizePolicy(sizePolicy) self.tableWidget.setMinimumSize(QtCore.QSize(0, 0)) self.tableWidget.setRowCount(8) self.tableWidget.setColumnCount(3) self.tableWidget.setObjectName("tableWidget") self.verticalLayout.addWidget(self.tableWidget) self.pushButton = QtWidgets.QPushButton(Form) self.pushButton.setObjectName("pushButton") #we have connected clicked signal of button with the selec_data method self.pushButton.clicked.connect(self.select_data) self.verticalLayout.addWidget(self.pushButton) self.verticalLayout_2.addLayout(self.verticalLayout) self.retranslateUi(Form) QtCore.QMetaObject.connectSlotsByName(Form) #this is the method for selecting data def select_data(self): try: dbname = self.lineEditDb.text() tablename = self.lineEditTable.text() mydb = mc.connect( host="localhost", user="root", password="", database=dbname ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM {} ".format(tablename)) result = mycursor.fetchall() self.tableWidget.setRowCount(0) for row_number, row_data in enumerate(result): print(row_number) self.tableWidget.insertRow(row_number) for column_number, data in enumerate(row_data): #print(column_number) self.tableWidget.setItem(row_number, column_number, QTableWidgetItem(str(data))) except mc.Error as e: print("Error") def retranslateUi(self, Form): _translate = QtCore.QCoreApplication.translate Form.setWindowTitle(_translate("Form", "Form")) self.label.setText(_translate("Form", "Enter Db Name:")) self.label_2.setText(_translate("Form", "Enter Table Name:")) self.pushButton.setText(_translate("Form", "Show Data")) if __name__ == "__main__": import sys app = QtWidgets.QApplication(sys.argv) Form = QtWidgets.QWidget() ui = Ui_Form() ui.setupUi(Form) Form.show() sys.exit(app.exec_()) |
This is the method for selecting data from Mysql database in the QTableWidget, first we have connected our application with the Wamp Server using MySQL Connector, now we need to get the database with tablename from the QLineEdit, you can use text() method from getting the input from the QLineEdit, after that we need to create the object
of cursor, at the end we need to execute the query for selecting the data from the database table.
|
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 |
def select_data(self): try: dbname = self.lineEditDb.text() tablename = self.lineEditTable.text() mydb = mc.connect( host="localhost", user="root", password="", database=dbname ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM {} ".format(tablename)) result = mycursor.fetchall() self.tableWidget.setRowCount(0) for row_number, row_data in enumerate(result): print(row_number) self.tableWidget.insertRow(row_number) for column_number, data in enumerate(row_data): #print(column_number) self.tableWidget.setItem(row_number, column_number, QTableWidgetItem(str(data))) except mc.Error as e: print("Error") |
Also we have connected our QPushButton clicked signal with this method.
|
1 |
self.pushButton.clicked.connect(self.select_data) |
Run the complete code give database and table name and this will be the result.
Subscribe and Get Free Video Courses & Articles in your Email
hi
I need to do a search in fields QTableWidget
thank you
I need to return items of my tablewidget to do a delete data from data base