Develop a web API that returns data stored in DB with Django and SQLite

Overview

This article walks you through the steps of a beginner developing a coupon distribution service for the iPhone with a RESTful API and swift. It is a very detour implementation because it was implemented while examining the technical elements one by one.

Up to the previous Display data acquired by API in TableView of swift, the webAPI that distributes coupon information and coupons using that webAPI The iPhone app to be delivered to the user has been completed at the "super" minimum required level. From here, we will improve the implementation method, functions, and UI of the web API and application.

This time, we will modify the hard-coded coupon data so that it will be managed in the database. The API request and response specifications will be rearranged accordingly. The database uses SQLite, which is built into Python from the beginning.

reference

• Python Django Super Introductory by Yano Palm Tatsu Shuwa System
• Introduction to Python Django (6) Python
• Summary of Django database operations
• [Retrieve specific values from Django's query set](http://wpress.biz/django/2017/02/22/django%E3%81%AE%E3%82%AF%E3%82%A8% E3% 83% AA% E3% 82% BB% E3% 83% 83% E3% 83% 88% E3% 81% 8B% E3% 82% 89% E7% 89% B9% E5% AE% 9A% E3% 81% AE% E5% 80% A4% E3% 82% 92% E5% 8F% 96% E3% 82% 8A% E5% 87% BA% E3% 81% 99 /)
• OR search with Django

environment

Mac OS 10.15 VSCode 1.39.2 pipenv 2018.11.26 Python 3.7.4 Django 2.2.6

procedure

• Organize web API request and response specifications
• Set up the database
• Create a model
• Migrate and automatically generate a table based on the model
• Populate the table
• Modified to respond to the information of one coupon registered in SQLite
• Modified to respond to multiple coupon information
• Modified to respond to all coupon information that meets the request parameter conditions
• Modified to respond only to coupons that meet the expiration date and status

Organize web API request and response specifications

Since you need to decide which data to manage in the database, consider the API request and response specifications. The coupon distribution API for the subject has the following specifications.

** Request parameters **

• Available stores (however, you can request without specifying)

response

• Coupon code
• Coupon benefits
• Comment
• Available stores
• Start date
• Expiration date
• Status (Available / Not available flag)

The table name should be Coupon.

If you receive an available store with the request parameter, only the coupon that can be used at the specified store will be returned. If the request parameter is empty, all currently available coupons will be returned.

The definitions of available coupons are as follows.

• The usage start date has passed
• The expiration date has not passed
• Status is available

Set up the database

The database settings are described in settings.py under the project name directory (/ami_coupon_api in the example of this article).

As shown in the screenshot, the settings are basically made when using SQLite from the beginning, so editing is not required when using SQLite. Just in case, I just checked if it was changed from the initial setting.

By default, db.sqlite3 is generated when the project is created.

Django supports MySQL and PostgreSQL in addition to SQLite.

Create a model

Django automatically creates the database and table based on the model, so you don't have to create the table yourself. Create the original model. The model (model class) is linked to the table 1: 1 and the model field and table items (instances) are also linked 1: 1 so the image is simple.

This time we need a Coupon table to store coupon information, so open models.py under the application directory (/ coupon in the example of this article) and create a Coupon class there.

The model is defined as follows. [Model field name] = models. [Data type]

Here is the model I created.

models.py

from django.db mport models

class Coupon(models.Model):
    code = models.CharField(max_length=20) #Coupon code
    benefit = models.CharField(max_length=1000) #Coupon benefits
    explanation = models.CharField(max_length=2000) #comment
    store = models.CharField(max_length=1000) #Available stores
    start = models.DateField() #Start date
    deadline = models.DateField() #Expiration date
    status = models.BooleanField() #Status (available/Impossible flag)

    def __str__(self):
        return '<Coupon:id=' + str(self.id) + ',' + self.code + ',' + self.benefit + '>'

The def __str__ (self): ~ part of the above code is the ** definition of display rules ** when you access the Django server with a browser and view the stored data. It works as a model even if there is no description, but I think it is better to define it for easy management.

In the case of the code below, when you access the management screen with a browser, the ʻid, code, and benefit of each coupon data are displayed. ʻId seems to be automatically generated if you don't set a primary key.

    def __str__(self):
        return '<Coupon:id=' + str(self.id) + ',' + self.code + ',' + self.benefit + '>'

Migrate and automatically generate a table based on the model

In the terminal, enter the pipenv shell and change to the project directory (the directory where manage.py is located). Then execute the command to create the migration file.

$ python manage.py makemigrations [Application name (Coupon in the example of this article)]

The migration file has been created.

Then execute the migration.

$ python manage.py migrate

The migration was successful and probably a coupon table was created in SQLite.

Populate the table

Use the database management tool that comes standard with django to check the coupon table that was probably created and populate the coupon demo data. Since the management tool is a web application that runs on django's server, you need to start django's server before using the tool.

Before you can log in to the administration tools, you need to register the administrator using the command. The following 4 items need to be set. When you enter the command, you will be prompted to enter it sequentially.

• Username
• Email address
• Password

$ python manage.py createsuperuser

Next, register the Coupon model so that it can be managed by the management tool. Registration is done in ʻadmin.py under the directory of the application (Coupon in the example of this article). Modify ʻadmin.py as follows.

• Import the Coupon class from models.py.
• Call the admin.site.register` method and specify the model name as an argument.

admin.py

from django.contrib import admin
from .models import Coupon #add to

# Register your models here.
admin.site.register(Coupon) #add to

Start the django server and access http://10.0.0.127:8000/admin to open the admin tool login screen.

If you log in with the administrator you created earlier, the available tables (models) will be displayed. (The Coupon table created by the Coupon application is displayed at the bottom. It is displayed as Coupons, but you do not need to worry because the "s" is displayed by the management tool without permission)

If you select the Coupon table, you can check the contents of the table. Since the data is empty in the initial state, nothing is displayed.

Press the ** "ADD COUPON +" ** button on the upper right to proceed to the data entry screen. Here, all the model fields defined in the model are displayed, so you can confirm that the table has been created without any problems.

Enter the data and press the ** "SAVE" ** button at the bottom right to register the data.

I think that intuitive operation is possible. Insert 5 coupon data in the same way. Available stores, start date, end date, and status have been added with variations so that they can be tested later.

Looking at the list display, the data in each row is displayed in the format defined by __str__ (self) of the coupon class of models.py.

Modified to respond with JSON for the information of one coupon registered in SQLite

Modify views.py to modify the information of one of the coupons registered in SQLite so that it responds in json format.

First, import the Coupon class of models.py into views.py. Add from .models import Coupon to views.py.

Next, define the response processing for the request. Here, add the process to get all the data of Coupon table. data = Coupon.object.all()

Next, create dictionary type data for Json.dump.

The data that can be acquired by'Coupon.object.all ()'is an array of data type, and each array contains a record in one row of the table. To retrieve a single row of records, specify the [x] th of an array of data type variables. To retrieve the items in the record, use data [x] followed by. [Column name of the item you want to get].

Furthermore, since json.dumps will not accept the data type as it is, cast it to the String type.

I wrote a process to specify the 0th of the array and format the coupon information of the first record into a dictionary type.

params = {
            'coupon_code':str(data[0].code),
            'coupon_benefits':str(data[0].benefit),
            'coupon_explanation':str(data[0].explanation),
            'coupon_store':str(data[0].store),
            'coupon_start':str(data[0].start),
            'coupon_deadline':str(data[0].deadline),
            'coupon_status':str(data[0].status),
         }

The process from json.dumps is the same as before the modification. The modified views.py is as follows.

views.py

from django.shortcuts import render
from django.http import HttpResponse
from .models import Coupon #Import Coupon class
import json

def coupon(request):
    data = Coupon.objects.all() #Get all records in the table
    params = {
            'coupon_code':str(data[0].code), #Since each record is of data type, cast it to String type
            'coupon_benefits':str(data[0].benefit),
            'coupon_explanation':str(data[0].explanation),
            'coupon_store':str(data[0].store),
            'coupon_start':str(data[0].start),
            'coupon_deadline':str(data[0].deadline),
            'coupon_status':str(data[0].status),
        }
    json_str = json.dumps(params, ensure_ascii=False, indent=2)
    return HttpResponse(json_str)

If you enter the request URL in the browser and check if json is responded, the information of one coupon is responded with json.

Modified to respond to multiple coupon information

What we want to realize with the API is ** a specification that responds to all coupons that meet the conditions received in the request parameters **, so first we will modify it so that multiple coupon information can be responded with json.

To respond to multiple records with json, store the information of one record in dictionary type in an array and pass it to json.dumps.

First, prepare an array to store multiple records as a dictionary type. The array name is coupons.

Next, add a for statement so that the process of making records into a dictionary type is repeated for the number of records.

Set to for record in data: so that one record line is stored in the dictionary variable (record) every round. This process is further enclosed in a for statement so that the coupon information converted into a dictionary type is stored in the coupons array, one record per lap.

Finally, change the dictionary type passed to json.dumps to pass the array coupons containing multiple dictionary types. The modified views.py is here.

views.py

from django.shortcuts import render
from django.http import HttpResponse
from .models import Coupon #Import Coupon class
import json

def coupon(request):
    data = Coupon.objects.all() #Get all records in the table

    coupons = [] #Prepare an array to store multiple records as a dictionary type
    for record in data: #Convert one record at a time to dictionary type using for statement
        params = {
            'coupon_code':str(record.code), #Since each record is of data type, cast it to String type
            'coupon_benefits':str(record.benefit),
            'coupon_explanation':str(record.explanation),
            'coupon_store':str(record.store),
            'coupon_start':str(record.start),
            'coupon_deadline':str(record.deadline),
            'coupon_status':str(record.status),
            }
        coupons.append(params) #Store dictionary-type records in an array

    json_str = json.dumps(coupons, ensure_ascii=False, indent=2) #Pass an array containing multiple records in dictionary type
    return HttpResponse(json_str)

If you make a request without specifying the request parameter in the browser, all coupon information will be returned in Json.

Modified to respond to coupons that meet the requirements of the request parameters

Use Coupon.object.filter ([conditional expression]) to get records that meet certain criteria.

Since the request parameter is an available store (item linked to the store in the Coupon table), use the ʻif else statement, and if there is a parameter, use Coupon.objects.filterto get the coupon that can be used at the specified store and all stores. However, if there are no parameters, you can get all the coupons withCoupon.objects.all`.

The conditional expression is ** Coupon table available stores are the same as the requested store OR all available stores ** It will be.

Multi-condition expressions are easier to write with Q objects, so import the Q object into'views.py'.

from django.db.models import Q

Next, of the programs created so far,

data = Coupon.objects.all() #Get all records in the table`

Add a conditional branch to the part of and do as follows. Using the Q object, as mentioned above, I have set the conditional expression of the OR condition of ** the same coupon ** as the request store ** and ** the coupon ** that is all stores **.

if 'coupon_store' in request.GET: 
    coupon_store = request.GET['coupon_store'] 
    data = Coupon.objects.filter(Q(store=coupon_store) | Q(store='All stores'))
else:
    data = Coupon.objects.all()

About the description method of multiple conditions using Q object

OR search
Q(store=coupon_store) | Q(store='All stores')

AND search
Q(store=coupon_store) , Q(store='All stores')

It will be.

If you request by specifying the Kanda store in the browser as a trial, the coupon information of the Kanda store and all stores will be displayed.

Modify so that only coupons that meet the expiration date and status will be responded.

Based on the program implemented so far, add search conditions so that only coupons that have not passed the expiration date and have a status of "available" ** will be responded.

Regarding the usage start date, we decided not to add it to the search conditions in consideration of the need to deliver coupons as a notice.

The items of the model field of the Coupon table linked to the expiration date and status are as follows.

Expiration date: deadline
Status: status

First, compare the date (date) of the request with the expiration date of the coupon, and modify it so that only those that have not passed the expiration date are obtained from the Coupon table.

Import datetime into views.py so that you can get the dates.

import datetime #Import datetime so you can get the date and time

Add the process to get the date in the coupon function.

today = datetime.date.today()

** Add the query Q (deadline__gte = today) to Coupon.object.filter to determine if the coupon expires after the date obtained above **.

if 'coupon_store' in request.GET: #Processing when a store is specified in the request parameter
        coupon_store = request.GET['coupon_store']
        data = Coupon.objects.filter(Q(deadline__gte=today),Q(store=coupon_store) | Q(store='All stores')) # リクエストされた店舗とAll storesで使えるクーポンを取得
    else: #Returns all coupons if there are no request parameters
        data = Coupon.objects.filter(Q(deadline__gte=today))

With this, we have completed the process of responding to items that have not expired. If you make a request without the request parameter, the expired coupon (0004) will not be responded.

Then add a Q (status = True) query to Coupon.object.filter so that only coupons with status available (True) are retrieved from the Coupon table.

    if 'coupon_store' in request.GET: #Processing when a store is specified in the request parameter
        coupon_store = request.GET['coupon_store']

        data = Coupon.objects.filter(Q(deadline__gte=today),Q(status=True),Q(store=coupon_store) | Q(store='All stores')) # リクエストされた店舗とAll storesで使えるクーポンを取得
    else: #Returns all coupons if there are no request parameters
        data = Coupon.objects.filter(Q(deadline__gte=today),Q(status=True))

When I request, the response no longer includes coupons with a status of False.

that's all.

Next, do Modify iOS app request to this API.