Basic Usage of MariaDB in a Python Application: A Step-by-Step Guide

MariaDB is a popular open-source relational database management system and a drop-in replacement for MySQL. It provides a powerful and reliable way to store and manage data. In this article, we will explore the basic usage of MariaDB in a Python application. We’ll cover the steps for connecting to a MariaDB database, performing common database operations, and handling errors.

Prerequisites

Before we start, ensure that you have the following prerequisites in place:

MariaDB Server: MariaDB should be installed and running. You can download it from the official MariaDB website.
MariaDB Client: Install the MariaDB client on your system. This client is required for interacting with the database from Python.
Python: Make sure you have Python installed on your system. You can download Python from the official Python website.
Python MariaDB Connector: Install the mysql-connector-python package, which is the MariaDB connector for Python. You can install it using pip:
```
pip install mysql-connector-python
```

Step 1: Connecting to MariaDB

To use MariaDB in a Python application, you first need to establish a connection to the database. Here’s a basic example of how to do this:

import mysql.connector

# Database configuration
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',
    'database': 'your_database_name',
}

# Create a connection
try:
    connection = mysql.connector.connect(**config)

    if connection.is_connected():
        print("Connected to MariaDB")

    # Perform database operations here

except mysql.connector.Error as e:
    print(f"Error: {e}")

finally:
    if 'connection' in locals():
        connection.close()
        print("Connection closed")

Replace 'your_username', 'your_password', 'localhost', and 'your_database_name' with your MariaDB credentials and the name of your database.

Step 2: Performing Database Operations

Once connected, you can perform various database operations, such as creating tables, inserting data, querying data, updating records, and deleting records. Here are some examples:

Creating a Table

try:
    cursor = connection.cursor()

    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL
    )
    """

    cursor.execute(create_table_query)
    print("Table 'users' created successfully")

except mysql.connector.Error as e:
    print(f"Error: {e}")

finally:
    if 'cursor' in locals():
        cursor.close()

Inserting Data

try:
    cursor = connection.cursor()

    insert_query = "INSERT INTO users (username, email) VALUES (%s, %s)"
    data = ("john_doe", "[email protected]")

    cursor.execute(insert_query, data)
    connection.commit()
    print("Data inserted successfully")

except mysql.connector.Error as e:
    print(f"Error: {e}")

finally:
    if 'cursor' in locals():
        cursor.close()

Querying Data

try:
    cursor = connection.cursor()

    select_query = "SELECT * FROM users"

    cursor.execute(select_query)

    for (id, username, email) in cursor:
        print(f"ID: {id}, Username: {username}, Email: {email}")

except mysql.connector.Error as e:
    print(f"Error: {e}")

finally:
    if 'cursor' in locals():
        cursor.close()

Updating Data

try:
    cursor = connection.cursor()

    update_query = "UPDATE users SET email = %s WHERE username = %s"
    data = ("[email protected]", "john_doe")

    cursor.execute(update_query, data)
    connection.commit()
    print("Data updated successfully")

except mysql.connector.Error as e:
    print(f"Error: {e}")

finally:
    if 'cursor' in locals():
        cursor.close()

Deleting Data

try:
    cursor = connection.cursor()

    delete_query = "DELETE FROM users WHERE username = %s"
    data = ("john_doe",)

    cursor.execute(delete_query, data)
    connection.commit()
    print("Data deleted successfully")

except mysql.connector.Error as e:
    print(f"Error: {e}")

finally:
    if 'cursor' in locals():
        cursor.close()

Step 3: Error Handling

It’s essential to handle errors gracefully when working with databases. In the examples above, we use try, except, and finally blocks to ensure that database connections and cursors are properly closed, even in the presence of errors.

Conclusion

In this article, we’ve covered the basic usage of MariaDB in a Python application. You’ve learned how to connect to a MariaDB database, perform common database operations like creating tables, inserting data, querying data, updating records, and deleting records. These fundamental skills will serve as a solid foundation for building more complex database-driven applications with MariaDB and Python.