athena2-load-postgres-data.md

Step 1 - Create table

create table madrid_air (
date text,
BEN text,
CO text,
EBE text,
NMHC text,
NO text,
NO_2 text,
O_3 text,
PM10 text,
PM25 text,
SO_2 text,
TCH text,
TOL text,
station text
)

Step 2 - Copy data into table

a.

import csv,psycopg2
conn = psycopg2.connect("host=localhost dbname=testdb user=postgres password=password")
cur = conn.cursor()
with open('/Users/arjunrao/Downloads/air-quality-madrid/csvs_per_year/madrid_2011.csv', 'r') as f:
    reader = csv.reader(f)
    next(reader)  # Skip the header row.
    for row in reader:
        cur.execute(
            "INSERT INTO madrid_air VALUES (%s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s, %s,%s, %s)",
            row
        )
conn.commit()

OR

b.

with open('/Users/arjunrao/Downloads/air-quality-madrid/csvs_per_year/madrid_2011.csv', 'r') as f:
    # Notice that we don't need the `csv` module.
    next(f)  # Skip the header row.
    cur.copy_from(f, 'madrid_air', sep=',')
    
conn.commit()

Step 3 - Read data from table

select * from madrid_air limit 1000