Post
Ask Question

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,
  1. It supports the storage of pictures, sounds, and videos
  2. It supports time intervals and timestamps.
  3. 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 
 
or
 
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.
  1. from sqlalchemy import create_engine  
  2.   
  3. class DbUtils:  
  4.     db_string = "postgresql+psycopg2://admin:admin@localhost/postgres"  
  5.       
  6.     def createTable(self):  
  7.         db = create_engine(self.db_string)  
  8.         db.execute("CREATE TABLE IF NOT EXISTS films (title text, director text, year text)")    
  9.   
  10.     def addNewFilm(self, title, director, year):  
  11.         db_string = "postgresql+psycopg2://admin:admin@localhost/postgres"  
  12.         db = create_engine(db_string)  
  13.         db.execute("INSERT INTO films(title, director, year) VALUES (%s,%s, %s)", title, director, year)  
  14.   
  15.     def getFilms(self):  
  16.         db_string = "postgresql+psycopg2://admin:admin@localhost/postgres"  
  17.         db = create_engine(db_string)  
  18.         films = db.execute("SELECT * FROM films")  
  19.         return films  
  20.   
  21. #can = DbUtils()  
  22. #can.CreateTable()  
  23. #can.AddNewFilm('dd', 'ddc', '2016')  
  24. #can.getFilms()  
filmService.py 
 
This module calls dbUtils and encapsulates the dbUtils method and creates service. It is basically a service layer.
  1. from flask import Blueprint  
  2. from flask import Flask, abort, request, jsonify  
  3. import json  
  4. from flask import jsonify  
  5. from dbutils import DbUtils  
  6.   
  7. film_api = Blueprint('film_api', __name__)  
  8.  
  9. @film_api.route('/addfilm', methods=['POST'])  
  10. def addfilm():  
  11.     if not request.json:  
  12.         abort(400)  
  13.           
  14.     filmData = json.dumps(request.json)  
  15.     filmObject = json.loads(filmData, object_hook=JSONObject)  
  16.     dbUtils = DbUtils()  
  17.     dbUtils.addNewFilm(filmObject.title, filmObject.director, filmObject.year)  
  18.     return json.dumps(filmData)  
  19.  
  20. @film_api.route('/getfilm')  
  21. def getFilms():  
  22.     films = []  
  23.     dbUtils = DbUtils()  
  24.     filmData = dbUtils.getFilms()  
  25.     for r in filmData:    
  26.         a = {"title": r[0], "director": r[1], "year":r[2]}  
  27.         films.append(a)  
  28.       
  29.     return jsonify(films)  
  30.  
  31. @film_api.route('/create_film_table')  
  32. def createFilmTable():  
  33.     dbUtils = DbUtils()  
  34.     dbUtils.createTable();  
  35.     result = {"result": "Films Table Created"}  
  36.     return jsonify(result)  
  37.   
  38. class JSONObject:  
  39.   def __init__( self, dict ):  
  40.       vars(self).update( dict )  
main.py 
 
This module encapsulates all services for the execution.
  1. from flask import Flask  
  2. from filmService import film_api  
  3.   
  4. app = Flask(__name__)  
  5. app.register_blueprint(film_api, url_prefix='/film_api')  
  6.  
  7. @app.route("/")  
  8. def service():  
  9.     return "Service is running!"  
  10.   
  11. if __name__ == "__main__":  
  12.     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.
  1. {  
  2.     "title": "3 Idiots",  
  3.     "director": "RajKumar Hirani",  
  4.     "year": "2009"  
  5. }  
 
 
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.
  1. [  
  2.     {  
  3.         "director": "RajKumar Hirani",  
  4.         "title": "3 Idiots",  
  5.         "year": "2009"  
  6.     },  
  7.     {  
  8.         "director": "RajKumar Hirani",  
  9.         "title": "pk",  
  10.         "year": "2104"  
  11.     },  
  12.     {  
  13.         "director": "Nitesh Tiwari",  
  14.         "title": "Dangal",  
  15.         "year": "2016"  
  16.     },  
  17.     {  
  18.         "director": "Advait Chandan",  
  19.         "title": "Secret Super Star",  
  20.         "year": "2017"  
  21.     }  
  22. ]  
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.


Similar Articles