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...
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
