Connecting to MariaDB Database From Python Using MariaDB Connector for Python

In this Instructable, you will learn how to connect Python to a locally running MariaDB server on a Windows 11 system and perform basic CRUD (Create, Read, Update, and Delete) operations.

The MariaDB Connector/Python library will be used to communicate with the database.

This MariaDB Python Instructable is designed for beginners who want to install the opensource database on their computers for logging data from sensors, host web applications etc.

We also have a Youtube Video

1. Explaining the entire process of connecting the MariaDB Database with Python

Below👇

Even though this specific instructable covers the Windows 11 system, principles discussed here and the MariaDB Connector/Python library are cross platform and can be used for installing and programming MariDB on embedded systems like Raspberry PI or other Linux Servers.

Here, we cover essential database setup tasks, including installing MariaDB, creating users, assigning databases to users, and creating a database.

After which, you will learn how to connect to the local MariaDB database using MariaDB Connector/Python. You will create tables, insert data, and read and update records in the database.

A Python virtual environment (venv) will be used to install the connector locally on Windows 11.

Source Codes can be downloaded from here

1. Connecting and Programming MariaDB Database using Python (MariaDB Connector/Python )

What is MariaDB Database

MariaDB is an open-source relational database management system designed to store, manage, and retrieve data efficiently using structured query language (SQL). It was created by the original developers of MySQL as a community-driven alternative after MySQL was acquired by Oracle.

1. MariaDB Official site

MariaDB is highly compatible with MySQL, meaning most MySQL applications can run on MariaDB with little or no modification. It is known for its performance, stability, enhanced storage engines, and commitment to remaining fully open source, making it a popular choice for web applications, enterprise systems etc.

MariaDB Database vs MySQL Database

MariaDB and MySQL are closely related relational database management systems, with MariaDB being a community-driven fork of MySQL created by the original MySQL developers after Oracle acquired MySQL.

While both databases share a common codebase and are largely compatible, they have diverged over time in terms of features, performance, and development philosophy.

MariaDB remains fully open source and is often preferred for its faster performance, additional storage engines, and more transparent development process, whereas MySQL is owned by Oracle

You can go to the official MariaDB website and download the MariaDB database installer for Windows 11.

The MariaDB is available in two versions

1. A Rolling Release Version
2. A Long Term Support Version

For latest features choose the Rolling Release Version and for stable dependent operation for your project or company choose the LTS version.

History of MariaDB Releases (Rolling & LTS)

Once the .msi installer is downloaded you can install it by clicking it and running it.

Provide a Good strong root password during installation.

Also note the port number. We need to use this for communicating with the MariaDB Database from our Python script.

The installation will also install HeidiSQL which is a open-source GUI database administration tool used for managing MariaDB and other Databases. With the new version of MariaDB ,HeidiSQL is automatically installed .

The MariaDB server Runs as a Service on Windows 11 Systems.

You can start and stop the MariaDB server by typing services.msc into the search bar.

Inside the services ,You can see a service called MariaDB and clicking it will give you the start /stop options.

By default MariaDB will be running from system start up.

Inside Windows 11 ,You can Find two Command line Clients for interacting with your Database or you can use the Heidisql GUI client too.

1. Here Command Prompt (MariaDB 12.0 (x64)) can be used to login to any account (root or user) using standard commands.(mariadb -u 'user-name' -p)
2. while MySQL Client (MariaDB 12.0 (x64)) can be used to login to only the root account.

After logging into the root account you need to create a database for your program to interact.

If you are working on an institutional/organizational setting ,the database along with your user id and password will be provided to you by the institution/organization that you are working with.

Creating a database in MariaDB server

In our case ,We will first create the database for us.

Log in to the root account of the MariaDB database server using MySQL Client (MariaDB 12.0 (x64)) command line client .

and create database using the following command

Creating a User

Once the database is created we need to create a user and assign that user to that specific user.

You can create a user by

so here we will use the username as rahul and password as EY4u^?%<_VB2tfY2

So on running that

Now the user rahul@localhost is created.

Assign a User to the Specific MariaDB database

You need to use the GRANT command to do that .

Now we will be using our database for development, So we will grant our user the ability to

1. CREATE - Create a Table
2. INSERT - Insert data
3. SELECT - Read Data
4. UPDATE - Update columns
5. DELETE - Delete data
6. ALTER -
7. DROP - Deletes theTable

This will allow the user to perform full CRUD operations on the MariaDB database from Python.

Lets grant our user the above privileges using the grant command as shown below.

Now that you have created your database ,You can log out of the root account and log in to our database from a more restrictive user account.

You need to use the Command Prompt (MariaDB 12.0 (x64))

A database connector is a piece of software (library, driver, or tool) that lets an application talk to a database.

Think of it as a translator and a bridge ,Your app (Python Script) speaks one language (Java, Python, PHP, etc.), the database speaks another (SQL + its own protocol), and the connector makes them understand each other.

Database connectors provide a secure and reliable way for applications to communicate with databases. They handle critical tasks such as establishing connections, managing authentication, sending queries, and receiving results, which saves developers from dealing with low-level database communication details. Connectors also help improve performance through features like connection pooling and enhance security by supporting prepared statements that reduce the risk of SQL injection

Installing MariaDB Connector for Python ( MariaDB Connector/Python)

MariaDB Connector/Python is a Python database driver that lets applications connect to MariaDB and MySQL databases.

The current MariaDB Connector/Python implementation supports

1. Python versions from 3.7 to 3.11
2. MariaDB server versions from version 10.3 or MySQL server versions from version 5.7.
3. MariaDB client library (MariaDB Connector/C) from version 3.3.1.

To install the MariaDB Connector/Python on Windows 11 you can use PIP

This would install mariadb connector for Python on the main Python installation on Windows 11.

Python MariaDB Connector Installation using VENV on Windows

you can use a Python venv virtual environment on windows to install mariadb locally.

Make sure that the virtual environment is active before using PIP to install mariadb python connector.

To connect to a MariaDB database,

1. you need the database server running,
2. valid credentials (username, password),
3. and a client or connector that matches your environment.

The connection process typically involves specifying the host, port (default 3306), database name, and login details. Once connected, you can execute SQL queries to create, read, update, and delete data

Key things to remember when connecting to a MariaDB database include ensuring that the MariaDB server is running before attempting a connection, using the correct connector or driver suitable for the programming language being used, keeping database credentials secure to prevent unauthorized access, and always closing the database connection after use to free resources and maintain optimal performance

In our case the parameters are

,

We are going to build a simple Python script that will attempt to communicate with the MariADB server running locally on our Windows 11 system.

The Script will attempt to connect with database "mariadb_testdb".

Before running this script, make sure that the venv virtual environment is active and the MariaDB server is running locally.

If the server is not running ,you will get an error.

After creating the database,We are going to create a single table (transaction_history)inside the said database using SQL Commands.

You can use either SQL Commands or Graphical User Frontends like HeidiSQL to Create Tables.

Here we will use SQL Commands that will be executed on the MariaDB Database using Python's Cursor object (cursor.execute()).

Format of our MariaDB Database Table is

The SQL Command for creating transaction_history table is

Now we need to run the SQL Command using a Python Script to create a Table inside a MariaDB database running on Windows 11

First,

cursor = conn.cursor() creates a cursor object from the existing database connection (conn). A cursor allows you to interact with the database by executing SQL queries and fetching results.

Inside the try block,

cursor.execute(create_table_sql_query) runs the SQL statement stored in the variable create_table_sql_query, which is CREATE TABLE command.

After executing the query,

conn.commit() saves (commits) the changes to the database permanently, ensuring that the new table is actually created.

You can check the Database by going inside it using HeidiSQL

We have created the Table, Now its time to insert data ie (rows or records ) into the MariaDB Database Table using the Python Script .

Now the basic SQL syntax for inserting a row into mariadb using Python is

Here table_name is the name of the table where the data will be stored, and the column names inside the parentheses specify which columns will receive the values.

The VALUES (?,?,?) part contains placeholders for the actual data.

These placeholders are used in Python when executing the query with a cursor, allowing you to safely pass values as parameters instead of writing them directly into the SQL string.

This approach helps prevent SQL injection and ensures proper handling of data types. When the query is executed with actual values supplied, a new row is inserted into the specified table.

Here is the Partial Code for that ,You can find the full code from our website

our code inserts a new record into the transaction_history table in a MariaDB database using Python.

First, it defines the data that will be inserted the customer’s name, age, email, and total bill amount are stored in separate variables.

Then, an SQL INSERT statement is stored in the variable sql_query_insert_data. This query specifies the columns (cust_name, cust_age, cust_email, total_bill, and time_of_pay) and uses placeholders (?,?,?,?,?) for the values that will be provided later when executing the query.

After a successful connection with MariaDB database,

a cursor object is created using conn.cursor().

The cursor is responsible for executing SQL commands.

Inside try block, the current date and time are generated using datetime.datetime.now() and stored in the variable timestamp, which will be inserted into the time_of_pay column.

The cursor.execute() method is then called with two arguments

1. the SQL insert query
2. a tuple containing the actual values to replace the placeholders.

This safely passes the data to the database and helps prevent SQL injection.

Once the query executes successfully, conn.commit() is called to permanently save the inserted record in the database.

You can run the code as shown below.Make sure that venv is active (green colour prompt)

After running check your table

After inserting data, our next goal should be to read the rows or records stored in the MariaDB database using Python.

For Readings the Rows and columns of the MariaDB we are provided with three methods by the Python API.

1. fetchall() method retrieves all the rows returned by the query at once. It returns the results as a list of tuples (or dictionaries, depending on how the cursor is configured). This method is useful when you want the entire result set

1. fetchone() method retrieves only a single row from the result set. It returns one tuple (or dictionary) representing that row. This method is ideal when you expect exactly one result, such as searching for a user by a unique ID or username. If no more rows are available, it returns None.

1. fetchmany(size) method retrieves a specified number of rows at a time. You pass an integer value (size) to indicate how many rows you want returned in that batch. It returns a list of tuples (or dictionaries). This method is especially useful for pagination or processing large datasets in smaller chunks, such as fetching 50 rows at a time to improve memory efficiency and performance.

We will begin with the most straightforward Read operation, which is the fetchall() method. This method is available through the cursor object and is used after running a SELECT statement.

When you call fetchall(), it retrieves every row returned by the query and stores them all in memory at the same time, typically as a list of tuples (or dictionaries, depending on how the cursor is set up).

Using fetchall() is practical and convenient when working with small databases that contain only a few hundred records, since the amount of data being loaded into memory is relatively small.

However, this method can become inefficient or even risky when dealing with very large datasets. If the query returns millions or billions of rows, loading all that data into memory at once can consume a huge amount of system resources, potentially slowing down the application or causing it to crash.

Here is the code for Reading the whole MariaDB database using fetchall()

here is the Partial code for reading data from MariaDB

Inside the try block,

cursor.execute(sql_query_read_all) runs a SELECT * FROM transaction_history query, which retrieves all columns and rows from the table.

After executing the query, cursor.fetchall() is called to fetch the entire result set at once and store it in the variable table.

This variable now contains a list of rows, where each row is typically represented as a tuple.

On Running the Code.

In this section we are going to read a single Row from a MariaDB Database using Python method fetchone().

This method is part of the cursor object and is used after executing a SELECT query. Unlike fetchall(), which retrieves all rows at once, fetchone() returns only the next available row from the result set.

When called, fetchone() retrieves a single row as a tuple (or dictionary, depending on the cursor configuration). This method is especially useful when you expect exactly one result, such as retrieving a record by a unique ID, email address, or primary key. If no matching record is found, fetchone() returns None.

For example if we run this query

The result will be a single row. To get such results you can use fetchone().

After executing the query, cursor.fetchone() is called. Unlike fetchall(), this method retrieves only the next available row from the result set.

Since the query is filtering by a specific id (which is usually a primary key and unique), we expect only one matching record. The returned row is stored in the variable just_single_row, typically as a tuple containing the column values of that record.