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 RCS.
Use SSH to access the server as a non-root sudo user.
Deploy a RCS Managed Database for MySQL or Install MySQL locally on the server.
Deploy a RCS Managed Database for Redis 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 -VUpdate the server.
$ sudo apt updateInstall the
python3-pippackage.$ sudo apt install -y python3-pipInstall the MySQL connection driver.
$ pip install mysql-connector-pythonInstall the
redislibrary.$ pip install redis
Set Up the MySQL Database
Log in to the MySQL server.
$ sudo mysqlCreate a sample
my_shopdatabase.mysql> CREATE DATABASE my_shop;Create a new MySQL user for accessing the new database. Replace
EXAMPLE-PASSWORDwith a strong password to protect.mysql> CREATE USER 'my_shop_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE-PASSWORD';Grant the
my_shop_userfull privileges to themy_shopdatabase.mysql> GRANT ALL PRIVILEGES ON my_shop.* TO 'my_shop_user'@'localhost';Refresh MySQL privileges.
mysql> FLUSH PRIVILEGES;Switch to the new
my_shopdatabase.mysql> USE my_shop;Create a sample
productstable 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
productstable 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
productstable 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
projectdirectory.$ mkdir projectSwitch to the directory.
$ cd projectUsing a text editor such as
Nano. Create a newmysql_gateway.pyfile.$ nano mysql_gateway.pyAdd 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 productsSave and close the file.
Below is what the above configuration does:
import mysql.connector: Declares themysql.connectordriver for Python to communicate with the MySQL server.MysqlGatewayclass: The (query_mysql(self, query_string)) method accepts aquery_stringargument 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 thedb_cursor.execute(query_string)function. Thedictionary = Truevalue, 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 usingreturn 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.pyfile.$ nano index.pyAdd 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. Thehttp.server,HTTPStatus, andsocketservermodules create an HTTP server that listens for incoming connections on your target port. Then, thejsonmodule allows you to work with JSON data,redisallows the application to communicate with the Redis server, and themysql_gatewayis 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 ... elsestatement 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 theproducts = obj.query_mysql(query_string)function and caches data to the Redis server using theredis_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 theproducts = json.loads(redis_server.get(query_string).decode("utf-8"))function. The value of5in theredis_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,300for 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.pyThe 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-ipRun the following
curlcommand.$ curl -X GET http://localhost:8080/Verify the output below. The
_sourcekey 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
curlcommand 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
_sourcekey ("_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.
