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):
51 print(uid, flavor, time)
53 c.execute("insert into coffees (id, flavor) values (?,?)", (uid,flavor))
55 c.execute("insert into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
59 def list_coffees(uid=None):
62 for row in c.execute("select id, time, flavor from coffees"):
65 for row in c.execute("select time, flavor from coffees where id = ?", (uid,)):
70 res = [row for row, in c.execute("select distinct name from flavors")]
74 def coffee_flavors(uid=None):
78 res = list(c.execute("""
79 select f.name, count(c.flavor) from flavors f left join
80 (select * from coffees) c
81 on f.name=c.flavor group by f.name
84 res = list(c.execute("""
85 select f.name, count(c.flavor) from flavors f left join
86 (select * from coffees where id = ?) c
87 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.time) from
99 (select num,date('now',-num || ' days') as d from days) ds
101 on d = date(c.time) group by d
104 res = list(c.execute(
106 select strftime('%d', ds.d),count(c.time) from
107 (select num,date('now',-num || ' days') as d from days) ds
109 (select time from coffees where id = ? ) c
110 on d = date(c.time) group by d
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)