In this PyQt5 Tutorial we want to learn How to Create MySQL Database in PyQt5, for GUI design we want to use pyqt5 and for MySQL database connection we use mysql connector, Mysql Connector 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).
Also if you are interested in Python GUI Development with different libraries, you can check the below links.
- PyQt5 GUI Development Tutorials
- Pyside2 GUI Development Tutorials
- wxPython GUI Development Tutorials
- TKinter GUI Development Tutorials
Installation
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.
OK now it is time to design our GUI application in PyQt5 using Qt Designer, you need to open your Qt Designer by writing pyqt5designer in your terminal, after that you need to choose Widget window from the template, there are different templates but we want to use Widget template. and start designing your GUI application.
- Add QHBoxLayout and in the HBoxLayout add a QLabel and a QLineEdit
- Add another QHBoxLayout, and in the HBoxLayout add two QPushButton
- Also you need to add a QLabel and at the end make the window layout vertically
- You need to add a vertical spacer between your QLineEdit and buttons
This will be your design at the end.
After completing the design you need to save the your design, the extension will .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. you need to open the terminal in the Scripts folder, and after that run this command.
|
1 |
pyuic5 mydatabase.ui -o mydatabase.py -x |
And this is the converted file, also we have added two method for creating database and connecting database.
|
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 # 'mydatabase.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(472, 292) 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.lineEdit = QtWidgets.QLineEdit(Form) self.lineEdit.setObjectName("lineEdit") self.horizontalLayout.addWidget(self.lineEdit) 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") #connected clicked signal with create_database() method self.pushButtonCreateDb.clicked.connect(self.create_database) self.horizontalLayout_2.addWidget(self.pushButtonCreateDb) self.pushButtonDbcon = QtWidgets.QPushButton(Form) self.pushButtonDbcon.setObjectName("pushButtonDbcon") # connected clicked signal with db_connect() method self.pushButtonDbcon.clicked.connect(self.db_connect) self.horizontalLayout_2.addWidget(self.pushButtonDbcon) self.verticalLayout.addLayout(self.horizontalLayout_2) self.labelResult = QtWidgets.QLabel(Form) font = QtGui.QFont() font.setPointSize(14) font.setBold(True) font.setWeight(75) self.labelResult.setFont(font) self.labelResult.setObjectName("labelResult") self.verticalLayout.addWidget(self.labelResult) self.retranslateUi(Form) QtCore.QMetaObject.connectSlotsByName(Form) #method for connecting to wamp server mysql database def create_database(self): try: mydb = mc.connect( host="localhost", user="root", password="" ) cursor = mydb.cursor() dbname = self.lineEdit.text() cursor.execute("CREATE DATABASE {} ".format(dbname)) self.labelResult.setText("Database {} Created ".format(dbname)) except mc.Error as e: self.labelResult.setText("Database creation failed ") #method for checking the connection def db_connect(self): try: mydb = mc.connect( host="localhost", user="root", password="", database="mydb" ) self.labelResult.setText("There is connection") except mc.Error as err: self.labelResult.setText("Error In Connection ") def retranslateUi(self, Form): _translate = QtCore.QCoreApplication.translate Form.setWindowTitle(_translate("Form", "Form")) self.label.setText(_translate("Form", "Enter Database Name:")) self.pushButtonCreateDb.setText(_translate("Form", "Database Creation")) self.pushButtonDbcon.setText(_translate("Form", "Database Connection")) self.labelResult.setText(_translate("Form", "TextLabel")) 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 creating the mysql database in Wamp Server. we have just our mysql connector in here, first you need to connect your application with your wamp server local host and after that execute your query, also you need to get the database name from the QLineEdit using text() method.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
def create_database(self): try: mydb = mc.connect( host="localhost", user="root", password="" ) cursor = mydb.cursor() dbname = self.lineEdit.text() cursor.execute("CREATE DATABASE {} ".format(dbname)) self.labelResult.setText("Database {} Created ".format(dbname)) except mc.Error as e: self.labelResult.setText("Database creation failed ") |
And this method is for checking database connection.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
def db_connect(self): try: mydb = mc.connect( host="localhost", user="root", password="", database="mydb" ) self.labelResult.setText("There is connection") except mc.Error as err: self.labelResult.setText("Error In Connection ") |
Run the complete code and give the database name, click on the create database button, this will be the result.
Subscribe and Get Free Video Courses & Articles in your Email