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

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

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 **

response

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.

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. check-settingpy-for-sqlite.png

By default, db.sqlite3 is generated when the project is created. check-sqlite-explorer.png

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. make-migration-file-coupon.png

Then execute the migration.

$ python manage.py migrate

The migration was successful and probably a coupon table was created in SQLite. migrate-coupon-01.png

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.

$ python manage.py createsuperuser
make-superuser-of-django-mask.png

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.

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. django-webconsole-login-s.png

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) django-webconsole-home-s.png

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. django-webconsole-coupon-home-s.png

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. django-webconsole-coupon-add-s.png

Enter the data and press the ** "SAVE" ** button at the bottom right to register the data. django-webconsole-after-input-s.png

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. django-webconsole-coupon-records-s.png

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. get-one-record-from-sqlite.png

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. get-all-couponData-from-sqlite-s.png

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. get-couponData-canuse-kanda-s.png

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. get-all-couponData-with-deadline-filter-s.png

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. get-all-couponData-with-status-filter-s.png

that's all.

Next, do Modify iOS app request to this API.

Recommended Posts

Develop a web API that returns data stored in DB with Django and SQLite
Tornado-Let's create a Web API that easily returns JSON with JSON
Create a web API that can deliver images with Django
[Python / Django] Create a web API that responds in JSON format
Build a data analysis environment that links GitHub authentication and Django with JupyterHub
I want to create an API that returns a model with a recursive relationship in the Django REST Framework
A class that creates DB creation-data insertion with SQLite3 quickly
(For beginners) Try creating a simple web API with Django
A server that returns the number of people in front of the camera with bottle.py and OpenCV
Create an API that returns data from a model using turicreate
Build a web application with Django
Launched a web application on AWS with django and changed jobs
GraphQL API with graphene_django in Django
I made a WEB application with Django
Put Docker in Windows Home and run a simple web server with Python
[ES Lab] I tried to develop a WEB application with Python and Flask ②
The story of making a web application that records extensive reading with Django
Try creating a web application with Vue.js and Django (Mac)-(1) Environment construction, application creation
Create a clean DB for testing with FastAPI and unittest the API with pytest
Generate and post dummy image data with Django
Delete data in a pattern with Redis Cluster
How to develop a cart app with Django
Start Django in a virtual environment with Pipenv
Build a Django environment with Vagrant in 5 minutes
Steps to develop a web application in Python
Launch a web server with Python and Flask
Configure a module with multiple files in Django
How to create a Rest Api in Django
Extract data from a web page with Python
Automate background removal for the latest portraits in a directory with Python and API
How to use fixture in Django to populate sample data associated with a user model
I created a stacked bar graph with matplotlib in Python and added a data label
I made a web application that maps IT event information with Vue and Flask
Create a flag in settings that will be True only when testing with Django
A memo that detects and returns an image acquired from a webcam with Django's OpenCV
Try making a simple website with responder and sqlite3
Train MNIST data with a neural network in PyTorch
Looking back on creating a web service with Django 1
(Python) Try to develop a web application using Django
Playing with a user-local artificial intelligence API in Python
Use Cursur that closes automatically with sqlite3 in Python
Looking back on creating a web service with Django 2
A server that echoes data POSTed with flask / python
Get comments and subscribers with the YouTube Data API
Creating an API that returns negative-positive inference results using BERT in the Django REST framework
A learning roadmap that allows you to develop and publish services from scratch with Python