How to connect MariaDB with Python

You can use Python and MariaDB to efficiently connect a database with applications and process information. The combination of Python and MariaDB is especially suitable for web apps, data analysis and automating reports.

What are the prerequisites?

You’ll need to fulfill the following requirements to connect Python programs with MariaDB:

• Python installations: You need to have Python installed on your system. Make sure that you’re using a version that’s supported by MariaDB.
• MariaDB database: You need to have a functional MariaDB database. You can either use a local MariaDB installation or create a connection with a remote database.
• Database access data: You’ll need information like the host name, port, database name, username and password, to connect with the MariaDB database.
• Python module: The Python/Connector module for connecting with MariaDB should be installed.

How to prepare and install MariaDB with Python

There are two options for gaining access to a MariaDB server. You can either install a MariaDB server on your own computer or server or use the MariaDB platform in the cloud.

Option 1: Install MariaDB on hardware

Downloading and installing the MariaDB package on your own hardware is quite simple. Here’s how to do that step by step:

Step 1: Check system requirements

Check the requirements for installing MariaDB on your system. Make sure that your hardware fulfills the minimum requirements like sufficient disk space and memory.

Step 2: Download MariaDB

Go to MariaDB’s official website and select the download for your operating system (Windows, Linux or macOS). Make sure you choose the right version for your system.

Step 3: Install package

Download the package for your operating system and architecture (32 bit or 64 bit). After the download is complete, run the installation. The installation will start differently on different operating systems. For example, to install MariaDB on Ubuntu 20.04, enter the command sudo apt install mariadb-server.

Step 4: Configure the database

Now you need to configure the MariaDB server. This includes specifying access data, security settings and other database parameters.

Step 5: Start MariaDB

Start the MariaDB server service. On Windows, you can do this with the Service Manager. On Linux and macOS, you can do it on the command line with sudo systemctl start mariadb or sudo service mariadb start.

Step 6: Perform initial configuration

After starting MariaDB, configure it using the command mysql_secure_installation. The command helps you define important security settings like passwords and deleting test accounts.

Step 7: Test database access

Check whether you can connect to the database. Log in as the root user with mysql -u root -p. Enter your password and run some simple SQL queries to ensure that the server is working properly.

Option 2: Use MariaDB SkySQL

MariaDB SkySQL is a cloud-based database platform that you can use to run MariaDB databases in the cloud. The platform includes the MariaDB Enterprise Server and offers auto scaling, high availability and integrated security features.

Step 1: Register and log in

Go to the official MariaDB SkySQL website and register for an account, if you don’t already have one. Then log in.

Step 2: Create a new service

After you log in, you’ll be redirected to the SkySQL home page. Click on the button to create a new database service. Choose a service type. You can decide from among different MariaDB platforms, including the MariaDB Enterprise Server and other database services.

Step 3: Configure the service

Configure the service, including selecting a MariaDB version, database capacity and other resource parameters. You can also choose options for high availability and auto scaling. Define the authentication and security settings. That includes configuring usernames and passwords and managing access to the database.

Step 4: Connect to the database

After the service has been deployed, you’ll receive information on the host name, port, username and password. Use this information to connect to your database. Run some simple queries to make sure the service is working.

Step 5: Monitor and manage the database

MariaDB SkySQL offers monitoring and analytical tools for monitoring the performance of your database. Use those tools to ensure that your database is running optimally. If necessary, you can manage and scale your database by adjusting your resource parameters or activating additional features.

How to connect to the MariaDB server

You can use the MariaDB library for Python to connect to a MariaDB database and perform database operations like querying, inserting or updating data. Install the library using the package manager pip:

pip3 install mariadb

Now you can connect to MariaDB using Python.

Step 1: Establish a connection

First, you’ll need to import the library and establish a connection to your MariaDB database. To do that, you need information like the host name, port, database name, username and password.

import mariadb
db_config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_hostname',
    'database': 'your_database',
    'port': 3306  # Standard port for MariaDB
}
# Establishing the connection
conn = mariadb.connect(**db_config)
# Create a cursor to execute queries
cursor = conn.cursor()

The cursor is an object you can use to perform database operations in a relational database like MariaDB. Those operations include, for example, SQL queries and processing data. In Python, you receive a cursor after you’ve established a connection to the database. You can retrieve it using the method cursor().

Step 2: Query data

Now we’ll use the cursor to perform SQL queries and retrieve data from the database.

# Define the SQL query
sql_query = "SELECT * FROM your_table"
# Execute the query
cursor.execute(sql_query)
# Fetch results
results = cursor.fetchall()
# Display data
for row in results:
    print(row)

Step 3: Insert data

To insert data into a table, define a SQL INSERT query and execute it using the cursor.

# Define the SQL insert query
insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
# Values to insert
values = ('value1', 'value2')
# Execute the insert query
cursor.execute(insert_query, values)
# Commit the transaction
conn.commit()

The SQL INSERT statement indicates which table (your_table) and which columns (column1 and column2) you’re inserting data into. Placeholders like %s stand for the values that will be inserted. The tuple values contains the corresponding values that you’re entering into the database. The cursor executes the INSERT query with the values you specify. Finally, the method conn.commit() confirms the transaction to ensure the changes are permanently saved in the database.

Step 4: Close the connection

Once the database operations are complete, close the cursor and the connection to free up resources.

# Close cursor and connection
cursor.close()
conn.close()

Step 5: Handle errors

It’s important to include error handling, so you can catch any possible exceptions that come up when establishing the connection or executing queries.

try:
        # Establish the connection
        conn = mariadb.connect(**db_config)
        # Create a cursor
        cursor = conn.cursor()
        # Execute the query
        cursor.execute(sql_query)
        # Fetch results
        results = cursor.fetchall()
        for row in results:
            print(row)
    except mariadb.Error as err:
        print(f"Error: {err}")
    finally:
        # Close cursor and connection
        if cursor:
            cursor.close()
        if conn:
            conn.close()

You can customise this basic structure for your specific application and implement different database operations.

Here is the full code:

import mariadb
# Database connection details
db_config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_hostname',
    'database': 'your_database',
    'port': 3306  # Standard port for MariaDB
}
try:
    # Establishing the connection
    conn = mariadb.connect(**db_config)
    # Create a cursor to execute queries
    cursor = conn.cursor()
    # Define the SQL query
    sql_query = "SELECT * FROM your_table"
    # Execute the query
    cursor.execute(sql_query)
    # Fetch results
    results = cursor.fetchall()
    # Display data
    for row in results:
        print(row)
    # Define the SQL insert query
    insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
    # Values to insert
    values = ('value1', 'value2')
    # Execute the insert query
    cursor.execute(insert_query, values)
    # Commit the transaction
    conn.commit()
except mariadb.Error as err:
    print(f"Error: {err}")
finally:
    # Close cursor and connection
    if cursor:
        cursor.close()
    if conn:
        conn.close()