COPY from CARTO

copyfrom.py

# create table trans_amt_rank_201805(
#     region_type text,
#     region_id text,
#     month text
#     industry_code text,
#     industry_name text,
#     trans_amt_share text,
#     trans_amt_rank integer
# 
# )

import requests
import time
import traceback

start_time = time.time()

api_key = '50e02854ebc4813f9b9f8210ac010639c260b2e5'
username = 'alasarr'
upload_file = 'trans_amt_rank_201805.csv'
q = "COPY trans_amt_rank_201805 (region_type,region_id,month,industry_code,industry_name,trans_amt_share,trans_amt_rank) FROM STDIN WITH (FORMAT csv, HEADER true)"

url = "http://%s.carto.com/api/v2/sql/copyfrom" % username
with open(upload_file, 'rb') as f:
    try:
        r = requests.post(url, params={'api_key': api_key, 'q': q}, data=f, stream=True)
        
        if r.status_code != 200:
            print(r.text)
        else:
            status = r.json()
            print("Success: %s rows imported" % status['total_rows'])
    except:
        # print(r)
        traceback.print_exc()

    end_time = time.time() - start_time
    print(end_time)