Bottle micro web services framework 6 : Bucket List App II - get & post

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 I, in this chapter, we'll cover the key features of Bottle framework by upgrading the Buck list app.

The list of wishes is shown below:

Here are the files:

bucket.py:

import sqlite3
from bottle import route, run, template

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)

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

wish_table.tpl:

%#template to generate a HTML table from a list of tuples
<p>The open wish items are as follows:</p>
<table border="1">
%for r in rows:
  <tr>
  %for c in r:
    <td>{{c}}</td>
  %end
  </tr>
%end
</table>

Now we want to add new items to our bucket list. The new item should be received from a normal HTML-based form, which sends its data by the GET method.

We need to add a new route to our script and tell the route that it should get data via GET:

bucket.py:

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

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():

    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

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

To access GET (or POST) data, we need to import request from Bottle. To assign the actual data to a variable, we use the statement request.GET.get('wish','').strip() statement, where wish is the name of the GET data we want to access. That's all. If our GET data has more than one variable, multiple request.GET.get() statements can be used and assigned to other variables.

The rest of this piece of code is just processing of the gained data: writing to the database, retrieve the corresponding id from the database and generate the output.

Though we can get the data using a static HTML page holding the form, we want to use a template which is the output when the route /new is called without GET data. So, the code should be modified a bit like this:

Our new bucket.py:

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

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')

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

The new_wish.tpl looks like this:

<p>Add a new wish to the bucket list:</p>
<form action="/new" method="GET">
    <input type="text" size="50" maxlength="50" name="wish">
    <input type="submit" name="save" value="Save the wish">
</form>

We may want to delete the 6th item, then we can use the following code:

# sq_delete.py
import sqlite3
con = sqlite3.connect('bucket.db')
with con:
    cur = con.cursor()
    con.execute("DELETE FROM bucket where id=6")
    con.commit()