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