4 dbdir = os.path.dirname(__file__)
5 dbdef = os.path.join(dbdir, "coffee_db.sql")
6 dbfile = os.path.join(dbdir, "coffee.db")
9 conn = sqlite3.connect(dbfile)
19 with open(dbdef, "r") as f:
20 c.executescript(f.read())
25 c.execute("insert or ignore into users (id) values (?)", (uid,))
30 for name, in c.execute("select name from users where id = ?",(uid,)):
37 def name_user(uid, name):
39 c.execute("update users set name = ? where id = ?", (name, uid))
44 for row in c.execute("select * from users"):
49 def add_coffee(uid, flavor, time=None):
52 c.execute("insert into coffees (id, flavor) values (?,?)", (uid,flavor))
54 c.execute("insert into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
58 def list_coffees(uid=None):
61 for row in c.execute("select id, time, flavor from coffees"):
64 for row in c.execute("select time, flavor from coffees where id = ?", (uid,)):
69 res = [row for row, in c.execute("select distinct name from flavors")]
73 def coffee_flavors(uid=None):
77 res = list(c.execute("""
78 select f.name, count(c.flavor) from flavors f left join
79 (select * from coffees) c
80 on f.name=c.flavor group by f.name
83 res = list(c.execute("""
84 select f.name, count(c.flavor) from flavors f left join
85 (select * from coffees where id = ?) c
86 on f.name=c.flavor group by f.name
93 def coffee_history(uid=None):
97 res = list(c.execute("""
98 select strftime('%d', ds.d),count(c.flavor),c.flavor from
99 (select num,date('now',-num || ' days') as d from days) ds
101 on d = date(c.time) group by d, c.flavor
104 res = list(c.execute(
106 select strftime('%d', ds.d),count(c.flavor),c.flavor from
107 (select num,date('now',-num || ' days') as d from days) ds
109 (select time,flavor from coffees where id = ?) c
110 on d = date(c.time) group by d, c.flavor
117 def coffee_count(uid=None, start=None, stop=None):
124 clauses.append("id = ?")
127 if start is not None:
128 clauses.append("date(time) >= date('now', '-%d days')" % int(start))
131 clauses.append("date(time) <= date('now', '-%d days')" % int(stop))
133 for count, in c.execute(
134 "select count(*) from coffees where " +
135 " and ".join(clauses)