Python MariaDB Tutorial: Connect, CRUD, Transactions, and Error Handling

1. Introduction

For those who want to work with MariaDB in Python

Many people want to connect to MariaDB using Python and manipulate data. In this article, we will explain everything from the basics to more advanced topics for working with MariaDB using Python.

Do you have any of these problems?

• You don’t know how to connect to MariaDB from Python
• You want to easily create, read, update, and delete data in MariaDB using Python
• You want to learn about common errors and how to fix them

What you will learn in this article

• How to easily connect to MariaDB from Python
• How to implement CRUD (Create, Read, Update, Delete)
• Common errors and their solutions
• How to protect data integrity with transactions

After reading this article, you will be able to work smoothly with MariaDB using Python. Now, let’s dive into the details.

2. Environment Setup (Installing MariaDB & Python)

What is MariaDB?

MariaDB is an open-source relational database management system (RDBMS) that is widely used as a successor to MySQL. While maintaining high compatibility with MySQL, MariaDB has improved performance and added new features.

Required tools

To work with MariaDB in Python, you need to prepare the following tools:

• Python (version 3.x or later recommended)
• MariaDB (server)
• MariaDB Python connector (the mariadb library)

How to install MariaDB (by OS)

For Windows

1. Download the MariaDB installer from the official website.
2. Run the installer and follow the instructions to complete the installation.
3. After installation, verify that MariaDB is working by running the following command:

mysql -u root -p

4. If you can log in successfully, the installation is complete.

For Mac

On Mac, you can easily install MariaDB using Homebrew.

1. If Homebrew is not installed, install it with the following command:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

2. Install MariaDB:

brew install mariadb

3. Start MariaDB:

brew services start mariadb

4. Verify the installation:

mysql -u root -p

For Linux (Ubuntu)

1. Update the package list and install MariaDB:

sudo apt update
sudo apt install mariadb-server mariadb-client

2. Start MariaDB:

sudo systemctl start mariadb

3. Enable MariaDB (start automatically on boot):

sudo systemctl enable mariadb

4. Run the secure installation script:

sudo mysql_secure_installation

This allows you to set the root password and remove unnecessary default settings.

Installing Python and the required library

To integrate MariaDB with Python, you need to install the mariadb library.

1. Install the mariadb library using pip:

pip install mariadb

2. Verify the installation:

import mariadb
print("The MariaDB library was installed successfully.")

If no error occurs, the installation was successful.

Now your environment setup for using Python with MariaDB is complete. Next, we will explain how to connect to MariaDB from Python.

3. Connecting to MariaDB

How to connect to MariaDB with Python

Import the required library

To connect to MariaDB, import the Python mariadb library.

import mariadb

Basic code to connect to the database

The following code shows the basic way to connect to MariaDB using Python.

import mariadb

# Database connection settings
config = {
    "host": "localhost",   # Hostname of the MariaDB server
    "user": "root",        # MariaDB username
    "password": "password", # MariaDB password
    "database": "sample_db" # Database name to connect to
}

try:
    # Connect to MariaDB
    conn = mariadb.connect(**config)
    print("Connected to MariaDB!")

    # Create a cursor
    cursor = conn.cursor()

    # If the connection succeeds, close it
    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Connection error: {e}")

About the arguments of connect()

If the server is remote, specify an IP address or domain name in host.

A secure connection method using environment variables

Hardcoding sensitive information such as passwords directly in your code is a security risk. By using environment variables, you can manage them more safely.

Install python-dotenv

First, install a library to manage environment variables.

pip install python-dotenv

Create a .env file and write your connection settings

Create a .env file in your project folder and add your connection settings like this:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=password
DB_NAME=sample_db

Load environment variables in your Python script

To load the .env file in a Python script, write the following:

import mariadb
import os
from dotenv import load_dotenv

# Load the .env file
load_dotenv()

config = {
    "host": os.getenv("DB_HOST"),
    "user": os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
    "database": os.getenv("DB_NAME")
}

try:
    conn = mariadb.connect(**config)
    print("Connected securely to MariaDB!")
    conn.close()
except mariadb.Error as e:
    print(f"Connection error: {e}")

Common connection errors and how to fix them

Below are some errors you may encounter when connecting Python to MariaDB, along with their solutions.

Access denied for user 'root'@'localhost'

Error message

mariadb.OperationalError: Access denied for user 'root'@'localhost' (using password: YES)

Causes

• The user’s credentials are incorrect
• MariaDB authentication settings are not configured properly

Solutions

1. Log in to MariaDB and check the user’s privileges.

mysql -u root -p

2. Grant the required privileges to the user.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3. Try connecting again.

Can't connect to MySQL server on 'localhost'

Error message

mariadb.OperationalError: Can't connect to MySQL server on 'localhost' (10061)

Causes

• The MariaDB server is not running
• The host setting is incorrect

Solutions

1. Check whether the server is running.

sudo systemctl status mariadb

2. If the server is stopped, start it.

sudo systemctl start mariadb

3. Check that the host setting is correct.

Summary

In this section, we explained how to connect to MariaDB from Python.

• Basic connection method using mariadb.connect()
• A secure connection method using a .env file
• Common connection errors and how to fix them

4. Creating Databases and Tables

Creating a database

In MariaDB, you need to create a database to store data. Let’s look at how to create a database using Python.

Create a database using a MariaDB management tool

You can create a database using the MariaDB command line (or GUI tools such as MySQL Workbench).

CREATE DATABASE sample_db;

To view the list of databases you created, use the following command:

SHOW DATABASES;

Create a database using Python

To create a database using Python, run the following code:

import mariadb

# Database connection (no database specified)
config = {
    "host": "localhost",
    "user": "root",
    "password": "password"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Create the database
    cursor.execute("CREATE DATABASE IF NOT EXISTS sample_db")

    print("Created database 'sample_db'.")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

By adding IF NOT EXISTS, you can prevent errors if a database with the same name already exists.

Creating a table

After creating the database, the next step is to create a table to store data.

Basic table structure

A table typically has a structure like the following:

Create a table with SQL

If you create a table using MariaDB SQL commands, write the following:

USE sample_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

To view the list of tables, run:

SHOW TABLES;

Create a table using Python

Here is how to create the same table using a Python script.

import mariadb

# Database connection
config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Table creation SQL
    table_creation_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """

    cursor.execute(table_creation_query)
    print("Created table 'users'.")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

By adding IF NOT EXISTS, you can prevent errors if the table already exists.

How to choose data types

When designing tables, it is important to choose appropriate data types. Below is a summary of commonly used data types in MariaDB.

For example, it is common to use VARCHAR(100) for the name field, but if you need a longer string, TEXT can be a good option.

How to check and drop existing tables

Check existing tables

To check the tables in a database, use the following SQL:

SHOW TABLES;

To view a table’s detailed structure, run the DESCRIBE command:

DESCRIBE users;

Drop a table

To drop a table, use DROP TABLE:

DROP TABLE users;

To drop a table from Python, run the following code:

cursor.execute("DROP TABLE IF EXISTS users")

Summary

In this section, we explained how to create databases and tables in MariaDB to store data.

• How to create a database in MariaDB
• How to create a table using Python
• How to choose appropriate data types
• How to check and drop existing tables

Now the basic setup for MariaDB is complete. In the next section, we will explain CRUD operations (Create, Read, Update, Delete) in detail.

5. CRUD Operations (Create, Read, Update, Delete)

After creating the MariaDB database and tables, the next step is to perform CRUD operations. CRUD stands for Create, Read, Update, and Delete, which are the basic operations of a database.

In this section, we will explain how to manipulate MariaDB data using Python.

Inserting data (INSERT)

To add a new record to the database, use the INSERT statement.

Insert data using SQL

To insert data using MariaDB SQL, execute the following command:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

To verify the inserted data, use the SELECT statement:

SELECT * FROM users;

Insert data using Python

To insert data using Python, run the following code:

import mariadb

# Database connection settings
config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Insert data
    insert_query = "INSERT INTO users (name, email) VALUES (?, ?)"
    data = ("Alice", "alice@example.com")
    cursor.execute(insert_query, data)

    # Save changes
    conn.commit()
    print("Data inserted successfully!")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

Key points:

• Using ? placeholders helps prevent SQL injection.
• If you do not execute conn.commit(), the data will not be saved to the database.

Retrieving data (SELECT)

To retrieve registered data, use the SELECT statement.

Retrieve data using SQL

To retrieve data using MariaDB SQL:

SELECT * FROM users;

To retrieve data with conditions, use the WHERE clause:

SELECT * FROM users WHERE email = 'alice@example.com';

Retrieve data using Python

To retrieve data using Python, run the following code:

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Retrieve data
    select_query = "SELECT id, name, email FROM users"
    cursor.execute(select_query)

    # Display retrieved data
    for (id, name, email) in cursor:
        print(f"ID: {id}, Name: {name}, Email: {email}")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

Key points:

• Execute SQL with cursor.execute(select_query) and retrieve data from the cursor object.
• You can process records one by one using a for loop.

Updating data (UPDATE)

To modify registered data, use the UPDATE statement.

Update data using SQL

To update data using MariaDB SQL, execute:

UPDATE users SET name = 'Alice Smith' WHERE email = 'alice@example.com';

Update data using Python

To update data using Python, run the following code:

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Update data
    update_query = "UPDATE users SET name = ? WHERE email = ?"
    data = ("Alice Smith", "alice@example.com")
    cursor.execute(update_query, data)

    # Save changes
    conn.commit()
    print("Data updated successfully!")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

Deleting data (DELETE)

To remove unnecessary data, use the DELETE statement.

Delete data using SQL

To delete data using MariaDB SQL, execute:

DELETE FROM users WHERE email = 'alice@example.com';

Delete data using Python

To delete data using Python, run the following code:

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Delete data
    delete_query = "DELETE FROM users WHERE email = ?"
    data = ("alice@example.com",)
    cursor.execute(delete_query, data)

    # Save changes
    conn.commit()
    print("Data deleted successfully!")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

Summary

In this section, we explained how to manipulate MariaDB data using Python.

• Insert data (INSERT)
• Retrieve data (SELECT)
• Update data (UPDATE)
• Delete data (DELETE)

You can now perform basic data operations in MariaDB using Python.

6. Transactions and Rollback (Ensuring Data Integrity)

When working with a database, it is important to use transactions to maintain data integrity.
In particular, the ability to restore data to its original state (rollback) when an error occurs during processing is essential for maintaining consistency.

In this section, we will explain how to manage transactions in MariaDB using Python.

What is a transaction?

A transaction is a mechanism that groups a series of database operations into a single unit, commits all changes if every process succeeds, and rolls back all changes if an error occurs.

Characteristics of transactions

• ACID properties
• Atomicity: All operations either complete successfully or none are applied.
• Consistency: Data integrity is maintained.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once committed, changes are permanently saved.

Basic transaction operations (COMMIT and ROLLBACK)

MariaDB transactions can be controlled with the following commands:

Using transactions in Python

Below is a basic example of managing MariaDB transactions using Python.

Managing multiple operations within a single transaction

The following code treats multiple insert operations as one transaction and commits only if all operations succeed.

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Start transaction
    conn.start_transaction()

    # Insert data
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Bob", "bob@example.com"))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Charlie", "charlie@example.com"))

    # Commit if all operations succeed
    conn.commit()
    print("Data added successfully.")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")
    conn.rollback()  # Roll back if an error occurs

Key points

• Use conn.start_transaction() to begin a transaction.
• Use conn.commit() to finalize changes (cannot be undone after commit).
• If an error occurs, use conn.rollback() to cancel changes and restore the original state.

Transaction-based error handling

Errors may occur during database operations.
For example, a duplicate email address (UNIQUE constraint violation) or a server timeout may happen.

Transaction management with error handling

The following code adds logic to roll back changes and restore the original state if an error occurs during processing.

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Start transaction
    conn.start_transaction()

    try:
        # First insert succeeds
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Dave", "dave@example.com"))

        # Second insert causes an error (duplicate email)
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Eve", "dave@example.com"))

        # Commit if all operations succeed
        conn.commit()
        print("Data inserted successfully.")

    except mariadb.Error as e:
        print(f"Error occurred during data processing: {e}")
        conn.rollback()  # Roll back on error
        print("Transaction rolled back.")

    cursor.close()
    conn.close()

except mariadb.Error as e:
    print(f"Database connection error: {e}")

Summary

In this section, we explained the basics of transactions and how to implement them in Python.

• The importance of transactions and ACID properties
• How to use commit() and rollback() in Python
• Transaction management combined with error handling

7. Error Handling (Common Errors and Solutions)

When working with MariaDB in Python, errors may occur.
In particular, database connection errors, SQL syntax errors, and data integrity errors are common, so it is important to understand their causes and solutions.

In this section, we introduce the causes of common errors and how to fix them.

Access denied for user (Authentication error)

Error message

mariadb.OperationalError: Access denied for user 'root'@'localhost' (using password: YES)

Cause

• The username or password is incorrect
• The MariaDB user does not have the appropriate privileges

Solution

1. Log in to MariaDB and check user privileges

mysql -u root -p

2. Grant privileges to the user

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3. Restart MariaDB

sudo systemctl restart mariadb

Can't connect to MySQL server on 'localhost' (Connection error)

Error message

mariadb.OperationalError: Can't connect to MySQL server on 'localhost' (10061)

Cause

• The MariaDB server is not running
• The host setting is incorrect

Solution

1. Check whether the MariaDB server is running

sudo systemctl status mariadb

2. If the server is stopped, start it

sudo systemctl start mariadb

3. Enable the server to start automatically

sudo systemctl enable mariadb

Unknown database 'sample_db' (Database does not exist)

Error message

mariadb.ProgrammingError: Unknown database 'sample_db'

Cause

• The specified database does not exist
• There is a typo in the database name

Solution

1. Check the list of databases

SHOW DATABASES;

2. Create the database if it does not exist

CREATE DATABASE sample_db;

Table doesn't exist (Table does not exist)

Error message

mariadb.ProgrammingError: Table 'sample_db.users' doesn't exist

Cause

• The specified table does not exist
• The database was not selected using USE sample_db;

Solution

1. Check the list of tables

SHOW TABLES;

2. Create the table if it does not exist

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Duplicate entry (Duplicate data error)

Error message

mariadb.IntegrityError: Duplicate entry 'alice@example.com' for key 'users.email'

Cause

• The email column has a UNIQUE constraint, so duplicate values cannot be inserted

Solution

• Check for duplicates before inserting data
• Use ON DUPLICATE KEY UPDATE

Python code to prevent duplicates

try:
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))
    conn.commit()
except mariadb.IntegrityError:
    print("Error: The email address already exists.")

Or use ON DUPLICATE KEY UPDATE

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE name = 'Alice Updated';

Incorrect number of bindings (Parameter count mismatch)

Error message

mariadb.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

Cause

• The number of parameters required by the SQL statement does not match the number of arguments provided

Solution

• Make sure the number of placeholders matches the number of parameters

Incorrect code

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice"))

Correct code

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))

Summary

In this section, we explained common errors that occur when working with MariaDB in Python and how to resolve them.

• Access denied for user (Authentication error)
• Can't connect to MySQL server (Connection error)
• Unknown database (Database does not exist)
• Table doesn't exist (Table does not exist)
• Duplicate entry (Duplicate data error)
• Incorrect number of bindings (Parameter count mismatch)

8. Conclusion

In this article, we explained how to work with MariaDB using Python, from basic concepts to more advanced topics.
By understanding fundamental database operations and properly implementing error handling and transaction management, you can perform safer and more efficient data operations.

Article recap

Environment setup

• How to install MariaDB (Windows / Mac / Linux)
• How to install the MariaDB connector library (mariadb) for Python

Connecting to MariaDB

• Basic database connection methods
• Secure connections using environment variables
• Solutions for common connection errors

Creating databases and tables

• Creating databases (SQL / Python)
• Creating tables and choosing data types
• Checking and dropping existing tables

CRUD operations (Create, Read, Update, Delete)

• Basic data operations using Python
• Using placeholders to prevent SQL injection
• Proper error handling

Transactions and rollback

• The importance of transactions (ACID properties)
• How to restore data on error (rollback)
• Managing transactions manually by disabling AutoCommit

Error handling (Common errors and solutions)

• Authentication errors (Access denied for user)
• Connection errors (Can't connect to MySQL server)
• Database or table not found errors
• Duplicate data errors (Duplicate entry)
• Handling deadlocks and implementing retry logic

Key points when combining Python and MariaDB

1. Use placeholders

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))

→ Bind variables instead of embedding them directly in SQL statements to prevent SQL injection.

2. Manage transactions properly

• Use conn.commit() to explicitly commit changes.
• Use conn.rollback() on error to maintain data integrity.

3. Implement thorough error handling

• Use try-except blocks to catch potential errors in advance.
• Prepare specific handling strategies for errors such as OperationalError and IntegrityError.

4. Consider performance

• Bulk inserts (insert multiple records at once)
• Proper indexing (improve search performance)

Resources for further learning

If you want to deepen your understanding of integrating Python and MariaDB, consider the following resources:

Official documentation

• MariaDB Official Documentation

Related learning content

• SQL Basics (W3Schools)
• Database Operations with Python (Real Python)

Recommended books

• SQL, 2nd Edition: Database Operations from Scratch – Learn SQL fundamentals thoroughly
• Effective Python: 90 Specific Ways to Improve Your Python Programs – Learn Python best practices

Summary

By integrating MariaDB with Python, you can enable script-based data management and automation.
By implementing not only basic CRUD operations but also proper error handling and transaction management, you can build a safer and more efficient system.

As a next step, consider learning about efficient data management and API integration to apply your knowledge to practical database operations.