Connecting to MariaDB with PyMySQL

In this article, we will discuss the MariaDB connection using the Python language. When we create a system, we need a database for maintaining records of these systems for backup or future use. If you want to learn how to build database connections in Python then you are at the right place. Here, we are using MariaDB connections for explaining the DB connection in Python applications. We will explain every point in detail for your better understanding. We divide the explanation process into different sections with appropriate examples and screenshots. We simply need to install the MariaDB setup on our system and then use the MariaDB library to connect it to our Python application.

How to Install and Launch Maria DB?

Here, we will discuss MariaDB installation on a Windows system. All of these steps must be completed to install MariaDB as an Apache XAMPP server on our Windows system.

1. Open the browser and download the package installer from the official MariaDB website. Run (https://mariadb.org/download/) in google chrome.
2. Click on the download button then MariaDB wizard file downloading starts as seen in the screenshot attached below.

3. Now, double-click on the downloaded file to start the installation.
4. Choose the MariaDB version you wish to implement then click the next button.
5. Review and accept the license by clicking on the checkbox then press the next button.
6. Select the installation path, click the next button
7. Select the configuration type and click the next button
8. Set the database name and password, press the next button
9. Review all settings and then press the install button for installing the database in our system.
10. Wait for the installation and then press the finish button.

Open the command line prompt then type ‘mysql’ to see the version of MySQL that is running in your system.

OR install the XAMPP server on your Windows system and use Google Chrome to access this local server.

What is PyMYSQL?

PYMYSQL is a database connector library in python language that connects python applications with the MYSQL database. By using the PYMYSQL library in python, we can easily perform our database operations like creating, updating, reading, and deleting our records in the MYSQL database using Python language. Without the PYMYSQL library, we cannot connect with the MySQL database.

How to Install PYMYSQL in Python?

We will show how to quickly install the MySQL library in our Python application in this section.

1. Open the PyCharm tool in your system and create a project.

2. Click on the create button as indicated in the screenshot below.
3. Create a new python file in this project and rename the file accordingly.

4. Press Enter then we fill in our project successfully.

5. Import the ‘pymysql’ library in the python application. If the pymysql library is not installed in your system, then firstly install the pymysql in the system and then import the ‘pymysql’ library in the python application. You can do this with the following command:

6. When we run the preceding command in our project terminal, the pymysql library is successfully installed.
7. Now, we will type a python code in the file and then connect the database to your project.

As mentioned in the above python code, we took different variables for setting hostname, username, and password. Here, we took three variables like ‘Host_1’, ‘User_1’, and ‘pass_1’ in our python file. In the variable ‘Host_1’, we defined the hostname as ‘localhost’ because we run our python application locally. In variable ‘User_1’, we defined username as root that was defined during installation. In variable ‘pass_1’, we defined a password that is null because we cannot put a password for this database.

The function whose name is connected can be seen. We call this function to connect from the pymysql library and then assign this function to a variable named ‘connection’. We push these variables like hostname, username, and password in this connect function for our MYSQL server connection.

After that, we call another function like a cursor here in our code. We can assign this cursor function to variable names as ‘curObj’. In PyMySQl, we used the connection.cursor() method that is used to create a cursor object through which SQL commands are executed on the connected database. We can handle commands like SELECT, INSERT, UPDATE, and DELETE by using this cursor object named ‘curObj’.

We can easily retrieve the results of all these commands in our MYSQL database by using the cursor method. In the line below, we run the execute method here for running the database. As above we established connections and now, we run the database and all tables of that database. The information of the database is stored in the variable ‘curObj’. We fetched all the databases that are stored in our local MariaDB by using the method fetchall() and assigned this result to the variable ‘dbList’. Finally, we show all the databases by applying For loop and print all these databases in the console. After that, we run the close method-like connection.close() for closing the database connection.

The output from the above code is given below:

The output shows that we established a database connection with our python application successfully. The For loop shows the database name that is enclosed in brackets and semicolons. Make sure that your database MariaDB server is working properly and the system must satisfy the requirements for running the MariaDB server in our system.

Conclusion

PyMySQL is a library of python through which we can easily connect the MariaDB server to our python projects to easily handle their database records. We also used various techniques for different purposes. Using the Python library, we built a connection in a python file and ran this file to handle crud operations for our python application. In the end, the output shows that we established a database connection successfully with the help of MyPySQL in the PyCharm Tool.