API tools faq
paste
Guest User

DB SETUP

a guest
Apr 24th, 2016
88
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 18.77 KB | None | 0 0
raw download clone embed print report
  1. from __future__ import print_function
  2.  
  3. import mysql.connector
  4. from mysql.connector import errorcode
  5.  
  6. import random
  7. import datetime
  8. import 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.  
  19. DB_NAME = 'gt_train'
  20.  
  21. TABLES = {}
  22.  
  23. TABLES['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.  
  32. TABLES['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.  
  45. TABLES['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.  
  55. TABLES['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.  
  73. TABLES['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.  
  88. TABLES['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.  
  103. TABLES['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.  
  112. TABLES['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.  
  124. TABLES['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.  
  134. TABLES['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.  
  147. TABLES['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.  
  161. def 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.  
  177. def 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.  
  516. cnx = mysql.connector.connect(user='root', password='',
  517. host='localhost', database='gt_train')
  518. cursor = cnx.cursor()
  519.  
  520. # 1. Create Database
  521. create_database(cursor)
  522.  
  523. # 2. Create Tables
  524. for 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
  537. populate_database(cursor)
  538. cnx.commit()
  539.  
  540. cursor.close()
  541. cnx.close()
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!