Master Python‑MariaDB Integration: Connect, Query, and Manage Data Efficiently

We explain how to use Python to connect to MariaDB, manage its data, and interact with components such as the server, MaxScale, and SkySQL.

Preparation and Installation

Download MariaDB Server from the official website and refer to the manual for details.

MariaDB provides a Python connector via the MariaDB Connector or the Python Package Index.

Install the connector with the following command:

pip install mariadb

Connecting to MariaDB

1. Import the module: import mariadb 2. Use connect() with parameters such as user, host, password, port, and database.

3. Obtain a cursor with cursor() to interact with the server.

import mariadb
import sys

try:
    conn = mariadb.connect(
        user="db_user",
        password="db_user_passwd",
        host="192.0.2.1",
        port=3306,
        database="employees"
    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

cur = conn.cursor()

Retrieving Data

After establishing the connection, you can query data. Example:

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}")

The connector uses prepared statements, inserting tuple values at the ? placeholders for safety.

Inserting Data

Use execute() with an INSERT statement to add rows:

cur.execute(
    "INSERT INTO employees (first_name, last_name) VALUES (?, ?)",
    (first_name, last_name)
)

By default, the connection autocommits. To manage transactions manually, disable autocommit: conn.autocommit = False Then use commit() or rollback() as needed. MariaDB’s InnoDB engine supports concurrent transactions without manual locking.

Error Handling

Capture SQL errors with a try‑except block:

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

Closing the Connection

After all operations, close the connection to free resources:

conn.close()