Bottle micro web services framework 9 : Bucket List App V - json

List of Bottle Micro Web Services Tutorials

1. Introduction
2. Static files
3. Template
4. json
5. Bucket List App I - sqlite, route, and template
6. Bucket List App II - get & post
7. Bucket List App III - Editing
8. Bucket List App IV - route validation, regex, and static_file
9. Bucket List App V - json
10. json to html table
11. Forms - Get & Post
12. Forms - Get & Post with editable and checkbox table cells

Continued from the previous chapter, Bucket List App IV, in this chapter, we'll cover the key features of Bottle framework by upgrading the Buck list app we made.

The list of wishes is shown below:

There are cases where we do not want our application to generate the output directly, but return data to be processed further on, e.g. by JavaScript or Python. For those cases, Bottle offers the possibility to return JSON objects, which is sort of standard for exchanging data between web applications. Furthermore, JSON can be processed by many programming languages, including Python.

So, here is an example returning the data generated in the regular expression route example as a JSON object:

@route('/json/<json:re:[0-9]+>')
def show_json(json):
    conn = sqlite3.connect('bucket.db')
    c = conn.cursor()
    c.execute("SELECT wish FROM bucket WHERE id LIKE ?", (json))
    result = c.fetchall()
    c.close()

    if not result:
        return {'wish':'This item number does not exist!'}
    else:
        return {'wish': result[0]}

As you can see, we just return a regular Python dictionary, and Bottle will convert it automatically into a JSON object prior to sending. So if we call "http://localhost/json3", Bottle returns the JSON object {"Wish": ["Bungee jumping from Golden Gate Bridge"]}.

Here is our code (bucket.py) so far:

import sqlite3
from bottle import route, run, template, request, static_file

HOST = 'localhost'
PORT = 8080

@route('/bucket')
def bucket_list():
    conn = sqlite3.connect('bucket.db')
    c = conn.cursor()
    c.execute("SELECT id, wish, status FROM bucket WHERE status LIKE '0' OR '1'")
    result = c.fetchall()
    c.close()
    return template('wish_table', rows=result)

@route('/new', method='GET')
def new_item():
    if request.GET.get('save','').strip():
        new = request.GET.get('wish', '').strip()
        conn = sqlite3.connect('bucket.db')
        c = conn.cursor()
        c.execute("INSERT INTO bucket (wish,status) VALUES (?,?)", (new,1))
        new_id = c.lastrowid
        conn.commit()
        c.close()
        return '<p>The new wish was inserted into the database, the ID is %s</p>' % new_id
    else:
        return template('new_wish.tpl')

@route('/edit/<wishId:int>', method='GET')
def edit_item(wishId):
    if request.GET.get('save','').strip():
        edit = request.GET.get('wish','').strip()
        status = request.GET.get('status','').strip()

        if status == 'open':
            status = 1
        else:
            status = 0

        conn = sqlite3.connect('bucket.db')
        c = conn.cursor()
        c.execute("UPDATE bucket SET wish = ?, status = ? WHERE id LIKE ?", (edit, status, wishId))
        conn.commit()

        return '<p>The item number %s was successfully updated</p>' % wishId
    else:
        conn = sqlite3.connect('bucket.db')
        c = conn.cursor()
        c.execute("SELECT wish FROM bucket WHERE id LIKE ?", str(wishId))
        cur_data = c.fetchone()

        return template('edit_wish', old=cur_data, wishId=wishId)

@route('/item/<item:re:[0-9]+>')
def show_item(item):
    conn = sqlite3.connect('bucket.db')
    c = conn.cursor()
    c.execute("SELECT wish FROM bucket WHERE id LIKE ?", (item))
    result = c.fetchall()
    c.close()
    if not result:
        return 'This item number does not exist!'
    else:
        return 'Wish: %s' %result[0]

@route('/help')
def help():
    return static_file('help.html', root='/home/k/TEST/Py/Bottle/Bucket/')

@route('/json/<json:re:[0-9]+>')
def show_json(json):
    conn = sqlite3.connect('bucket.db')
    c = conn.cursor()
    c.execute("SELECT wish FROM bucket WHERE id LIKE ?", (json))
    result = c.fetchall()
    c.close()

    if not result:
        return {'wish':'This item number does not exist!'}
    else:
        return {'wish': result[0]}

run(host=HOST, port=PORT, debug=True)