How to Cache MySQL Data with Redis® and Python on Ubuntu 20.04

Published on June 28, 2023•Updated on June 20, 2024

Introduction

You can improve the reliability of Python applications by caching frequently accessed data in a fast in-memory storage system like Redis®. Most frequently accessed data in business systems includes products, payment methods, list box items, countries, and more. Without a cache, your application fetches data from a slow relational disk-based database like MySQL.

Redis® caching improves application reliability in the following ways:

Reduces the database load - Only the first data retrieval request hits the MySQL database. After caching the query result from the database, the application routes future requests to the Redis® server.
Utilizes server RAM when caching data - Allows your application to fetch data more quickly in the future.

In this tutorial, you learn how to implement a Redis® caching solution for a Python application that uses MySQL as the backend database on a Ubuntu 20.04 server.

Prerequisites

Before you start:

Deploy a Ubuntu 20.04 server on Vultr.
Use SSH to access the server as a non-root sudo user.
Deploy a Vultr Managed Database for MySQL or Install MySQL locally on the server.
Deploy a Vultr Managed Database for Caching or Install Redis® on the server.

Install Dependencies

To connect your Python application with MySQL and Redis®, you need the PIP package manager that allows you to install and manage libraries written in Python. Install all necessary Python libraries as described in the steps below.

Verify the available Python3 version on your server.
```
 $ python3 -V
```
Update the server.
```
 $ sudo apt update
```
Install the python3-pip package.
```
 $ sudo apt install -y python3-pip
```
Install the MySQL connection driver.
```
 $ pip install mysql-connector-python
```
Install the redis library.
```
 $ pip install redis
```

Set Up the MySQL Database

Log in to the MySQL server.
```
 $ sudo mysql
```
Create a sample my_shop database.
```
 mysql> CREATE DATABASE my_shop;
```
Create a new MySQL user for accessing the new database. Replace EXAMPLE-PASSWORD with a strong password to protect.
```
 mysql> CREATE USER 'my_shop_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE-PASSWORD';
```

Grant the my_shop_user full privileges to the my_shop database.

 mysql> GRANT ALL PRIVILEGES ON my_shop.* TO 'my_shop_user'@'localhost';

Refresh MySQL privileges.
```
 mysql> FLUSH PRIVILEGES;
```
Switch to the new my_shop database.
```
 mysql> USE my_shop;
```

Create a sample products table with the following columns.

 mysql> CREATE TABLE products (
            product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            product_name VARCHAR(50),
            retail_price  DOUBLE     
        ) ENGINE = InnoDB;

Populate the products table with sample data as below.

 mysql> INSERT INTO products (product_name, retail_price) VALUES ('PORTABLE MINI PROJECTOR', 90);
        INSERT INTO products (product_name, retail_price) VALUES ('BLUETOOTH SPEAKER', 23.5);
        INSERT INTO products (product_name, retail_price) VALUES ('NAIL POLISH', 5.29);
        INSERT INTO products (product_name, retail_price) VALUES ('KIDS TABLET', 60);
        INSERT INTO products (product_name, retail_price) VALUES ('THERMOS CUP', 4.89);

Query the products table to view table data.

 mysql> SELECT
            product_id,
            product_name,
            retail_price
        FROM products;

Output:

 +------------+-------------------------+--------------+
 | product_id | product_name            | retail_price |
 +------------+-------------------------+--------------+
 |          1 | PORTABLE MINI PROJECTOR |           90 |
 |          2 | BLUETOOTH SPEAKER       |         23.5 |
 |          3 | NAIL POLISH             |         5.29 |
 |          4 | KIDS TABLET             |           60 |
 |          5 | THERMOS CUP             |         4.89 |
 +------------+-------------------------+--------------+
 5 rows in set (0.00 sec)

Exit the MySQL shell.
```
 mysql> QUIT;
```

Initialize a Project Directory and Create a MySQL Database Module

Designing Python applications in modules makes code more readable and quick to troubleshoot. In this section, set up a project directory that separates your Python source code from the system files. Then, import a MySQL database module to use in other Python files as described below.

Create a new project directory.
```
 $ mkdir project
```
Switch to the directory.
```
 $ cd project
```
Using a text editor such as Nano. Create a new mysql_gateway.py file.
```
 $ nano mysql_gateway.py
```
Add the following configurations to the file.
```
 import mysql.connector

 class MysqlGateway:

     def query_mysql(self, query_string):

         db_con = mysql.connector.connect(
                      host     = "localhost",
                      user     = "my_shop_user",
                      password = "EXAMPLE_PASSWORD",
                      database = "my_shop"
                  )

         db_cursor = db_con.cursor(dictionary = True)

         db_cursor.execute(query_string)

         products = db_cursor.fetchall()

         return products
```
Save and close the file.

Below is what the above configuration does:
- import mysql.connector: Declares the mysql.connector driver for Python to communicate with the MySQL server.
- MysqlGateway class: The (query_mysql(self, query_string)) method accepts a query_string argument that queries the MySQL database and retrieves records from the database table.
- db_con = mysql.connector.connect(...): Connects to the MySQL server using the database user, and password you created earlier.
- db_cursor = db_con.cursor(dictionary = True): Creates a cursor that executes the SQL statement using the db_cursor.execute(query_string) function. The dictionary = True value, instructs Python to return the SQL result as key-value pairs (dictionary). The dictionary format allows you to format the response to JSON format when displaying the data.
- products = db_cursor.fetchall(): Fetches all records from the MySQL table and returns all products to the calling script using return products.

Create the Application's Starting Point

Like any other programming language, a Python application requires a main file that acts as a starting point. This file fires the main function when the application starts. To create the file, follow the steps below:

Create a new index.py file.
```
 $ nano index.py
```

Add the following configurations to the file.

 import http.server
 from http import HTTPStatus
 import socketserver

 import json
 import redis

 import mysql_gateway

 class HttpHandler(http.server.SimpleHTTPRequestHandler): 

     def do_GET(self):

         self.send_response(HTTPStatus.OK)
         self.send_header('Content-type', 'application/json')
         self.end_headers()

         query_string = "select * from products"

         redis_server = redis.Redis()

         resp = {}

         if redis_server.get(query_string) is None:

             obj  =  mysql_gateway.MysqlGateway()

             products = obj.query_mysql(query_string)  

             resp = {
                 "_source": "MySQL Server",
                  "data": products
             }

             redis_server.setex(query_string, 5, value = json.dumps(products))

         else:

             products = json.loads(redis_server.get(query_string).decode("utf-8"))

             resp = {
                 "_source": "Redis Server - Retrieved from Cache",
                  "data": products
             } 

         self.wfile.write(bytes(json.dumps(resp, indent = 2), "utf8")) 

 httpd = socketserver.TCPServer(('', 8080), HttpHandler)
 print("The HTTP server is running at port 8080...")

 try:
     httpd.serve_forever()
 except KeyboardInterrupt: 
     httpd.server_close()
     print("The HTTP server has stopped running.")

Save and close the file.

Below is what each configuration line does:

import...: Declares the application modules. The http.server, HTTPStatus, and socketserver modules create an HTTP server that listens for incoming connections on your target port. Then, the json module allows you to work with JSON data,redis allows the application to communicate with the Redis® server, and the mysql_gateway is the custom MySQL module you created earlier.
HttpHandler(): handles all HTTP requests. Within the class, (do_GET(self)) allows the application to listen for HTTP GET requests.
Under the do_GET(self) method, your application executes the following logic:
- Set appropriate HTTP headers.
```
    ...
    self.send_response(HTTPStatus.OK), 
    self.send_header('Content-type', 'application/json')
    self.end_headers()
```
- Craft a query string to fetch products from the MySQL database.
```
    ...
    query_string = "select * from products"
```
- Connect to a Redis® server and initialize an empty response list (resp = {}).
```
    ...
    redis_server = redis.Redis()
    resp = {}
```
- Performing an if ... else statement checks if the Redis® server contains a cached copy of the products data. In case the Redis® server doesn't have a cached copy (if redis_server.get(query_string) is None:) the application retrieves data from the MySQL server using the products = obj.query_mysql(query_string) function and caches data to the Redis® server using the redis_server.setex(query_string, 5, value = json.dumps(products)) function. If there is a cache in the Redis® server, the application loads data from Redis® database using the products = json.loads(redis_server.get(query_string).decode("utf-8")) function. The value of 5 in the redis_server.setex(query_string, 5, value = json.dumps(products)) defines the duration in seconds the cache should expire. You can set a different value, for example, 300 for 300 seconds.
```
  ...
  if redis_server.get(query_string) is None:

      obj  =  mysql_gateway.MysqlGateway()

      products = obj.query_mysql(query_string)  

      resp = {
          "_source": "MySQL Server",
           "data": products
      }

      redis_server.setex(query_string, 5, value = json.dumps(products))

  else:

      products = json.loads(redis_server.get(query_string).decode("utf-8"))

      resp = {
          "_source": "Redis Server - Retrieved from Cache",
           "data": products
      }
```
- At the end of the file, you start a web server that listens on port 8080.
```
  httpd = socketserver.TCPServer(('', 8080), HttpHandler)
  print("The HTTP server is running at port 8080...")

  try:
    httpd.serve_forever()
  except KeyboardInterrupt: 
    httpd.server_close()
    print("The HTTP server has stopped running.")
```

Test the Application's Logic

Run the application's start-up file.
```
 $ python3 index.py
```
The above command establishes an HTTP server and has a blocking function. Don't run any other command in the session.
```
 The HTTP server is running at port 8080...        
```
In a new terminal session, establish a second SSH connection to your server.
```
 $ ssh example_user@your-server-ip
```

Run the following curl command.

 $ curl -X GET http://localhost:8080/

Verify the output below. The _source key value confirms that the application fetches data from the MySQL server.

 {
   "_source": "MySQL Server",
   "data": [
     {
       "product_id": 1,
       "product_name": "PORTABLE MINI PROJECTOR",
       "retail_price": 90.0
     },
     {
       "product_id": 2,
       "product_name": "BLUETOOTH SPEAKER",
       "retail_price": 23.5
     },
     {
       "product_id": 3,
       "product_name": "NAIL POLISH",
       "retail_price": 5.29
     },
     {
       "product_id": 4,
       "product_name": "KIDS TABLET",
       "retail_price": 60.0
     },
     {
       "product_id": 5,
       "product_name": "THERMOS CUP",
       "retail_price": 4.89
     }
   ]
 }

Run the curl command again before 5 seconds expire.

 $ curl -X GET http://localhost:8080/

This time around, the application fetches data from the Redis® server (cache) as indicated by the _source key ("_source": "Redis Server - Retrieved from Cache").

 {
   "_source": "Redis Server - Retrieved from Cache",
   "data": [
     {
       "product_id": 1,
       "product_name": "PORTABLE MINI PROJECTOR",
       "retail_price": 90.0
     },
     {
       "product_id": 2,
       "product_name": "BLUETOOTH SPEAKER",
       "retail_price": 23.5
     },
     {
       "product_id": 3,
       "product_name": "NAIL POLISH",
       "retail_price": 5.29
     },
     {
       "product_id": 4,
       "product_name": "KIDS TABLET",
       "retail_price": 60.0
     },
     {
       "product_id": 5,
       "product_name": "THERMOS CUP",
       "retail_price": 4.89
     }
   ]
 }

Your application logic is working as expected.

Conclusion

You have implemented a caching solution with Redis® for a Python application that uses a MySQL database. Use the logic in this tutorial to cache frequently used data in your applications. Each time you update data in the MySQL database, invalidate the Redis® cache to load new data, you can achieve this in the redis_server.setex(query_string, 5, value = json.dumps(products)) function.

How to Cache MySQL Data with Redis® and Python on Ubuntu 20.04

Introduction

Prerequisites

Install Dependencies

Set Up the MySQL Database

Initialize a Project Directory and Create a MySQL Database Module

Create the Application's Starting Point

Test the Application's Logic

Conclusion

Next Steps

Products

Features

Marketplace

Resources

Company