How to do a basic CRUD REST API with an ORM-Less Django
The other day had a client that needed to build a very basic web service and the only requirement that I had was that this would needed to be done on top of an existing database and table.
Normally, like when you are doing things from scratch, you would let Django deal with the database and the SQL. You would specify your table as a Model subclass and use the fantastic ORM that Django provides.
In this occasion though we couldn’t use the ORM, the table was already specified, had live data, and if we attempted to map the current table to a Model defined ad-hoc I wasn’t sure if the fields would match or if it would be a convenient long-term solution.
Therefore I needed to skip Django’s ORM. BTW, I also questioned if Django was the right platform, I thought on maybe attempting to use Flask, but Django has been constructed from the get go to be highly compartmentalized and for you to use all the parts that you need and to ignore or turn off the parts that you don’t need. So I tried Django.
Here is how to do it:
Project Setup and Settings
you start by creating the project as any other project. In the settings you also define your DATABASE as you would if you would use Django’s ORM.
That is because we DO want to use Django’s DB interface, what we DO NOT want to use is just the Model to SQL translation that it does (the ORM part)
Start by installing Django, installing your DB python drivers (psycopg2 for instance if you are using Postgresql), and creating your Django project.
Then in settings you still add:
# Database
# https://docs.djangoproject.com/en/1.9/ref/settings/#databases
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': '<db name>',
'USER': '<db user>',
'PASSWORD': '<db password>',
'HOST': 'localhost',
'PORT': '',
}
}(BTW my DB was a postgresql)
The Models
What does change is your models.py file.
I chose to follow some of Django’s architecture choices and mimic their Model and Manager separation. So I ended up implementing a Model class and a Manager class, only that I did without subclassing from Django’s own models.Model and managers.Manager classes.
My model class:
from .managers import CompanyManager
class Company:
objects = CompanyManager()
def __init__(self): # the private key
self.id = None
# title or name (for displaying purposes)
# of this company (required)
self.name = None
# email where all emails generated to this company
# will be redirected (required)
self.email = None
# email of the contact person, in case is different
# from leads_email (optional)
self.contact_email = None
# phone of the contact person where calls to this company
# should be directed (optional)
self.contact_phone = None
# contact person first name (optional)
self.contact_first_name = None
# contact person last name (optional)
self.contact_last_name = None
# ... and many more fields ... def save(self):
if self.id is not None:
self.objects.update(self)
else:
self.objects.insert(self)
def delete(self):
self.objects.delete(self)
The first thing that you need to notice is that the Company class has no super class. I am not subclassing models.Model
Second thing is that none of the fields are “Model Fields” (everything is initialized as None)
The actions that my Company class supports are 2, save() and delete(). Save will take care of creating a new instance if this is a new one, or updating an existing one, if not. And those are also delegated to the objects object which is an instance of CompanyManager.
My CompanyManager is the class that take care of all the SQL. This is again mimicking Django’s architecture.
from django.db import connection
class CompanyManager():
def __init__(self):
pass
@staticmethod
def format_boolean(value):
return 'TRUE' if value else 'FALSE'
@staticmethod
def format_date(value):
return value.isoformat()
@staticmethod
def format_datetime(value):
return value.isoformat()
@staticmethod
def delete(c):
pass
@staticmethod
def update(c):
cursor = connection.cursor()
query = """UPDATE ayudacontador_company
SET name='{0}',
email='{1}',
contact_email='{2}',
contact_phone='{3}',
contact_first_name='{4}',
contact_last_name='{5}',
web='{6}',
description='{7}',
logo='{8}',
invoicing_data='{9}',
lead_quota='{10}',
notes='{11}',
subscription_active='{12}',
lead_active='{13}',
last_notification_date='{14}',
received_leads_count={15},
trial_start_date='{16}',
remove_from_guide='{17}',
mailing_active='{18}',
created='{19}'
WHERE id={20};""".format(
c.name,
c.email,
c.contact_email,
c.contact_phone,
c.contact_first_name,
c.contact_last_name,
c.web,
c.description,
c.logo,
c.invoicing_data,
c.lead_quota,
c.notes,
CompanyManager.format_boolean(c.subscription_active),
CompanyManager.format_boolean(c.lead_active),
CompanyManager.format_datetime(
c.last_notification_date),
c.received_leads_count,
CompanyManager.format_date(c.trial_start_date),
CompanyManager.format_boolean(c.remove_from_guide),
CompanyManager.format_boolean(c.mailing_active),
CompanyManager.format_datetime(c.created),
c.id)
cursor.execute(query)
@staticmethod
def insert(c):
cursor = connection.cursor()
query = """INSERT INTO ayudacontador_company
(name,email,contact_email,contact_phone,
contact_first_name,contact_last_name,
web,description,logo,invoicing_data,
lead_quota,notes,subscription_active,
lead_active,last_notification_date,
received_leads_count,trial_start_date,
remove_from_guide,mailing_active,
created)
VALUES ('{0}','{1}','{2}','{3}','{4}',
'{5}','{6}','{7}','{8}','{9}','{10}',
'{11}','{12}','{13}','{14}'{15},
'{16}','{17}','{18}','{19}');""".format(
c.name,
c.email,
c.contact_email,
c.contact_phone,
c.contact_first_name,
c.contact_last_name,
c.web,
c.description,
c.logo,
c.invoicing_data,
c.lead_quota,
c.notes,
CompanyManager.format_boolean(c.subscription_active),
CompanyManager.format_boolean(c.lead_active),
CompanyManager.format_datetime(
c.last_notification_date),
c.received_leads_count,
CompanyManager.format_date(c.trial_start_date),
CompanyManager.format_boolean(c.remove_from_guide),
CompanyManager.format_boolean(c.mailing_active),
CompanyManager.format_datetime(c.created))
cursor.execute(query)
@staticmethod
def all():
cursor = connection.cursor()
query = """SELECT name,email,contact_email,contact_phone,
contact_first_name,contact_last_name,
web,description,logo,invoicing_data,lead_quota,
notes,subscription_active,lead_active,
last_notification_date,received_leads_count,
trial_start_date,remove_from_guide,mailing_active,
created,id
FROM ayudacontador_company;"""
cursor.execute(query)
objects = []
from .models import Company
for row in cursor.fetchall():
c = Company()
c.name = row[0]
c.email = row[1]
c.contact_email = row[2]
c.contact_phone = row[3]
c.contact_first_name = row[4]
c.contact_last_name = row[5]
c.web = row[6]
c.description = row[7]
c.logo = row[8]
c.invoicing_data = row[9]
c.lead_quota = row[10]
c.notes = row[11]
c.subscription_active = row[12]
c.lead_active = row[13]
c.last_notification_date = row[14]
c.received_leads_count = row[15]
c.trial_start_date = row[16]
c.remove_from_guide = row[17]
c.mailing_active = row[18]
c.created = row[19]
c.id = row[20]
objects.append(c)
return objects
@staticmethod
def get(id):
cursor = connection.cursor()
query = """SELECT name,email,contact_email,contact_phone,
contact_first_name,contact_last_name,
web,description,logo,invoicing_data,
lead_quota,notes,subscription_active,lead_active,
last_notification_date,received_leads_count,
trial_start_date,remove_from_guide,
mailing_active,
created,id
FROM ayudacontador_company
WHERE id={0};""".format(id)
cursor.execute(query)
objects = []
from .models import Company
for row in cursor.fetchall():
c = Company()
c.name = row[0]
c.email = row[1]
c.contact_email = row[2]
c.contact_phone = row[3]
c.contact_first_name = row[4]
c.contact_last_name = row[5]
c.web = row[6]
c.description = row[7]
c.logo = row[8]
c.invoicing_data = row[9]
c.lead_quota = row[10]
c.notes = row[11]
c.subscription_active = row[12]
c.lead_active = row[13]
c.last_notification_date = row[14]
c.received_leads_count = row[15]
c.trial_start_date = row[16]
c.remove_from_guide = row[17]
c.mailing_active = row[18]
c.created = row[19]
c.id = row[20]
objects.append(c)
return None if len(objects) == 0 else objects[0]As you see we are executing SQL statements directly. But we are using Django’s DB Connection feature and letting Django deal with creating a pool of connections and all that stuff.
BTW doc for performing raw SQL queries on Django you can find it here.
So as you see our manager provides the CRUD methods and is responsible for interfacing between our “ Model” instance, Company, and the Database SQL data types.
As we are responsible for writing the queries we are responsible for formating our python fields, such as Booleans and Datetimes, into their SQL compatible alter-egos. And here you need to take into consideration what your DB engine supports and expects. It’s not the same MySQL than PostgreSQL. Beware of that.
But reading the SQL and getting Python types back we don’t have to worry. Django’s connection takes care of that apparently.
The REST Framework
Ok. Up to this point we have a fully functional Model and Manager. Now we want to create our REST API and for that we are using rest_framework, so execute:
pip install djangorestframeworkNow what we need to do is to create our serializers. The serializers are the ones that will interface between our DB and the API output (json or whatever we use)
create a serializers.py and type the following:
from rest_framework import serializers
from .models import Company
class CompanySerializer(serializers.Serializer):
id = serializers.IntegerField(read_only=True)
name = serializers.CharField(required=False, allow_blank=True, max_length=128)
email = serializers.CharField(required=False, allow_blank=True, max_length=128)
contact_email = serializers.CharField(required=False, allow_blank=True, max_length=128)
contact_phone = serializers.CharField(required=False, allow_blank=True, max_length=128)
contact_first_name = serializers.CharField(required=False, allow_blank=True, max_length=128)
contact_last_name = serializers.CharField(required=False, allow_blank=True, max_length=128)
web = serializers.CharField(required=False, allow_blank=True, max_length=128)
description = serializers.CharField(required=False, allow_blank=True, max_length=128)
logo = serializers.CharField(required=False, allow_blank=True, max_length=128)
invoicing_data = serializers.CharField(required=False, allow_blank=True, max_length=128)
lead_quota = serializers.IntegerField()
notes = serializers.CharField(required=False, allow_blank=True, max_length=128)
subscription_active = serializers.BooleanField()
lead_active = serializers.BooleanField()
last_notification_date = serializers.DateTimeField(required=False)
received_leads_count = serializers.IntegerField()
trial_start_date = serializers.DateField()
remove_from_guide = serializers.BooleanField()
mailing_active = serializers.BooleanField()
created = serializers.DateTimeField()
def create(self, validated_data):
c = Company()
c.name = validated_data.get("name")
c.email = validated_data.get("email")
c.contact_email = validated_data.get("contact_email")
c.contact_phone = validated_data.get("contact_phone")
c.contact_first_name = validated_data.get("contact_first_name")
c.contact_last_name = validated_data.get("contact_last_name")
c.web = validated_data.get("web")
c.description = validated_data.get("descrition")
c.logo = validated_data.get("logo")
c.invoicing_data = validated_data.get("invoicing_data")
c.lead_quota = validated_data.get("lead_quota")
c.notes = validated_data.get("notes")
c.subscription_active = validated_data.get("subscription_active")
c.lead_active = validated_data.get("lead_active")
c.last_notification_date = validated_data.get("last_notification_date")
c.received_leads_count = validated_data.get("received_leads_count")
c.trial_start_date = validated_data.get("trial_start_date")
c.remove_from_guide = validated_data.get("remove_from_guide")
c.mailing_active = validated_data.get("mailing_active")
c.created = validated_data.get("created")
c.save()
return c
def update(self, c, validated_data):
c.id = validated_data.get("id", c.id)
c.name = validated_data.get("name", c.name)
c.email = validated_data.get("email", c.email)
c.contact_email = validated_data.get("contact_email", c.contact_email)
c.contact_phone = validated_data.get("contact_phone", c.contact_phone)
c.contact_first_name = validated_data.get("contact_first_name", c.contact_first_name)
c.contact_last_name = validated_data.get("contact_last_name", c.contact_last_name)
c.web = validated_data.get("web", c.web)
c.description = validated_data.get("description", c.description)
c.logo = validated_data.get("logo", c.logo)
c.invoicing_data = validated_data.get("invoicing_data", c.invoicing_data)
c.lead_quota = validated_data.get("lead_quota", c.lead_quota)
c.notes = validated_data.get("notes", c.notes)
c.subscription_active = validated_data.get("subscription_active", c.subscription_active)
c.lead_active = validated_data.get("lead_active", c.lead_active)
c.last_notification_date = validated_data.get("last_notification_date", c.last_notification_date)
c.received_leads_count = validated_data.get("received_leads_count", c.received_leads_count)
c.trial_start_date = validated_data.get("trial_start_date", c.trial_start_date)
c.remove_from_guide = validated_data.get("remove_from_guide", c.remove_from_guide)
c.mailing_active = validated_data.get("mailing_active", c.mailing_active)
c.created = validated_data.get("created", c.created)
c.save()
return cAs you see we are not using a ModelSerializer, the ModelSerializers from rest_framework are glorious because they read all the data from the Model subclass and automatically generate that code that you see above.
But here we do it ourselves.
The serializers here specifies (not unlike Django’s forms or Model classes) the fields that our API inputs/responses will include and their types.
And with those 2 methods, create() and update() how to create or modify our Company instances with the data that we took from the API invocations.
Now we need to create the API endpoints. The views:
in views.py add this:
from django.http import HttpResponse
from rest_framework import status
from rest_framework.decorators import api_view
from rest_framework.response import Response
from .models import Company
from .serializers import CompanySerializer
@api_view(['GET', 'POST'])
def company_list(request, format=None):
"""
List all code snippets, or create a new snippet.
"""
if request.method == 'GET':
companies = Company.objects.all()
serializer = CompanySerializer(companies, many=True)
return Response(serializer.data)
elif request.method == 'POST':
serializer = CompanySerializer(data=request.data)
if serializer.is_valid():
serializer.save()
return Response(serializer.data, status=status.HTTP_201_CREATED)
return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
@api_view(['GET', 'PUT', 'DELETE'])
def company_detail(request, id, format=None):
"""
Retrieve, update or delete a code snippet.
"""
company = Company.objects.get(id)
if company is None:
return Response(status=status.HTTP_404_NOT_FOUND)
if request.method == 'GET':
serializer = CompanySerializer(company)
return Response(serializer.data)
elif request.method == 'PUT':
serializer = CompanySerializer(company, data=request.data)
if serializer.is_valid():
serializer.save()
return Response(serializer.data)
return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
elif request.method == 'DELETE':
company.delete()
return HttpResponse(status=status.HTTP_204_NO_CONTENT)Same as before. If we used one of rest_framework’s shorthands for views creation it would do all of that in 2 lines but as we are not using Django’s ORM we have to do this ourselves.
Last but not least we need to now modify our urls.py so these views can be reached:
from django.conf.urls import url
from rest_framework.urlpatterns import format_suffix_patterns
from table_orders import views
urlpatterns = [
url(r'^companies/$', views.company_list),
url(r'^companies/(?P<id>[0-9]+)/$', views.company_detail),
]
urlpatterns = format_suffix_patterns(urlpatterns)And in the root urls.py:
from django.conf.urls import url, include
from django.contrib import admin
from table_orders import views
urlpatterns = [
url(r'^', include('table_orders.urls')),
url(r'^admin/', admin.site.urls),
]And that’s it !
Now you can just run the server and visit http://127.0.0.1:8000/companies and start playing with it.
