esv0dvJZ

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 references country(country_code), exc_rate FLOAT NOT NULL, currency VARCHAR(10) NOT NULL);"
12    
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
21
22def save_to_db(cur, data):
23    country = [
24        data['alpha3'], data['name']
25    ]
26
27    cur.execute(
28        "INSERT INTO country (country_code, name) VALUES (UPPER(%s), %s) ON CONFLICT DO NOTHING ", country
29    )
30    
31    
32def save_to_big_mac(cur, data):
33    bigmac = [
34        data[1], data[5], data[2]
35    ]
36    cur.execute(
37        "INSERT INTO bigmac (country_code, exc_rate, currency) VALUES (%s, %s, %s)", bigmac
38
39    )
40
41
42conn = psycopg2.connect(
43    host="localhost",
44    database="postgres",
45    user="postgres",
46    password="pw")
47
48cur = conn.cursor()
49print ("Connected")
50create_tables(cur)
51
52with open('countries.txt') as json_file:
53    data = json.load(json_file)
54    for i in data:
55        save_to_db(cur, i)
56
57conn.commit()
58
59with open('bigmac.csv') as csv_file:
60    csv_reader = csv.reader(csv_file, delimiter=',')
61    line_count = 0
62    for row in csv_reader:
63        if line_count == 0:
64            print(f'Column names are {", ".join(row)}')
65            line_count += 1
66        else:
67            save_to_big_mac(cur, row)
68            line_count += 1
69
70conn.commit()