Python x MariaDB Complete Guide: Install, Connect, CRUD, Error Handling

1. Introduction

For Those Who Want to Operate MariaDB with Python

Many people probably want to connect to MariaDB using Python and perform data operations. In this article, we will explain in detail from the basics to advanced techniques for operating MariaDB using Python.

Do You Have These Concerns?

• I don’t know how to connect to MariaDB from Python
• I want to easily add, retrieve, update, and delete data using Python
• I want to know common errors and how to handle them

What You Can Learn from This Article

• How to easily connect to MariaDB from Python
• How to implement CRUD (adding, retrieving, updating, and deleting data)
• Common errors and their solutions
• How to maintain data integrity using transaction processing

By reading this article, you’ll be able to handle MariaDB smoothly using Python. Let’s dive into the specifics.

2. Environment Preparation (Installing MariaDB & Python)

What is MariaDB?

MariaDB is an open-source relational database management system (RDBMS) and is widely used as a successor to MySQL. It maintains high compatibility with MySQL while improving performance and adding new features.

Required Tools

To handle MariaDB with Python, you need to prepare the following tools.

• Python (Version 3.x or higher recommended)
• MariaDB (Server)
• MariaDB Python Connector (mariadb library)

MariaDB Installation Procedure (By OS)

For Windows

1. Download the MariaDB installer from the official site.
2. Run the installer and follow the instructions to install.
3. After installation, verify MariaDB is running with 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 (set it to start automatically on boot).

sudo systemctl enable mariadb

4. Configure MariaDB.

sudo mysql_secure_installation

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

Installing Python and Required Libraries

To connect MariaDB and 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 has been successfully installed.")

If no errors occur, the installation is successful.

With this, the environment preparation for connecting Python and MariaDB is complete. Next, we will explain how to connect to MariaDB from Python.

3. Connecting to MariaDB

How to Connect to MariaDB Using Python

Importing Required Libraries

To connect to MariaDB, import the Python mariadb library.

import mariadb

Basic Code for Connecting to the Database

The following code shows the basic method for connecting to MariaDB using Python.

import mariadb

# Database connection information
config = {
    "host": "localhost",   # MariaDB server hostname
    "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 is successful, close the connection
    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Connection error: {e}")

About the Arguments of the connect() Function

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

Secure Connection Method Using Environment Variables

Directly writing sensitive information such as passwords in the code is dangerous from a security perspective. By using environment variables, you can manage it more securely.

Installing python-dotenv

First, install the library for managing environment variables.

pip install python-dotenv

Create a .env File and Describe Connection Information

Create a .env file in the project folder and describe the connection information as follows.

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

Loading Environment Variables in Python Script

To load the .env file in a Python script, describe it as follows.

import mariadb
import os
from dotenv import load_dotenv

# Load .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("Securely connected to MariaDB!")
    conn.close()
except mariadb.Error as e:
    print(f"Connection error: {e}")

Common Connection Errors and Their Solutions

Introducing possible errors that may occur when connecting Python to MariaDB and their solutions.

Access denied for user 'root'@'localhost'

Error Content

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

Cause

• User authentication information is incorrect
• MariaDB authentication settings are not appropriate

Solution

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

mysql -u root -p

2. Grant the necessary permissions 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 Content

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

Cause

• MariaDB server is not started
• host setting is incorrect

Solution

1. Check if the server is running.

sudo systemctl status mariadb

2. If the server is stopped, start it.

sudo systemctl start mariadb

3. Check if the host setting is correct.

Summary

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

• mariadb.connect() Basic connection method using
• .env Secure connection method utilizing the file
• Common connection errors and their solutions

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.

Creating a Database Using MariaDB Management Tools

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

CREATE DATABASE sample_db;

To check the list of created databases, use the following command.

SHOW DATABASES;

Creating a Database Using Python

When creating a database using Python, execute 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()

    # Creating 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 Tables

Once the database is created, next create a table to store the data.

Basic Table Structure

Tables have a structure like the following.

Creating a Table with SQL

When creating a table using MariaDB’s SQL commands, describe it as follows.

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 check the list of tables, use the following command.

SHOW TABLES;

Creating a Table Using Python

Here’s 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.

Choosing Data Types

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

For example, it’s common to use VARCHAR(100) for the name field, but if you need longer strings, using TEXT is also a good choice.

How to Check and Delete Existing Tables

Checking Existing Tables

To check tables in the database, use the following SQL.

SHOW TABLES;

Also, to check the detailed structure of a table, execute the DESCRIBE command.

DESCRIBE users;

Deleting a Table

To delete a table, use DROP TABLE.

DROP TABLE users;

When deleting from Python, execute the following code.

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

Summary

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

• How to Create a Database in MariaDB
• How to Create Tables Using Python
• How to Choose Appropriate Data Types
• How to Check and Delete Existing Tables

This completes the basic setup of MariaDB. In the next section, we will explain in detail about CRUD operations (creating, reading, updating, and deleting data).

5. CRUD Operations (Creating, Reading, Updating, and Deleting Data)

MariaDB database and table creation is complete, so next perform CRUD operations. CRUD is an acronym for Create (creation), Read (retrieval), Update (update), Delete (deletion), referring to the basic operations of a database.

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

Data Insertion (INSERT)

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

Data Insertion Using SQL

When inserting data using MariaDB SQL, execute the following command.

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

To confirm the inserted data, use the SELECT statement.

SELECT * FROM users;

Inserting Data in Python

When inserting data using Python, execute 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 added!")

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

Points:

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

Data Retrieval (SELECT)

To retrieve registered data, use the SELECT statement.

Data Retrieval Using SQL

The method to retrieve data using MariaDB SQL is as follows.

SELECT * FROM users;

When specifying conditions for retrieval, use the WHERE clause.

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

Retrieving Data in Python

To retrieve data using Python, execute 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}")

Points:

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

Data Update (UPDATE)

To modify registered data, use the UPDATE statement.

Data Update Using SQL

When updating data using MariaDB SQL, execute the following command.

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

Updating Data in Python

To update data using Python, execute 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!")

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

Data Deletion (DELETE)

To delete unnecessary data, use the DELETE statement.

Data Deletion Using SQL

To delete data using MariaDB SQL, execute the following command.

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

Deleting Data in Python

To delete data using Python, execute 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!")

    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.

• Data Addition (INSERT)
• Data Retrieval (SELECT)
• Data Update (UPDATE)
• Data Deletion (DELETE)

With this, you can now perform basic data operations on MariaDB using Python.

6. Transactions and Rollback (Ensuring Data Integrity)

When operating on a database, it is important to utilize transactions to maintain data integrity.
In particular, the function to revert data to its original state (rollback) if an error occurs midway is essential for maintaining data consistency.

This section explains 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 the data if all processes succeed, and rolls back all changes if an error occurs.

Features of Transactions

• ACID Properties
• Atomicity: The processing is either fully completed or not performed at all.
• Consistency: Data maintains integrity.
• Isolation: Concurrent transactions do not affect each other.
• Durability: Once the processing is completed, the changes are permanently saved.

Basic Transaction Operations (COMMIT and ROLLBACK)

Transactions in MariaDB can be controlled with the following commands.

Using Transactions in Python

Here is an introduction to basic code for managing MariaDB transactions using Python.

Managing Multiple Data Operations in One Transaction

The following code treats the process of adding multiple data as one transaction and commits (finalizes) if all processes 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()

    # Add 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 (finalize) if all processes succeed
    conn.commit()
    print("Data added successfully.")

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

Points

• Start the transaction with conn.start_transaction().
• Commit changes with conn.commit() (cannot revert after commit).
• If an error occurs, use conn.rollback() to cancel data changes and revert to the original state.

Error Handling Using Transactions

In database operations, there is a possibility of errors occurring midway.
For example, cases such as duplicate email address (UNIQUE constraint violation) or server timeout can be considered.

Transaction Management with Error Handling

The following code adds processing to execute rollback if an error occurs midway and revert data to the original state.

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:
        # Add data (first one succeeds)
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Dave", "dave@example.com"))

        # Error occurs in second processing (email duplicate)
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Eve", "dave@example.com"))

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

    except mariadb.Error as e:
        print(f"Error occurred during data processing: {e}")
        conn.rollback()  # Cancel changes on error
        print("Data 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 implementation methods using 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 operating MariaDB with Python, errors may occur.
In particular, database connection errors, SQL statement errors, and data integrity errors occur frequently, so it is important to understand their causes and solutions.

This section introduces the causes of common errors and how to handle them.

Access denied for user (User Authentication Error)

Error Details

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

Causes

• Username or password is incorrect
• Appropriate permissions are not set for the MariaDB user

Solutions

1. Log in to MariaDB and check user permissions

   mysql -u root -p

2. Set user permissions

   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 Details

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

Causes

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

Solutions

1. Check if the MariaDB server is running

   sudo systemctl status mariadb

2. Start the server if it is stopped

   sudo systemctl start mariadb

3. Set the server to start automatically

   sudo systemctl enable mariadb

Unknown database 'sample_db' (Database Does Not Exist)

Error Details

mariadb.ProgrammingError: Unknown database 'sample_db'

Causes

• The specified database does not exist
• Typo in the database name

Solutions

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 Details

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

Causes

• The specified table does not exist
• Did not select the database with USE sample_db;

Solutions

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 (Data Duplication Error)

Error Details

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

Causes

• Cannot insert the same value because the email column has a UNIQUE constraint

Solutions

• Perform a duplicate check before inserting data
• Use ON DUPLICATE KEY UPDATE

Code to Prevent Duplicates in Python

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 (Mismatch in Number of Parameters)

Error Details

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

Causes

• The number of parameters required by the SQL does not match the number of arguments actually passed

Solutions

• Match the number of placeholders with the number of data items

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 discussed common errors that occur when operating MariaDB with Python and their countermeasures.

• 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 (Data Duplication Error)
• Incorrect number of bindings (Mismatch in Number of Parameters)

8. Summary

In this article, we explained in detail how to operate MariaDB using Python, from basics to advanced topics.
By understanding basic database operations and properly handling error handling and transaction management, you can perform safer and more efficient data operations.

Article Review

Environment Preparation

• How to Install MariaDB (Windows / Mac / Linux)
• Installing the Python and MariaDB Connection Library (mariadb)

Connecting to MariaDB

• Basic Database Connection Methods
• Secure Connections Using Environment Variables
• Solutions for Errors Occurring During Connection

Creating Databases and Tables

• Creating Databases (SQL/Python)
• Creating Tables and Choosing Data Types
• Checking and Deleting Existing Tables

CRUD Operations (Adding, Retrieving, Updating, and Deleting Data)

• Basic Data Operations Using Python
• Using Placeholders to Prevent SQL Injection
• Appropriate Error Handling

Transactions and Rollback

• Importance of Transactions (ACID Properties)
• How to Revert Data on Error (Rollback)
• Disabling AutoCommit to Manually Manage Transactions

Error Handling (Common Errors and Solutions)

• Authentication Errors (Access denied for user)
• Connection Errors (Can't connect to MySQL server)
• Errors for Non-Existent Databases or Tables
• Data Duplication Errors (Duplicate entry)
• Deadlock Occurrence and Implementing Retry Processing

Key Points When Combining Python and MariaDB

1. Leverage Placeholders

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

→ By binding variables instead of embedding them directly into SQL statements, you can prevent SQL injection.

2. Perform Appropriate Transaction Management

• Use conn.commit() to explicitly commit changes.
• In case of errors, use conn.rollback() to maintain data integrity.

2. Implement Solid Error Handling

• Use try-except to catch potential errors in advance.
• Prepare countermeasures for each error, such as OperationalError or IntegrityError.

2. Consider Performance

• Bulk Insert (adding multiple records at once)
• Appropriate Index Settings (to improve search speed)

Resources for Further Learning

For those who want to learn more deeply about integrating Python and MariaDB, the following resources are recommended.

Official Documentation

• MariaDB Official Documentation

Related Learning Content

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

Recommended Books

• SQL, 2nd Edition: Starting Database Operations from Zero – A solid way to learn SQL fundamentals
• Effective Python: 90 Specific Ways to Write Better Python – Learn Python best practices

Summary

By integrating MariaDB with Python, data management using scripts and automation become possible.

Not only basic CRUD operations, but by properly implementing error handling and transaction management, you can build a safer and more efficient system.

As the next step, let’s learn about “efficient data management” and “integration with APIs” and apply it to practical database operations.