In this PyQt6 lesson we are going to learn about PyQt6 MySQL Database Programming, we will learn that how you can create database using GUI designer, how you can insert and retrieve data and how you can create a simple login in PyQt6 with MySQL database.

First we need to install MySQL Connector, 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).

After that you need to download and install WAMP Server, because we want to use wamp server as virtual server for mysql.

Creating MySQL Database

First we want to create our database using a GUI design, so open your Qt Designer using pyqt5designer.

• Add QLabel and QLineEdit make the layout horizontally 
• Add two QPushButton make the layout horizontally 
• Add QLabel below the QPushButton
• Make the main window layout vertically 
• Add horizontal spacer between buttons and lineedit

Now we want to convert our UI file to Py file.

This is the converted file and we have added some codes for creating the database and database connection.

In the above code this is our method for creating the database in the WAMP Server, we can use connect() method from MySQL connection, you need to add the host, username and password as parameter, after that  create the object of cursor(), also we need to get the value or database name from the QLineEdit, at the end just execute your query for creating the database.

Run the code give the database name and you have your database in WAMP server.

Using this code you can check database connection.

Click on the Database Connection button and this will be the result, if you change the database name you will get error.

Inserting Data to MySQL Database

Now we want to insert some data in to our database, first of all you need to create a table in your pyqt6 database, iam going to call it users, after that we design our GUI application for inserting data, open Qt Designer.

• Add two QLabel and two QLineEdit, make the layout horizontally 
• Add QPushButton and QLabel.
• Make the main window layout vertically
• Add horizontally spacer between QLineEdit and QPushButton

Now convert your UI file to PY file.

This is our converted file.

And this is the method for inserting the data, we have connected this method with the clicked signal of the QPushButton, this is the change that we have brought to our file, first we have created our database connection code and after that we have got the value from the QLineEdits, at the end we have executed our query for inserting the data.

Run the code and insert the data this will be the result.

Check your Wamp Server and we have the data.

Retrieving Data from MySQL in QTableWidget 

Now we want to retrieve data from MySQL database in to QTableWidget, open your Qt Designer.

• Add two QLabel and two QLineEdit, make the layout horizontally
• Add QTableWidget,add column row and column count
• Add QPushButton
• Make the main window layout vertically

Now convert your UI file to PY file.

This is the converted file, also we have added a new method in here for showing the data in the QTableWidget, we have connected the method with the clicked signal of QPushButton.

Run the code, give the database and table name, this will be the result.

Creating Simple Login in PyQt6 with MySQL

Now we want to create a simple login with MySQL database in PyQt6, 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 QLabel with a QPushButton
• At the end click on the main window and select layout vertically for all widgets
• Also you need to add a vertical spacer between the lineedits and button.

After completing the design you need to save the .ui file, iam going to name it Login.ui, now convert the file to py.

This is the converted file, we have added one method in our file and that is for login, first we want to get the email and password from the user and after we compare that with the registered email and password of our database. in the successful login we want to open the second dialog.

Run the complete code give the correct email and password you will see the second dialog.