Creating a Python Django REST Service backend with Postgres database
REST API services are becoming increasingly popular these days as a means of providing data to Javascript frontends such as React, Vue, Angular and Svelte. Today we will develop a REST API service backend using Python (3.9.1) and Django which connects to a Postgres database and fetches all the record in a GET request.
Subsequently, if you would like to build a frontend and connect to this backend service with React, node,js and the Material-UI/Data Grid component, check out my other article (which also discusses how to create a Java Springboot REST backend), Creating a fullstack React/Material-UI DataGrid frontend data grid of users connected to a Java SpringBoot REST GET API backend with axios.
This article is further extended with my other article, Creating a fullstack Java app with AJAX HTML frontend using jsGrid datagrid and Apex Charts chart component calculating monthly averages from uploaded Excel data files on a Spring Boot REST backend running Apache Tomcat.
My recent article, Creating a CRUD node.js REST service backend with node.js, Express and MongoDB (mongoose) and a React-Data-Grid / Material-UI DataGrid frontend to access the service, also discusses how to create a REST CRUD backend using node.js, Express.js and connects to the MongoDB NoSQL database with the help of mongoose and then create frontends to access this REST service using React-Data-Grid and Material-UI/DataGrid.
The above article is further extended with Deploying a Node.js server-side backend CRUD REST service to Heroku connected to a cloud-based MongoDB Atlas database which shows how to deploy the node.js app created to Heroku cloud hosting and also to use the cloud-based MongoDB Atlas for public deployment.
For a Python-Django REST API stack implementing HTTP GET, check out my other article, Creating a Python Django REST Service backend with Postgres database.
An Android/Google Location API article, Creating a Realtime Handphones Locations Tracker For Android Devices with Google Location API and Google Maps).
Create User Table In Postgres
If you don’t have Postgres, you can download it here. First we have to setup our database and create a sample table called users. Here is the SQL :
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
user_name VARCHAR(60),
user_tel_no VARCHAR(60),
user_email VARCHAR(60),
user_password VARCHAR(60),
user_role VARCHAR(60),
login_datetime TIMESTAMP
)
;
You can create the table using pgAdmin or one of your favourite SQL editors such as HeidiSQL. Then we populate the Users table with some sample data so that we can test with our web app later. You can insert the data directly row by row with HeidiSQL.
Tools
For this project, I am using Visual Code editor (you can also use Sublime or Atom or your favourite editor), HeidiSQL and pip to install Python packages.
Activating the virtual environment
In order to start programming in Python, create and activate a virtual environment where we can run and test our app. To create and activate our virtual environment, type the following two commands in the command prompt window:
python -m venv envenv\Scripts\activate
This will create the virtual environment env (see screenshot below):
Now, after creating and activating our virtual environment, we go to our project folder from the command prompt and type: “code .” to open Visual Code editor from within that current folder.
Install PIP
pip is a popular package installer for Python which we are going to use to install our packages. You can get and install pip via this Python command:
python get-pip.pyInstall Django
Next we want to install the Django framework to help us create our Python web app. We do this by using pip (that we have just installed):
pip install django
Install Django REST framework
The Django REST framework is needed to build RESTful web services with Python. To install the package, run the following command in terminal or command prompt:
pip install djangorestframework
Changing settings.py to reflect the Postgres database
Next, you need to edit the settings.py file to reflect the database you are using. Here, we are using Postgres and the DATABASES entry for my machine is as follows:
DATABASES = {
‘default’: {
‘ENGINE’: ‘django.db.backends.postgresql_psycopg2’,
‘NAME’: ‘postgres’,
‘USER’: ‘postgres’,
‘PASSWORD’: ‘tiger123’,
‘HOST’: ‘127.0.0.1’,
‘PORT’: ‘5432’,
}
}
Note that if you are using MySQL, the ‘ENGINE’ entry would be:
‘ENGINE’: ‘django.db.backends.mysql’,
and the port would be 3306:
‘PORT’: ‘3306’,
Download and Install psycopg2
psycopg2 is a popular dependency package to enable the Python Django web app to connect to the Postgres database.
You can install it using pip with the following command:
pip install psycopg2 -OR- pip install psycopg2-binary
(For MySQL database, you can use pymysql and install with pip using ‘pip install pymysql’).
settings.py
Below is the settings.py screenshot showing both the INSTALLED_APPS and MIDDLEWARE groups for our app thus far:
Run the server to test your installation
Next run the following command to start the inbuilt web server and test that your app can now run in your web browser:
python manage.py runserver
If everything works fine, you should get the following message in command prompt or the VC terminal:
You can now test open your app in your browser by typing : http://localhost:8000 in the URL address bar. You should be able to see the default Django screen below:
Configure CORS
Configuring CORS for cross origin access, allows us to use multiple ports within the same machine without conflicts. This is because our backend Python Django REST service is running on the Python Django default port 8000 and we might have a frontend to access our REST service running on a different port. To avoid CORS conflicts, we configure CORS to accept requests from localhost:8000.
First, install the django-cors-headers library:pip install django-cors-headers
In settings.py, add the configuration for CORS:
INSTALLED_APPS = [
...
# CORS
'corsheaders',
]You also need to add a middleware class to listen in on responses:
MIDDLEWARE = [
...
# CORS
'corsheaders.middleware.CorsMiddleware',
'django.middleware.common.CommonMiddleware',
]Note: CorsMiddleware should be placed as high as possible, especially before any middleware that can generate responses such as CommonMiddleware.
Next, set CORS_ORIGIN_ALLOW_ALL and add the host to CORS_ORIGIN_WHITELIST:
CORS_ORIGIN_ALLOW_ALL = False
CORS_ORIGIN_WHITELIST = (
'http://localhost:8000',
)- CORS_ORIGIN_ALLOW_ALL: If
True, all origins will be accepted (not use the whitelist below). Defaults toFalse. - CORS_ORIGIN_WHITELIST: List of origins that are authorized to make cross-site HTTP requests. Defaults to
[].
Test database connection
Lets check to see if our Python web app can indeed connect to the Postgres database and return records. We create a test file called fetch_all.py to fetch data from the Users table, we created earlier. And then in the terminal window, we execute this test file with the command:
python fetch_all.py
We get the following screen:
We can see that indeed the app is correctly returning the database row(s) from our User table, from the terminal view.
Creating the view for our GET/ALL
Now that we know the database connection and fields retrieval is working correctly, the final step is to retrieve all the fields of the Users table, convert it to a JSON object (using json.dumps) and display it as a JSON string in the browser.
views.py:
from rest_framework.decorators import api_view, permission_classes
from django.http import JsonResponse
from django.views.decorators.csrf import csrf_exempt
from django.http import HttpResponse
from .serializers import UsersSerializer
from .models import Users
from rest_framework import status
from rest_framework import generics
import json
import psycopg2
def db(database_name=’postgres’):
return psycopg2.connect(database=database_name, user=’postgres’, password=’ezani1')
def query_db(query, args=(), one=False):
cur = db().cursor()
cur.execute(query, args)
r = [dict((cur.description[i][0], value) \
for i, value in enumerate(row)) for row in cur.fetchall()]
cur.connection.close(),
return (r[0] if r else None) if one else r
my_query = query_db(“SELECT h1.users.user_id, h1.users.user_name, h1.users.user_tel_no, h1.users.user_email, h1.users.user_role FROM h1.users”, (3,))
@api_view([“GET”])
@csrf_exempt
def testEzani(request):
return JsonResponse(json.dumps(my_query), safe=False)
Finally, we should be able to see all the user records returned as a JSON array in our browser as below:
(Note: if you would like to build a frontend that connects to this backend service with React, node,js and the Material-UI/Data Grid component, check out my other article Creating a fullstack React/Material-UI DataGrid frontend data grid of users connected to a Java SpringBoot REST GET API backend with axios — this article also discusses how to build a REST API backend using the Java and Springboot stack with MySQL database or my recent article — Creating a CRUD node.js REST service backend with node.js, Express and MongoDB (mongoose) and a React-Data-Grid / Material-UI DataGrid frontend to access the service. — this article discusses how to create a REST CRUD backend using node.js, Express.js and connects to the MongoDB NoSQL database with the help of mongoose and then create frontends to access this REST service using React-Data-Grid and Material-UI/DataGrid)
