Creating a Python Application Using Oracle MySQL Cloud Service on Oracle Application Container Cloud Service

Purpose

This tutorial shows you how to create a simple REST service in Python using the Flask framework and Oracle MySQL Cloud Service and how to deploy the application on Oracle Application Container Cloud Service.

Time to Complete

45 minutes

Background

Oracle Application Container Cloud Service lets you deploy Java SE, Node.js, PHP, and Python applications to the Oracle Cloud.

What Do You Need?

• Access to an instance of Oracle Application Container Cloud Service
• A storage replication policy for your service instance
• An SSH key pair. See Creating SSH Keys for Use with Oracle Cloud Services
• Python 3.6+
• The package management system pip
• cURL 7.0+ with SSL support

1. Create the python-service directory in your local system to store the project files.

2. In python-service directory, create the app.py file and add the following content:

   
                             #!/usr/bin/env python
   from __future__ import print_function
   from flask import Flask, jsonify, abort, request
   from flask_cors import CORS, cross_origin
   
   import logging
   import pymysql
   import os
   
   app = Flask(__name__)
   CORS(app)
   cors = CORS(app, resources={r"/api/*": {"origins": "*","methods":"POST,DELETE,PUT,GET,OPTIONS"}})
   
   def creatConnection():
       # Read MySQL Environment Parameters
       connectString = os.environ.get('MYSQLCS_CONNECT_STRING', 'localhost:3306/myDB') 
       hostname = connectString[:connectString.index(":")]
       database = connectString[connectString.index("/")+1:]
       conn = pymysql.connect(host=hostname, 
   	                       port=int(os.environ.get('MYSQLCS_MYSQL_PORT', '3306')), 
   						   user=os.environ.get('MYSQLCS_USER_NAME', 'root'), 
   						   passwd=os.environ.get('MYSQLCS_USER_PASSWORD', ''), 
   						   db=database,
   						   cursorclass=pymysql.cursors.DictCursor)
       return conn;
   
   @app.route('/')
   def index():
       return 'The application is running!'
   	
   @app.route('/employees/setupdb')
   def setupDB():
       conn = creatConnection()
       cur = conn.cursor()
       cur.execute('''CREATE TABLE EMPLOYEE (
   				  ID INTEGER NOT NULL AUTO_INCREMENT,
   				  FIRSTNAME VARCHAR(255),
   				  LASTNAME VARCHAR(255),
   				  EMAIL VARCHAR(255),
   				  PHONE VARCHAR(255),
   				  BIRTHDATE VARCHAR(10),
   				  TITLE VARCHAR(255),
   				  DEPARTMENT VARCHAR(255),
   				  PRIMARY KEY (ID)
   				  ); ''') 
       conn.commit()
       cur.close()
       conn.close()
       return 'The EMPLOYEE tables was created succesfully'
   	
   @app.route('/employees')
   def employees():
       conn = creatConnection()
       cur = conn.cursor()
       cur.execute('''SELECT ID, FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT FROM EMPLOYEE''')
       results = cur.fetchall()	
       cur.close()
       conn.close()
       return jsonify( results)	
   
   @app.route('/employees/<int:employee_id>', methods=['GET'])
   def get_employee(employee_id):
       conn = creatConnection()
       cur = conn.cursor()
       cur.execute('''SELECT ID, FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT FROM EMPLOYEE WHERE ID = %s'''%(employee_id))
       rv = cur.fetchone()    
       if rv is None:
           abort(404)
       cur.close()
       conn.close()
       return jsonify( rv)	
   
   @app.route('/employees', methods=['POST'])
   def create_employee():
       conn = creatConnection()
       cur = conn.cursor()
       try:
           cur.execute('''INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT) 
   	                VALUES('%s','%s','%s','%s','%s','%s','%s') '''%(request.json['firstName'],request.json['lastName'],
   				    request.json['email'],request.json['phone'],request.json['birthDate'],request.json['title'],request.json['dept']))    
           conn.commit()
           message = {'status': 'New employee record is created succesfully'}
           cur.close()	 
       except Exception as e:
           logging.error('DB exception: %s' % e)
           message = {'status': 'The creation of the new employee failed.'}
       conn.close()
       return jsonify(message)
   
   @app.route('/employees/<int:employee_id>', methods=['PUT'])
   def update_employee(employee_id):
       conn = creatConnection()
       cur = conn.cursor()
       try:
           cur.execute('''UPDATE EMPLOYEE SET FIRSTNAME='%s', LASTNAME='%s', EMAIL='%s', PHONE='%s', BIRTHDATE='%s', TITLE='%s', DEPARTMENT='%s' 
   	               WHERE ID=%s '''%(request.json['firstName'],request.json['lastName'],
   				   request.json['email'],request.json['phone'],request.json['birthDate'],request.json['title'],request.json['dept'],employee_id))    
           conn.commit()
           message = {'status': 'The employee record is updated succesfully'}
           cur.close()	 
       except Exception as e:
           logging.error('DB exception: %s' % e)	
           message = {'status': 'Employee update failed.'}
       conn.close()
       return jsonify(message)
   
   @app.route('/employees/<int:employee_id>', methods=['DELETE'])
   def delete_employee(employee_id):
       conn = creatConnection()
       cur = conn.cursor()
       try:
           cur.execute('''DELETE FROM EMPLOYEE WHERE ID=%s '''%(employee_id))    
           message = {'status': 'The employee record is deleted succesfully'}
           conn.commit()
           cur.close()	 
       except Exception as e:
           logging.error('DB exception: %s' % e)	
           message = {'status': 'Employee delete failed.'}
       conn.close()
       return jsonify(message)
   
   if __name__ == '__main__':
         app.run(host='0.0.0.0', port=os.environ.get('PORT', '8080'))
   
   

1. In python-service directory, create the start.sh file and add the following content:

   #!/bin/sh
   export PYTHONPATH=modules
   python app.py 

2. Create the manifest.json file and add the following content:

   {
     "runtime": {
       "majorVersion": "3.6.0"
     },
     "command": "sh ./start.sh",
     "isClustered": true,
     "release": {
       "version": "1.0"
     },
     "notes": "Python App using Flask and MySQL"
   }
   

3. Create the requeriments.txt file and add the dependencies:

   flask==0.12.1
   pymysql==0.7.11 

4. Open a command-line window (or terminal in Linux).

5. Go to the python-service directory and install the project dependencies:

   pip install -r requirements.txt -t modules

6. Create the python-service-dist.zip file with the content of the python-service directory.

   zip -r python-service-dist.zip app.py start.sh manifest.json modules requirements.txt

In this section, you create an Oracle MySQL Cloud Service instance using the user interface. When you create your Oracle MySQL Cloud Service instance, you need to provide a secure shell (SSH) public key to establish secure connections. If you don't have one, see the "What do you need?" section of this tutorial.

You need the public key when creating a service instance, and you need the paired private key when you access it using the ssh utility.

1. Log in to Oracle Cloud at http://cloud.oracle.com/. Enter the identity domain, user name, and password for your account.

2. In the Oracle Cloud Services dashboard click and select Oracle MySQL Cloud Service.

3. To open the Oracle MySQL Cloud Service console, click Services.

4. Click Create Service.

5. Enter or select the following information, and then click Next:

   • Service Name: TestDB
   • Service Description (optional)
   • Metering Frequency: Hourly

6. Enter or select the following information, and then click Next:

   • Compute Shape: OC3-1.0 OCPU, 7.5GB RAM
   • SSH Public Key (Enter the public key.)
   • Usable Database Storage(GB):25
   • Administration User: root
   • Administration Password
   • Confirm Administration Password
   • Database Schema Name: mydatabase
   • Server Character Set: utf8mb4 - UTF-8 Unicode
   • Configure MySQL Enterprise Monitor: No
   • Backup Destination: None

7. Review the details of your instance and then click Create.

8. Wait until the instance is created.

   

1. Go back to your Oracle Cloud account, click , and select Oracle Application Container Cloud Service.

2. To open the Oracle Application Container Cloud Service console, click Services.

3. In the Applications list view, click Create Application.

4. Click Python.

5. In the Application section, enter a name for your application and click Browse next to Archive.

6. In the File Upload window, select the python-service-dist.zip file and click Open.

7. If your account has access to Oracle Cloud Infrastructure regions, in the Region field select the same region that you used to create your Oracle Database Cloud Service instance.

8. Keep the default values in the Topology section and click Create.

9. Wait until the application is completely created.

   Your application could take a few minutes to complete the process.

   

1. In the Applications list view, click your application.

2. Click the Deployments tab and in the Service Binding section, click Add.

3. In the Add Service Binding dialog box, select MySQLCS Service in the Service Type field, enter or select values in the Service Name, Username, and Password fields, and click Save.

4. In the Deployments dialog box, click Apply Edits.

5. Copy the URL of your application, you'll use it in the next section.

   

1. Open a command-line window (or terminal in Linux).

2. Create the EMPLOYEE table. Replace the app_endpoint placeholder with the URL of your application.

   curl -X GET -i app_endpoint/employees/setupdb

3. Create an employee.

   curl -X POST -i -H "Content-Type: application/json" -d "{\"firstName\":\"Luis\",\"lastName\":\"Gray\",\"email\":\"john.gray@example.com\",\"phone\":\"1203123\",\"birthDate\":\"1975-05-14\",\"title\":\"Developer Manager\",\"dept\":\"IT\"}" app_endpoint/employees
   

4. Get all the employees.

   curl -X GET -i app_endpoint/employees

5. Update the employee.

   curl -X DELETE -i app_endpoint/employees/1

6. Get the employee by ID.

   curl -X GET -i app_endpoint/1
   

• Oracle Application Container Cloud Service in the Oracle Help Center

• Flask website flask.pocoo.org

• PyMysql website pymysql.readthedocs.io