Simple API using Python, Flask and pyodbc

Hi Guys,

I recently learnt the fundamentals of python, one of widely used languages right now. It is super fast in executing and I recommend everyone to give it a try.

So I decided to the create a Simple API which will connect to a SQL Server database to perform CRUD operations. Hope you enjoy.

1) Make sure you have installed python

Go to command prompt type "python --version", it will return the installed version of python in your computer as shown in the image. If it throws an error, don't worry you can go to https://www.python.org/downloads/ to download the latest version of python to your pc

2) Install pyodbc to connect to SQL Server Database

Go to command prompt type "pip install pyodbc"

3) Install flask to create the api application

Go to command prompt type "pip install flask"

4) Open Visual Studio Code and create a new file "sqlapi.py" and paste the following code

import pyodbc 
import flask
import json
import datetime 
from flask import request, jsonify

app = flask.Flask(__name__)
app.config["DEBUG"] = True

#Constant Strings
#Connection String
conStr = """Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyLocations;
Trusted_Connection=yes;"""
#Select Statement
selectStr = """SELECT * FROM dbo.Client"""
#Insert Statement
insertStr = """INSERT INTO dbo.Client([Code],[Name],[ContactNo],[Location],[Gender],
[DateOfBirth],[FoodOrientation],[StartingWeight],[Goal],[CreatedOn],[CreatedBy]) 
VALUES (?,?,?,?,?,?,?,?,?,?,?)"""
#Update Statement
updateStr = """UPDATE dbo.Client SET [Code]=?,[Name]=?,[ContactNo]=?,
[Location]=?,[Gender]=?,[DateOfBirth]=?,[FoodOrientation]=?,[StartingWeight]=?,
[Goal]=?,[ModifiedOn]=?,[ModifiedBy]=? WHERE ID = ?"""
#Delete Statement
deleteStr = """DELETE FROM dbo.Client"""

#Home Route
@app.route('/', methods=['GET'])
def home():
    return '''<h1>Client Archive</h1>
<p>A prototype API for Clients.</p>'''

#404 Route
@app.errorhandler(404)
def page_not_found(e):
    return "<h1>404</h1><p>The resource could not be found.</p>", 404

#New Client Route
@app.route('/api/v1/resources/newclient', methods=['POST'])
def api_newclient():
    client = request.get_json()
    if client:
        conn = pyodbc.connect(conStr)
        cursor = conn.cursor()
        cursor.execute(insertStr, 
        client["Code"],       
        client["Name"],
        client["ContactNo"],
        client["Location"],
        client["Gender"],
        client["DateOfBirth"],
        client["FoodOrientation"],
        client["StartingWeight"],
        client["Goal"],
        datetime.datetime.now(),
        client["CreatedBy"])
        conn.commit()  
        #Get the Auto Generated ID and Send it back to the Client
        row = cursor.execute("SELECT @@IDENTITY").fetchone()
        if row: 
            client["ID"] = str(row[0])
    else:
        return jsonify({"Error":"No Client JSON field provided. Please specify 
        Client JSON."})        


    return jsonify(client)

#Save Client Route
@app.route('/api/v1/resources/saveclient', methods=['POST'])
def api_saveclient():
    client = request.get_json()

    if client:       
        if 'ID' in client:
            id = client['ID']
        else:
            return jsonify( {"Error":"No id field provided. Please specify an id."})  
             
 
        conn = pyodbc.connect(conStr)
        cursor = conn.cursor()
        cursor.execute(updateStr, 
        client["Code"],       
        client["Name"],
        client["ContactNo"],
        client["Location"],
        client["Gender"],
        client["DateOfBirth"],
        client["FoodOrientation"],
        client["StartingWeight"],
        client["Goal"],
        datetime.datetime.now(),
        client["ModifiedBy"],
        id)
        conn.commit()  
    else:
        return jsonify({"Error":"No Client JSON field provided. 
        Please specify Client JSON."})    


    return jsonify(client)


#Get All Clients Route
@app.route('/api/v1/resources/clients', methods=['GET'])
def api_all():
    conn = pyodbc.connect(conStr)
    cursor = conn.cursor()
    cursor.execute(selectStr)
    result = {}
    data = [] 

    for row in cursor.fetchall():            
        for i, crow in enumerate(cursor.columns(table='Client')):        
            result[crow.column_name] = str(row[i])          
        data.append(result)
        result = {}
        
    return jsonify(data)

#Get Client Route
@app.route('/api/v1/resources/client', methods=['GET'])
def api_client():

    if 'id' in request.args:
        id = request.args['id']
    else:
        return jsonify( {"Error":"No id field provided. Please specify an id."})  


    conn = pyodbc.connect(conStr)
    cursor = conn.cursor()
    cursor.execute(selectStr + ' where ID= ?',id)
    result = {}
    for row in cursor:
        for i, crow in enumerate(cursor.columns(table='Client')):
            result[crow.column_name] = str(row[i])     


    return jsonify(result)

#Delete Client
@app.route('/api/v1/resources/deleteclient', methods=['DELETE'])
def api_deleteclient():

    if 'id' in request.args:
        id = request.args['id']
    else:
        return jsonify({"Error":"No id field provided. Please specify an id."})  


    conn = pyodbc.connect(conStr)
    cursor = conn.cursor()
    cursor.execute(deleteStr + ' where ID= ?',id)
    conn.commit()


    return jsonify({"Success":"Client with ID: "+id+" deleted successfully" })

#Start Flask Server
app.run()