Build a Python App with CockroachDB and Django

$ python3 -m pip install 'django>=3.1.*'

$ python3 -m pip install 'django-cockroachdb>=3.1.*'

$ django-admin startproject cockroach_example

ALLOWED_HOSTS = ['0.0.0.0']

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'cockroach_example',
]

from django.db import models


class Customers(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=250)


class Products(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=250)
    price = models.DecimalField(max_digits=18, decimal_places=2)


class Orders(models.Model):
    id = models.AutoField(primary_key=True)
    subtotal = models.DecimalField(max_digits=18, decimal_places=2)
    customer = models.ForeignKey(
        Customers, on_delete=models.CASCADE, null=True)
    product = models.ManyToManyField(Products)

from django.http import JsonResponse, HttpResponse
from django.utils.decorators import method_decorator
from django.views.generic import View
from django.views.decorators.csrf import csrf_exempt
from django.db import Error, OperationalError
from django.db.transaction import atomic
from psycopg2 import errorcodes
from functools import wraps
import json
import sys
import time

from .models import *

# Warning: Do not use retry_on_exception in an inner nested transaction.


def retry_on_exception(view, num_retries=3, on_failure=HttpResponse(status=500), delay_=0.5, backoff_=1.5):
    @wraps(view)
    def retry(*args, **kwargs):
        delay = delay_
        for i in range(num_retries):
            try:
                return view(*args, **kwargs)
            except OperationalError as ex:
                if i == num_retries - 1:
                    return on_failure
                elif getattr(ex.__cause__, 'pgcode', '') == errorcodes.SERIALIZATION_FAILURE:
                    time.sleep(delay)
                    delay *= backoff_
                else:
                    return on_failure
            except Error as ex:
                return on_failure
    return retry


class PingView(View):
    def get(self, request, *args, **kwargs):
        return HttpResponse("python/django", status=200)


@method_decorator(csrf_exempt, name='dispatch')
class CustomersView(View):
    def get(self, request, id=None, *args, **kwargs):
        if id is None:
            customers = list(Customers.objects.values())
        else:
            customers = list(Customers.objects.filter(id=id).values())
        return JsonResponse(customers, safe=False)

    @retry_on_exception
    @atomic
    def post(self, request, *args, **kwargs):
        form_data = json.loads(request.body.decode())
        name = form_data['name']
        c = Customers(name=name)
        c.save()
        return HttpResponse(status=200)

    @retry_on_exception
    @atomic
    def delete(self, request, id=None, *args, **kwargs):
        if id is None:
            return HttpResponse(status=404)
        Customers.objects.filter(id=id).delete()
        return HttpResponse(status=200)

    # The PUT method is shadowed by the POST method, so there doesn't seem
    # to be a reason to include it.


@method_decorator(csrf_exempt, name='dispatch')
class ProductView(View):
    def get(self, request, id=None, *args, **kwargs):
        if id is None:
            products = list(Products.objects.values())
        else:
            products = list(Products.objects.filter(id=id).values())
        return JsonResponse(products, safe=False)

    @retry_on_exception
    @atomic
    def post(self, request, *args, **kwargs):
        form_data = json.loads(request.body.decode())
        name, price = form_data['name'], form_data['price']
        p = Products(name=name, price=price)
        p.save()
        return HttpResponse(status=200)

    # The REST API outlined in the github does not say that /product/ needs
    # a PUT and DELETE method


@method_decorator(csrf_exempt, name='dispatch')
class OrdersView(View):
    def get(self, request, id=None, *args, **kwargs):
        if id is None:
            orders = list(Orders.objects.values())
        else:
            orders = list(Orders.objects.filter(id=id).values())
        return JsonResponse(orders, safe=False)

    @retry_on_exception
    @atomic
    def post(self, request, *args, **kwargs):
        form_data = json.loads(request.body.decode())
        c = Customers.objects.get(id=form_data['customer']['id'])
        o = Orders(subtotal=form_data['subtotal'], customer=c)
        o.save()
        for p in form_data['products']:
            p = Products.objects.get(id=p['id'])
            o.product.add(p)
        o.save()
        return HttpResponse(status=200)

"""cockroach_example URL Configuration

The `urlpatterns` list routes URLs to views. For more information please see:
    https://docs.djangoproject.com/en/2.2/topics/http/urls/
Examples:
Function views
    1. Add an import:  from my_app import views
    2. Add a URL to urlpatterns:  path('', views.home, name='home')
Class-based views
    1. Add an import:  from other_app.views import Home
    2. Add a URL to urlpatterns:  path('', Home.as_view(), name='home')
Including another URLconf
    1. Import the include() function: from django.urls import include, path
    2. Add a URL to urlpatterns:  path('blog/', include('blog.urls'))
"""
from django.contrib import admin
from django.conf.urls import url

from .views import CustomersView, OrdersView, PingView, ProductView

urlpatterns = [
    url('admin/', admin.site.urls),

    url('ping/', PingView.as_view()),

    # Endpoints for customers URL.
    url('customer/', CustomersView.as_view(), name='customers'),
    url('customer/<int:id>/', CustomersView.as_view(), name='customers'),

    # Endpoints for customers URL.
    url('product/', ProductView.as_view(), name='product'),
    url('product/<int:id>/', ProductView.as_view(), name='product'),

    url('order/', OrdersView.as_view(), name='order'),
]

$ python3 manage.py makemigrations cockroach_example

$ python3 manage.py migrate

> USE bank;

> SHOW TABLES;

  schema_name |            table_name            | type  | estimated_row_count
--------------+----------------------------------+-------+----------------------
  public      | auth_group                       | table |                   0
  public      | auth_group_permissions           | table |                   0
  public      | auth_permission                  | table |                  36
  public      | auth_user                        | table |                   0
  public      | auth_user_groups                 | table |                   0
  public      | auth_user_user_permissions       | table |                   0
  public      | cockroach_example_customers      | table |                   0
  public      | cockroach_example_orders         | table |                   0
  public      | cockroach_example_orders_product | table |                   0
  public      | cockroach_example_products       | table |                   0
  public      | django_admin_log                 | table |                   0
  public      | django_content_type              | table |                   9
  public      | django_migrations                | table |                   1
  public      | django_session                   | table |                   0
(14 rows)

$ python3 manage.py runserver 0.0.0.0:8000

$ curl --header "Content-Type: application/json" \
--request POST \
--data '{"name":"Carl"}' http://0.0.0.0:8000/customer/

$ curl http://0.0.0.0:8000/customer/

[{"id": 523377322022797313, "name": "Carl"}]

> SELECT * FROM cockroach_example_customers;

          id         | name
---------------------+-------
  523377322022797313 | Carl
(1 row)