Using API calls with VBA and Python to Read and Write to a Database

Using API calls with VBA and Python to write a database is actually a pretty decent architecture. Here, I would like to say that “It takes the best of the both worlds”, but the truth is that it takes the best from Python and somehow through a lot of “magic” it makes the VBA code works as well.

This is how the architecture looks like:

So, we obviously have 3 parts of the architecture:

• Excel with VBA, where the user writes the data and makes GET and POST api calls;
• Python with Flask, responsible to run a server and handle these api calls in a way to write these to a DB;
• A DB, which is supposed to store these calls;

Excel with VBA

The first part of the architecture consists of worksheets and VBA code. The worksheets have the input in a table:

This table is uploaded to the database with a POST api call, through the function MainUpload:

Sub MainUpload()
    
    Dim myLastRow As Long: myLastRow = lastRow(tblRanking.Name)
    Dim http As New MSXML2.XMLHTTP60
    Dim i As Long
    
    For i = 2 To myLastRow
        Dim jsonPerLine As String: jsonPerLine = GenerateJson(i, tblRanking)
        
        http.Open "POST", "http://127.0.0.1:4000/football_ranking", False
        http.setRequestHeader "Content-Type", "application/json"
        http.send jsonPerLine
        
        If http.Status = 201 Then
            Application.StatusBar = "201 OK -> " & tblRanking.Cells(i, 2)
        Else
            Err.Raise 999, Description:="Line " & i & " Error!"
        End If
    Next i
    
    Application.StatusBar = ""
    
End Sub
 
Public Function GenerateJson(i As Long, tbl As Worksheet) As String
    
    Dim result As String: result = "{"
    Dim lastCol As Long: lastCol = LastColumn(tbl.Name)
    Dim myCell As Range
    
    For Each myCell In tbl.Range(tbl.Cells(i, 1), tbl.Cells(i, lastCol))
        With WorksheetFunction
            Dim myKey As String: myKey = .Trim("""" & tbl.Cells(1, myCell.Column) & """")
            Dim myVal As String: myVal = .Trim("""" & myCell.Value & """")
        End With
        
        If myCell.Column = lastCol Then
            result = result & myKey & ":" & Trim(myVal) & "}"
        Else
            result = result & myKey & ":" & Trim(myVal) & ","
        End If
    Next
    
    GenerateJson = result
        
End Function

Well, obviously the function above hopes that there is a running server at http://127.0.0.1:4000 and calling a POST to it would do something. The function GenerateJson()  makes JSON, based on the table column names and the i-th value.

Additionally, there is a function called MainDownload() , which looks like this:

Sub MainDownload()
    
    Dim http As New MSXML2.XMLHTTP60
    http.Open "GET", "http://127.0.0.1:4000/football_ranking", False
    http.send
    
    Dim allTeams As Object
    Set allTeams = JsonConverter.ParseJson(http.responseText)
    
    tblDb.Cells.Clear
    Dim r As Long: r = 1
    Dim c As Long: c = 1
    Dim team As Object
    
    For Each team In allTeams
        Dim element As Variant
        For Each element In team
            If r = 1 Then
                tblDb.Cells(r, c) = element
            Else
                tblDb.Cells(r, c) = team(element)
            End If
            c = c + 1
        Next
        r = r + 1
        c = 1
    Next
    
End Sub

It uses the JsonConvertor class from vba-tools, which actually works pretty decent (thumbs up for the documentation, Tim). What it does is to take the http.responseText and to write it to a worksheet, which has been cleared previously with tblDb.Cells.Clear. So far so good.

Python + Flask, running a server and serving the API

Here the story is a bit easier. Somehow, Python is used to run servers and make API calls. Thus, in general we are having plenty of libraries and two files – main.py  and FootballRankingModel.py. I hope that it is easier to understand which one makes what as these two are really small. Anyway, run main.py  to start the local server.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import json
from settings import app

db = SQLAlchemy(app)


class FootballRanking(db.Model):
    __tablename__ = 'football_ranking'
    Id = db.Column(db.Integer, primary_key=True)
    Name = db.Column(db.String(100), nullable=False)
    Wins = db.Column(db.Integer, nullable=False)
    Coach = db.Column(db.String(100))

    def json(self):
        return {
            'Id': self.Id,
            'Coach': self.Coach,
            'Wins': self.Wins,
            'Name': self.Name,
        }

    def __repr__(self):
        football_ranking_object = {
            'Id': self.Id,
            'Name': self.Name,
            'Wins': self.Wins,
            'Coach': self.Coach,
        }
        return json.dumps(football_ranking_object)

    def add_line(_name, _wins, _coach):
        new_team_line = FootballRanking(
            Name=_name,
            Wins=_wins,
            Coach=_coach
        )
        db.session.add(new_team_line)
        db.session.commit()

    def get_all_lines():
        return [FootballRanking.json(line) for line in FootballRanking.query.all()]

import json
from flask import Flask, jsonify, request, Response
from FootballRankingModel import *
from settings import *


def football_ranking_object_is_valid(football_ranking):
    print(football_ranking)
    if ("Wins" in football_ranking) and ("Coach" in football_ranking) and ("Name" in football_ranking):
        return True
    else:
        print("Error from main.football_ranking_object_is_valid")
        return False


# GET /football_ranking
@app.route('/football_ranking')
def get_football_ranking():
    return jsonify(FootballRanking.get_all_lines())

# POST /football_ranking
@app.route('/football_ranking', methods=['POST'])
def add_line():
    request_data = request.get_json()
    if football_ranking_object_is_valid(request_data):
        print (request_data['Name'])
        FootballRanking.add_line(
            request_data['Name'],
            request_data['Wins'],
            request_data['Coach'])
        response = Response("OK", 201, mimetype='application/json')
        return response
    else:
        invalid_football_ranking_object = {
            "error": "Invalid ns_cos object passed in the request!",
            "help_string": "Hello dear friend! Data passed should be a bit similar to the expected JSON!"
        }
        response = Response(json.dumps(invalid_ns_cos), status=400, mimetype='application/json')
        return response


app.run(port=4000, debug=True)

Database

For the database part I have left 2 things – the SQL, used to create the table football_ranking and the settings.py, which shows the path to the Flask app and the DB.

from flask import Flask

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///football_db.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

CREATE TABLE "football_ranking" (
	"Id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"Name"	TEXT NOT NULL,
	"Wins"	INTEGER NOT NULL,
	"Coach"	TEXT NOT NULL
)

At the end conda env export > requirements_py37.yml  would export all the libraries and packages, used for the project.

Enjoy everything in Git – https://github.com/Vitosh/Python_personal/tree/master/PythonProjects/VBA_API!