cYrezXmW

1import mysql.connector
2import json
3
4def checkIfNull(data):
5      if(data == "NULL" or data == "Not available"):
6            return None
7      else:
8            return data
9
10mydb = mysql.connector.connect(
11  host="localhost",
12  user="root",
13  passwd="password",
14  database="travelog"
15)
16mycursor = mydb.cursor() 
17mycursor.execute("DROP TABLE IF EXISTS country")
18mycursor.execute("DROP TABLE IF EXISTS individual")
19
20mycursor.execute("DROP TABLE IF EXISTS listing")
21
22listing = "CREATE TABLE listing (destinationName VARCHAR(100), duration VARCHAR(20), rating float(11,2),link VARCHAR(255), id int PRIMARY KEY AUTO_INCREMENT)"
23country = "CREATE TABLE country (countryName VARCHAR(50),listingId int,FOREIGN KEY(listingId) REFERENCES listing(id))"
24indListing = "CREATE TABLE individual (checkIn VARCHAR(20),checkOut VARCHAR(20), savings VARCHAR(50), beforePrice VARCHAR(50),price VARCHAR(50),indivId int, FOREIGN KEY(indivId) REFERENCES listing(id))"
25mycursor.execute(listing)
26mycursor.execute(country)
27mycursor.execute(indListing)
28mydb.commit()
29link = '/home/dalus/Desktop/Semetre_project/python/data.json'
30with open(link) as json_file:
31      data=json.load(json_file)
32      for d in data['Novaturas Keliones']:
33            toPrint1 = (checkIfNull(d['Destination Name']) , checkIfNull(d['Duration']) , checkIfNull(d['Rating']) , checkIfNull(d['Link']))
34            mycursor.execute("INSERT INTO listing(destinationName, duration, rating, link) VALUES (%s, %s, %s, %s)", toPrint1)
35            lastID=mycursor.lastrowid
36            for c in d['Countries']:
37                  shitface = c.lower()
38                  mycursor.execute("INSERT INTO country(listingId,countryName) VALUES (%s,%s)", (lastID,shitface))
39            for i in d['Individual data']:
40                  toPrint=(checkIfNull(i['Check In']),checkIfNull(i['CheckOut']),checkIfNull(i['savings']),checkIfNull(i['beforeSavings']),checkIfNull(i['price']),lastID)
41                  mycursor.execute("INSERT INTO individual(checkIn,checkOut,savings,beforePrice,price,indivId) VALUES (%s,%s,%s,%s,%s,%s)", toPrint)
42      mydb.commit()