[Tutor] How to create a sqlite table schema dynamically

[Peter Otten]

Alan Gauld wrote:

> I confess I'm still not clear on your schema. What should the populated
> table(s) look like? It all feels very un-SQL like to me.

I'll make a bold guess that he wants to make a pivot table, something that 
is indeed not supported by sqlite. 

E. g., start with

week     | os       | installs
-------- | -------- | --------
2014-01  | redhat   | 5       
2014-01  | suse     | 2       
2014-02  | debian   | 2       
2014-02  | redhat   | 7       
2014-03  | suse     | 3       
2014-03  | ubuntu   | 3       
2014-03  | mint     | 1       


and wield it into something like

week     | debian   | mint     | redhat   | suse     | ubuntu  
-------- | -------- | -------- | -------- | -------- | --------
2014-01  | 0        | 0        | 5        | 2        | 0       
2014-02  | 2        | 0        | 7        | 0        | 0       
2014-03  | 0        | 1        | 0        | 3        | 3       


Below is my attempt:

import sqlite3

db = sqlite3.connect(":memory:")
cs = db.cursor()

data = [
    # week, os, installs
    ("2014-01", "redhat", 5),
    ("2014-01", "suse", 2),
    ("2014-02", "debian", 2),
    ("2014-02", "redhat", 7),
    ("2014-03", "suse", 3),
    ("2014-03", "ubuntu", 3),
    ("2014-03", "mint", 1),
    ]

def print_row(row, space=" "):
    print(" | ".join(str(field).ljust(8, space) for field in row))

def show(sql):
    first = True
    for row in cs.execute(sql):
        if first:
            print_row(d[0] for d in cs.description)
            print_row(("" for d in cs.description), "-")
            first = False
        print_row(row)
    print("")

def sanitized(name):
    """Prevent SQL injection"""
    if not name.isalpha(): # XXX a tad too rigid
        raise ValueError("Illegal name {!r}".format(name))
    return name

cs.execute("create table weekly_installs (week, os, installs);")
cs.executemany(
    "insert into weekly_installs "
    "(week, os, installs) values (?, ?, ?)", data)

show("select * from weekly_installs")

distros = sorted(
    sanitized(distro) for [distro] in
    cs.execute("select distinct os from weekly_installs"))

cs.execute("create table pivot (week, {})".format(
        ", ".join(d + " default 0" for d in distros)))
cs.executemany(
        "insert into pivot (week) values (?)",
        cs.execute("select distinct week from weekly_installs").fetchall())

for distro in distros:
    update = "update pivot set {distro} = ? where week = ?"
    update = update.format(distro=distro)
    lookup = ("select installs, week from weekly_installs "
              "where os = '{distro}'")
    lookup = lookup.format(distro=distro)

    cs.executemany(update, cs.execute(lookup).fetchall())

show("select * from pivot order by week")


OK, it still may serve as a bad example ;) Doing it in Python should be 
much cleaner, but I'll leave that as an exercise...