Building APIs with Python+Flask #2
Database Integration.
Hi, welcome to the second part of this series on APIs with Python and Flask.
In the first part, we went through how to build a simple Store API. We could create, delete, edit and list items in our store. Kindly go through if you haven’t:
In this part, we’ll be looking at building APIs with the integration of a database.
Database
A database is a collection of information that is organized so that it can be easily accessed, managed and updated. It serves as means of storage and can help in easy accessibility of data when needed in the future.
Recall that in the last part of this series, we used a dictionary to represent the store. This implies we would always use in-memory every time we run our app.py script and whatever data (items and prices) we store or edit when we run our script would not be retrievable after the session hence database comes in handy to solve this problem.
There are different kinds of databases based on several parameters. Based on structure, we have:
In this article, we’ll be using a SQL database. Python has an inbuilt SQL module called sqlite3.
Let’s have a recap of the code from the last article
In place of the current store model:
store = { “item”:
[
{ “name”: ‘Book’,
“price”: 99.99
}
]
}We’ll be using a database model.
At the end of this article, our folder structure should be in this form:
flask_tutorial|____app.py|____data.db|____db.py|____venv
First and foremost, we create a db.py file.
connection = sqlite3.connect(‘data.db’) creates a connection to our sqlite3 database.
create_table = “CREATE TABLE IF NOT EXISTS items (name text PRIMARY KEY, price real)” is a SQL query that helps us create a database for items.
cursor.execute(create_table) executes that SQL query for us.
While connection.commit() saves that operation for that session and connnection.close() ends close the connection for that session.
Our database is ready!
Hmmm, we’re not done yet. We have to restructure our script to save to the database rather than the dictionary. Let’s go back to our app.py file and do some restructuring.
The first thing we’ll do here is to create a find_by_name function, this will help us to search for items by name and get a specific item in the database.
Line 5, queries the database for items where name is a variable(name=?). Line 6 is the execution of our query but with a little difference here, name=? implies name is expecting a variable to replace ? . So we have name as the variable we would be fitting to replace ? .
Also, note that for result, name has a comma after it, which implies that it is a tuple, this is very important, without the comma it would return an error because a tuple is expected here.
The next thing to do is rewrite our route to get a specific item
If the item is not found we are returning a message and a status code; 404, which implies NOT FOUND.
The next route to work is the route to list all items in the database
To insert an item into the database we’ll create a function to help us handle this, then we’ll use this function in our route for inserting items.
Now, let’s create our route for inserting an item into the database.
Next route will be for deleting an item from the database.
The last route we’ll be creating will be for updating items in the database. For this route, we’d also be creating a helper function for handling updates.
All endpoints are set and our API now has database integrated into it.
Kindly note that we will have to run db.py (just once) before we run the app.py file. This would create the data.db file which is the file for our sqlite3 database.
More concepts are still involved in building APIs and these concepts will be touched in the subsequent parts of the series. Kindly drop a comment below if you have any concept in APIs you will like to read about.
A link to the code in this article can be found here
Thanks for reading.
