· 10 years ago · Apr 24, 2016, 11:21 PM
1from __future__ import print_function
2
3import mysql.connector
4from mysql.connector import errorcode
5
6import random
7import datetime
8import time
9
10
11# This script creates and initializes a mysql database on your
12# localhost. The default config mysql connects to the database with
13# the user: cs4400 and password: cs4400password, this can be customized
14# by changing the 'cnx' variable in the main method. In addition to
15# creating the database and its tables, this script also populates
16# the database according to the given guidelines.
17
18
19DB_NAME = 'gt_train'
20
21TABLES = {}
22
23TABLES['trainroute'] = (
24 "CREATE TABLE `trainroute` ("
25 "`trainnumber` varchar(255) NOT NULL,"
26 "`first_class_price` DECIMAL(5,2),"
27 "`second_class_price` DECIMAL(5,2),"
28 "PRIMARY KEY(`trainnumber`)"
29 ") ENGINE=InnoDB"
30)
31
32TABLES['stop'] = (
33 "CREATE TABLE `stop` ("
34 "`trainnumber` varchar(255) NOT NULL,"
35 "`stationname` varchar(255) NOT NULL,"
36 "`arrival_time` TIME NOT NULL,"
37 "`departure_time` TIME NOT NULL,"
38 "PRIMARY KEY(`trainnumber`, `stationname`),"
39 "FOREIGN KEY(`trainnumber`) references trainroute(`trainnumber`),"
40 "FOREIGN KEY(`stationname`) references station(`name`)"
41 ") ENGINE=InnoDB"
42)
43
44
45TABLES['station'] = (
46 "CREATE TABLE `station` ("
47 "`name` varchar(255) NOT NULL,"
48 "`location` varchar(255) NOT NULL,"
49 "PRIMARY KEY(`name`)"
50 ") ENGINE=InnoDB"
51
52
53)
54
55TABLES['reserves'] = (
56 "CREATE TABLE `reserves` ("
57 "`trainnumber` varchar(255) NOT NULL,"
58 "`reservationid` INT NOT NULL,"
59 "`class` enum('first','second') NOT NULL,"
60 "`departure_date` date NOT NULL,"
61 "`passenger_name` varchar(255) NOT NULL,"
62 "`number_of_bags` INT NOT NULL,"
63 "`departs_from` varchar(255) NOT NULL,"
64 "`arrives_at` varchar(255) NOT NULL,"
65 "PRIMARY KEY (`trainnumber`, `reservationid`),"
66 "FOREIGN KEY(`trainnumber`) references trainroute(`trainnumber`),"
67 "FOREIGN KEY(`reservationid`) references reservation(`reservationid`)"
68 ") ENGINE=InnoDB"
69
70
71)
72
73TABLES['reservation'] = (
74 "CREATE TABLE `reservation` ("
75 "`reservationid` INT NOT NULL,"
76 "`username` varchar(255) NOT NULL,"
77 "`cardnumber` varchar(16),"
78 "`is_cancelled` boolean DEFAULT false,"
79 "`total_cost` DECIMAL(5,2) NOT NULL,"
80 "PRIMARY KEY(`reservationid`),"
81 "FOREIGN KEY(`username`) references user_customer(`username`),"
82 "FOREIGN KEY(`cardnumber`) references payment_info(`cardnumber`) ON DELETE SET NULL"
83 ") ENGINE=InnoDB"
84
85
86)
87
88TABLES['review'] = (
89 "CREATE TABLE `review` ("
90 "`reviewid` INT NOT NULL,"
91 "`comment` TEXT,"
92 "`rating` enum('very good','good','neutral','bad','very bad') NOT NULL,"
93 "`username` varchar(255) NOT NULL,"
94 "`trainnumber` varchar(255) NOT NULL,"
95 "PRIMARY KEY(`reviewid`),"
96 "FOREIGN KEY(`username`) references user_customer(`username`),"
97 "FOREIGN KEY(`trainnumber`) references trainroute(`trainnumber`)"
98 ") ENGINE=InnoDB"
99
100
101)
102
103TABLES['user'] = (
104 "CREATE TABLE `user` ("
105 "`username` varchar(255) NOT NULL,"
106 "`password` varchar(255) NOT NULL,"
107 "PRIMARY KEY(`username`)"
108 ") ENGINE=InnoDB"
109
110)
111
112TABLES['user_customer'] = (
113 "CREATE TABLE `user_customer` ("
114 "`username` varchar(255) NOT NULL,"
115 "`email` varchar(255) NOT NULL,"
116 "`is_student` BOOLEAN DEFAULT false,"
117 "PRIMARY KEY(`username`),"
118 "FOREIGN KEY(`username`) references user(`username`)"
119 ") ENGINE=InnoDB"
120
121
122)
123
124TABLES['user_manager'] = (
125 "CREATE TABLE `user_manager` ("
126 "`username` varchar(255) NOT NULL,"
127 "PRIMARY KEY(`username`),"
128 "FOREIGN KEY(`username`) references user(`username`)"
129 ") ENGINE=InnoDB"
130
131
132)
133
134TABLES['payment_info'] = (
135 "CREATE TABLE `payment_info` ("
136 "`cardnumber` varchar(16) NOT NULL,"
137 "`cvv` INT NOT NULL,"
138 "`exp_date` date NOT NULL,"
139 "`name_on_card` varchar(255) NOT NULL,"
140 "`username` varchar(255) NOT NULL,"
141 "PRIMARY KEY(`cardnumber`),"
142 "FOREIGN KEY(`username`) references user_customer(`username`)"
143 ") ENGINE=InnoDB"
144
145)
146
147TABLES['system_info'] = (
148 "CREATE TABLE `system_info` ("
149 "`max_num_of_baggage` INT NOT NULL,"
150 "`num_free_baggage` INT,"
151 "`student_discount` DECIMAL(2,2),"
152 "`change_fee` DECIMAL(5,2),"
153 "PRIMARY KEY(`max_num_of_baggage`)"
154 ") ENGINE=InnoDB"
155
156
157)
158
159
160
161def create_database(cursor):
162 try:
163 cursor.execute(
164 "CREATE DATABASE {} DEFAULT CHARACTER SET `utf8`".format(DB_NAME))
165 except mysql.connector.Error as err:
166 print("Failed creating database: {}".format(err))
167
168 try:
169 cnx.database = DB_NAME
170 except mysql.connector.Error as err:
171 if err.errno == errorcode.ER_BAD_DB_ERROR:
172 create_database(cursor)
173 cnx.database = DB_NAME
174 else:
175 print(err)
176
177def populate_database(cursor):
178
179 # GUIDELINES #
180 #------------------------------------------------#
181 #
182 # Jan.1.2016 - May.31.2016
183 # At least 25 users (20 customers, 5 managers) *DONE*
184 # At least 5 customers w/o payment info *DONE* (kinda)
185 # At least 5 customers w/ a credit card stored *DONE*
186 # 6 stations *DONE*
187 # 8 Train routes (each one w/ at least 3 stops) *DONE*
188 # At least 40 reservations
189 # - 20 should have a future end date
190 # - 7 should have two tickets
191 # - 10 should be cancelled
192 # Each train route reserved at least twice
193 # At least 20 reviews (>1 for each route) *DONE*
194
195 "USERS"
196 users = []
197 customers = []
198 for i in range(20):
199 if (i < 10):
200 name = "customer0" + str(i)
201 else:
202 name = "customer" + str(i)
203 uname = name
204 pword = name + "password"
205 student = False
206 if i%3==0:
207 email = name + "@gmail.com"
208 elif i%7==0:
209 email = name + "@yahoo.com"
210 else:
211 email = name + "@gatech.edu"
212 student = True
213 users.append((uname, pword))
214 customers.append((uname, email, student))
215
216 managers = []
217 for i in range(5):
218 uname = "manager" + str(i)
219 pword = "manager" + str(i) + "password"
220 users.append((uname, pword))
221 managers.append(uname)
222
223 for (u, p) in users:
224 user_data = (u, p)
225
226
227 new_user = ("CREATE USER %s@'localhost'"
228 "IDENTIFIED BY %s;")
229 try:
230 cursor.execute(new_user, user_data)
231 except mysql.connector.Error as err:
232 print("MySQL User already exists")
233
234
235 grant_permissions = ("GRANT ALL ON gt_train.* TO %s@'localhost'")
236 try:
237 cursor.execute(grant_permissions, (u,))
238 except mysql.connector.Error as err:
239 print(err.msg)
240
241
242 add_user = ("INSERT INTO user "
243 "(username, password) "
244 "VALUES (%s, %s)")
245
246 try:
247 cursor.execute(add_user, user_data)
248 except mysql.connector.Error as err:
249 print(err.msg)
250
251 for (u, e, s) in customers:
252 add_user = ("INSERT INTO user_customer "
253 "(username, email, is_student) "
254 "VALUES (%s, %s, %s)")
255 user_data = (u, e, s)
256 try:
257 cursor.execute(add_user, user_data)
258 except mysql.connector.Error as err:
259 print(err.msg)
260
261 # Not sure why tf this wont worl
262
263 for u in managers:
264 #print(u)
265 add_user = ("INSERT INTO user_manager "
266 "(username) "
267 "VALUES (%s)")
268 try:
269 cursor.execute(add_user, (u,))
270 except mysql.connector.Error as err:
271 print(err.msg)
272
273
274 "Payment Info"
275 # 10 users with Payment Info
276 card_names = ["Sean Washington", "Brandon Riggs", "Derek Smith",
277 "Rebecca Hamilton", "Lorin Ashton", "Sam Jackson",
278 "Rohan Patel", "Bill Blunt", "Kendall Rogers",
279 "Hannibal Burress"]
280 nameSelector = 0
281 for i in range(10):
282 (uname, email, student) = customers[i*2]
283 card_no = ""
284 cvv_string = ""
285 for i in range(16):
286 card_no += str(random.randint(0, 9))
287 for i in range(3):
288 if i == 0:
289 cvv_string += str(random.randint(1, 9))
290 else:
291 cvv_string += str(random.randint(0, 9))
292 cvv = int(cvv_string)
293
294 yy = random.randint(2016, 2030)
295 mm = random.randint(1, 12)
296 dd = random.randint(1, 28)
297 if yy == 2016 and mm < 5:
298 yy += 1
299 if dd < 10:
300 dd_str = "0" + str(dd)
301 dd = int(dd_str)
302
303 exp_date = datetime.date(yy, mm, dd)
304
305 real_name = card_names[nameSelector]
306 nameSelector += 1
307
308 add_payment_info = ("INSERT INTO payment_info "
309 "(cardnumber, cvv, exp_date, name_on_card, username) "
310 "VALUES (%s, %s, %s, %s, %s)")
311 card_data = (card_no, cvv, exp_date, real_name, uname)
312 try:
313 cursor.execute(add_payment_info, card_data)
314 except mysql.connector.Error as err:
315 print(err.msg)
316
317 # 6 Train Stations
318
319 train_stations = {}
320 train_stations['Atlanta'] = "ATL Midtown Station"
321 train_stations['Boston'] = "Red Sox Trains"
322 train_stations['Nashville'] = "Music City Train Station"
323 train_stations['New York City'] = "NYC Terminal"
324 train_stations['Detroit'] = "Detroit Trains"
325 train_stations['Miami'] = "South Beach Station"
326 train_stations['Richmond'] = "Virginia Train Stop"
327
328 for key in train_stations:
329 station_info = ("INSERT INTO station "
330 "(name, location) "
331 "VALUES (%s, %s)")
332 station_data = (train_stations[key], key)
333 try:
334 cursor.execute(station_info, station_data)
335 except mysql.connector.Error as err:
336 print(err.msg)
337
338 # 8 Train Routes
339 train_routes = [("101", 110.0, 50.0),
340 ("246", 120.0, 60.0),
341 ("808", 100.0, 50.0),
342 ("123", 125.0, 60.0),
343 ("420", 200.0, 100.0),
344 ("666", 80.0, 40.0),
345 ("707", 100.0, 50.0),
346 ("912", 90.0, 50.0)]
347 for (num, fcp, scp) in train_routes:
348 route_info = ("INSERT INTO trainroute "
349 "(trainnumber, first_class_price, second_class_price) "
350 "VALUES (%s, %s, %s)")
351 route_data = (num, fcp, scp)
352 try:
353 cursor.execute(route_info, route_data)
354 except mysql.connector.Error as err:
355 print(err.msg)
356
357 # Add Stops to Train Routes
358
359 route1 = [("420", train_stations['Miami'], datetime.time(4,0,0), datetime.time(4,30,0)),
360 ("420", train_stations['Atlanta'], datetime.time(11,0,0), datetime.time(11,30,0)),
361 ("420", train_stations['Richmond'], datetime.time(20,0,0), datetime.time(20,30,0)),
362 ("420", train_stations['New York City'], datetime.time(23,0,0), datetime.time(23,30,0))]
363 route2 = [("707",train_stations['Atlanta'], datetime.time(6,0,0), datetime.time(6,30,0)),
364 ("707", train_stations['Nashville'], datetime.time(10,0,0), datetime.time(10,30,0)),
365 ("707", train_stations['Detroit'], datetime.time(14,0,0), datetime.time(14,30,0))]
366 route3 = [("123", train_stations['New York City'], datetime.time(4,0,0), datetime.time(4,30,0)),
367 ("123", train_stations['Boston'], datetime.time(9,0,0), datetime.time(9,30,0)),
368 ("123", train_stations['Nashville'], datetime.time(19,0,0), datetime.time(19,30,0)),
369 ("123", train_stations['Atlanta'], datetime.time(23,0,0), datetime.time(23,30,0))]
370 route4 = [("808", train_stations['Miami'], datetime.time(4,0,0), datetime.time(4,30,0)),
371 ("808", train_stations['Atlanta'], datetime.time(12,0,0), datetime.time(12,30,0)),
372 ("808", train_stations['Nashville'], datetime.time(16,0,0), datetime.time(16,30,0)),
373 ("808", train_stations['Boston'], datetime.time(23,0,0), datetime.time(23,30,0))]
374 route5 = [("246",train_stations['Detroit'], datetime.time(6,0,0), datetime.time(6,30,0)),
375 ("246", train_stations['Boston'], datetime.time(10,0,0), datetime.time(10,30,0)),
376 ("246", train_stations['Atlanta'], datetime.time(14,0,0), datetime.time(14,30,0))]
377 route6 = [("101",train_stations['Richmond'], datetime.time(6,0,0), datetime.time(6,30,0)),
378 ("101", train_stations['Boston'], datetime.time(10,0,0), datetime.time(10,30,0)),
379 ("101", train_stations['New York City'], datetime.time(14,0,0), datetime.time(14,30,0))]
380 route7 = [("912",train_stations['Atlanta'], datetime.time(6,0,0), datetime.time(6,30,0)),
381 ("912", train_stations['Richmond'], datetime.time(15,0,0), datetime.time(15,30,0)),
382 ("912", train_stations['New York City'], datetime.time(19,0,0), datetime.time(19,30,0))]
383 route8 = [("666",train_stations['Nashville'], datetime.time(6,0,0), datetime.time(6,30,0)),
384 ("666", train_stations['Richmond'], datetime.time(14,0,0), datetime.time(14,30,0)),
385 ("666", train_stations['Boston'], datetime.time(18,0,0), datetime.time(18,30,0))]
386
387 allRoutes = [route1, route2, route3, route4, route5, route6, route7, route8]
388
389 for route in allRoutes:
390 for stp in route:
391 add_stop = ("INSERT INTO stop"
392 "(trainnumber, stationname, arrival_time, departure_time)"
393 "VALUES (%s, %s, %s, %s)")
394 stop_data = (stp[0], stp[1], stp[2], stp[3])
395 try:
396 cursor.execute(add_stop, stop_data)
397 except mysql.connector.Error as err:
398 print(err.msg)
399
400 reviews = ("INSERT INTO review "
401 "(reviewid, comment, rating, username, trainnumber)"
402 "VALUES (%s, %s, %s, %s, %s)")
403 review_info = ('1', 'Awesome', "very good", "customer00", "101")
404 try:
405 cursor.execute(reviews, review_info)
406 except mysql.connector.Error as err:
407 print(err.msg)
408 review_info = ('2', 'Poor', "very bad", "customer00", "101")
409 try:
410 cursor.execute(reviews, review_info)
411 except mysql.connector.Error as err:
412 print(err.msg)
413 review_info = ('3', 'Great', "very good", "customer00", "123")
414 try:
415 cursor.execute(reviews, review_info)
416 except mysql.connector.Error as err:
417 print(err.msg)
418 review_info = ('4', 'Okay', "good", "customer00", "123")
419 try:
420 cursor.execute(reviews, review_info)
421 except mysql.connector.Error as err:
422 print(err.msg)
423 review_info = ('5', 'Bad', "bad", "customer00", "123")
424 try:
425 cursor.execute(reviews, review_info)
426 except mysql.connector.Error as err:
427 print(err.msg)
428 review_info = ('6', 'Bad', "very bad", "customer00", "123")
429 try:
430 cursor.execute(reviews, review_info)
431 except mysql.connector.Error as err:
432 print(err.msg)
433 review_info = ('7', 'Awesome', "very good", "customer10", "246")
434 try:
435 cursor.execute(reviews, review_info)
436 except mysql.connector.Error as err:
437 print(err.msg)
438 review_info = ('8', 'Not Good', "bad", "customer10", "246")
439 try:
440 cursor.execute(reviews, review_info)
441 except mysql.connector.Error as err:
442 print(err.msg)
443 review_info = ('9', "Don't Care", "neutral", "customer19", "420")
444 try:
445 cursor.execute(reviews, review_info)
446 except mysql.connector.Error as err:
447 print(err.msg)
448 review_info = ('10', 'Great', "good", "customer19", "420")
449 try:
450 cursor.execute(reviews, review_info)
451 except mysql.connector.Error as err:
452 print(err.msg)
453 review_info = ('11', 'Great', "good", "customer14", "420")
454 try:
455 cursor.execute(reviews, review_info)
456 except mysql.connector.Error as err:
457 print(err.msg)
458 review_info = ('12', 'Poor service', "bad", "customer14", "666")
459 try:
460 cursor.execute(reviews, review_info)
461 except mysql.connector.Error as err:
462 print(err.msg)
463 review_info = ('13', 'Horrible People', "very bad", "customer14", "666")
464 try:
465 cursor.execute(reviews, review_info)
466 except mysql.connector.Error as err:
467 print(err.msg)
468 review_info = ('14', 'Great', "good", "customer16", "707")
469 try:
470 cursor.execute(reviews, review_info)
471 except mysql.connector.Error as err:
472 print(err.msg)
473 review_info = ('15', 'Horrible', "very bad", "customer16", "808")
474 try:
475 cursor.execute(reviews, review_info)
476 except mysql.connector.Error as err:
477 print(err.msg)
478 review_info = ('16', "Don't Care", "neutral", "customer03", "707")
479 try:
480 cursor.execute(reviews, review_info)
481 except mysql.connector.Error as err:
482 print(err.msg)
483 review_info = ('17', 'Horrible', "bad", "customer03", "808")
484 try:
485 cursor.execute(reviews, review_info)
486 except mysql.connector.Error as err:
487 print(err.msg)
488 review_info = ('18', 'Good Service', "good", "customer04", "912")
489 try:
490 cursor.execute(reviews, review_info)
491 except mysql.connector.Error as err:
492 print(err.msg)
493 review_info = ('19', 'Poor chairs', "very bad", "customer04", "912")
494 try:
495 cursor.execute(reviews, review_info)
496 except mysql.connector.Error as err:
497 print(err.msg)
498 review_info = ('20', 'Not bad', "good", "customer04", "101")
499 try:
500 cursor.execute(reviews, review_info)
501 except mysql.connector.Error as err:
502 print(err.msg)
503
504 sys_info = ("INSERT INTO system_info"
505 "(max_num_of_baggage, num_free_baggage, student_discount, change_fee)"
506 "VALUES (%s, %s, %s, %s)")
507 systeminfo = (4, 2, 20, 50)
508 try:
509 cursor.execute(sys_info, systeminfo)
510 except mysql.connector.Error as err:
511 print(err.msg)
512
513
514
515
516cnx = mysql.connector.connect(user='root', password='',
517 host='localhost', database='gt_train')
518cursor = cnx.cursor()
519
520# 1. Create Database
521create_database(cursor)
522
523# 2. Create Tables
524for name, ddl in TABLES.iteritems():
525 try:
526 print("Creating table {}: ".format(name), end='')
527 cursor.execute(ddl)
528 except mysql.connector.Error as err:
529 if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
530 print("already exists.")
531 else:
532 print(err.msg)
533 else:
534 print("OK")
535
536# 3. Populate Database
537populate_database(cursor)
538cnx.commit()
539
540cursor.close()
541cnx.close()