Python Flask Simple API

Photo by Anete Lusina from Pexels

Overview

This is an example of a small API with Python Flask to return a JSON object. Then we will query an MS SQL Server database and return the data as a JSON object.

First install Flask

pip install -U Flask

Them install pyodbc

pip install pyodbc

Create a file labeled flaskAPI.py.

You will need to import the following:

from flask import Flask, render_template
from flask import jsonify
import pyodbc 
import json

Use the connection from previous articles.

connection = pyodbc.connect("Driver={SQL Server Native Client 11.0};"                       "Server=DESKTOP-JLUMQNO;"                       "Database=VirtualInvestor;"                       "Trusted_Connection=yes;")

Flask Routing

@app.route('/', methods=['GET'])
def index():
    return jsonify({'message': 'Hello, World!'})

For converting the SQL query result to JSON, get the row headers and append then to an array. Put this under a new route and function labeled getdata.

@app.route('/getdata', methods=['GET']) def getdata():     cursor = connection.cursor()     cursor.execute('SELECT * FROM Companies')     row_headers=[x[0] for x in cursor.description] #this will extract row headers     rv = cursor.fetchall()     connection.close()     json_data=[]     for result in rv:         json_data.append(dict(zip(row_headers,result)))     return json.dumps(json_data)

Putting it all together with the Debug Option ON

from flask import Flask, render_template from flask import jsonify import pyodbc  import json connection = pyodbc.connect("Driver={SQL Server Native Client 11.0};"                       "Server=DESKTOP-JLUMQNO;"                       "Database=VirtualInvestor;"                       "Trusted_Connection=yes;") app = Flask(__name__) @app.route('/', methods=['GET']) def index():     return jsonify({'message': 'Hello, World!'}) @app.route('/getdata', methods=['GET']) def getdata():     cursor = connection.cursor()     cursor.execute('SELECT * FROM Companies')     row_headers=[x[0] for x in cursor.description] #this will extract row headers     rv = cursor.fetchall()     connection.close()     json_data=[]     for result in rv:         json_data.append(dict(zip(row_headers,result)))     return json.dumps(json_data)
 if name == "main":
     app.run(debug=True)

Run the application

python flaskAPI.py

   Use a production WSGI server instead.
 * Debug mode: on
 * Restarting with stat
 * Debugger is active!
 * Debugger PIN: 233-028-411
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

Run http://127.0.0.1:5000

You should see JSON data in your browser

Then try http://127.0.0.1:5000/getdata