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