Skip to content

Instantly share code, notes, and snippets.

@pramsey pramsey/copy-test.py
Created Jun 15, 2018

Embed
What would you like to do?
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'])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.