Build an API under 30 lines of code with Python and Flask



Hello everyone. Now a days developers need to perform many jobs. Like web development, database development, API development and so on. Some companies are just having jobs called API developer on their openings sheet.What role APIs are playing now and why one should learn building them is our topic today. Developing an API with Python is a very easy task when compared to other languages. So, sit back and grab this skill for you. Take my words ,this skill is hot right now in the market.

For my other works on software development, visit my publication:

What is a REST API?

REST (REpresentational State Transfer) is an architectural style, and an approach to communications that is often used in the development of Web services. The use of REST is often preferred over the more heavyweight SOAP (Simple Object Access Protocol) style because REST does not leverage as much bandwidth, which makes it a better fit for use over the Internet. The SOAP approach requires writing or using a provided server program (to serve data) and a client program (to request data).

In simple three lines REST API is a:

1 ) A way to expose your internal system to the outside world.

2) Programmatic way of interfacing third party systems.

3) Communication between different domains and technologies.

I think we are sounding technical. let us jump into practical things.By the end of this tutorial ,you will be comfortable in creating any API using Python and Flask.

Ingredients to build our API

We are going to use these things to build a running API.

*  Python

*  Flask web framework

*  Flask-RESTFul extension

*  SQLite3

* SQLAlchemy

Let us build Chicago employees salary API under 30 lines of code

I am going to build a Salary info API of Chicago city employees. Do you know ?,it is damn easy. An API can give you computation result or data from a remote database in a nice format. It is what API is intended for.API is a bridge between private databases and applications. I am collecting employee salary details from Chicago city data website

code of this entire project can be found at this link

Let’s begin the show……..

First , i downloaded the data-set as CSV and dumped it into my sqlite database.

$ sqlite3 salaries.db
sqlite> .mode csv salaries
sqlite> .import employee_chicago.csv salaries

and imported CSV.

Now we are going to build a flask app that serves this data as a REST API.

$ virtualenv rest-api
$ source rest-api/bin/activate
$ mkdir ~/rest-app
$ cd ~/rest-app

Now we are in the main folder of app.Create a file called in that folder.We need few libraries to finish our task.Install them by typing below commands.

$ pip install flask
$ pip install flask-restful
$ pip install sqlalchemy

That’s it. We are ready to build a cool salary API that can even be accessed through mobile. Let us recall the REST API design .It has 4 options. GET,PUT,POST,DELETE



here we are  dealing with an open data which can be accessed by multiple applications. So we implement GET here and remaining REST options becomes quite obvious.


from flask import Flask, request
from flask_restful import Resource, Api
from sqlalchemy import create_engine
from json import dumps

#Create a engine for connecting to SQLite3.
#Assuming salaries.db is in your app root folder

e = create_engine('sqlite:///salaries.db')

app = Flask(__name__)
api = Api(app)

class Departments_Meta(Resource):
    def get(self):
        #Connect to databse
        conn = e.connect()
        #Perform query and return JSON data
        query = conn.execute("select distinct DEPARTMENT from salaries")
        return {'departments': [i[0] for i in query.cursor.fetchall()]}

class Departmental_Salary(Resource):
    def get(self, department_name):
        conn = e.connect()
        query = conn.execute("select * from salaries where Department='%s'"%department_name.upper())
        #Query the result and get cursor.Dumping that data to a JSON is looked by extension
        result = {'data': [dict(zip(tuple (query.keys()) ,i)) for i in query.cursor]}
        return result
        #We can have PUT,DELETE,POST here. But in our API GET implementation is sufficient
api.add_resource(Departmental_Salary, '/dept/<string:department_name>')
api.add_resource(Departments_Meta, '/departments')

if __name__ == '__main__':

save it as and run as

 $ python 

That’s it. Your salary API is up and running now on localhost , port 5000. There are two rules we defined in the API. One is to get details of all departments available and second is to get employee full detail, who is working in a particular department.

So now go to


and you will find this.


See how flask is serving database data into JSON through the REST API we defined. Next modify URL to peek all employees who are working in Police department.



Oh man, seems like police officers are well paid in Chicago but they can’t beat a Django or Python developer who earns $ 1,00,000 per annum. just kidding.

My code walk-through is as follows

*  I downloaded latest Salary dataset from chicago data site

*  Dumped that CSV  into my SQLite db.

*  Used SQLAlchemy to connect to database and do select operations.

*  Created Flask-Restful classes to map functions with API URL

*  Returned the queried data as JSON ,which can be used universally.

See how simple it is to create a data API. We can also add support to PUT,POST and DELETE on data too.We can also have an authentication system for fetching data through API. Python and Flask are very powerful tools to create API rapidly. GitHub link is given below. Give it a try and extend it with the things mentioned above.

See you soon with more stories.


32 thoughts on “Build an API under 30 lines of code with Python and Flask

  1. Yeah Derrick ,I modified that.There is no need to create table manually. We should give schema while creating table. When we say “.mode csv salaries” it automatically infers schema from csv file. 🙂

    1. I don’t think that is would act as a SQL injection. The first part

      query = conn.execute(“select * from salaries where Department=’%s’”

      is a format string, hence the %s. The second part:


      replaces the %s in the first part with department_name.upper(). If you examine the String, there are no quote or parenthesis violations that would be indicative of a SQL injection. It just looks like it at first glance.

      1. Yes, it’s a SQL injection.
        If you try to access url:

        department_name=”‘; drop table salaries ; select ‘”

        It’ll execute:
        conn.execute(“select * from salaries where Department=”; DROP TABLE SALARIES ; SELECT ””)

        That is a perfectly valid query that will select nothing, but will drop your table.
        To avoid this type of vulnerability, NEVER use string substitution ( fmt % params, ‘{}’.format(params) ) on SQL queries, *specially* if they use parameters coming from public URLs/APIs.
        Instead, use parameters substitution functions available in all SQL engines, like:

        conn.execute(“select * from salaries where Department=? “, (department_name,))

        See? Tuple as 2nd parameter, it will normalize and escape any error-prone substring and pass it as a correct string query.

    2. Seriously, SQL Injection, this is a bad bad example. Jordan what with that explanation? department_name is an untrusted input, nobody is validating it, or the framework somehow magically does it?

  2. Thanks for sharing excellent informations. Your website is very cool. I’m impressed by the details that you’ve on this web site. It reveals how nicely you understand this subject. Bookmarked this website page, will come back for extra articles. You, my pal, ROCK! I found simply the info I already searched everywhere and just could not come across. What a perfect web-site.

  3. Hello,
    I am new with python. I have to create an API to collect some information from a commercial website (like price, article, brand, description …)
    I have to use Linux envirement. I started with installing Pycharm avec Django.
    I really don’t know what to do next
    COuld you give me some tip please?
    Thank you in advance

      1. Hello Naren, thank you for your reply
        I’m asked to develop an API for a commercial website. So it’s an API.

  4. Great example, but I am having a hard time interpreting what I should do if I want to expand the API for inserting new departments and employees. For example, what should a Flask API return when inserting values? I wouldn’t want to return a new template right? only headers and body or some kind of success code?

  5. Hi

    I have maybe a stupid question about the code arrengment
    Should I write/import all the APIs in one file? Or maybe there are better practicies ?

    I have many APIs to write + support versioning, and I have doubts if its good practice to import all my APIs to one .py file (

  6. I don’t understand where “%department_name.upper()” is coming from. It’s not estabished as a variable anywhere else, it’s not the field name in the database, so how does it know the list to pass? I am using this code for a personal database and trying to replace that with a different variable, and getting “get() got an unexpected keyword argument”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s