SQLite3 and web server

Fri Aug 21 05:32:51 EDT 2015

Cecil Westerhof wrote:

> At the moment I serve a AngularJS web application with:
>     python3 -m http-server
> 
> This only servers static html pages with the data contained in js
> files, like:
>         $scope.links = [
>             { desc: 'Album',            url:
>             { 'https://plus.google.com/collection/MuwPX'                 
>             { }, desc: 'Heron Sunbathing', url:

> I would like to retrieve the information out a SQLite3 database. I did
> some Googling, but until now I did not find something useful. How
> would I implement this?

I like bottle for (not much) dabbling around with generated html.
Assuming there is an sqlite database "links.sqlite" with a table "links" 
featuring columns "desc" and "url" you get a traditional html page built on 
the fly from the data in that table with the code shown below. Producing 
JSON is also demonstrated.

Integrating the JSON data is a matter of the javascript framework; google 
found http://api.jquery.com/jquery.getjson/.

$ cat serve.py
#!/usr/bin/env python3

import bottle
import json
import sqlite3
from contextlib import contextmanager

@contextmanager
def open_db():
    db = sqlite3.connect("links.sqlite")
    cs = db.cursor()
    try:
        yield cs
    finally:
        db.close()

def read_links():
    with open_db() as cs:
        return cs.execute("select desc, url from links;").fetchall()

TEMPLATE = """
<html>
<head>
<title>"Links as found in the db</title>
</head>
<body>
  % for desc, url in rows:
    <a href={{url}}>{{desc}}</a><br/>
  % end
</body></html>
"""

@bottle.route("/links")
def links_as_html():
    rows = read_links()
    return bottle.template(TEMPLATE, rows=rows)

@bottle.route("/links/data")
def links_as_json():
    bottle.response.content_type = "application/json"
    return json.dumps([
        {"desc": desc, "url": url}
        for desc, url in read_links()], indent=4)

if __name__ == "__main__":
    bottle.run(host="localhost", port=8080)

[Instead of the following commandline gymnastics you can just invoke the 
script with 
$ python3 serve.py
and then point your browser to
http://localhost:8080/links]

$ python3 serve.py 2>/dev/null &
[1] 8418
$ curl http://localhost:8080/links 2>/dev/null | head

<html>
<head>
<title>"Links as found in the db</title>
</head>
<body>
    <a href=https://plus.google.com/collection/MuwPX>Album</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/bHvSzBGobEj>Heron 
Sunbathing</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/TY3asc5oCnB>Heron 
Fishing</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/AtTwhL8SdnH>Water 
Lily</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/TyiZbUWdnrm>Tree 
at Pond</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/MoQ7vXs8HqP>Fish</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/BDYkPKSMUwZ>Fountain</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/ed3ZGNzb8kM>Digitalis</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/DPbHHSFXBY4>Sunset</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/ZZtSUwNb6RC>Digitalis 
2</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/LY62DqLEJhG>Water 
Lilies</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/XFKyTcoakcy>Flower</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/bfg5irDAn2T>Waterfalls</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/jKr5B6EQyo1>Frogs</a><br/>
$ curl http://localhost:8080/links/data 2>/dev/null | head
[
    {
        "desc": "Album",
        "url": "https://plus.google.com/collection/MuwPX"
    },
    {
        "desc": "Heron Sunbathing",
        "url": "https://plus.google.com/+CecilWesterhof/posts/bHvSzBGobEj"
    },
    {
        "desc": "Heron Fishing",
        "url": "https://plus.google.com/+CecilWesterhof/posts/TY3asc5oCnB"
    },
    {
        "desc": "Water Lily",
        "url": "https://plus.google.com/+CecilWesterhof/posts/AtTwhL8SdnH"
    },
    {
        "desc": "Tree at Pond",
        "url": "https://plus.google.com/+CecilWesterhof/posts/TyiZbUWdnrm"