Python With PostgresSql, SQLAlchemy And Flask

Introduction

Exposing REST service by connecting to the database and fetching/inserting records is very interesting and important to any project or personal skill. So, this interesting exercise can be done by using PostgreSQL, Python, SQLAlchemy, and Flask. All these technologies provide very fast end to end development of REST service with the interaction of DB. And, these technologies are very popular, scalable, open-source and easy to learn nowadays.

PostgreSQL

PostgreSQL is a powerful and open-source RDBMS. Its architecture provides several wonderful features like powerful data integrity, correctness, and reliability. It can run on operating systems like Windows, Linux, UNIX, etc. It is fully ACID based and this ACID is the beauty of any powerful database.

Additional features supported by PostgreSQL Db,

It supports the storage of pictures, sounds, and videos
It supports time intervals and timestamps.
It has native programming interfaces for C, C++, Java, .NET, Python, Ruby, ODBC, etc.

PG Admin

PostgreSQL Shell

SQLAlchemy

SQLAlchemy is a Python-based library and provides the feature of OMR and Object Relation Mapping. It gives the flexibility to a developer to handle the SQL operation easily. It provides high-performance database access.

Below command to install SQLAlchemy,

pip install SqlAlchemy

pip3 install SqlAlchemy

Flask

Flask is a micro-framework developed in python. It is used to expose REST services and many other things. It is based on Werkzeung and Jinga2.

NodeJs Vs Flask

Flask is a Python Web Framework. It is based on WSGI and its nature is synchronous. So, Python thread can't run parallel and it is blocking due to GIL. It needs to spawn of threads for the serving of request. And, the spawning thread is very expensive.

NodeJs is not a framework. It is JavaScript-based binding of libuv. It is asynchronous by design so it is not blocking. It sends a notification back while doing I/O also.

	Nodejs	Flask
Throughput (RPM)	13460	11557
Average Latency (ms)	5	9
95th Percentile (ms)	9	17
CPU Utilization (%)	72	47
Network Packets Out (minute)	96000	31000
MySQL Connection	7	2

Code Explanation

dbUtils.py

This module handles the database operations like table creation, data insert into a table and fetch record from the table, etc.

from sqlalchemy import create_engine
class DbUtils:
db_string = "postgresql+psycopg2://admin:admin@localhost/postgres"
def createTable(self):
db = create_engine(self.db_string)
db.execute("CREATE TABLE IF NOT EXISTS films (title text, director text, year text)")
def addNewFilm(self, title, director, year):
db_string = "postgresql+psycopg2://admin:admin@localhost/postgres"
db = create_engine(db_string)
db.execute("INSERT INTO films(title, director, year) VALUES (%s,%s, %s)", title, director, year)
def getFilms(self):
db_string = "postgresql+psycopg2://admin:admin@localhost/postgres"
db = create_engine(db_string)
films = db.execute("SELECT * FROM films")
return films
#can = DbUtils()
#can.CreateTable()
#can.AddNewFilm('dd', 'ddc', '2016')
#can.getFilms()

filmService.py

This module calls dbUtils and encapsulates the dbUtils method and creates service. It is basically a service layer.

from flask import Blueprint
from flask import Flask, abort, request, jsonify
import json
from flask import jsonify
from dbutils import DbUtils
film_api = Blueprint('film_api', __name__)
@film_api.route('/addfilm', methods=['POST'])
def addfilm():
if not request.json:
abort(400)
filmData = json.dumps(request.json)
filmObject = json.loads(filmData, object_hook=JSONObject)
dbUtils = DbUtils()
dbUtils.addNewFilm(filmObject.title, filmObject.director, filmObject.year)
return json.dumps(filmData)
@film_api.route('/getfilm')
def getFilms():
films = []
dbUtils = DbUtils()
filmData = dbUtils.getFilms()
for r in filmData:
a = {"title": r[0], "director": r[1], "year":r[2]}
films.append(a)
return jsonify(films)
@film_api.route('/create_film_table')
def createFilmTable():
dbUtils = DbUtils()
dbUtils.createTable();
result = {"result": "Films Table Created"}
return jsonify(result)
class JSONObject:
def __init__( self, dict ):
vars(self).update( dict )

main.py

This module encapsulates all services for the execution.

from flask import Flask
from filmService import film_api
app = Flask(__name__)
app.register_blueprint(film_api, url_prefix='/film_api')
@app.route("/")
def service():
return "Service is running!"
if __name__ == "__main__":
app.run()

Execution

Open the command prompt and type the below command:

python main.py

main.py will start execution with the below output,

Now, open Postman and test like below:

Test 1

whether service is running or not.

Test 2

Create a Table service.

Url in Postman should be like below with GET:

http://127.0.0.1:5000/film_api/create_film_table

If you will check into PostgreSQL pgAdmin, then Films table will be created like below.

Test 3

Insert the Films data into Films table and test with Postman like below.

It is a POST request and its URL should be like below.

http://127.0.0.1:5000/film_api/addfilm

Data will be sent in JSON format from the body and JSON Data will be like below.

{
"title": "3 Idiots",
"director": "RajKumar Hirani",
"year": "2009"
}

Got to PostgreSQL PgAdmin, apply select query on films table, then you will get the record like below.

Test 4

Get film data from the films table of PostgresSQL.

It is a GET request and its Url should be like below,

http://127.0.0.1:5000/film_api/getfilm

The response will be in JSON format like below.

[
{
"director": "RajKumar Hirani",
"title": "3 Idiots",
"year": "2009"
},
{
"director": "RajKumar Hirani",
"title": "pk",
"year": "2104"
},
{
"director": "Nitesh Tiwari",
"title": "Dangal",
"year": "2016"
},
{
"director": "Advait Chandan",
"title": "Secret Super Star",
"year": "2017"
}
]

I have attached the completed code in zipped formate. It is based on Python 3.5 or above version.

Conclusion

Python is a very powerful programming language. SQLAlchemy, PostgreSQL database and Flask are wonderful and easy for learning and development. Please try for the development of REST service using all these technologies then you will be amazed.