h1KSuJST

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, start_date INTEGER NOT NULL, end_date INTEGER NOT NULL, title VARCHAR(200) NOT NULL, 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);"
10    country_query = "CREATE TABLE IF NOT EXISTS country (country_code VARCHAR(10) NOT NULL PRIMARY KEY, name VARCHAR(100) NOT NULL);"
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(continent_query)
15    # cur.execute(plan_query)
16    # cur.execute(experience_query)
17    cur.execute(country_query)
18    cur.execute(bigmac_query)
19    conn.commit()
20    
21def pop_continent(cur):
22    cur.execute("INSERT INTO continents (name) VALUES ('Europe');")
23    cur.execute("INSERT INTO continents (name) VALUES ('Africa');")
24    cur.execute("INSERT INTO continents (name) VALUES ('South America');")
25    cur.execute("INSERT INTO continents (name) VALUES ('North America');")
26    cur.execute("INSERT INTO continents (name) VALUES ('Antarctica');")
27    cur.execute("INSERT INTO continents (name) VALUES ('Oceania');")
28    cur.execute("INSERT INTO continents (name) VALUES ('Asia');")
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
51
52conn = psycopg2.connect(
53    host="localhost",
54    database="postgres",
55    user="postgres",
56    password="pw")
57
58cur = conn.cursor()
59print ("Connected")
60create_tables(cur)
61pop_continent(cur)
62
63with open('countries.txt') as json_file:
64    data = json.load(json_file)
65    for i in data:
66        save_to_db(cur, i)
67
68conn.commit()
69
70with open('bigmac.csv') as csv_file:
71    csv_reader = csv.reader(csv_file, delimiter=',')
72    line_count = 0
73    for row in csv_reader:
74        if line_count == 0:
75            print(f'Column names are {", ".join(row)}')
76            line_count += 1
77        else:
78            save_to_big_mac(cur, row)
79            line_count += 1
80
81conn.commit()
82