· 8 years ago · Dec 31, 2017, 12:00 AM
1########################################################################################################################
2# File analyser.py
3# Author: Dan Huckson
4# Date: 20171230
5########################################################################################################################
6
7import math
8import sqlite3
9
10from itertools import combinations
11from statistics import median, mode, mean, stdev
12
13
14def format_tuple(t):
15 if isinstance(t, tuple):
16 string = "("
17 for i in t:
18 string += str(i) + ","
19
20 return string[:-1] + ")"
21 else:
22 return False
23
24
25def ints_to_str(ints):
26
27 string = ""
28 for i in ints:
29 string += chr(i)
30
31 return string
32
33
34def str_to_ints(chars):
35 numbers = list()
36
37 for i in chars:
38 numbers.append(ord(i))
39
40 return tuple(numbers)
41
42
43def odds(digits, poolSize):
44 return (math.factorial(poolSize)/(math.factorial(digits)*(math.factorial(poolSize - digits))))
45
46
47def combos(depth, iterable):
48 return [tuple(combinations(iterable, d)) for d in range(1, depth+1)]
49
50
51class LottoGame:
52
53 def __init__(self, name, low, high, balls, bonus, database=":memory:"):
54 self.name = name
55 self.index = 1
56 self.lowBall = low
57 self.highBall = high
58 self.drawnBalls = balls
59 self.bonusBalls = bonus
60 self.gameCount = 0
61 self.draws = list()
62 self.db_conn = False
63 self.db_cursor = False
64 self.database = database
65
66 try:
67 self.db_conn = sqlite3.connect(self.database)
68 self.db_cursor = self.db_conn.cursor()
69
70 except Exception as err:
71 print("Error:44 Could connect to database. %s" % (str(err)))
72 exit()
73
74 def __iter__(self):
75 return self
76
77 def __next__(self):
78 db = self.db_cursor
79
80 if self.index > self.gameCount:
81 self.index = 1
82 raise StopIteration
83
84 try:
85 draw = ""
86 sql = "SELECT ID, DrawNumber, SequenceNumber, DrawDate, NumbersDrawn, BonusNumber FROM Draws WHERE ID = ?"
87 db.execute(sql, [self.index])
88
89 for row in db.fetchall():
90 draw = (row[0], row[1], row[2], row[3], str_to_ints(row[4]), row[5])
91
92 self.index += 1
93
94 return draw
95
96 except Exception as err:
97 print("Error:10 Could not select rows from database. %s" % (str(err)))
98
99 return False
100
101 def import_csv_file(self, file):
102 try:
103 fh = open(file, encoding="utf-8")
104
105 fh.readline()
106 while True:
107 line = fh.readline()
108
109 if not line:
110 break
111
112 line = line.replace('"', '').rstrip().split(",")
113 line.pop(0)
114
115 s = ""
116 for i in line[3:-1]:
117 s += chr(int(i))
118
119 tmp = line[:3]
120 tmp.append(s)
121 tmp.append(line[-1:][0])
122
123 self.draws.append(tuple(tmp))
124 self.gameCount += 1
125
126 fh.close()
127
128 return True
129
130 except IOError:
131 print('Error:5 Could not open file "{}"'.format(file))
132
133 return False
134
135 def populate_draws(self):
136 db = self.db_cursor
137
138 try:
139 for d in self.draws:
140 sql = "INSERT INTO Draws (DrawNumber, SequenceNumber, DrawDate, NumbersDrawn, BonusNumber) " \
141 'VALUES (?,?,?,?,?)'
142 db.execute(sql, (int(d[0]), int(d[1]), d[2], d[3], d[4]))
143
144 self.db_conn.commit()
145 return True
146
147 except Exception as err:
148 print("Error:2 Could insert row into table. %s" % (str(err)))
149
150 return False
151
152 def populate_combos(self):
153 db = self.db_cursor
154
155 try:
156 for draw in self:
157 level = 0
158 for c in combos(self.drawnBalls, draw[4]):
159 level += 1
160 for i in c:
161 sql = "INSERT INTO Match"+str(level)+" (DrawID, DrawNumber, SequenceNumber, Match) VALUES (?,?,?,?)"
162 db.execute(sql,(draw[0], int(draw[1]), draw[2], ints_to_str(i)))
163
164 self.db_conn.commit()
165 return True
166
167 except Exception as err:
168 print("Error:4 Could insert row into table. %s" % (str(err)))
169
170 return False
171
172 def populate_stats(self):
173 db = self.db_cursor
174
175 try:
176 for draw in self:
177
178 sql1 = "SELECT * FROM Stats " \
179 "WHERE Match = ?"
180 sql2 = "UPDATE Stats SET " \
181 "Match=?, Hits=?, LastHit=?, Mean=?, Median=?, Mode=?, Deviation=?, Range=? WHERE ID = ?"
182 sql3 = "INSERT INTO Stats " \
183 "(Match, Hits, LastHit, Mean, Median, Mode, Deviation, Range) VALUES(?,?,?,?,?,?,?,?)"
184
185 for i in range(1, self.drawnBalls+1):
186 hits = 0
187 for c in combinations(draw[4], i):
188 db.execute(sql1, [ints_to_str(c)])
189 rows = db.fetchall()
190 s = self.get_stats(c)
191
192 hits += 1
193 if len(rows):
194 db.execute(sql2, (ints_to_str(s["numbers"]), s["hits"], draw[3], s["mean"],
195 s["median"], s["mode"], s["deviation"], str(s["range"]), rows[0][0]))
196 else:
197 db.execute(sql3, (ints_to_str(s["numbers"]), s["hits"], draw[3], s["mean"],
198 s["median"], s["mode"], s["deviation"], str(s["range"])))
199
200 self.db_conn.commit()
201
202 except Exception as err:
203 print("Error:44 Could not insert row into table. %s" % (str(err)))
204 exit()
205
206 def create_database(self):
207 db = self.db_cursor
208
209 try:
210 db.execute("DROP TABLE IF EXISTS Draws")
211
212 db.execute("CREATE TABLE Draws (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
213 "DrawNumber INTEGER NOT NULL, SequenceNumber INTEGER NOT NULL, "
214 "DrawDate TEXT NOT NULL, NumbersDrawn TEXT NOT NULL, BonusNumber INTEGER NOT NULL)")
215
216 db.execute("CREATE INDEX IF NOT EXISTS idx_DrawNumber ON Draws (DrawNumber)")
217 db.execute("CREATE INDEX IF NOT EXISTS idx_NumbersDrawn ON Draws (NumbersDrawn)")
218
219 cnt = 0
220 for i in range(1, self.drawnBalls+1):
221 cnt += 1
222 db.execute("DROP TABLE IF EXISTS Match"+str(i)+"")
223
224 db.execute("CREATE TABLE Match"+str(i)+" (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
225 "DrawID INTEGER NOT NULL, DrawNumber INTEGER NOT NULL, "
226 "SequenceNumber INTEGER NOT NULL, Match TEXT NOT NULL)")
227
228 db.execute("DROP INDEX IF EXISTS idx_Match"+str(i))
229 db.execute("CREATE INDEX IF NOT EXISTS idx_Match"+str(i)+" ON Match"+str(i)+" (Match)")
230
231 db.execute("CREATE TABLE Stats (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
232 "Match TEXT NOT NULL, Hits INTEGER, LastHit TEXT NOT NULL, Mean REAL, Mode INTEGER, "
233 "Median INTEGER, Deviation REAL, Range TEXT)")
234
235 db.execute("DROP INDEX IF EXISTS idx_Match")
236 db.execute("CREATE INDEX IF NOT EXISTS idx_Match ON Stats (Match)")
237
238
239 return True
240
241 except Exception as err:
242 print("Error:1 Could not create table. %s" % (str(err)))
243
244 return False
245
246 def get_hits(self, draw_numbers):
247 db = self.db_cursor
248
249 try:
250 sql = "SELECT DrawID, DrawNumber, SequenceNumber, Match " \
251 "FROM Match"+str(len(draw_numbers))+" WHERE Match = ?"
252 db.execute(sql, [ints_to_str(draw_numbers)])
253
254 except Exception as err:
255 print("Error:9 Could not select rows from database. %s" % (str(err)))
256
257 return False
258
259 rows = []
260 for row in db.fetchall():
261 rows.append((row[0], row[1], row[2], str_to_ints(row[3])))
262
263 if len(rows):
264 return rows
265 else:
266 return False
267
268 def get_draw(self, draw_number, sequence_number=None):
269 db = self.db_cursor
270
271 try:
272 sequence = ""
273 if sequence_number:
274 sequence = "AND SequenceNumber = ?"
275
276 sql = "SELECT ID, DrawNumber, SequenceNumber, DrawDate, NumbersDrawn, BonusNumber" \
277 " FROM Draws WHERE DrawNumber = ? " + sequence
278
279 if not sequence_number:
280 db.execute(sql, [draw_number])
281 else:
282 db.execute(sql, (draw_number, sequence_number))
283
284 rows = list()
285 for row in db.fetchall():
286 rows.append((row[0], row[1], row[2], row[3], str_to_ints(row[4]), row[5]))
287
288 return rows
289
290 except Exception as err:
291 print("Error:7 Could not select rows from database. %s" % (str(err)))
292
293 return False
294
295 def get_stats(self, numbers):
296 db = self.db_cursor
297
298 stats = []
299 statistics = {}
300 hits = self.get_hits(numbers)
301
302 if hits:
303 for r in hits:
304 sql = "SELECT DrawDate FROM Draws WHERE ID = ?"
305 db.execute(sql, [str(r[0])])
306
307 date = None
308 for q in db.fetchall():
309 date = q[0]
310
311 stats.append([r[0], r[1], r[2], date, format_tuple(r[3])])
312 #print("#", [r[0], r[1], r[2], date, format_tuple(r[3])])
313
314 values = []
315 previous = 0
316 for s in stats:
317 values.append(s[0] - previous)
318 previous = s[0]
319
320 statistics["hits"] = len(hits)
321 statistics["numbers"] = numbers
322
323 try:
324 statistics["mean"] = round(mean(values), 2)
325 except Exception as err:
326 statistics["mean"] = None
327
328 try:
329 statistics["mode"] = mode(values)
330 except Exception as err:
331 statistics["mode"] = None
332
333 try:
334 statistics["median"] = median(values)
335 except Exception as err:
336 statistics["median"] = None
337
338 try:
339 statistics["deviation"] = round(stdev(values), 2)
340 except Exception as err:
341 statistics["deviation"] = None
342
343 try:
344 statistics["range"] = (min(values), max(values))
345 except Exception as err:
346 statistics["range"] = None
347
348 return statistics
349
350 def list_draws(self, detail=False):
351 db = self.db_cursor
352
353 try:
354 sql = "SELECT ID, DrawNumber, SequenceNumber, DrawDate, NumbersDrawn, BonusNumber FROM Draws"
355 db.execute(sql)
356 for row in db.fetchall():
357 if detail:
358 print("ID:", row[0])
359 print("Draw Number:", row[1])
360 print("Sequence Number:", row[2])
361 print("Draw Date:", row[3])
362 print("Numbers Drawn:", str_to_ints(row[4]))
363 print("Bonus Number: {}\n".format(row[5]))
364 else:
365 print("{} {} {} {} {} ({})".format(row[0],row[1],row[2],row[3],str_to_ints(row[4]),row[5]))
366
367 return True
368
369 except Exception as err:
370 print("Error:6 Could not select rows from database. %s" % (str(err)))
371
372 return False
373
374 def update_draw(self, draw_number, sequence_number, draw_date, drawn_numbers, bonus_number):
375 db = self.db_cursor
376
377 try:
378 sql = "UPDATE Draws SET DrawNumber=?, SequenceNumber=?, DrawDate=?, NumbersDrawn=?, BonusNumber=? " \
379 "WHERE DrawNumber=? AND SequenceNumber=?"
380 db.execute(sql, (draw_number, sequence_number, draw_date,
381 ints_to_str(drawn_numbers), bonus_number, draw_number, sequence_number))
382
383 return True
384
385 except Exception as err:
386 print("Error:8 Could not update row in database. %s" % (str(err)))
387
388 return False
389
390
391def main():
392
393 game = LottoGame("Lotto 6/49", 1, 49, 6, 1, "lotto.db")
394 game.import_csv_file("649.csv")
395 #game.import_csv_file("LOTTOMAX.csv")
396 game.create_database()
397 game.populate_draws()
398 game.populate_combos()
399 game.populate_stats()
400
401 """
402 stats = []
403 for i in range(1,50):
404 s = game.get_stats([i])
405 stats.append(s)
406
407 for i in sorted(stats, key=lambda k: k['deviation']):
408 print(i)
409 """
410
411 print(game.get_stats((3,)))
412
413 #game.list_draws()
414
415 #print(odds(3, 6))
416
417 #for row in game.get_draw(2964):
418 # print(row)
419
420 """"
421 for i in range(1, game.drawnBalls+1):
422 stats = []
423 for c in combinations((13,14,18,20,40,45), i):
424 stats.append(game.get_stats(c))
425
426 for s in stats:
427 print(s)
428
429 """
430
431 game.db_cursor.execute("SELECT * FROM Stats WHERE Match = '\x03'")
432 rows = game.db_cursor.fetchall()
433
434 for i in rows:
435 print(i)
436
437
438 game.db_conn.close()
439
440main()