xcHQuXVm

1import psycopg2
2import json
3import time
4import csv
5
6def create_tables(cur):
7    continent_query = "CREATE TABLE IF NOT EXISTS continent (name VARCHAR(150) NOT NULL PRIMARY KEY);"
8    plan_query = "CREATE TABLE  IF NOT EXISTS plan (id SERIAL NOT NULL PRIMARY KEY UNIQUE, name VARCHAR(30) NOT NULL references country(name), start_date INTEGER NOT NULL, end_date INTEGER NOT NULL, title VARCHAR(200) NOT NULL UNIQUE, description VARCHAR(9999) NOT NULL);"
9    experience_query = "CREATE TABLE IF NOT EXISTS experience (id SERIAL NOT NULL PRIMARY KEY, start_datetime INTEGER NOT NULL, end_datetime INTEGER NOT NULL, title VARCHAR(200) NOT NULL, description VARCHAR(9999) NOT NULL, plan_title VARCHAR(200) NOT NULL references plan(title));"
10    country_query = "CREATE TABLE IF NOT EXISTS country (country_code VARCHAR(10) NOT NULL PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE);"
11    bigmac_query = "CREATE TABLE IF NOT EXISTS bigmac (country_code VARCHAR(10) NOT NULL, exc_rate FLOAT NOT NULL, currency VARCHAR(10) NOT NULL);"    
12    continent_query = "CREATE TABLE IF NOT EXISTS continents (name VARCHAR(20) NOT NULL PRIMARY KEY)"
13
14    cur.execute(country_query)
15    cur.execute(continent_query)
16    cur.execute(plan_query)
17    cur.execute(experience_query)
18    cur.execute(bigmac_query)
19    conn.commit()
20    
21def pop_continent(cur):
22    cur.execute("INSERT INTO continents (name) VALUES ('Europe') ON CONFLICT DO NOTHING;")
23    cur.execute("INSERT INTO continents (name) VALUES ('Africa') ON CONFLICT DO NOTHING;")
24    cur.execute("INSERT INTO continents (name) VALUES ('South America') ON CONFLICT DO NOTHING;")
25    cur.execute("INSERT INTO continents (name) VALUES ('North America') ON CONFLICT DO NOTHING;")
26    cur.execute("INSERT INTO continents (name) VALUES ('Antarctica') ON CONFLICT DO NOTHING;")
27    cur.execute("INSERT INTO continents (name) VALUES ('Oceania') ON CONFLICT DO NOTHING;")
28    cur.execute("INSERT INTO continents (name) VALUES ('Asia') ON CONFLICT DO NOTHING;")
29
30
31
32def save_to_db(cur, data):
33    country = [
34        data['alpha3'], data['name']
35    ]
36
37    cur.execute(
38        "INSERT INTO country (country_code, name) VALUES (UPPER(%s), %s) ON CONFLICT DO NOTHING", country
39    )
40    
41    
42def save_to_big_mac(cur, data):
43    bigmac = [
44        data[1], data[5], data[2]
45    ]
46    cur.execute(
47        "INSERT INTO bigmac (country_code, exc_rate, currency) VALUES (%s, %s, %s)", bigmac
48
49    )
50    
51def dummy_queries(cur):
52 
53    cur.execute("INSERT INTO plan (name, start_date, end_date, title, description) VALUES ('Sweden', '1', '2', 'Visit Vetlanda', 'See all the famous sights such as Sapa Arena, Sapa Building Systems main office, Hasses Pizzeria') ON CONFLICT DO NOTHING")
54    cur.execute("INSERT INTO plan (name, start_date, end_date, title, description) VALUES ('Denmark', '3', '4', 'Copenhagen', 'Have some beer, go to istegade, visit Sweden')")
55    cur.execute("INSERT INTO plan (name, start_date, end_date, title, description) VALUES ('Norway', '5', '6', 'Bodö', 'Club seals')")
56    cur.execute("INSERT INTO plan (name, start_date, end_date, title, description) VALUES ('Iceland', '7', '8', 'Reykjavik', 'Have a look at the swastika by the main square')")
57    cur.execute("INSERT INTO plan (name, start_date, end_date, title, description) VALUES ('Somalia', '9', '10', 'Mogadishu', 'Become a warlord')")
58    
59def dummy_queries_experience(cur):
60    cur.execute("INSERT INTO experience (start_datetime, end_datetime, title, description, plan_title) VALUES ('1', '2', 'Finally Vetlanda!', 'We will visit this LOVELY place', 'Visit Vetlanda') ON CONFLICT DO NOTHING;")
61    cur.execute("INSERT INTO experience (start_datetime, end_datetime, title, description, plan_title) VALUES ('3', '4', 'Swedens retarded baby brother', 'We will have some drinks and do the only smart think wile in Denmark, go to Sweden', 'Copenhagen') ON CONFLICT DO NOTHING;")
62    cur.execute("INSERT INTO experience (start_datetime, end_datetime, title, description, plan_title) VALUES ('5', '6', 'Swedens retarded baby sister', 'We will kill seals and go clubbing', 'Bodö') ON CONFLICT DO NOTHING;")
63    cur.execute("INSERT INTO experience (start_datetime, end_datetime, title, description, plan_title) VALUES ('7', '8', 'Shithole country', 'Doesnt even have McDonalds', 'Reykjavik') ON CONFLICT DO NOTHING;")
64    cur.execute("INSERT INTO experience (start_datetime, end_datetime, title, description, plan_title) VALUES ('9', '10', 'Lovely Somalia', 'Love this place', 'Mogadishu') ON CONFLICT DO NOTHING;")
65    
66
67
68conn = psycopg2.connect(
69    host="localhost",
70    database="postgres",
71    user="postgres",
72    password="pw")
73
74cur = conn.cursor()
75print ("Connected")
76create_tables(cur)
77pop_continent(cur)
78
79with open('countries.txt') as json_file:
80    data = json.load(json_file)
81    for i in data:
82        save_to_db(cur, i)
83
84conn.commit()
85# dummy_queries(cur)
86dummy_queries_experience(cur)
87
88
89with open('bigmac.csv') as csv_file:
90    csv_reader = csv.reader(csv_file, delimiter=',')
91    line_count = 0
92    for row in csv_reader:
93        if line_count == 0:
94            print(f'Column names are {", ".join(row)}')
95            line_count += 1
96        else:
97            save_to_big_mac(cur, row)
98            line_count += 1
99
100conn.commit()
101
102