Walton API Explained

Below I will break down the inner workings of the main class.

First we import all our required libraries and declare our flask app.

from http import server
import os
from flask import Flask, jsonify
import pyodbc
import sqlite3

app = Flask(__name__)

Then we establish a database connection using pyodbc and use environment variables to login. Environment variables are a common way to store sensitive information, such as passwords, in a secure manner. Environment variables are like variables in programming languages, which store a value that can be used by applications. The advantage of using environment variables to store sensitive information is that the information is not stored directly in the code or configuration files, so it is not easily accessible to anyone who might view the code or configuration files.

def connect_db():
    #con = sqlite3.connect('crosswalk.db')
    con = pyodbc.connect(database=os.environ.get('DB_NAME'), uid=os.environ.get('DB_USER_NAME'), pwd=os.environ.get('DB_PWD'), server=os.environ.get('DB_HOST'), driver='ODBC Driver 18 for SQL Server', TrustServerCertificate='yes')
    return con

Then we define a function called sql_query that takes in a parameter called y. The function then establishes a connection to a database using the connect_db function. Next, the code creates a cursor, which is used to execute a SQL query provided as the y parameter. The code then prints the query, executes it using the cursor, and retrieves the query results as a list of rows. Each row is represented as a tuple, with the values in the tuple corresponding to the columns in the query results. The column names are also extracted from the query results and stored in a list called row_headers. Finally, the list of rows is stored in a variable called tables.

def sql_query(y):
    con = connect_db()
    cur = con.cursor()
    print(f"{y}")
    cur.execute(f"{y}")
    row_headers=[x[0] for x in cur.description]
    tables = cur.fetchall()

Then we convert the rows from our SQL query into a JSON object. The code first creates an empty list called jsontable. It then iterates over the list of rows, which is stored in the tables variable. For each row, the code creates a dictionary that pairs the column names with the values in the row. This dictionary is then added to the jsontable list. Finally, the code uses the jsonify function to convert the list of dictionaries into a JSON object, which is then returned by the function. This allows the query results to be easily accessed and manipulated in a JSON format.

jsontable = []
    for result in tables:
        jsontable.append(dict(zip(row_headers, result)))
    return jsonify(jsontable)