import os
BASEURL = os.environ.get('CARTO_API_URL','https://jsanz.carto.com') # <-- replace with your username or set up the envvar
APIKEY = os.environ.get('CARTO_API_KEY',False) # <-- replace False with your CARTO API key or set up the envvar
ORG = os.environ.get('CARTO_ORG')
if BASEURL and APIKEY and ORG:
print('All good!')
from carto.auth import APIKeyAuthClient
carto_key = APIKeyAuthClient(BASEURL, APIKEY, ORG)
from carto.sql import SQLClient
from carto.exceptions import CartoException
import pandas as pd
from IPython.core.display import display, HTML
# SQL client
sql = SQLClient(carto_key)
# Helper function to render a nice table from a query
def print_query(query):
try:
dic = sql.send(query)
df = pd.DataFrame(dic['rows'])
display(HTML(df.to_html()))
except CartoException as e:
print("some error ocurred", e)
Let's run a geocoding function
print_query('''
SELECT name,
pop_max,
ST_AsText(cdb_geocode_namedplace_point(name,'Spain')) as wkt
FROM populated_places
WHERE adm0_a3 = 'ESP'
ORDER BY pop_max DESC LIMIT 5
''')
from carto.file_import import FileImportJob
import time
Importing from a local file
fi = FileImportJob("brooklyn_poverty.gpkg",carto_key)
fi.run()
while fi.state != 'complete':
print('Importing...')
fi.refresh()
time.sleep(5)
print_query('SELECT * FROM brooklyn_poverty LIMIT 5')
Importing from a URL
url = 'https://builder-demo.carto.com/api/v2/sql?q=select%20*%20from%20%22builder-demo%22.railroad_data&format=gpkg&filename=railroad_data'
fi = FileImportJob(url,carto_key)
fi.run()
while fi.state != 'complete':
print('Importing...')
fi.refresh()
time.sleep(5)
print_query('SELECT accident_type, cause_subcategory FROM railroad_data LIMIT 5')
from carto.datasets import DatasetManager
import warnings
warnings.filterwarnings('ignore')
dm = DatasetManager(carto_key)
Get metadata of all datasets
datasets = dm.all()
len(datasets)
Find datasets by their name
for dataset in datasets:
if dataset.name.startswith('railroad') or dataset.name.startswith('brooklyn_poverty'):
print(dataset.name)
Remove datasets
for dataset in datasets:
if dataset.name.startswith('railroad') or dataset.name.startswith('brooklyn_poverty'):
print("Deleting {}...".format(dataset.name))
dataset.delete()
Edit dataset metadata, save, restore
places_table = dm.get('populated_places')
print(places_table.description)
print(places_table.privacy)
import carto.permissions
places_table.description = 'Populated places dataset (desc set from the SDK)'
places_table.privacy = carto.permissions.PRIVATE
places_table.save()
places_table = dm.get('populated_places')
print(places_table.description)
print(places_table.privacy)
places_table.description = ''
places_table.privacy = carto.permissions.LINK
places_table.save()
places_table = dm.get('populated_places')
print(places_table.description)
print(places_table.privacy)
from carto.sql import BatchSQLClient
batch_client = BatchSQLClient(carto_key)
print_query('SELECT COUNT(*) from flights')
job = batch_client.create([
'''CREATE TABLE flights_batch AS SELECT * FROM flights TABLESAMPLE SYSTEM(25)''',
'''SELECT CDB_CartoDBfytable('jsanz','flights_batch')'''
])
job_id = job['job_id']
print("Job {} created at {}".format(job_id,job['created_at']))
# Let's check the status
while job['status'] == 'pending':
print(job['status'])
time.sleep(5)
job = batch_client.read(job_id)
print(job['status'])
print_query('SELECT COUNT(*) FROM flights_batch')
try:
dm.get('flights_batch').delete()
except Exception as e:
print('Something happened!')
from carto.visualizations import VisualizationManager
vm = VisualizationManager(carto_key)
Get all the account maps metadata
vizs = vm.all()
Show the names of the first 10
for viz in vizs[0:10]:
print(viz.name)
Show the description from one of them
flickr = vm.get('Flickr Great Shots 2016')
flickr.description
Get the map download link
flickr.export()
from carto.maps import NamedMapManager
nm = NamedMapManager(carto_key)
named_maps = nm.all()
Print the custom templates in the account (those that don't start with tpl)
for named_map in named_maps:
template_id = named_map.template_id
if not template_id.startswith('tpl'):
print(named_map.template_id)
Print some details from one of the templates
test_named = nm.get('test_named_map')
for layer in test_named.layergroup['layers']:
print()
if layer['type'] == 'http':
print('HTTP Layer')
print(layer['options']['urlTemplate'])
elif layer['type'] == 'cartodb':
print('CARTO Layer')
print(layer['options']['sql'])