In this Python PyQt5 Tutorial we are going to learn about creating Python PyQt5 MySQL Database, in this lesson we want to learn how you can create mysql database with Python PyQt5 GUI Application. for this purpose we are going to use a library that is called mysql connector, so it is MySQL driver written in Python which does not depend on MySQL C client libraries and implements the DB API v2.0 specification (PEP-249).
You can simply use pip for the installation.
|
1 |
pip install mysql-connector-python |
Also you need to download and install Wamp Server, because we want to use Wamp Server as virtual server for the 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 HBoxLayout and in the HBoxLayout add a label and a lineedit
- Add another HBoxLayout, and in the HBoxLayout add two buttons
- Also you need to add a label and at the end make the window layout vertically
- You need to add a vertical spacer between your lineedit and buttons
This is the design.
After completing the design you need to save the .ui file, iam going to name it database.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 database.ui -o database.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 110 111 112 113 114 115 116 117 118 119 120 |
# -*- coding: utf-8 -*- # Form implementation generated from reading # ui file 'database.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 class Ui_Form(object): def setupUi(self, Form): Form.setObjectName("Form") Form.resize(488, 187) self.verticalLayout = QtWidgets.QVBoxLayout(Form) self.verticalLayout.setObjectName("verticalLayout") 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.addLayout(self.horizontalLayout) spacerItem = QtWidgets.QSpacerItem(20, 40, QtWidgets.QSizePolicy.Minimum, QtWidgets.QSizePolicy.Expanding) self.verticalLayout.addItem(spacerItem) self.horizontalLayout_2 = QtWidgets.QHBoxLayout() self.horizontalLayout_2.setObjectName("horizontalLayout_2") self.pushButtonCreateDb = QtWidgets.QPushButton(Form) self.pushButtonCreateDb.setObjectName("pushButtonCreateDb") self.pushButtonCreateDb.clicked.connect(self.create_database) self.horizontalLayout_2.addWidget(self.pushButtonCreateDb) self.pushButtondbCon = QtWidgets.QPushButton(Form) self.pushButtondbCon.setObjectName("pushButtondbCon") #we have connected the push self.pushButtondbCon.clicked.connect(self.db_connect) self.horizontalLayout_2.addWidget(self.pushButtondbCon) self.verticalLayout.addLayout(self.horizontalLayout_2) self.label_2 = QtWidgets.QLabel(Form) font = QtGui.QFont() font.setPointSize(14) font.setBold(True) font.setWeight(75) self.label_2.setFont(font) self.label_2.setText("") self.label_2.setObjectName("label_2") self.verticalLayout.addWidget(self.label_2) self.retranslateUi(Form) QtCore.QMetaObject.connectSlotsByName(Form) def create_database(self): try: mydb = mc.connect( host="localhost", user="root", password="" ) cursor = mydb.cursor() dbname = self.lineEditDbName.text() cursor.execute("CREATE DATABASE {}".format(dbname)) self.label_2.setText("Database {} created ".format(dbname)) except mc.Error as e: self.label_2.setText("Database creation failed") def db_connect(self): try: mydb = mc.connect( host="localhost", user="root", password="", database="pyqt5" ) self.label_2.setText("There is a connection") except mc.Error as e: self.label_2.setText("Error in connection") def retranslateUi(self, Form): _translate = QtCore.QCoreApplication.translate Form.setWindowTitle(_translate("Form", "Form")) self.label.setText(_translate("Form", "Database Name:")) self.pushButtonCreateDb.setText(_translate("Form", "Create Database")) self.pushButtondbCon.setText(_translate("Form", "Database Connection ")) 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 two new methods in the file, the first method is for creating the database in our Wamp Server. we have just connected our application with the wamp server using mysql.connector library that we have already installed and we execute query for creating table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
def create_database(self): try: mydb = mc.connect( host="localhost", user="root", password="" ) cursor = mydb.cursor() dbname = self.lineEditDbName.text() cursor.execute("CREATE DATABASE {}".format(dbname)) self.label_2.setText("Database {} created ".format(dbname)) except mc.Error as e: self.label_2.setText("Database creation failed") |
The second method is for checking the database connection.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
def db_connect(self): try: mydb = mc.connect( host="localhost", user="root", password="", database="pyqt5" ) self.label_2.setText("There is a connection") except mc.Error as e: self.label_2.setText("Error in connection") |
Also we have connected these methods with the clicked signal of QPushButton.
|
1 2 |
self.pushButtonCreateDb.clicked.connect(self.create_database) self.pushButtondbCon.clicked.connect(self.db_connect) |
So now run the code, give the name of the database and click on the create database.
And if you check your wamp server, now we have a database of pyqt5 in their.
