How to connect MariaDB with Python

You can use Python and MariaDB to ef­fi­ciently connect a database with ap­plic­a­tions and process in­form­a­tion. The com­bin­a­tion of Python and MariaDB is es­pe­cially suitable for web apps, data analysis and auto­mat­ing reports.

What are the pre­requis­ites?

You’ll need to fulfill the following re­quire­ments to connect Python programs with MariaDB:

• Python in­stall­a­tions: 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 func­tion­al MariaDB database. You can either use a local MariaDB in­stall­a­tion or create a con­nec­tion with a remote database.
• Database access data: You’ll need in­form­a­tion like the host name, port, database name, username and password, to connect with the MariaDB database.
• Python module: The Python/Connector module for con­nect­ing 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

Down­load­ing and in­stalling the MariaDB package on your own hardware is quite simple. Here’s how to do that step by step:

Step 1: Check system re­quire­ments

Check the re­quire­ments for in­stalling MariaDB on your system. Make sure that your hardware fulfills the minimum re­quire­ments like suf­fi­cient 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 ar­chi­tec­ture (32 bit or 64 bit). After the download is complete, run the in­stall­a­tion. The in­stall­a­tion will start dif­fer­ently 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 spe­cify­ing access data, security settings and other database para­met­ers.

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 con­fig­ur­a­tion

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 En­ter­prise Server and offers auto scaling, high avail­ab­il­ity and in­teg­rated 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 re­dir­ec­ted 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 En­ter­prise Server and other database services.

Step 3: Configure the service

Configure the service, including selecting a MariaDB version, database capacity and other resource para­met­ers. You can also choose options for high avail­ab­il­ity and auto scaling. Define the au­then­tic­a­tion and security settings. That includes con­fig­ur­ing usernames and passwords and managing access to the database.

Step 4: Connect to the database

After the service has been deployed, you’ll receive in­form­a­tion on the host name, port, username and password. Use this in­form­a­tion 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 mon­it­or­ing and ana­lyt­ic­al tools for mon­it­or­ing the per­form­ance 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 para­met­ers or ac­tiv­at­ing ad­di­tion­al 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 op­er­a­tions 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 con­nec­tion

First, you’ll need to import the library and establish a con­nec­tion to your MariaDB database. To do that, you need in­form­a­tion like the host name, port, database name, username and password.

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
}
# 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 op­er­a­tions in a re­la­tion­al database like MariaDB. Those op­er­a­tions include, for example, SQL queries and pro­cessing data. In Python, you receive a cursor after you’ve es­tab­lished a con­nec­tion 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. Place­hold­ers like %s stand for the values that will be inserted. The tuple values contains the cor­res­pond­ing 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 trans­ac­tion to ensure the changes are per­man­ently saved in the database.

Step 4: Close the con­nec­tion

Once the database op­er­a­tions are complete, close the cursor and the con­nec­tion 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 ex­cep­tions that come up when es­tab­lish­ing the con­nec­tion 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 ap­plic­a­tion and implement different database op­er­a­tions.

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()