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

• Python asyncio

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.

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

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

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

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.

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.

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.

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.

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

That data will disappear.

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