So you need to build an API? You know some Python, have worked with pandas, and coded some SQL. Well, the good thing about all those skills, is you have the requirements to make an API, quickly!

Building an API with Python

There are a few routes you can go when building an API in Python. Probably the two most common are Django and Flask. They both have their pros and cons, but that's not for this article to discuss. We're going to utilize Flask. There are a few reasons for this, but the primary reason is: Flask is lightweight. This results in quite a few benefits:

• Quick development time (for less complex apps)

• Easier to learn

• Flexibility - this one in particular can be both a pro and a con (depending on who you are talking to), because of Flask's lightweight nature, you often have to build most features by hand, but it gives you more granular control of your app

Our Use Case

For the purpose of this post, let's assume we work for a credit card company. They have a customer service team that has expressed complaints with the existing application they utilize to work with customers. You have been tasked with building a prototype application that has the following requirements:

• Customer search feature

• Pull the last 500 charges for a customer

• Summarize the customer's account for amount owed and the last payment date

Before you build the front end of the application, you need to build the backend API that will serve the information up to the front end.

Requirements

Before we get started, let's go through some of the basic requirements for building out our API, besides Python itself, you'll need the following:

• IDE - your choice of an integrated development environment, I generally utilize VSCode, but there are plenty of other options out there

• Rest Client (API Testing)

  • Postman - Postman is probably one of the most comprehensive API testing tools out there, and personally one of my favorite development tools

  • Thunder Client - this is an inbuilt option in VSCode, if you don't want to install another app outside of VSCode, this is a pretty great option

• Python Modules (required)

  • Flask python -m pip install flask

  • Pandas python -m pip install pandas

  • Python database module for your respective provider

    • Microsoft SQL Server - pyodbc
      python -m pip install pyodbc

    • Postgresql - psycopg2 (or psycopg2-binary if you have issues installing)
      python -m pip install psycopg2

    • mysql-connector-python - MySQL
      python -m pip install mysql-connector-python

    • The above ones are pretty common, however, if you have another database provider, do some research for their corresponding database module

• Python Modules (optional)

  • SQLAlchemy - this is the recommended module to utilize when interacting with databases in pandas, it also has plenty of other features as your application scales that you might find useful
    python -m pip install sqlalchemy

  • Dotenv - this is a great tool for creating application-specific configuration files (in the form of .env files), this is not required, but something I highly recommend as it's a good way to develop, as you can store confidential/sensitive information such as database users and passwords, and import it into your app - here's a post discussing .env files
    python -m pip install python-dotenv

As well, as part of the development process, you can also create a Python virtual environment. A virtual environment helps you segregate your application's Python environment (with all required modules) from your core Python installation (the main one installed on your PC). Utilizing a virtual environment is a recommended practice when developing a Python application, so if you haven't used one, I highly recommend it!

Here is a post about virtual environments. It goes through the importance of virtual environments, and installation on linux. As well, you can read this smaller article that's more general and covers linux and windows.

Coding our API

To get started you need to create a new application root folder. So in a location you would like to work in, create a new folder. For my application I'm just going to call it "charges_api". Open that folder in your IDE of choice, and within that folder we're going to create a couple things:

• Our main app file app.py

  • This is where we will initiate our app, and run it. As well, we're going to create a response at root of our API, so we know it's running

• Python Virtual Environment (optional) - please refer to the links above in the requirements section if you would like to create a virtual environment

Once we're done, our app.py should look something like the following:

from flask import Flask

app = Flask(__name__)

@app.route("/")
def app_root():
    return "API is running", 200

if __name__ == '__main__':    
    app.run()

As you can see, we added a decorator using the app object to the method app_root() - when using Flask, the @app.route() will mark that method to be the "response" at that particular portion of the application URL. So in this example, at our localhost, the response at http://localhost:5000/ is "API is running".

If we wanted that response at a different part of the API, we could adjust the route to be something like @app.route("/main"), we could get the same response at http://localhost:5000/main.

This feature will be utilized as we scale out our app, and you'll definitely see more of it as you code more in Python.

When you create a method with a route, it has to return 2 different things:

• The response itself, this can be any type, as long as it can be converted into a string - this can be anything from json to html

• A response code - this is a typical HTTP code, so a successful response will always return a 200

Our folder structure should look something like:

• charges_api/
  ├── app.py
  └── env/

If you want to test that it's all working, open bash at the "charges_api" folder, enable your virtual environment (if you have one), and type python -m app - if everything works, it should look something like the following:

You can also open a web browser (or utilize Postman or another API testing tool), and test the root method:

Blueprints

To build larger applications when using Flask, you'll want to utilize blueprints. Blueprints serve as a framework so you can build additional components and routes that you add to your app without having to build everything in your app.py file.

We're going to create a folder for our blueprints, and then we're going to create blueprints for our application. For our app, we need a couple blueprints:

• One for our user search feature - we'll create a blueprint for this in person.py

• One for our charges features - we'll create a blueprint for this in charges.py

Once our folders/files are created, the folder structure will look like:

• charges_api/
  ├── app.py
  ├── blueprints/
  │ ├── charges.py
  │ └── person.py
  └── env/

Within both of our blueprints, we're going to start the same way, we're going to import the flask Blueprint module, then we're going to define our blueprints. We're also going to define some routes for our blueprints. It'll be very similar to how our app_root() got defined, with a couple other features:

• person.py - for persons we just need one route, one for our search feature

    from flask import Blueprint
  
    blueprint = Blueprint("Person Blueprint",__name__)
  
    @blueprint.route("/person")
    def person():
        return "Person Endpoint", 200
  

  • For this, we'll define our route as "/person", and for now we'll just keep our endpoint returning "Person Endpoint", 200

• charges.py - very similar to person.py but we need two routes, one to pull the most recent 500 charges for a customer, and a summary of charges (current amount owned, and last payment date)

    from flask import Blueprint
  
    blueprint = Blueprint("Charges Blueprint",__name__)
  
    @blueprint.route("/charges/<personid>")
    def charges(personid:int):
        return "Charges Endpoint", 200
  
    @blueprint.route("/charges/<personid>/summary")
    def charges_summary(personid:int):
        return "Charges Summary Endpoint", 200
  

  • As you'll notice with these two endpoints we're adding <personid> to our route. This means we're defining a variable path. Someone can pass the personid field within the url to affect the response. This will make more sense as we start to build it out more, but just know with a variable URL, we have to define our route method with the same variable, so we've defined our charges() and charges_summary() with the personid:int

To add our blueprints to our app, we import them and register them within the app.py file:

from flask import Flask
from blueprints.person import blueprint as person_blueprint
from blueprints.charges import blueprint as charges_blueprint

app = Flask(__name__)
app.register_blueprint(person_blueprint)
app.register_blueprint(charges_blueprint)

@app.route("/")
def app_root():
    return "API is running", 200

if __name__ == '__main__':    
    app.run()

Let's run our app and check that our blueprints are added:

All our endpoints are functioning! So now let's get to the real nitty gritty of the API development, and build the backend logic!

Our Backend Logic

The Database

Our operational database is a postgres database, and it consists of two separate tables:

• users.person - this houses user/individual level demographic information such as first name, last name, date of birth, and address information. As well, it's going to house our unique person identifier (personid)

• users.charges - this houses all charges and payments associated to a particular person via personid, it simply houses the charge category, charge date, and charge amount.

We'll need to utilize these two tables to generate our endpoints.

Person Blueprint

Our person blueprint needs to allow for searching of people within our database. We'll allow the front end to pass first name, last name, state (either full name or abbreviation), and date of birth.

As well, we'll need to define our connection object to our database using SQLAlchemy.

We'll start off by importing some additional modules to support building our blueprint:

• pandas

• sqlalchemy

• dotenv

• os

• request

Our import at the top of our person.py file will look something like this:

from flask import Blueprint, request
import pandas as pd
import sqlalchemy
import dotenv
import os

Next, let's create a SQLAlchemy engine that we can utilize to connect to the database. We also need to load our .env file that contains our environmental variables.

#load .env file that houses our database information
dotenv.load_dotenv()

#define sql alchemy connection url
connection_url = (
    sqlalchemy.URL.create(
            drivername='postgresql+psycopg2'
            ,username=os.environ.get("DBUSER")
            ,password=os.environ.get("DBPASSWORD")
            ,host=os.environ.get("DBHOST")
            ,database=os.environ.get("DBNAME")
            ,query={"sslmode":"require"}
        )
)
#create the sql alchemy engine based on the connection URL
engine = sqlalchemy.create_engine(connection_url)

Now that we have all this information defined, we can work on our function to search our database for persons. We'll utilize the "request" module to pull the parameters passed as part of the http request, looking for our search variables. For our endpoint we're going to accept:

• First Name

• Last Name

• State

• Date of Birth

We'll then construct the query dynamically based on those variables. I've utilized a general select, and added some dynamic f strings to build the SQL dynamically.

Once we have our SQL defined, we can connect to the database with the engine, execute our query, and return the results. We'll then take these results, convert them to json, and return them as part of the response:

@blueprint.route("/person",methods=["GET"])
def person():
    if request.method == "GET":

        request_parameters = request.args.to_dict()

        firstname = request_parameters.get("firstname",None)
        lastname = request_parameters.get("lastname",None)
        state = request_parameters.get("state",None)
        dob = request_parameters.get("dob",None)

        query = f"""
        select 
            id as personid, firstname, gender, middlename, lastname, dateofbirth, statename, stateabbreviation, zipcode, address1, address2
        from users.person
        where 1=1
            {f"and lower(firstname) = '{firstname.lower()}'" if firstname else ""}
            {f"and lower(lastname) = '{lastname.lower()}'" if lastname else ""}
            {f"and dateofbirth::date = '{dob}'" if dob else ""}
            {f"and (lower(stateabbreviation) = '{state.lower()}' or lower(statename) = '{state.lower()}')" if state else ""}
        limit 500
        """      
        with engine.connect() as con:
            persons = pd.read_sql(query,con)             

        return persons.to_json(orient='records'), 200

You'll notice the methods=["GET"] and the if request.method == "GET", these aren't required, but highly recommended. These methods correspond to typical REST API methods (GET, PUT, POST, etc.). For our API, we're only going to be "GET"ing information. That being said, if you plan to scale your API, you need to consider the method, and make decisions based on it within your API logic itself.

The methods=["GET"] restricts that endpoint to only "GET" type requests. As well, the if request.method == "GET" is our logic for our particular "GET" request.

Now that we have this endpoint built, let's test it! We're going to launch our app, then using our API testing tool to see our responses:

For this test, I just executed it passing firstname and lastname to the endpoint. So now that our search feature is working, let's build our charges endpoints!

Charges Blueprint

Charges will be very similar to the person blueprint. We need to:

• Import our modules

• Load our .env file

• Construct our SQL Alchemy Engine

The top of our blueprint should look something like the following:

from flask import Blueprint, request
import pandas as pd
import sqlalchemy
import dotenv
import os

blueprint = Blueprint("Charges Blueprint",__name__)

#load .env file
dotenv.load_dotenv()

#define sql alchemy connection and engine
connection_url = (
    sqlalchemy.URL.create(
            drivername='postgresql+psycopg2'
            ,username=os.environ.get("DBUSER")
            ,password=os.environ.get("DBPASSWORD")
            ,host=os.environ.get("DBHOST")
            ,database=os.environ.get("DBNAME")
            ,query={"sslmode":"require"}
        )
)
engine = sqlalchemy.create_engine(connection_url)

However, for the charges endpoints, we're going to need to construct a different set of queries, one for each endpoint. The queries will need to utilize the personid, and we'll need to pull the last 500 charges for the customer, and a summary of the customer's amount owed and last payment date

• The charges(personid:int) method will look something like:

    @blueprint.route("/charges/<personid>",methods=["GET"])
    def charges(personid:int):
        if request.method == "GET":
            query = f"""
                select *
                from users.charges
                where 1=1
                    and personid = {personid}
                order by chargedate desc, chargecategory asc, chargeamount asc
                limit 500                    
            """
            with engine.connect() as con:
                result = pd.read_sql(query,con)
  
            return result.to_json(orient="records",date_format='iso'), 200
  

• The charges_summary(personid:int) method will look something like:

    @blueprint.route("/charges/<personid>/summary",methods=["GET"])
    def charges_summary(personid:int):
        if request.method == "GET":
            query = f"""
            select 
                sum(chargeamount) as amount_owed
                , max(case when chargecategory = 'Payment' then chargedate else null end) as last_payment
            from users.charges
            where 1=1
                and personid = {personid}
            """
            with engine.connect() as con:
                result = pd.read_sql(query,con)
  
            return result.to_json(orient="records",date_format='iso'), 200
  

Now that we have our charges endpoints built, let's test our API using the user we found in our person endpoint:

That's it! Our API is fully functional! All we have to do is utilize it while developing our front end, and demo it to the customer service team!

Some Closing Thoughts

Building an API with Flask can be pretty quick and easy, but generally requirements aren't so simple. So there's always some additional things to consider:

• Authentication - this is a pretty common requirement for a lot of applications and API's. You don't want to leave your API unsecured, and you'd likely want some sort of validation of who is accessing the API. Depending on the requirements, there are a lot of different ways to tackle this, so just do some research based on your needs

• SQL Injection - this is a VERY real concern for security; SQL injection happens when someone takes advantage of your API endpoint to adjust what gets executed against the database. Utilizing raw SQL code isn't recommended without some stringent controls. That being said utilizing an ORM, or limiting what can access your API can help with this

• Scalability - Flask is great, really... it is. It's the first module I ever utilized to build an application. I also really enjoy granular control, and building most things from scratch. That being said, without strong design principles, developing a large application in Flask can be difficult while managing all the moving pieces. It's not impossible, it can just be difficult. So if you know your application is going to grow, have a plan for how you plan to grow it. Whether it's a microservices approach, or utilizing another tool. Just do your research, and plan accordingly.

Conclusion

As you can see, building an API can be quick/easy with the right tools. It's not a one size fits all, and you should do research and ensure the tools and approach you utilize meets all defined (and undefined) requirements.

Thanks for reading, I hope you learned something!

Solution Link