Step By Step Guide to Connect to Maria DB using Python

Data Analytics is an important step to help in objective decision-making at any organization. With the increased 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 tasks 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. The MariaDB connector will be used to connect a Python program to a database server in this tutorial. This module enables you to do database server queries from within your application. You’ll configure MariaDB for use in a Python environment and develop a Python script to connect to MariaDB and run queries.

Prerequisites

• You need to install Python in your system. This python guide can be referred for installation.
• Install MariaDB Server on your own computer. Step-by-step instructions can be found in our documentation’s Deployment Guide.
• The MariaDB Connector/Python, which is available through the Python Package Index, provides Python support. Use PIP to install:

$ pip3 install mariadb

What is 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. 

The official 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. 

Procedure to Implement Python MariaDB Integration

• Method 1: Python MariaDB Integration using MariaDB Connector
• Method 2: Python MariaDB Integration using Hevo

Method 1: Python MariaDB Integration using MariaDB Connector

• Step 1: Preparation and Installation
• Step 2: Connecting to MariaDB
• Step 3: Creating a Table
• Step 4: Inserting Data into Table
• Step 5: Retrieving Data

Step 1: Preparation and Installation

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. To begin, open your terminal and type the following command to launch the MariaDB shell:

$ sudo mariadb

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. 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. 

$ pip3 install mariadb-connector-python

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, to retrieve the cursor, use the connection’s cursor() method. The cursor gives you a way to communicate with the Server, such as through running SQL queries and managing transactions.

cursor = connection.cursor()

Now, 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:

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()

Auto-commit is enabled by default in MariaDB Python Connector. You can disable auto-commit by setting the auto-commit attribute on the connection to False if you want to manually manage your transactions, only committing when you’re ready.

# Disable Auto-Commit
conn.autocommit = False

After that, you may use the commit() and rollback() methods to commit and roll back transactions. When using the InnoDB Storage Engine, MariaDB Server allows you to conduct several concurrent transactions on the same table without locking it.

Step 5: Caching Exceptions

You should strive to trap errors for any SQL activities (querying, updating, deleting, or inserting records) so that you can verify that your actions are being conducted as planned and that you are aware of any issues as they arise. Use the Error class to catch errors:

try: 
    cursor.execute("some MariaDB query"))
except mariadb.Error as e: 
    print(f"Error: {e}")

MariaDB Server throws a SQL exception if the query in the try clause of the above code fails, which is caught in the except and displayed to stdout. When working with a database, this programming best practice for handling exceptions is very critical since you must protect the integrity of the data.

Step 6: 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 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. 

Complete Script

This article shows how simple it is to establish Python MariaDB Integration. The following is an example of a complete script:

#!/usr/bin/python 
import mariadb 
conn = mariadb.connect( 
    user="db_user", 
    password="db_user_passwd", 
    host="localhost", 
    database="employees"
) 

cur = conn.cursor() 

#retrieving information 
some_name = "Georgi" 
cur.execute("SELECT first_name,last_name FROM employees WHERE first_name=?", (some_name,))
for first_name, last_name in cur: 
    print(f"First name: {first_name}, Last name: {last_name}") 

#insert information
try: 
    cur.execute("INSERT INTO employees (first_name,last_name) VALUES (?, ?)", ("Maria","DB"))
except mariadb.Error as e: 
    print(f"Error: {e}") 
conn.commit() 
print(f"Last Inserted ID: {cur.lastrowid}") 
      
conn.close()

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.

Hevo focuses on three simple steps to get you started

• Connect: Connect Hevo with a data source by simply logging in with your credentials.
• Integrate: Consolidate your data from several sources in Hevo’s Google BigQuery-powered Managed Data Warehouse and automatically transform it into an analysis-ready form.
• Visualize: Connect Hevo with your desired BI tool and easily visualize your unified data to gain better insights.

Step 1: Configure your MariaDB Source

Perform the following steps to configure MariaDB as a Source in Hevo:

1. Click PIPELINES in the Navigation Bar.
2. Click + CREATE PIPELINE in the Pipelines List View.
3. In the Select Source Type page, select MariaDB.
4. In the Configure your MariaDB Source page, specify the following:

1. Click TEST CONNECTION. This button is enabled once you specify all the mandatory fields. Hevo’s underlying connectivity checker validates the connection settings you provide.
2. Click TEST & CONTINUE to proceed for setting up the Destination. This button is enabled once you specify all the mandatory fields.

Step 2: Configure your Destination

Choose from a variety of destinations available on Hevo’s product and configure the details.

With these two simple but easy steps you have successfully configured your pipeline and are ready to transfer data from source to destination.

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.

Visit our Website to Explore Hevo

FAQ on Python MariaDB

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of learning about Python MariaDB Integration! Let us know in the comments section below!