How To Store and Retrieve Data in MariaDB Using Python on Ubuntu 18.04

Introduction

This guide shows how to connect Python to MariaDB and perform basic CRUD operations from a Python script. It uses the official MySQL Connector/Python (mysql-connector-python) to talk to a MariaDB server running on Ubuntu. Examples include creating a simple workplace database, an employees table, and Python functions that insert and select rows safely using parameterized queries.

Prerequisites

• An Ubuntu system (this tutorial uses Ubuntu 18.04 as an example) with a non-root sudo user.
• Python 3 installed. A virtual environment is recommended for the mysql-connector-python package.
• MariaDB server installed and running. Have a database user and password ready.

1. Prepare the database

Start by creating a database and a table in the MariaDB shell. Open a terminal and enter the MariaDB prompt:

$ sudo mysql

In the MariaDB prompt, create a database and an employees table:

CREATE DATABASE workplace;
USE workplace;
CREATE TABLE employees (
  first_name VARCHAR(35),
  last_name VARCHAR(35)
);
EXIT;

To avoid embedding credentials in code, export the MariaDB username and password into environment variables:

$ export DB_USER="your_db_user"
$ export DB_PASS="your_db_password"

2. Install the Python connector

Install the Python MariaDB connector package (the MySQL Connector/Python works with MariaDB):

$ pip3 install mysql-connector-python

3. Python example: insert and select

The following database.py demonstrates how to connect to MariaDB from Python, add rows with a parameterized INSERT, and query rows with SELECT. Using parameterized queries prevents SQL injection and is the recommended pattern for Python MariaDB connector use.

# database.py
import os
import mysql.connector

DB_USER = os.environ.get("DB_USER")
DB_PASS = os.environ.get("DB_PASS")
DB_HOST = os.environ.get("DB_HOST", "localhost")
DB_NAME = "workplace"

# Establish connection using the MySQL Connector
conn = mysql.connector.connect(
    user=DB_USER,
    password=DB_PASS,
    host=DB_HOST,
    database=DB_NAME
)
cur = conn.cursor()

def add_employee(first_name, last_name):
    try:
        sql = "INSERT INTO employees (first_name, last_name) VALUES (%s, %s)"
        params = (first_name, last_name)
        cur.execute(sql, params)
        conn.commit()
        print("Inserted:", first_name, last_name)
    except mysql.connector.Error as err:
        print("Error inserting row:", err)

def get_employees_by_lastname(last_name):
    try:
        sql = "SELECT first_name, last_name FROM employees WHERE last_name = %s"
        cur.execute(sql, (last_name,))
        for first, last in cur:
            print("Found:", first, last)
    except mysql.connector.Error as err:
        print("Error querying rows:", err)

if __name__ == "__main__":
    add_employee("Kofi", "Doe")
    get_employees_by_lastname("Doe")
    cur.close()
    conn.close()

4. Run and verify

Execute the script from the directory where database.py lives:

$ python3 database.py

Output should confirm the insertion and retrieval. You can also verify directly from the MariaDB shell:

$ sudo mysql
USE workplace;
SELECT * FROM employees;

Security and best practices

• Keep credentials out of source code. Use environment variables or a secure secret manager.
• Always use parameterized queries or prepared statements to prevent SQL injection.
• Close cursors and connections when finished to free resources.
• For production, consider connection pooling and least-privilege database users.

Tip: The mysql-connector-python library works with MariaDB and can be installed with pip on Ubuntu. For a MariaDB-specific client, you can also evaluate the MariaDB Connector/Python.

Conclusion

This tutorial demonstrated how to connect Python to MariaDB on Ubuntu, perform secure inserts, and query data using the Python MariaDB connector (mysql-connector-python). The patterns shown—using environment variables, parameterized queries, and proper resource cleanup—apply to many Python database tutorials and real-world projects.