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

The author selected the Tech Education Fund to receive a donation as part of the Write for DOnations program.

Introduction

MariaDB is an open source version of the popular MySQL relational database management system (DBMS) with a SQL interface for accessing and managing data. It is highly reliable and easy to administer, which are essential qualities of a DBMS capable of serving modern applications. With Python’s growing popularity in technologies like artificial intelligence and machine learning, MariaDB makes a good option for a database server for Python.

In this tutorial, you will connect a Python application to a database server using the MySQL connector. This module allows you to make queries on the database server from within your application. You’ll set up MariaDB for a Python environment on Ubuntu 18.04 and write a Python script that connects to and executes queries on MariaDB.

Prerequisites

Before you begin this guide, you will need the following:

• One Ubuntu 18.04 server with at least 1GB of RAM set up by following the Ubuntu 18.04 Initial Server Setup Guide, including a sudo non-root user and a firewall.
• Python installed on your server. Follow our How To Install Python and Set Up a Programming Environment tutorial.
• MariaDB installed on your server. Follow our How To Install MariaDB tutorial. Make sure to note your authorization credentials (username and password), because you will use that later in the tutorial.

Step 1 — Preparing and Installing

In this step, you’ll create a database and a table in MariaDB.

First, open your terminal and enter the MariaDB shell from the terminal with the following command:

sudo mysql

Once you’re in the MariaDB shell, your terminal prompt will change. In this tutorial, you’ll write Python to connect to an example employee database named workplace and a table named employees.

Start by creating the workplace database:

CREATE DATABASE workplace;

Next, tell MariaDB to use workplace as your current database:

USE workplace;

You will receive the following output, which means that every query you run after this will take effect in the workplace database:

Output
Database changed

Next, create the employees table:

CREATE TABLE employees (first_name CHAR(35), last_name CHAR(35));

In the table schema, the parameters first_name and a last_name are specified as character strings (CHAR) with a maximum length of 35.

Following this, exit the MariaDB shell:

exit;

Back in the terminal, export your MariaDB authorization credentials as environment variables:

export username="username"
export password="password"

This technique allows you to avoid adding credentials in plain text within your script.

You’ve set up your environment for the project. Next, you’ll begin writing your script and connect to your database.

Step 2 — Connecting to Your Database

In this step, you will install the MySQL Connector and set up the database.

In your terminal, run the following command to install the Connector:

pip3 install mysql-connector-python

pip is the standard package manager for Python. mysql-connector-python is the database connector Python module.

Once you’ve successfully installed the connector, create and open a new file Python file:

nano database.py

In the opened file, import the os module and the mysql.connector module using the import keyword:

import os
import mysql.connector as database

The as keyword here means that mysql.connector will be referenced as database in the rest of the code.

Next, initialize the authorization credentials you exported as Python variables:

. . .
username = os.environ.get("username")
password = os.environ.get("password")

Follow up and establish a database connection using the connect() method provided by database. The method takes a series of named arguments specifying your client credentials:

. . .
connection = database.connect(
    user=username,
    password=password,
    host=localhost,
    database="workplace")

You declare a variable named connection that holds the call to the database.connect() method. Inside the method, you assign values to the user, password, host, and database arguments. For user and password, you will reference your MariaDB authorization credentials. The host will be localhost by default if you are running the database on the same system.

Lastly, call the cursor() method on the connection to obtain the database cursor:

. . .
cursor = connection.cursor()

A cursor is a database object that retrieves and also updates data, one row at a time, from a set of data.

Leave your file open for the next step.

Now you can connect to MariaDB with your credentials; next, you will add entries to your database using your script.

Step 3 — Adding Data

Using the execute() method on the database cursor, you will add entries to your database in this step.

Define a function add_data() to accept the first and last names of an employee as arguments. Inside the function, create a try/except block. Add the following code following your cursor object:

. . .
def add_data(first_name, last_name):
    try:
        statement = "INSERT INTO employees (first_name,last_name) VALUES (%s, %s)"
        data = (first_name, last_name)
        cursor.execute(statement, data)
        connection.commit()
        print("Successfully added entry to database")
    except database.Error as e:
        print(f"Error adding entry to database: {e}")

You use the try and except block to catch and handle exceptions (events or errors) that disrupt the normal flow of program execution.

Under the try block, you declare statement as a variable holding your INSERT SQL statement. The statement tells MariaDB to add to the columns first_name and last_name.

The code syntax accepts data as parameters that reduce the chances of SQL injection. Prepared statements with parameters ensure that only given parameters are securely passed to the database as intended. Parameters are generally not injectable.

Next you declare data as a tuple with the arguments received from the add_data function. Proceed to run the execute() method on your cursor object by passing the SQL statement and the data. After calling the execute() method, you call the commit() method on the connection to permanently save the inserted data.

Finally, you print out a success message if this succeeds.

In the except block, which only executes when there’s an exception, you declare database.Error as e. This variable will hold information about the type of exception or what event happened when the script breaks. You then proceed to print out an error message formatted with e to end the block using an f-string.

After adding data to the database, you’ll next want to retrieve it. The next step will take you through the process of retrieving data.

Step 4 — Retrieving Data

In this step, you will write a SQL query within your Python code to retrieve data from your database.

Using the same execute() method on the database cursor, you can retrieve a database entry.

Define a function get_data() to accept the last name of an employee as an argument, which you will call with the execute() method with the SELECT SQL query to locate the exact row:

. . .
def get_data(last_name):
    try:
      statement = "SELECT first_name, last_name FROM employees WHERE last_name=%s"
      data = (last_name,)
      cursor.execute(statement, data)
      for (first_name, last_name) in cursor:
        print(f"Successfully retrieved {first_name}, {last_name}")
    except database.Error as e:
      print(f"Error retrieving entry from database: {e}")

Under the try block, you declare statement as a variable holding your SELECT SQL statement. The statement tells MariaDB to retrieve the columns first_name and last_name from the employees table when a specific last name is matched.

Again, you use parameters to reduce the chances of SQL injection.

Smilarly to the last function, you declare data as a tuple with last_name followed by a comma. Proceed to run the execute() method on the cursor object by passing the SQL statement and the data. Using a for loop, you iterate through the returned elements in the cursor and then print out if there are any successful matches.

In the except block, which only executes when there is an exception, declare database.Error as e. This variable will hold information about the type of exception that occurs. You then proceed to print out an error message formatted with e to end the block.

In the final step, you will execute your script by calling the defined functions.

Step 5 — Running Your Script

In this step, you will write the final piece of code to make your script executable and run it from your terminal.

Complete your script by calling add_data() and get_data() with sample data (strings) to verify that your code is working as expected.

If you would like to add multiple entries, you can call add_data() with further sample names of your choice.

Once you finish working with the database make sure that you close the connection to avoid wasting resources: connection.close():

import os
import mysql.connector as database

username = os.environ.get("username")
password = os.environ.get("password")

connection = database.connect(
    user=username,
    password=password,
    host=localhost,
    database="workplace")

cursor = connection.cursor()

def add_data(first_name, last_name):
	try:
    statement = "INSERT INTO employees (first_name,last_name) VALUES (%s, %s)"
    data = (first_name, last_name)
	  cursor.execute(statement, data)
    cursor.commit()
    print("Successfully added entry to database")
	except database.Error as e:
    print(f"Error adding entry to database: {e}")

def get_data(last_name):
    try:
      statement = "SELECT first_name, last_name FROM employees WHERE last_name=%s"
      data = (last_name,)
      cursor.execute(statement, data)
      for (first_name, last_name) in cursor:
        print(f"Successfully retrieved {first_name}, {last_name}")
    except database.Error as e:
      print(f"Error retrieving entry from database: {e}")

add_data("Kofi", "Doe")
get_data("Doe")

connection.close()

Make sure you have indented your code correctly to avoid errors.

In the same directory, you created the database.py file, run your script with:

python3 ./database.py

You will receive the following output:

Output
Successfully added entry to database
Successfully retrieved Kofi, Doe

Finally, return to MariaDB to confirm you have successfully added your entries.

Open up the MariaDB prompt from your terminal:

sudo mysql

Next, tell MariaDB to switch to and use the workplace database:

USE workplace;

After you get the success message Database changed, proceed to query for all entries in the employees table:

SELECT * FROM employees;

You output will be similar to the following:

Output
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Kofi       | Doe       |
+------------+-----------+
1 row in set (0.00 sec)

Putting it all together, you’ve written a script that saves and retrieves information from a MariaDB database.

You started by importing the necessary libraries. You used mysql-connector to connect to the database and os to retrieve authorization credentials from the environment. On the database connection, you retrieved the cursor to carry out queries and structured your code into add_data and get_data functions. With your functions, you inserted data into and retrieved data from the database.

If you wish to implement deletion, you can build a similar function with the necessary declarations, statements, and calls.

Conclusion

You have successfully set up a database connection to MariaDB using a Python script on Ubuntu 18.04. From here, you could use similar code in any of your Python projects in which you need to store data in a database. This guide may also be helpful for other relational databases that were developed out of MySQL.

For more on how to accomplish your projects with Python, check out other community tutorials on Python.