App Development for Django

This tutorial shows you how to build a simple Python application based on TiDB and Django. The sample application to build here is a simple CRM tool where you can add, query, and update customer and order information.

Step 1. Start a TiDB cluster

Start a pseudo TiDB cluster on your local storage:

docker run -p 127.0.0.1:$LOCAL_PORT:4000 pingcap/tidb:v5.1.0

The above command starts a temporary and single-node cluster with mock TiKV. The cluster listens on the port $LOCAL_PORT. After the cluster is stopped, any changes already made to the database are not persisted.

Step 2. Create a database

1. In the SQL shell, create the django database that your application will use:

   CREATE DATABASE django;
   

2. Create a SQL user for your application:

   CREATE USER <username> IDENTIFIED BY <password>;
   

   Take note of the username and password. You will use them in your application code when initializing the project.

3. Grant necessary permissions to the SQL user you have just created:

   GRANT ALL ON django.* TO <username>;
   

Step 3. Set virtual environments and initialize the project

1. Use Poetry, a dependency and package manager in Python, to set virtual environments and initialize the project.

   Poetry can isolate system dependencies from other dependencies and avoid dependency pollution. Use the following command to install Poetry.

   pip install --user poetry
   

2. Initialize the development environment using Poetry:

   poetry init --no-interaction --dependency django
   poetry run django-admin startproject tidb_example
   
   mv pyproject.toml ./tidb_example
   cd tidb_example
   
   poetry add django-tidb
   
   poetry shell
   

3. Modify the configuration file. The configuration in tidb_example/settings.py is as follows.

   USE_TZ = True
   
   DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.sqlite3',
           'NAME': BASE_DIR / 'db.sqlite3',
       }
   }
   
   DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField'
   

   Modify the configuration above as follows. This is used for connection to TiDB.

   USE_TZ = False
   
   DATABASES = {
       'default': {
           'ENGINE': 'django_tidb',
           'NAME': 'django',
           'USER': 'root',
           'PASSWORD': '',
           'HOST': '127.0.0.1',
           'PORT': 4000,
       },
   }
   
   DEFAULT_AUTO_FIELD = 'django.db.models.AutoField'
   

Step 4. Write the application logic

After you have configured the application's database connection, you can start building out the application. To write the application logic, you need to build the models, build the controller, and define the URL routes.

1. Build models that are defined in a file called models.py. You can copy the sample code below and paste it into a new file.

   from django.db import models
   
   class Orders(models.Model):
       id = models.AutoField(primary_key=True)
       username = models.CharField(max_length=250)
       price = models.FloatField()
   

2. Build class-based views in a file called views.py. You can copy the sample code below and paste it into a new file.

   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 functools import wraps
   import json
   import sys
   import time
   
   from .models import *
   
   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 Exception as e:
                   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 OrderView(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())
        username = form_data['username']
        price = form_data['price']
        c = Orders(username=username, price=price)
        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)
        Orders.objects.filter(id=id).delete()
        return HttpResponse(status=200)
```

3. Define URL routes in a file called urls.py. The django-admin command-line tool has generated this file when you create the Django project, so the file should already exist in tidb_example/tidb_example. You can copy the sample code below and paste it into the existing urls.py file.

   from django.contrib import admin
   from django.urls import path
   from django.conf.urls import url
   
   from .views import OrderView, PingView
   
   urlpatterns = [
       path('admin/', admin.site.urls),
   
       url('ping/', PingView.as_view()),
   
       url('order/', OrderView.as_view(), name='order'),
       url('order/<int:id>/', OrderView.as_view(), name='order'),
   ]
   

Step 5. Set up and run the Django application

In the top tidb_example directory, use the manage.py script to create Django migrations that initialize the database for the application:

python manage.py makemigrations tidb_example
python manage.py migrate tidb_example
python manage.py migrate

Then start the application:

python3 manage.py runserver 0.0.0.0:8000

To test the application by inserting some example data, run the following commands:

curl --request POST '127.0.0.1:8000/order/' \
--data-raw '{
    "uid": 1,
    "price": 3.12
}'

curl --request PATCH '127.0.0.1:8000/order/' --data-raw '{ "oid": 1, "price": 312 }'

curl --request GET '127.0.0.1:8000/order/' --data-raw '{ "oid": 1 }'

To verify whether the data insertion is successful, open the terminal with the SQL shell to check:

MySQL root@127.0.0.1:(none)> select * from django.tidb_example_orders;
+-----+-----+-------+
| oid | uid | price |
+-----+-----+-------+
| 1   | 1   | 312.0 |
+-----+-----+-------+
1 row in set
Time: 0.008s

The result above shows that the data insertion is successful. Then you can delete the inserted data:

curl --request DELETE '127.0.0.1:8000/order/' --data-raw '{ "oid": 1 }'