[Edu-sig] from back to front end (SQL to HTML)

Mon Apr 4 14:21:43 EDT 2016

Below is a script I recently published to another list, mostly of math
teachers, designed to start with SQL table creation and end with something
HTML, as this models record-keeping and visualization at the industrial
scale.

I'm using Python in the middle, but sure, the controller could be a
different language performing the same task.  I use collections.namedtuple,
one of my favorites, and exercise the DB API to a minimum, leaving plenty
of room for others to expand.

Kirby

===
# -*- coding: utf-8 -*-
"""
Created on Sun Apr  3 14:09:02 2016

@author: Kirby Urner (c) MIT License

>From SQL to HTML with geometry in the middle.  Developed
for a five day crash course in STEM-relevant mathematics
which includes learning a computer language, in this case
Python.

The script store row data about a few polyhedra in a table,
using SQL to create the table and insert the rows. Then
two of the columns are selected to build an HTML page
containing a volumes table.

Expressions involving phi, the golden ratio, are evaluated
to give floating point values for volumes in some cases.
Other volumes are simply integers.

The volumes are expressed in less familiar 'tetravolume'
units as developed in Buckminster Fuller's writings, and
in subsequent follow-on investigations by students of
Fuller's approach, such as David Koski.

Interesting discussion and further topics for investigation
derive from using this somewhat alien system of mensuration,
wherein a regular tetrahedron of edges L has the volume
L * L * L or L to the 3rd power.
"""

import sqlite3 as sql               # SQL library included in Python
from collections import namedtuple  # used for row data
from math import sqrt as rt2        # rename sqrt to rt2

phi = (1 + rt2(5))/2                # could use the Greek letter

# information about polyhedrons, could be a file
data = """\
TETRA | 4 | 4 | 6 | 1
CUBE | 8 | 6 | 12 | 3
OCTA | 6 | 8 | 12 | 4
RD | 12 | 14 | 24 | 6
CUBOCTA | 12 | 14 | 24 | 20
ICOSA | 12 | 20 | 30 | rt2(2) * 5 * phi**2
PD | 20 | 12 | 30 | (phi**2 + 1) * 3 * rt2(2)
RT | 30 | 32 | 60 | 15 * rt2(2)"""

# create the SQL table in memory for this script
conn = sql.connect(":memory:")
curs = conn.cursor()

# here's the table
curs.execute(
"""create table shapes (
name text,
V integer,
F integer,
E integer,
vol float)""")

Rec = namedtuple('Rec', 'name V F E vol') # e.g. Rec.

shapes = [ ]
for shape in data.split("\n"):
    rec = shape.split("|")
    table_row = Rec(rec[0].strip(), # name
                  int(rec[1]),      # V
                  int(rec[2]),      # F
                  int(rec[3]),      # E
                  float(eval(rec[4]))) # volume
    print(table_row)

    # for adding rows to the table
    to_do = """insert into shapes (name, V, F, E, vol)
    VALUES ('{}', {}, {}, {}, {})""".format(
    table_row.name,  # substitutes into insert command
    table_row.V,
    table_row.F,
    table_row.E,
    table_row.vol)

    curs.execute(to_do)

conn.commit()

# this is the skeleton of a web page, into which row data is inserted
output = """\
<!DOCTYPE html>
<html>
<head>
<title>Volumes Table</title>
</head>
<body>
<table>
{}
</table>
</body>
</html>
"""

# get the row data back out from the table...
curs.execute("""select name, vol from shapes order by vol""")

table_data = ""  # starts empty
for name, vol in curs.fetchall():
    table_data += """<tr><td>{}</td><td>{}</td></tr>\n""".format(name, vol)

conn.close()

# save the html file, view in browser
fh = open("shapes.html", "w")
output = output.format(table_data)
print(output)
print(output, file = fh)
fh.close()
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/edu-sig/attachments/20160404/2eef34b0/attachment.html>