Path: csiph.com!news.swapon.de!eternal-september.org!feeder.eternal-september.org!border1.nntp.ams1.giganews.com!nntp.giganews.com!newsfeed.xs4all.nl!newsfeed7.news.xs4all.nl!news.tele.dk!news.tele.dk!small.news.tele.dk!newsgate.cistron.nl!newsgate.news.xs4all.nl!nzpost1.xs4all.net!not-for-mail
To: python-list@python.org
From: Peter Otten <__peter__@web.de>
Subject: Re: SQLite3 and web server
Date: Fri, 21 Aug 2015 11:32:51 +0200
Organization: None
References: <87pp2hp3vg.fsf@Equus.decebal.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 7Bit
User-Agent: KNode/4.13.3
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.7.1440149603.13558.python-list@python.org>
Lines: 145
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:95519

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"