Try making a simple website with responder and sqlite3

Recently, I read a book called "Make with Nuxt.js and Python! Introduction to slimy AI application development", and the book is a fairly new python web site. Since the framework responder is introduced, I would like to make a simple website after studying various things with interest.

The target site this time is a site where you can connect to a SQL database to view, add, update, and delete data.

The database uses sqlite3, which is the simplest.

About the module to use

Besides the book, I read many articles about how to use responder and sqlite3.

responder -Simple chat app with Responder + WebSocket -Build a web application with Python + Responder. -First Responder (Python's next-generation web framework) -About the cool Python Web Framework "responder" for humans, and the author Kenneth Reitz

sqlite3 -SQLite in Python

These articles have been very helpful, so I'll omit the basic usage here.

Construction

Preparation

The database should be SQLite for simplicity.

SQLite is not very suitable when creating an actual site, but unlike MySQL and PostgreSQL, SQLite does not need to be installed and is convenient because it is included from the beginning.

The only python modules used are mainly responder and sqlite3.

sqlite3 is a standard python module, so you only need to install responder.

Responder official website says to use pipenv, but you can easily install it with ordinary pip.

pip install responder

function

Create a simple website like this.

--Only one python file controls the site --Each page is made into a jinja2 template through responder --Only 3 pages ---- Index page where you can view and download all data ---- Page where you can edit the data ---- Page when an error occurs --The style sheet is also a simple css file, but prepare it for the time being --Input and edit data with forms and input --javascript doesn't come into play --A table with only two columns in the database

Database

Column name Data type
name namae text
level lv integer

My goal this time is just to make sure I can connect to the database and interact with it, so for the sake of simplicity I'll make it a table with only two columns.

If this table is SQL code

create table kyara (namae text,lv integer, primary key (namae))

File

The site is all just made up of these 5 files.

截屏2020-05-19-19.42.03.png

The .html file is a jinja2 template. I've never used jinja2 before, but it's a bit like a django template.

By the way, as you can see, the name of the file is " [A Certain Magical Index](https://ja.wikipedia.org/wiki/ A Certain Magical Index) ".

│- majutsu.py server executable code
│─ index.html index page
│- toaru.html data display and editing page
│- no.html Page that appears when something wrong happens
└─ librorum
   └─prohibitorum.css stylesheet

environment

I think that there is not much problem even if the OS and the version of python and responder are different, but I will write the environment when I tried this time.

--Mac OS 10.15.4 [Catalina](https://ja.wikipedia.org/wiki/ I have reincarnated as a villain daughter who has only the ruin flag of the maiden game ...)

code

Next is a description of the code in each file.

html template

html file of jinja2 template

index.html

First, the index page.

<head>
    <meta charset="utf-8">
    <title>INDEX of a certain site</title>
    <link rel="stylesheet" href="/librorum/prohibitorum.css" type="text/css" media="all">
</head>

<body>
    <h3>A certain sqlite3 responder</h3>
    <ul>
        {% for k in kyara %}
        <li>
            <a href="/toaru/{{ k[0] }}">{{ k[0] }}</a> lv {{ k[1] }}
        </li>
        {% endfor %}
    </ul>

    <form action="/insert" method="post">
        <div>name<input type="text" name="namae"></div>
        <div>level<input type="text" name="lv"><br></div>
        <div><input type="submit" value="add to"></div>
    </form>

    <a href="/download">download</a>
</body>

The composition is --Data enumeration --Form to add new data- --There is a link to download the data.

toaru.html

Next is a page to display and edit the data of a certain character.

<head>
    <meta charset="utf-8">
    <title>a{{ namae }}page of</title>
    <link rel="stylesheet" href="/librorum/prohibitorum.css" type="text/css" media="all">
</head>

<body>
    <form action="/update/{{ namae }}" method="post">
        <div>name: <input type="text" name="namae" value="{{ namae }}"></div>
        <div>level: <input type="text" name="lv" value="{{ lv }}"></div>
        <input type="submit" value="update">
    </form>

    <form action="/delete/{{ namae }}" method="delete">
        <input type="submit" value="Delete">
    </form>
    
    <div><a href="/">Return</a></div>
</body>

The composition is --Editable character data form --Delete data button --Link back to index

no.html

And the page that appears when something goes wrong. There is only a link back to the index.

<head>
    <meta charset="utf-8">
</head>

<body>
    <h1>NO!!</h1>
The sky is so blue but the tip is pitch black<br><br>
    <a href="/">~-Return-~</a>
</body>

python

* A file that uses magic to control everything on the * </ s> site.

If it is a large site, it may be necessary to decompose it into various files, but this time it is a small site, so it is not necessary to separate it into one file.

The controllers for all routes and all the code that connects to the database can be found here.

majutsu.py

import responder,sqlite3,urllib,os

#File to save data
dbfile = 'dedicatus545.db'
#API object
api = responder.API(templates_dir='.', #Template folder
                    static_dir='librorum', #Static file folder
                    static_route='/librorum') #Static file root

#Index page
@api.route('/')
def index(req,resp):
    with sqlite3.connect(dbfile) as conn:
        sql_select = '''
            select * from kyara
        ''' #Display data of all characters
        kyara = conn.execute(sql_select).fetchall()
        resp.html = api.template('index.html',kyara=kyara)

#Each data display and editing page
@api.route('/toaru/{namae}')
def select(req,resp,*,namae):
    with sqlite3.connect(dbfile) as conn:
        sql_select = '''
            select * from kyara where namae==?
        ''' #Take the data of the character with that name
        kyara = conn.execute(sql_select,[namae]).fetchone()
        if(kyara):
            resp.html = api.template('toaru.html',namae=kyara[0],lv=kyara[1])
        else:
            print('This page does not exist')
            api.redirect(resp,'/no') #If a name that does not exist is entered, go to the error page
    
#Page if something goes wrong
@api.route('/no')
def no(req,resp):
    resp.html = api.template('no.html')

#After adding data
@api.route('/insert')
async def insert(req,resp):
    try:
        with sqlite3.connect(dbfile) as conn:
            param = await req.media() #Get data from form
            namae = param['namae']
            lv = param['lv']
            sql_insert = '''
                insert into kyara (namae,lv)
                values (?,?)
            ''' #Add new data
            conn.execute(sql_insert,(namae,lv))
        api.redirect(resp,'/') #Return to index page
    except Exception as err:
        print(f'Error: {type(err)} {err}')
        api.redirect(resp,'/no') #If something goes wrong

#After updating the data
@api.route('/update/{namae0}')
async def update(req,resp,*,namae0):
    try:
        with sqlite3.connect(dbfile) as conn:
            param = await req.media() #Get data from form
            namae = param['namae']
            lv = param['lv']
            
            sql_update = '''
                update kyara set namae=?,lv=? where namae==?
            ''' #Data update
            conn.execute(sql_update,(namae,lv,namae0))
        #Return to data display page**If you do not escape the name here, you may get an error, so urllib.parse.need quote
        api.redirect(resp,f'/toaru/{urllib.parse.quote(namae)}')
    except Exception as err:
        print(f'Error: {type(err)} {err}')
        api.redirect(resp,'/no') #If there is something wrong

#After deleting the data
@api.route('/delete/{namae}')
def delete(req,resp,*,namae):
    try:
        with sqlite3.connect(dbfile) as conn:
            sql_delete = '''
                delete from kyara where namae==?
            ''' #Data deletion
            conn.execute(sql_delete,[namae])
        api.redirect(resp,'/') #Return to index page
    except Exception as err:
        print(f'Error: {type(err)} {err}')
        api.redirect(resp,'/no') #If something goes wrong

#Load data
@api.route('/download')
def download(req,resp):
    with sqlite3.connect(dbfile) as conn:
        #Data to json file
        data = conn.execute('select * from kyara').fetchall()
        resp.media = [{'namae': d[0], 'lv': d[1]} for d in data]
        #Specify in the header to make it a page for downloading files
        resp.headers['Content-Disposition'] = 'attachment; filename=data.json'



if(__name__=='__main__'):
    #Create a new table when you run it for the first time
    if(not os.path.exists(dbfile)):
        with sqlite3.connect(dbfile) as conn:
            sql_create = '''
                create table kyara (
                    namae text,
                    lv integer,
                    primary key (namae)
                )
            '''
            conn.execute(sql_create)
    
    #Server start
    api.run()

There are 7 routes, but I actually use the template

  • '/'
  • '/toaru/{namae}'
  • '/no'

Only three.

In addition to it

  • '/insert'
  • '/update/{namae0}'
  • '/delete/{namae}'

They interact with the database and redirect to other pages.

The pages **'/ insert' ** and **'/ update / {namae0}' ** are async functions because they need to receive data from the form and await is used.

I just learned about python's async and await recently. I read various qiita articles and it was helpful, so I will introduce it here

-Python asyncio to understand with sushi -Asynchronous processing in Python: asyncio reverse lookup reference

When using responder, even if we don't write async and await directly, it is very useful to understand asynchronous processing because there are many functions that work with async and await in responder in the first place.

Finally, **'/ download' ** is a page to save all the data in the database to a json file.

The database is created the first time you run the server. After that, a file containing the database (here named dedicatus545.db) will appear.

The responder.API object is set like this

templates_dir = '.'
static_dir    = 'librorum'
static_route  = '/librorum'

templates_dir is the folder with the templates. By default, it is in a folder called templates, but this time we will not use the folder, so specify it as'.'.

static_dir is the folder that contains the static files. The default folder is static, but here it is'librorum'.

static_route is the root of the static file, which defaults to'/ static', but should be'/ librorum' as well.

css

Website decoration is not the main purpose of this time, so css suitable enough to be a spectacle

librorum/prohibitorum.css

div,li,h3 {
    padding: 2px;
    font-size: 20px;
}
input {
    border: solid #194 2px;
    font-size: 19px;
}
form {
    margin: 3;
}

Execution and results

Once the code is ready, it's time to run the server .py code.

python majutsu.py

Then access http://127.0.0.1:5042/ with your browser.

Here we will go with firefox.

If there are no mistakes, you should see a page like this.

q01.png

There is no data yet, so let's add it first.

q02.png

Enter the name and level and click the Add button to add the data. Add another one to try.

q03.png

q04.png

If you click the "Download" link, the data will be downloaded as a json file.

q05.png

However, if you use this method, the kanji will be Unicode like this when converting to json.

[{"namae": "\u4e0a\u6761\u5f53\u9ebb", "lv": 0}, {"namae": "\u5fa1\u5742\u7f8e\u7434", "lv": 5}]

How to avoid this is described in this article https://qiita.com/nassy20/items/adc59c4b7abd202dda26

However, even so, when I looked it up in firefox, it returned to kanji properly, so I think it's okay this time.

q06.png

Next, if you try clicking the Add button without filling in the form, an error will occur and you will be taken to this page. q07.png

The cause of the error is that when using req.media () because the name is not entered, calling param ['namae'] without the'namae' key will result in an error.

It's better to use .get () to avoid errors, but this time it doesn't have to be empty in the first place, so you can leave it as it is here.

Go back to the index, click one name and enter the link to go to the data edit page.

q08.png

When I try to empty the level and click the update button, an error occurs and I go to the no page again.

If you go back to this page again, enter the new data properly this time, and click the button, the data will be updated.

When you return to the index, you can confirm that the data has been updated properly.

q09.png

Finally, when you enter the edit page again and click the delete button

q10.png

That data will disappear.

q11.png

This completes the test of all functions.

At the end

This is how the website was created with responder and sqlite3.

It may just be a simple and unusable website, but I think it can be used as a basic practice for creating a full-fledged site.

An example of adding javascript etc. to make SPA is written in the next article >> https://qiita.com/phyblas/items/f3f40df76f85d61d5286

Recommended Posts

Try making a simple website with responder and sqlite3
Build a drone simulator environment and try a simple flight with Mission Planner
Let's make a simple game with Python 3 and iPhone
Try programming with a shell!
Try Amazon Simple Workflow Service (SWF) with Python and boto3
A simple interactive music player made with Chuck and OpenPose
Try to bring up a subwindow with PyQt5 and Python
(For beginners) Try creating a simple web API with Django
I made a simple circuit with Python (AND, OR, NOR, etc.)
Build a detonation velocity website with Cloud Run and Python (Flask)
Make a simple OMR (mark sheet reader) with Python and OpenCV
Rails users try to create a simple blog engine with Django
Creating a simple app with flask
Create a simple Python development environment with VS Code and Docker
Introduction and usage of Python bottle ・ Try to set up a simple web server with login function
SDN Basic Course for Programmers 3: Try Making a Switching Hub with Ryu
Make a wireless LAN Ethernet converter and simple router with Raspberry Pi
The story of making a sound camera with Touch Designer and ReSpeaker
Creating a simple PowerPoint file with Python
A simple RSS reader made with Django
Try drawing a simple animation in Python
Try drawing a normal distribution with matplotlib
A memo with Python2.7 and Python3 on CentOS
Let's try gRPC with Go and Docker
Let's make a simple language with PLY 1
Create a simple web app with flask
Try HTML scraping with a Python library
Try drawing a map with python + cartopy 0.18.0
Implement a model with state and behavior
Publish your website with responder + Gunicorn + Apache
I made a simple blackjack with Python
Try TensorFlow RNN with a basic model
Put Docker in Windows Home and run a simple web server with Python
A simple workaround for bots to try to post tweets with the same content
Create a simple reception system with the Python serverless framework Chalice and Twilio
WEB scraping with python and try to make a word cloud from reviews
Try creating a web application with Vue.js and Django (Mac)-(1) Environment construction, application creation
I tried to make a simple image recognition API with Fast API and Tensorflow
Set up a simple HTTPS server with asyncio
Connect Scratch X and Digispark with a bottle
Try Tensorflow with a GPU instance on AWS
Building a python environment with virtualenv and direnv
Try running Google Chrome with Python and Selenium
Make a simple pixel art generator with Flask
Try to draw a life curve with python
Try sending a message with Twilio's SMS service
Start a simple Python web server with Docker
Try to communicate with EV3 and PC! (MQTT)
Try to make a "cryptanalysis" cipher with Python
[Python] Make a simple maze game with Pyxel
I made a simple Bitcoin wallet with pycoin
Build a virtual environment with pyenv and venv
Launch a web server with Python and Flask
Compile and run Rust with a single command
Try to make a dihedral group with Python
A simple to-do list created with Python + Django
Try drawing a map with Python's folium package
Try creating a FizzBuzz problem with a shell program
I tried using a database (sqlite3) with kivy
Develop a web API that returns data stored in DB with Django and SQLite
Get a large amount of Starbucks Twitter data with python and try data analysis Part 1