Append Values To Dictionary In REST API Using Python

{
  "data": [
    {
      "Code": "AX001",
      "Date": "29.10.2022",
      "Designation": "Architect",
      "EmployeeId": "1001",
      "Location": "UK",
      "Name": "Vijai"
    },
    {
      "Code": "AX001",
      "Date": "29.10.2022",
      "Designation": "Manager",
      "EmployeeId": "1002",
      "Location": "India",
      "Name": "Anand"
    },
    {
      "Code": "AX001",
      "Date": "29.10.2022",
      "Designation": "CEO",
      "EmployeeId": "1003",
      "Location": "USA",
      "Name": "Advik"
    },
    {
      "Code": "AX001",
      "Date": "29.10.2022",
      "Designation": "CTO",
      "EmployeeId": "1004",
      "Location": "India",
      "Name": "Adhvik"
    }
  ]
}

mkdir AppendItemsToDic
cd .\AppendItemsToDic

Open AppendItemsToDic folder in Visual Studio Code. Click Terminal -> New Terminal.

Step 2

Run the following command to install the packages required for this project.

pip install flask
pip install flask-restful
pip install pyodbc

• flask is a simple framework for building complex web applications.
• flask-restful is an extension for Flask that adds support for quickly building REST APIs
• pyodbc is an open source Python module that makes accessing ODBC databases simple.

Task 3: Implement the REST API

In this task, you will see how to implement the REST API to read the Microsoft SQL table and send the results as REST API JSON response.

Step 1

In Visual Studio Code, create a new file app.py under AppendItemsToDic folder.

Step 2

Copy and paste the below code.

from flask import Flask, jsonify
from flask_restful import Resource, Api, request
import pyodbc 
from datetime import date

# Input Parameters
getdatacmd='SELECT id AS EmployeeId, name AS Name, designation AS Designation, location AS Location FROM Employees'
connstring='DRIVER={ODBC Driver 18 for SQL Server};SERVER=LHR-**********\SQLEXPRESS;DATABASE=demo;ENCRYPT=no;Trusted_Connection=yes;'
today=date.today().strftime('%d.%m.%Y')

# Create the flask app
app = Flask(__name__)
# Create an API object
api = Api(app)

# Class for GetData
class GetData(Resource):
	# GET Request
	def get(self):        
		cnxn = pyodbc.connect(connstring)
		cursor = cnxn.cursor()
		cursor.execute(getdatacmd)	
		columns = [column[0] for column in cursor.description]
		results = []	
		rows = cursor.fetchall()
		for row in rows:
			value=dict(zip(columns, row))					
			value.update({'Date': today})
            # Get the values from query parameter
            # /?code='<value>'
			value.update({'Code': request.args['code']})		
			results.append(value)		
		return jsonify({'data': results})

# Add the defined resources along with their corresponding urls
api.add_resource(GetData, '/')

# Driver function
if __name__ == '__main__':

	app.run(debug = True)

Task 3: Test the API

In this task, you will see how to test the API which returns the SQL table results as JSON response.

Step 1

In Visual Studio Code, run the following command in the Terminal.

python .\app.py 

Step 2

Open the browser and access the below URL to view the JSON response as shown below.

http://127.0.0.1:5000/?Code=AX001

References

1. Flask-RESTful documentation
2. Python SQL Driver - pyodbc

Summary

This article describes how to read Microsoft SQL table and append additional values to the result which will be sent as REST API JSON response using Python.