SQLite3 and web server
Peter Otten
__peter__ at web.de
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"
More information about the Python-list
mailing list