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.

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.

Create User Table In Postgres

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

Activating the virtual environment

  1. python -m venv env
  2. env\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

python get-pip.py

Install Django

pip install django

Install Django REST framework

pip install djangorestframework

Changing settings.py to reflect the Postgres database

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

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

Run the server to test your installation

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

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 to False.
  • CORS_ORIGIN_WHITELIST: List of origins that are authorized to make cross-site HTTP requests. Defaults to [].

Test database connection

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

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 axiosthis 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)

30+ years as a computer programmer. Started with Visual Basic, now doing Java, SpringBoot, React and .NET. Running my own software company at mysoftware2u.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store