In this Python PyQt5 lesson we want to learn about Python PyQt5 MySQL Database Select Data, so we have already learned that how you can insert data in to mysql database.
Now 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 an HBoxLayout in QHBoxLayout add a label and lineedit
- Add another HBoxLayout, you need to also add a label and lineedit in this layout
- Add a VBoxLayout, 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 our design.
After completing the design you need to save the .ui file, iam going to name it select.ui, now copy the file and paste it in the Scripts folder of your Python installation, because we want to convert our ui file in to python file and for converting you need to use pyuic5 module. pyuic5 module is located in the Scripts folder of your Python installation, run this command for converting in your terminal.
|
1 |
pyuic5 select.ui -o select.py -x |
So this is the converted file, also we have brought some changes to this file.
|
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 |
# -*- coding: utf-8 -*- # Form implementation generated from # reading ui file 'select.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(552, 499) 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.lineEditDbName = QtWidgets.QLineEdit(Form) self.lineEditDbName.setObjectName("lineEditDbName") self.horizontalLayout.addWidget(self.lineEditDbName) 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.lineEditTableName = QtWidgets.QLineEdit(Form) self.lineEditTableName.setObjectName("lineEditTableName") self.horizontalLayout_2.addWidget(self.lineEditTableName) self.verticalLayout_2.addLayout(self.horizontalLayout_2) self.verticalLayout = QtWidgets.QVBoxLayout() self.verticalLayout.setObjectName("verticalLayout") self.tableWidget = QtWidgets.QTableWidget(Form) 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") #connecting button clicked signal with the 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) def select_data(self): try: dbname = self.lineEditDbName.text() tablename =self.lineEditTableName.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): self.tableWidget.insertRow(row_number) for column_number, data in enumerate(row_data): self.tableWidget.setItem(row_number, column_number, QTableWidgetItem(str(data))) except mc.Error as e: print("Error occured") 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_()) |
We have added one 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 lineedit, after that we need to create the object of cursor, also we need to get the value from the QLineEdit, at the end we need to execute the query.
|
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 |
def select_data(self): try: dbname = self.lineEditDbName.text() tablename =self.lineEditTableName.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): self.tableWidget.insertRow(row_number) for column_number, data in enumerate(row_data): self.tableWidget.setItem(row_number, column_number, QTableWidgetItem(str(data))) except mc.Error as e: print("Error occured") |
Also we have connected our QPushButton clicked signal with this method.
|
1 |
self.pushButton.clicked.connect(self.select_data) |
Run the complete code give the database and tablename, after that click on the button.
