· 8 years ago · Dec 26, 2017, 02:26 AM
1import sqlite3
2from itertools import combinations
3
4
5class LottoGame:
6
7 def __init__(self, name, csv):
8 self.name = name
9 self.csvFile = csv
10 self.index = 1
11 self.drawCount = 0
12 self.drawLength = 0
13 self.draws = list()
14 self.db_conn = None
15
16 def __iter__(self):
17 return self
18
19 def __next__(self):
20 db = self.db_conn
21
22 if self.index > self.drawCount:
23 self.index = 1
24 raise StopIteration
25
26 try:
27 results = db.execute("SELECT ID, DrawNumber, SequenceNumber, DrawDate, NumbersDrawn, BonusNumber FROM Draws WHERE ID = "+str(self.index)+";")
28
29 draw = ""
30 self.index += 1
31 for row in results:
32 draw = (row[0], row[1], row[2], row[3], str_to_ints(row[4]), row[5])
33
34 return draw
35
36 except:
37 print("Error:10 Could not select rows from database.")
38
39 return False
40
41 def create_database(self, database=":memory:"):
42 self.db_conn = sqlite3.connect(database)
43
44 db = self.db_conn
45
46 if self.parse_csv():
47 self.drawLength = len(self.draws[0][3])
48
49 try:
50 if database.lower() != ":memory:":
51 db.execute("DROP TABLE IF EXISTS Draws")
52
53 db.execute("CREATE TABLE Draws (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
54 "DrawNumber INTEGER NOT NULL, SequenceNumber INTEGER NOT NULL, "
55 "DrawDate TEXT NOT NULL, NumbersDrawn TEXT NOT NULL, BonusNumber INTEGER NOT NULL)")
56
57 db.execute("CREATE INDEX idx_DrawNumber ON Draws (DrawNumber)")
58 db.execute("CREATE INDEX idx_NumbersDrawn ON Draws (NumbersDrawn)")
59
60 self.populate_draws()
61
62 for i in range(self.drawLength):
63 if database.lower() != ":memory:":
64 db.execute("DROP TABLE IF EXISTS Match"+str(i+1)+"")
65
66 sql = "CREATE TABLE Match"+str(i+1)+" (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " \
67 "DrawNumber INTEGER NOT NULL, Match TEXT NOT NULL, " \
68 "FOREIGN KEY(DrawNumber) REFERENCES Draws(DrawNumber))"
69
70 db.execute(sql)
71 db.execute("CREATE INDEX idx_Match"+str(i+1)+" ON Match"+str(i+1)+" (Match)")
72
73 self.populate()
74
75 return True
76
77 except Exception as err:
78 print("Error:1 Could not create table. %s" % (str(err)))
79
80 return False
81
82 def populate_draws(self):
83 db = self.db_conn
84
85 try:
86 for i in self.draws:
87 db.execute('INSERT INTO Draws (DrawNumber, SequenceNumber, DrawDate, NumbersDrawn, BonusNumber) '
88 'VALUES (?,?,?,?,?)', (int(i[0]), int(i[1]), i[2], i[3], i[4]))
89
90 #print((int(i[0]), int(i[1]), i[2], i[3], i[4]))
91
92 db.commit()
93
94 return True
95
96 except Exception as err:
97 print("Error:2 Could insert row into table. %s" % (str(err)))
98
99 return False
100
101 def populate(self):
102 db = self.db_conn
103
104 try:
105 for draw in self:
106 level = 0
107 for i in combos(6, draw[4]):
108 level += 1
109 for j in i:
110 #print(draw[1], level, j)
111 db.execute('INSERT INTO Match'+str(level)+' (DrawNumber, Match) VALUES (?,?)',
112 (int(draw[1]), ints_to_str(j)))
113 db.commit()
114
115 return True
116
117 except Exception as err:
118 print("Error:4 Could insert row into table. %s" % (str(err)))
119
120 return False
121
122
123 def parse_csv(self):
124 try:
125 fh = open(self.csvFile, encoding="utf-8")
126
127 fh.readline()
128 while True:
129 line = fh.readline()
130
131 if not line:
132 break
133
134 line = line.replace('"', '').rstrip().split(",")
135 line.pop(0)
136
137 s = ""
138 for i in line[3:-1]:
139 s += chr(int(i))
140
141 tmp = line[:3]
142 tmp.append(s)
143 tmp.append(line[-1:][0])
144
145 self.draws.append(tuple(tmp))
146 self.drawCount += 1
147
148 fh.close()
149
150 return True
151
152 except IOError:
153 print('Error:5 Could not open file "{}"'.format(self.csvFile))
154
155 return False
156
157 def list_rows(self, detail=False):
158 db = self.db_conn
159
160 try:
161 sql = "SELECT ID, DrawNumber, SequenceNumber, DrawDate, NumbersDrawn, BonusNumber FROM Draws"
162
163 for row in db.execute(sql):
164 if detail:
165 print("ID:", row[0])
166 print("Draw Number:", row[1])
167 print("Sequence Number:", row[2])
168 print("Draw Date:", row[3])
169 print("Numbers Drawn:", str_to_ints(row[4]))
170 print("Bonus Number: {}\n".format(row[5]))
171 else:
172 print("{} {} {} {} {} ({})".format(row[0],row[1],row[2],row[3],str_to_ints(row[4]),row[5]))
173
174 return True
175
176 except Exception as err:
177 print("Error:6 Could not select rows from database. %s" % (str(err)))
178
179 return False
180
181 def get_draw(self, draw_number, sequence_number=0):
182 db = self.db_conn
183
184 try:
185 sql = "SELECT ID, DrawNumber, SequenceNumber, DrawDate, NumbersDrawn, BonusNumber" \
186 " FROM Draws WHERE DrawNumber = ? AND SequenceNumber = ?"
187
188 results = db.execute(sql, (draw_number, sequence_number))
189
190 rows = list()
191 for row in results:
192 rows.append((row[0], row[1], row[2], row[3], self.str_to_ints(row[4]), row[5]))
193
194 return rows
195
196 except Exception as err:
197 print("Error:7 Could not select rows from database. %s" % (str(err)))
198
199 return False
200
201 def update_draw(self, draw_number, sequence_number, draw_date, drawn_numbers, bonus_number):
202 db = self.db_conn
203
204 try:
205 sql = "UPDATE Draws SET DrawNumber=?, SequenceNumber=?, DrawDate=?, NumbersDrawn=?, BonusNumber=? " \
206 "WHERE DrawNumber=? AND SequenceNumber=?"
207
208 db.execute(sql, (draw_number, sequence_number, draw_date, ints_to_str(drawn_numbers), bonus_number,
209 draw_number, sequence_number))
210
211 return True
212
213 except Exception as err:
214 print("Error:8 Could not update row in database. %s" % (str(err)))
215
216 return False
217
218 def search_draws(self, draw_numbers):
219 db = self.db_conn
220
221 try:
222 sql = "SELECT DrawNumber, Match FROM Match"+str(len(draw_numbers))+" WHERE Match = ?"
223
224 #print(sql, draw_numbers)
225 results = db.execute(sql, (ints_to_str(draw_numbers),))
226
227 rows = list()
228
229 for row in results:
230 rows.append((row[0], str_to_ints(row[1])))
231
232 if not len(rows):
233 return False
234 else:
235 return rows
236
237 except Exception as err:
238 print("Error:9 Could not select rows from database. %s" % (str(err)))
239
240 return False
241
242 def stats(self, combo):
243 db = self.db_conn
244
245 for c in combos(6, combo):
246 for i in c:
247 num = self.search_draws(i)
248
249 if num:
250 for j in num:
251
252 sql = "SELECT ID, DrawDate FROM Draws WHERE DrawNumber = '"+str(j[0])+"' LIMIT 1"
253 results = db.execute(sql)
254
255 date = None
256
257 for q in results:
258 date = q[1]
259
260 print("\nDraw Number: {}\nDraw Date: {}\nCombination: {}".format(j[0], date, j[1]))
261
262
263
264def str_to_ints(chars):
265 numbers = list()
266
267 for i in chars:
268 numbers.append(ord(i))
269
270 return tuple(numbers)
271
272
273def ints_to_str(ints):
274
275 string = ""
276 for i in ints:
277 string += chr(i)
278
279 return string
280
281
282def combos(max_depth, iterable):
283 return [list(combinations(iterable, d)) for d in range(1, max_depth+1)]
284
285def main():
286 game = LottoGame("Lotto 6/49", "649.csv")
287 #game = LottoGame("Lotto 6/49", "LOTTOMAX.csv")
288
289 game.create_database()
290
291 #i = 0
292 #for row in game.db_conn.execute(sql, (ints_to_str((31,)))):
293 # i+=1
294 # print("Match1:{} {} {} {} {} {} ({})".format(i, row[0], row[1], row[2], row[3], str_to_ints(row[4]), row[5]))
295
296
297 game.stats((11, 16, 19, 23, 33, 45))
298
299
300 game.db_conn.close()
301
302
303main()