How to Use Vultr Managed Databases for MySQL in Python

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:

• Deploy a Vultr Managed Database for MySQL

• Install the MySQL client tool on your development machine

  Depending on your computer operating system, use a package manager such as brew on macOS, apt on Ubuntu/Debian systems, dnf on RHEL systems, among others to install the MySQL client tool. For example, on Ubuntu, run the following command:

    $ sudo apt install mysql
  

• Update the Python Pip package manager

    $ pip install --upgrade pip

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.