Setting up Python MariaDB Integration: 2 Easy Methods

on Data Analytics, Data Integration, Tutorials • May 7th, 2021 • Write for Hevo

Data Analytics is an important step to help in objective decision making at any organisation. With the increase generation and storage of complex data, conventional methods to interact with data such as simple queries using SQL are proving to be inadequate for the current scenario. Thus companies are looking forward to using languages like Python to perform complex task during this Data Analytics process to analyze their MariaDB Data.

In this article you will be introduced to MariaDB, learn about the need for Python MariaDB Integration, and two easy methods to implement Python MariaDB Integration.

Table of Contents

Introduction to MariaDB

MariaDB logo - Python MariaDB

MariaDB is a popular database management system among individuals and companies. It is used for the storage and manipulation of data. MariaDB is an SQL database, hence, you can use SQL queries to store and access data from it. SQL is also a common tool for extracting insights from data stored in MariaDB. 

offical documentation for MariaDB can be found here.

Understanding the Need for Python MariaDB Integration

However, SQL alone is not adequate for performing data analytics tasks. Individuals and companies will always need to use advanced data analytics tools to analyze their MariaDB data. The insights extracted from the data are good for decision making. Python is a good example of such a tool. There are many data analytics libraries developed specifically for Python. Hence,  learning how to integrate Python and MariaDB will help you know how to pull data from MariaDB into Python for analytics. In this article, we will be discussing how to integrate Python and MariaDB. 

2 Ways to Set up Python MariaDB Integration

You can use any of the following methods to implement your Python MariaDB Integration according to your need:

Method 1: Python MariaDB Integration using MariaDB Connector

In this method, you will be manually required to implement Python MariaDB Integration using MariaDB connector for Python and then set it up using a command-line interface.

Method 2: Python MariaDB Integration using Hevo

Hevo is a No-code Data Pipeline. It will automatically load your Firebase data into Google Data Studio without writing any line of code. 

Let’s discuss each of them in detail.

Pre-Requisites

This is what you need for this article:

  • Python 3.X
  • MariaDB

Procedure to Implement Python MariaDB Integration

Method 1: Python MariaDB Integration using MariaDB Connector

Step 1: Preparation and Installation

I am assuming that you have installed both Python and MariaDB on your computer. If not, you can download the two from their official websites and follow the given instructions to install them on your computer. 

You also need an active MariaDB database to play around with in this article. If you don’t have one, launch the MariaDB terminal on your computer and enter your password to login. 

Next, let us create a database named companydb. Run the following command on the MariaDB terminal:

create database companydb;

To work on the database, you must select it. Run the following command:

use companydb

Your MariaDB terminal should change to show that you’ve selected the companydb database as shown below:

terminal command - Python MariaDB

It’s now time to connect to the database from Python. 

MariaDB provides a Python connector called MariaDB that you can use to establish a connection from Python to your MariaDB database. 

We can install this connector using the pip package manager. Pip comes in-built in Python. Open the terminal of your operating system and run the following command to install the MariaDB connector:

pip install mariadb

The command should install the MariaDB connector successfully. 

Step 2: Connecting to MariaDB

To establish a connection to MariaDB from Python, you should first import the MariaDB connector. This should be done using Python’s import statement. 

Next, you should call the connect() function to establish a connection to your MariaDB database of choice. This function expects you to pass a number of arguments to it specifying the client details like your MariaDB username, hostname, and password. 

After the connection, you’ll be able to execute SQL queries against the selected MariaDB database. 

Create a new Python file and give it the name mariadbconn.py. Add the following code to the file to connect to the companydb:

import sys

import mariadb


try: con = mariadb.connect( 

    user="root", 

    password="", 

    host="localhost", 

    port=3306, 

    database="companydb" 

)

except mariadb.Error as ex: 

    print(f"An error occurred while connecting to MariaDB: {ex}") 

    sys.exit(1) 


# Get Cursor 

cur = con.cursor()

In the above code, we imported two libraries, that is, sys and mariadb. The code for connecting to the database has been surrounded by try and except statements so as to throw any exceptions that may occur during the connection process. 

Next, we will be running an SQL query to create a table in the database. 

Step 3: Creating a Table

We will use the execute() function for this as shown in the following code:

cur.execute("CREATE TABLE employees (name VARCHAR(30), age INTEGER)");

If you go to the database level, you will find that the new table has been created:

database sample- Python MariaDB

Step 4: Inserting Data into Table

Now that the table has been created, let us go ahead and insert some data into it. This is possible by combining the execute() function with the SQL’s INSERT statement as shown below:

cur.execute("INSERT INTO employees (name, age) VALUES ('Joel M', 27), ('Mercy K', 33),('Bosco C', 45)")

con.commit()

The code should add three rows into the table. The commit() function helps us commit the changes made to the database and they cannot be undone. 

Step 5: Retrieving Data

Our table now has 3 records. Data retrieval is an important part of the data analytics process. We can retrieve data from the table using Python. The following code demonstrates this:

cur.execute("SELECT name, age FROM employees")

Note that we have used the SQL’s SELECT statement to retrieve data from the table. The query results will be stored in a list in our cursor object, cur. We can use a for loop to iterate over the cursor and get the data. This is shown below:

for (name, age) in cur:
    print("Name:", {name}, "Age:", {age})

The code should return the following result:

code result - Python MariaDB

The table data was retrieved successfully. Every data in the table was passed from the cursor as a tuple with columns in the statement. 

We are done with the database, but there is still an open connection. Open and unused connections waste resources. We can call the close() function to close the connection as shown below:

con.close()

The connection should be closed successfully. 

Limitations of using MariaDB Connector for Python MariaDB Integration

The following are the challenges of Python MariaDB integration:

  1. The process is lengthy and complex. One takes a long time to establish a connection and perform tasks like inserting and querying data from the database. 
  2. The integration process is very technical and requires coding knowledge. Hence, it is not a suitable approach for non-programmers. 
  3. It is not possible to pull data from MariaDB into Python using the MariaDB connector in real-time. 

Method 2: Python MariaDB Integration using Hevo

Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 100+ data sources. Hevo offers a fully managed solution for your data migration process to MariaDB Server. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data at MariaDB Server.

Let’s look at some salient features of Hevo:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support call.

Explore more about Hevo by signing up for a 14-day free trial today

Conclusion:

In this article learned the procedure to integrate Python with MariaDB and the steps to perform tasks like creating tables, inserting, and retrieving data from MariaDB using Python. 

Integrating and analyzing data from a huge set of diverse sources can be challenging, this is where Hevo comes into the picture. Hevo Data, a No-code Data Pipeline helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo with its strong integration with 100+ sources & BI tools, allows you to not only export & load Data but also transform & enrich your Data & make it analysis-ready in a jiffy.

Get started with Hevo today! Sign up here for a 14-day free trial

No-code Data Pipeline for MariaDB