copy-test.py

import sys
import os
import gzip
import random
import md5
from carto.auth import APIKeyAuthClient
from carto.sql import SQLClient
import requests
import hashlib

COPYFILE="/tmp/copyfile"
NROWS=100

APIKEY="xxxxxxxxxxxxxxxxxxxx"
USERNAME="pramsey"

USR_BASE_URL = "https://{user}.carto.com/".format(user=USERNAME)
auth_client = APIKeyAuthClient(api_key=APIKEY, base_url=USR_BASE_URL)

sql = SQLClient(auth_client)

#### DROP #####

try:
    data = sql.send('DROP TABLE IF EXISTS copy_test')
except CartoException as e:
    print("An error ocurred in drop", e)

print data

#### CREATE #####

try:
    data = sql.send("""
        CREATE TABLE copy_test (
            cartodb_id serial, 
            the_geom geometry, 
            the_geom_webmercator geometry,
            name text,
            age integer,
            ts timestamptz default now()
        )"""
    )
except CartoException as e:
    print("An error ocurred in create", e)

print data


#### CARTODBFY #####

try:
    data = sql.send("SELECT CDB_CartodbfyTable('copy_test')")
except CartoException as e:
    print("An error ocurred in create", e)

print data


#### GENERATOR ####

def copy_file_line(n):
    
    for i in range(n):    
        lon = random.random() * 360.0 - 180.0
        lat = random.random() * 180.0 - 90.0
        the_geom = 'SRID=4326;POINT(%g %g)' % (lon, lat)
        name = hashlib.md5(the_geom).hexdigest()
        age = str(int(random.random() * 100.0))
        line = "\t".join([the_geom, name, age]) + "\n"
        yield line
        
    yield "\\.\n"

def create_copy_file(n):

    try:
        os.remove(COPYFILE)
    except OSError:
        pass

    with gzip.open(COPYFILE, "w+") as f:
        for line in copy_file_line(n):
            f.write(line)

# for l in copy_file_line(3):
#     sys.stdout.write(l)
#
# sys.exit()

#### COPY #####

copy_url = "http://%s.carto.com/api/v2/sql/copyfrom" % USERNAME
copy_sql = "COPY copy_test (the_geom, name, age) FROM STDIN WITH (FORMAT text)"
create_copy_file(NROWS)

ah = dict()
ah['content-encoding'] = 'gzip'

with open(COPYFILE, "rb") as f:
    r = requests.post(copy_url, params={'api_key': APIKEY, 'q': copy_sql}, data=f, stream=True, headers=ah)

#r = requests.post(copy_url, params={'api_key': APIKEY, 'q': copy_sql}, data=copy_file_line(NROWS), stream=True)
if r.status_code != 200:
    print(r.text)
else:
    status = r.json()
    print("Success: %s rows imported" % status['total_rows'])