How to Use Vultr Managed Databases for MySQL in Python

Updated on June 22, 2024
How to Use Vultr Managed Databases for MySQL in Python header image

Introduction

Python is a high-level programming language that allows you to create highly available applications with support for popular relational database systems such as MySQL. To permanently store data records and interact with databases, you can use a Vultr Managed Database for MySQL with Python to improve your application structure and availability.

This guide explains how to use a Vultr Managed Database for MySQL with Python. You are to create a sample application that uses the Python MySQL connector to query user data and interact with the database tables.

Prerequisites

Before you begin:

Set Up the Database

To interact with your Vultr Managed Database for MySQL, install the Python mysql-connector-python driver and set up the database as described in the steps below.

  1. Using pip, install the Python MySQL driver

     $ pip install mysql-connector-python
  2. Using the MySQL client tool, connect to your Vultr Managed Database for MySQL

     $ mysql -h prod-db.vultrdb.com -P 16751 -u vultradmin -p 

    Replace the above values with your actual Vultr Managed Database for MySQL details:

    • Host: prod-db.vultrdb.com
    • Username: vultradmin
    • Port: 16751

    When prompted, enter your Database password and press Enter to access the console.

  3. Create a sample company_portal database

     mysql> CREATE DATABASE company_portal;
  4. Create a new database user app_user with a strong password

     mysql> CREATE USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'strong-password';
  5. Grant the user full privileges to the company_portal database

     mysql> GRANT ALL PRIVILEGES ON  company_portal.* TO 'app_user'@'%';
  6. Refresh the MySQL privileges

     mysql> FLUSH PRIVILEGES;
  7. Switch to the company_portal database

     mysql> USE company_portal;
  8. Create a sample products table with three columns

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

    In the above table, the AUTO_INCREMENT value on the product_id column assigns unique product_ids for new records.

  9. Add sample data to the products table

     mysql> INSERT INTO products (product_name, retail_price) VALUES ('8GB MEMORY CARD', 6.95);  
            INSERT INTO products (product_name, retail_price) VALUES ('2TB SSD', 300);   
            INSERT INTO products (product_name, retail_price) VALUES ('WI-FI DONGLE', 14.50); 
  10. View the products table data

     mysql> SELECT 
                product_id,
                product_name, 
                retail_price
            FROM products;

    Output:

     +------------+-----------------+--------------+
     | product_id | product_name    | retail_price |
     +------------+-----------------+--------------+
     |          1 | 8GB MEMORY CARD |         6.95 |
     |          2 | 2TB SSD         |          300 |
     |          3 | WI-FI DONGLE    |         14.5 |
     +------------+-----------------+--------------+
     3 rows in set (0.00 sec)   
  11. Exit the MySQL console

     mysql> QUIT;

You have set up a MySQL database, a products table, and added sample table data to use in your Python application. You can add multiple columns and records to match your application structure.

Create the Python Application

Data-driven applications use Create, Read, Update, and Delete (CRUD) operations to handle user interactions. Set up a Python application that performs these CRUD operations with the following parts:

  • A User Interface (UI)

  • An Application Programming Interface (API). The user interface communicates to the API that uses the following HTTP methods to translate requests to CRUD operations

    • POST: Creates a new resource in the application
    • GET: Retrieves the application resources
    • PUT: Updates the details of an existing resource
    • DELETE: Removes a resource from the application.

Based on the above structure, create a Python API application that accepts HTTP requests to interact with the MySQL database.

Create the Python MySQL Module

  1. Create a new project directory

     $ mkdir project
  2. Switch to the directory

     $ cd project
  3. Using a text editor such as nano, create a new mysql_gateway.py file

     $ nano mysql_gateway.py 
  4. Add the following contents to the file. Replace the host, password, and port values with your actual Vultr Managed Database for MySQL details

     import mysql.connector
    
     class MysqlGateway:
    
         def __init__(self):
    
             self.last_row_id = 0
    
         def db_conn(self):
    
             mysql_con = mysql.connector.connect(
                             host     = "prod-db.vultrdb.com",
                             user     = "app_user",
                             password = "strong-password",
                             database = "company_portal",
                             port     = "16751"                    
                          )
    
             return mysql_con
    
         def query(self, query_string, resource_id = ""):
    
             mysql_con  = self.db_conn()
             db_cursor  = mysql_con.cursor(dictionary = True)
    
             if resource_id == "":
                 db_cursor.execute(query_string)
             else:
                 db_cursor.execute(query_string, (resource_id,))
    
             return db_cursor.fetchall()
    
         def execute(self, query_string, data):
    
             mysql_con  = self.db_conn()
             db_cursor  = mysql_con.cursor(dictionary = True)
             db_cursor.execute(query_string, data)
             mysql_con.commit()
    
             self.last_row_id = db_cursor.lastrowid

    Save and close the file.

    In the above application:

    • import mysql.connector imports the MySQL connector for Python to your application

    • The MysqlGateway class defines the following methods:

      • __init__(self) executes every time you create a new instance of the MysqlGateway class and initializes the last_row_id variable to 0
      • db_conn(self) connects to the managed database and returns a reusable connection using the return mysql_con statement
      • query(self, query_string, resource_id = "") runs the SELECT SQL command and returns results from the database table as a dictionary with the column names and values
      • execute(self, query_string, data) runs the INSERT, UPDATE, and DELETE operations and returns the lastrowid when you insert a new record

Create the Products Resource Module

In the sample application database, you have a single products table. A mission critical-application can have hundreds of tables such as payment_methods, banks, customers, sales, and inventories. To organize your application data, create a resource module for every table. In this section, create the products resource module as described below.

  1. Create a new products.py file

     $ nano products.py
  2. Add the following contents to the file

     class Products:
    
         def __init__(self, mysql_gateway):
    
             self.dg = mysql_gateway
    
         def create(self, json_data):
    
             sql_query = "insert into products (product_name, retail_price) values (%s, %s)"   
    
             self.dg.execute(sql_query, (json_data["product_name"], json_data["retail_price"]))     
    
             return self.read(self.dg.last_row_id)
    
         def read(self, resource_id = ""):
    
             if resource_id == "" :
                 sql_query = "select * from products"
             else:
                 sql_query = "select * from products where product_id = %s" 
    
             resp = self.dg.query(sql_query, resource_id)     
    
             return resp
    
         def update(self, json_data, resource_id):
    
             sql_query = "update products set product_name = %s, retail_price = %s where product_id = %s"
             self.dg.execute(sql_query, (json_data["product_name"], json_data["retail_price"], resource_id))     
    
             return self.read(resource_id)
    
         def delete(self, resource_id):
    
             sql_query = "delete from products where product_id = %s"       
    
             self.dg.execute(sql_query, (resource_id,)) 
    
             return "Success"

    Save and close the file

    In the above module, the Products class has the following methods:

    • __init__(self, mysql_gateway) runs when you call the module for the first time. The method takes the mysql_gateway class instance as an argument to connect to the database for each CRUD operation
    • create(self, json_data) accepts data in JSON format and uses the insert into products (product_name, retail_price) values (%s, %s) SQL statement to insert the data to the products table by executing the self.dg.execute(...) from the mysql_gateway module
    • read(self, resource_id = "") runs the select * from products or select * from products where product_id = %s SQL statements to either return all products or a single product.
    • update(self, json_data, resource_id) runs the update products set product_name = %s, retail_price = %s where product_id = %s SQL command to update a product that matches the product_id
    • delete(self, resource_id) deletes a product that matches the given resource_id using the delete from products where product_id = %s SQL command

Create the main Application Entry Point

To use the database structure that includes a MySQL gateway class and products module, create the application's main function that executes when you run the application as described below.

  1. Create a new index.py file

     $ nano index.py
  2. Add the following contents to the file

     import http.server
     from http import HTTPStatus
     import socketserver
    
     import json
     import mysql_gateway
     import products
    
     class WebServerHandler(http.server.SimpleHTTPRequestHandler):
    
         def init_db(self):
    
             self.db_gateway = mysql_gateway.MysqlGateway()
    
         def write_http_output(self, resp):
    
             self.send_response(HTTPStatus.OK)
             self.send_header('Content-type', 'application/json')
             self.end_headers()
    
             self.wfile.write(bytes(json.dumps(resp, indent = 2) + "\r\n", "utf8")) 
    
         def do_POST(self):
    
             self.init_db()
    
             json_data = json.loads(self.rfile.read(int(self.headers['Content-Length'])))     
    
             http_resource = products.Products(self.db_gateway)
    
             self.write_http_output({"data": http_resource.create(json_data)})
    
         def do_GET(self):
    
             self.init_db()
    
             http_resource = products.Products(self.db_gateway)   
    
             resource_id = ""
    
             if len(self.path.split("/")) >= 3: resource_id = self.path.split("/")[2]   
    
             self.write_http_output({"data": http_resource.read(resource_id)})
    
         def do_PUT(self):
    
             self.init_db()
    
             json_data = json.loads(self.rfile.read(int(self.headers['Content-Length'])))  
    
             resource_id = ""
    
             if len(self.path.split("/")) >= 3: resource_id = self.path.split("/")[2] 
    
             http_resource  = products.Products(self.db_gateway)
    
             self.write_http_output({"data": http_resource.update(json_data, resource_id)})
    
         def do_DELETE(self):
    
             self.init_db()
    
             resource_id = ""
    
             if len(self.path.split("/")) >= 3: resource_id = self.path.split("/")[2] 
    
             http_resource  = products.Products(self.db_gateway) 
    
             self.write_http_output({"data": http_resource.delete(resource_id)})
    
     httpd = socketserver.TCPServer(('', 8080), WebServerHandler)
    
     print("Web server started at port 8080...")
    
     try:
    
         httpd.serve_forever()
    
     except KeyboardInterrupt: 
    
         httpd.server_close()
    
         print("Web server stopped.")

    Save and close the file

    In the above application code:

    • The import section declares the necessary modules to offer HTTP functionalities and imports the custom mysql_gateway and products modules you created earlier
    • The WebServerHandler() is a handler class for the HTTP server. Within the class, init_db() invokes your custom mysql_gateway module. Then, the write_http_output(self, resp) method sets the correct HTTP response headers for the web application.
    • The do_POST(self), do_GET(self), do_PUT(self), and do_DELETE(self) methods match each HTTP client request to the correct resource method
    • The httpd declaration starts an HTTP server that listens for incoming requests on port 8080 and directs the requests to the WebServerHandler() class

Test the Application

  1. List files in your working directory

     $ ls

    Output:

     index.py  mysql_gateway.py  products.py 

    Verify that the mysql_gateway.py, products.py, and index.py files are available

  2. Run the Python application as a background process

     $ python3 index.py &

    Output

     Web server started at port 8080...
  3. Establish a new SSH connection in a new terminal window and execute the following curl commands to test all CRUD operations:

  4. Using the curl utility, test the following application CRUD operations

    • Create a new product

        $ curl -X POST http://localhost:8080/ -H 'Content-Type: application/json' -d '{"product_name": "WIRE STRIPPER", "retail_price": 15.28}'

      Output:

        {
          "data": [
            {
              "product_id": 4,
              "product_name": "WIRE STRIPPER",
              "retail_price": 15.28
            }
          ]
        }
    • Retrieve all products in the database

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

      Output:

        {
          "data": [
           {
             "product_id": 1,
             "product_name": "8GB MEMORY CARD",
             "retail_price": 6.95
           },
           {
             "product_id": 2,
             "product_name": "2TB SSD",
             "retail_price": 300.0
           },
           {
             "product_id": 3,
             "product_name": "WI-FI DONGLE",
             "retail_price": 14.5
           },
           {
             "product_id": 4,
             "product_name": "WIRE STRIPPER",
             "retail_price": 15.28
           }
         ]
        }

      * Get a single product

            $ curl -X GET http://localhost:8080/products/1
      
        Output:
      
            {
              "data": [
                {
                  "product_id": 1,
                  "product_name": "8GB MEMORY CARD",
                  "retail_price": 6.95
                }
              ]
            }
    • Update product details. For example, the product ID 4

        $ curl -X PUT http://localhost:8080/products/4 -H 'Content-Type: application/json' -d '{"product_name": "WIRE STRIPPER", "retail_price": 23.50}'

      Output:

        {
          "data": [
            {
              "product_id": 4,
              "product_name": "WIRE STRIPPER",
              "retail_price": 23.5
            }
          ]
        }
    • Delete a product

        $ curl -X DELETE http://localhost:8080/products/2

      Output:

        {
          "data": "Success"
        }

Conclusion

You have used a Vultr Managed Database for MySQL for Python by creating a sample CRUD operation application that allows you to create, update, and delete database records. By integrating a managed database, you can concentrate development efforts on the Python application to offer more features and handle user interactions.

Next Steps

To implement more solutions using your Vultr Managed Database, visit the following resources: