· 9 years ago · Nov 02, 2016, 02:56 AM
1import sqlite3
2from datetime import datetime, date
3from operator import itemgetter
4
5
6
7firstmonth = '2016-05'
8lastmonth = '2016-06'
9
10# Connect to SQLite Database
11db_loc = 'boardgamecollection_xml.sqlite' # name of database
12conn = sqlite3.connect(db_loc)
13c = conn.cursor()
14
15
16def sql(query):
17 c.execute(query)
18
19 out = c.fetchall()
20 return out
21
22def pprint(out):
23 toprint = ''
24 # for row in out:
25 # for item in row:
26 # toprint += str(item) + " - "
27 # toprint = toprint[:-3]
28 # print toprint
29 # toprint = ''
30
31 numCol = len(out[0])-1
32 s = "{:<50} "
33 for col in xrange(numCol):
34 s += "{: <50} "
35 for row in out:
36 print(s.format(*row))
37
38
39
40def percentcomplete(c):
41
42 c.execute("SELECT max(date) FROM collection WHERE date < '{dt}%'".format(dt = date.today().strftime('%Y-%m')))
43 prev_month = c.fetchone()[0]
44 prev_month = datetime.strptime(prev_month, '%Y-%m-%d')
45
46 c.executescript("""
47 DROP TABLE IF EXISTS countbggid;
48
49 CREATE TEMP TABLE IF NOT EXISTS countbggid
50 (bggid INTEGER);
51
52 INSERT INTO countbggid
53 SELECT count(bggid)
54 FROM collection
55 WHERE date like '{dt}%'
56 """.format(dt = prev_month.strftime('%Y-%m')))
57 query = ''' select count(a.bggid)*100.0 / b.bggid from collection a
58 cross join countbggid b
59 where a.date like '{dt}%'
60 '''.format(dt = date.today().strftime('%Y-%m'))
61
62 # c.execute(query)
63 # tot = c.fetchone()[0]
64
65 pprint(sql(query))
66
67def upandcomers(c,firstmonth, lastmonth, count):
68 # Create temp table with the last month's data:
69 c.executescript("""
70 DROP TABLE IF EXISTS lastmonth;
71
72 CREATE TEMP TABLE IF NOT EXISTS lastmonth
73 (bggid INTEGER, name VARCHAR(100), owned INTEGER);
74
75 INSERT INTO lastmonth
76 SELECT c.bggid, g.name, c.owned
77 FROM collection c
78 INNER JOIN games g ON c.bggid = g.bggid
79 WHERE c.date like '{mn}%';
80 """.format(mn=lastmonth))
81
82
83 query = '''
84 SELECT l.bggid, l.name, round(l.owned*1.0/f.owned,2) as ratio--, l.owned, f.owned
85 FROM lastmonth l
86 INNER JOIN collection f on f.bggid = l.bggid
87 INNER JOIN games g ON g.bggid = l.bggid
88 WHERE f.date like '{mn}%' AND l.owned > 0 and f.owned > 50 and g.isexpansion <> 1
89 ORDER BY ratio DESC
90 LIMIT {cnt};
91 '''.format(mn=firstmonth, cnt = count)
92
93 # query = '''
94 # SELECT l.bggid, l.name, round((l.owned-f.owned)*1.0/f.owned,2) AS ratio
95 # FROM lastmonth l
96 # INNER JOIN collection f on f.bggid = l.bggid
97 # INNER JOIN games g ON g.bggid = l.bggid
98 # WHERE f.date like '{mn}%' AND l.owned > 0 and f.owned > 50 and g.isexpansion <> 1
99 # ORDER BY ratio DESC
100 # LIMIT {cnt}
101 # '''.format(mn=firstmonth, cnt = count)
102
103 print("{:<50} {: <50} {: <50}".format("BGGid", "Game Name", "Hotness Ratio"))
104 print("{:<50} {: <50} {: <50}".format("----", "----", "----"))
105 pprint(sql(query))
106
107def gamesbypub(c, x):
108 query = '''SELECT publishers.name, count(gamepub.pubid) AS countpub
109 FROM publishers
110 INNER JOIN gamepub ON publishers.pubid = gamepub.pubid
111 GROUP BY publishers.name
112 ORDER BY countpub DESC LIMIT {cnt}'''.format(cnt = x)
113
114 pprint(sql(query))
115
116def ownershipbypub(c, x):
117 ## TO DO: add ability to search by pub name, date
118 ## TO DO: this isn't working in python
119 query = '''
120 SELECT publishers.name, sum(collection.owned) as sumowned
121 FROM publishers
122 INNER JOIN gamepub ON gamepub.pubid = publishers.pubid
123 INNER JOIN games ON gamepub.bggid = games.bggid
124 INNER JOIN collection ON collection.bggid = games.bggid
125 GROUP BY publishers.name, collection.date
126 ORDER BY collection.date DESC, sumowned DESC LIMIT {cnt}
127 '''.format(cnt = x)
128
129 # print query
130 pprint(sql(query))
131
132query = '''SELECT publishers.name, sum(collection.owned) as sumowned FROM publishers INNER JOIN gamepub ON gamepub.pubid = publishers.pubid INNER JOIN games ON gamepub.bggid = games.bggid INNER JOIN collection ON collection.bggid = games.bggid GROUP BY publishers.name, collection.date ORDER BY collection.date DESC, sumowned DESC LIMIT 10'''
133
134
135# upandcomers(c,'2016-06', '2016-07', 10)
136
137# conn.close()
138
139
140def scanning(c, comm):
141 if comm == "":
142 True
143 elif comm == "help" or comm == "h":
144 functions = ["upandcomers", "gamesbypub"]
145 description = ["Ranks games within month range (YYYY-mm) by number owned last month / number owned first month.", "Number of games for each publisher, ranked"]
146 usage = ["upandcomers first month (YYYY-mm) lastmonth X", "gamesbypub X"]
147
148 result = []
149 for i in xrange(len(functions)):
150 result.append([functions[i], description[i], usage[i]])
151
152 result = sorted(result, key=itemgetter(0))
153
154 print("{:<20} {: <100} {: <20}".format("Function", "Description", "Usage"))
155 print("{:<20} {: <100} {: <20}".format("----", "----", "----"))
156 for row in result:
157 print("{:<20} {: <100} {: <20}".format(*row))
158 elif comm[0:11] == "upandcomers":
159 comm = comm.lstrip("upandcomers ")
160 firstmonth = comm[0:7]
161 lasmonth = comm[8:15]
162 count = comm[16:len(comm)]
163
164 upandcomers(c,firstmonth, lastmonth, count)
165 elif comm[0:10] == "gamesbypub":
166 count = comm.lstrip("gamesbypub ")
167 gamesbypub(c, count)
168 elif comm[0:14] == "ownershipbypub":
169 startTime = datetime.datetime.now()
170 count = comm.lstrip("ownershipbypub ")
171 ownershipbypub(c, count)
172 print str(datetime.datetime.now() - startTime)
173
174# while True:
175# comm = raw_input('Enter command: ')
176#
177# scanning(c, comm)
178#