Today I Learned How to Make a Static API With Flask & Sqlite

This post was initially adapted from here. I wrote this to make it simpler to understand, more like a straight forward approach to creating API's in Python using flask routes.

Link to repository

 
 

1 ==> This is where our imports happen. All that we are going to need in our project.

2 ==> We also want to make sure our Flask server is running in debug mode so that we can see when things go wrong.

3 ==> Our iterator that keeps track of index and object we're looking for int the given database.

4 ==> This is our welcome page, but feel free to add whatever custom message you want.

5 ==> The /all router that fetches all columns and row in the database without filter.

6 ==> If we hit a dead-end in our database or if you can't find what you looking for.

7 ==> Does the opposite of the #5 except you can add more than one column_filter per request, unlike traditional static APIs.

8 ==> If you can't run how'd your program be alive; I'm guessing it would be a zombie process?

 

Here's how it all works:

 
 

1. Essentially, the variable query gets populated with however many column_filters (parameters) we want to use on our database. This is even better than rerouting every single column in the database onto a different url like in traditional static APIs.

 

2. It's all about query

In the /api/v1/resources/books?column_filter we notice that inside the api_filter(), the variable query is declared as a string. However, right below follows a series of boolean expressions that make our filtering possible.
 

Within these booleans, all the operations inside are pretty much the same. Our variable query gets populated by an additional string each step of the way; in the first place being we have: query += ' id=? AND'.
 

3. cur.execute()

But finally at the end of the logical expressions; our results are assigned to cur.execute(), which holds our query and column_filter(s). Basically meaning when our JSON gets displayed, to_filter.fetchall is going to show us a display of all the filters inside our query statement.
 

Just because you have the query, it don't mean I will show you the results.

Even if the SQL database finds our results, it's still our repsonsibliity as the developer to fetchall() using a filter in this case, it's to_filter, populated as a result of the prior logical expression.

 

4. dict_factory()

dict_factory iterates our database while keeping track of our index and Object. Finally, the result is a an Object that then prepares us to turn the data into JSON format for us to interact with on the front-end.

 
 
 
 
What would be really cool to see is how to write to our database coming from JSON format into our SQLITE database.