MariaDB Connector/Python

Warnings

• breaking MariaDB Connector/Python 2.x (currently in release candidate) introduces significant breaking changes from 1.x, particularly concerning asynchronous support, connection string URIs, and potentially API signatures. Code written for 1.x will likely require modifications to work with 2.x.
  Fix: Consult the official 2.x release notes and documentation for specific migration paths. If developing new applications, consider starting with 2.x for async capabilities, otherwise stick to 1.x for current stability.
• gotcha The `mariadb` connector is a native C extension. If pre-compiled wheels are not available for your platform/Python version, or if you're installing from source, you may need to install the MariaDB Connector/C library (e.g., `libmariadb-dev` on Debian/Ubuntu, `mariadb-connector-c-devel` on Fedora/RHEL) on your system before `pip install mariadb`.
  Fix: Ensure the necessary system-level development libraries for MariaDB Connector/C are installed before attempting to install the Python package. Common errors include 'mariadb.Error: Can't find libmariadb.so' or compilation failures.
• gotcha Always use parameterized queries (e.g., `cursor.execute("INSERT INTO table VALUES (?) ", (value,))`) instead of string formatting or f-strings for passing user-supplied data into SQL queries. Failing to do so makes your application vulnerable to SQL injection attacks.
  Fix: Rewrite any queries that concatenate user input directly into SQL strings to use the `execute()` method's parameter substitution feature, where `?` acts as a placeholder for positional arguments.
• gotcha Connections and cursors should always be properly closed to release database resources. Failing to do so can lead to resource exhaustion and performance issues, especially in high-traffic applications.
  Fix: Explicitly call `conn.close()` and `cursor.close()` in a `finally` block, or preferably, use `with` statements for connections and cursors if supported by the API (though not directly for `mariadb.connect` itself, cursors can often be managed this way if the connection object supports context management for cursors).

Install

• pip install mariadb Install stable version
• pip install --pre mariadb Install pre-release (e.g., 2.0.0rc)

Imports

• mariadb

  import mariadb

• Error

  import mariadb.Error

  While `from mariadb import Error` works, `mariadb.Error` is often used for clarity and to avoid name collisions.

Quickstart

This quickstart demonstrates how to connect to a MariaDB database, create a table, insert data using parameterized queries, and fetch results. It retrieves connection details from environment variables for secure and flexible deployment. Remember to set `MARIADB_HOST`, `MARIADB_PORT`, `MARIADB_USER`, `MARIADB_PASSWORD`, and `MARIADB_DATABASE`.

import mariadb
import os

host = os.environ.get('MARIADB_HOST', '127.0.0.1')
port = int(os.environ.get('MARIADB_PORT', 3306))
user = os.environ.get('MARIADB_USER', 'root')
password = os.environ.get('MARIADB_PASSWORD', '')
database = os.environ.get('MARIADB_DATABASE', 'test_db')

conn = None
cursor = None
try:
    # Connect to MariaDB Platform
    conn = mariadb.connect(
        user=user,
        password=password,
        host=host,
        port=port,
        database=database
    )

    # Get a cursor
    cursor = conn.cursor()

    # Create a table
    cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))")
    conn.commit()

    # Insert data
    cursor.execute("INSERT INTO users (name) VALUES (?) ", ("Alice",))
    cursor.execute("INSERT INTO users (name) VALUES (?) ", ("Bob",))
    conn.commit()

    # Query data
    cursor.execute("SELECT id, name FROM users")
    for (id_val, name) in cursor:
        print(f"ID: {id_val}, Name: {name}")

except mariadb.Error as e:
    print(f"Error connecting to or interacting with MariaDB Platform: {e}")
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()