{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# CARTO Python SDK workshop"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Get the credentials"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"All good!\n"
]
}
],
"source": [
"import os\n",
"\n",
"BASEURL = os.environ.get('CARTO_API_URL','https://jsanz.carto.com') # <-- replace with your username or set up the envvar\n",
"APIKEY = os.environ.get('CARTO_API_KEY',False) # <-- replace False with your CARTO API key or set up the envvar\n",
"ORG = os.environ.get('CARTO_ORG')\n",
"\n",
"if BASEURL and APIKEY and ORG:\n",
" print('All good!')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Create an auth key"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from carto.auth import APIKeyAuthClient\n",
"carto_key = APIKeyAuthClient(BASEURL, APIKEY, ORG)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Running queries (SQL API)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from carto.sql import SQLClient\n",
"from carto.exceptions import CartoException\n",
"import pandas as pd\n",
"from IPython.core.display import display, HTML\n",
"\n",
"# SQL client\n",
"sql = SQLClient(carto_key)\n",
"\n",
"# Helper function to render a nice table from a query\n",
"def print_query(query):\n",
" try:\n",
" dic = sql.send(query)\n",
" df = pd.DataFrame(dic['rows'])\n",
" display(HTML(df.to_html()))\n",
" except CartoException as e:\n",
" print(\"some error ocurred\", e)\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's run a geocoding function"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
" \n",
" \n",
" | \n",
" name | \n",
" pop_max | \n",
" wkt | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" Madrid | \n",
" 5567000 | \n",
" POINT(-3.669245 40.429913) | \n",
"
\n",
" \n",
" 1 | \n",
" Barcelona | \n",
" 4920000 | \n",
" POINT(2.159592 41.400347) | \n",
"
\n",
" \n",
" 2 | \n",
" Seville | \n",
" 1212045 | \n",
" POINT(-5.97997 37.383606) | \n",
"
\n",
" \n",
" 3 | \n",
" Bilbao | \n",
" 875552 | \n",
" POINT(-2.937123 43.258534) | \n",
"
\n",
" \n",
" 4 | \n",
" Valencia | \n",
" 808000 | \n",
" POINT(-0.363258 39.464773) | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"print_query('''\n",
" SELECT name, \n",
" pop_max,\n",
" ST_AsText(cdb_geocode_namedplace_point(name,'Spain')) as wkt\n",
" FROM populated_places \n",
" WHERE adm0_a3 = 'ESP' \n",
" ORDER BY pop_max DESC LIMIT 5\n",
"''')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Import a resource"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from carto.file_import import FileImportJob\n",
"import time"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Importing from a local file"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Importing...\n",
"Importing...\n",
"Importing...\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" | \n",
" cartodb_id | \n",
" commuters_16_over_2011_2015 | \n",
" geoid | \n",
" pop_determined_poverty_status_2011_2015 | \n",
" poverty_count | \n",
" poverty_per_pop | \n",
" the_geom | \n",
" the_geom_webmercator | \n",
" total_pop_2011_2015 | \n",
" total_population | \n",
" walked_to_work_2011_2015 | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 59 | \n",
" 7933.785122 | \n",
" 360470760003 | \n",
" 16593.199780 | \n",
" 289.389328 | \n",
" 0.200408 | \n",
" 0106000020E61000000100000001030000000100000008... | \n",
" 0106000020110F00000100000001030000000100000008... | \n",
" 25611.885816 | \n",
" 1444 | \n",
" 0.011213 | \n",
"
\n",
" \n",
" 1 | \n",
" 96 | \n",
" 12422.379729 | \n",
" 360470381001 | \n",
" 23171.502272 | \n",
" 277.517123 | \n",
" 0.234192 | \n",
" 0106000020E61000000100000001030000000100000007... | \n",
" 0106000020110F00000100000001030000000100000007... | \n",
" 31835.053276 | \n",
" 1185 | \n",
" 0.006302 | \n",
"
\n",
" \n",
" 2 | \n",
" 97 | \n",
" 7998.999141 | \n",
" 360470387003 | \n",
" 21176.235374 | \n",
" 104.167832 | \n",
" 0.135459 | \n",
" 0106000020E61000000100000001030000000100000007... | \n",
" 0106000020110F00000100000001030000000100000007... | \n",
" 24021.310491 | \n",
" 769 | \n",
" 0.007314 | \n",
"
\n",
" \n",
" 3 | \n",
" 116 | \n",
" NaN | \n",
" 360470702030 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" None | \n",
" None | \n",
" NaN | \n",
" 0 | \n",
" NaN | \n",
"
\n",
" \n",
" 4 | \n",
" 127 | \n",
" 5464.206106 | \n",
" 360470039002 | \n",
" 15644.554430 | \n",
" 181.884956 | \n",
" 0.360883 | \n",
" 0106000020E61000000100000001030000000100000009... | \n",
" 0106000020110F00000100000001030000000100000009... | \n",
" 10386.507474 | \n",
" 504 | \n",
" 0.016299 | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"fi = FileImportJob(\"brooklyn_poverty.gpkg\",carto_key)\n",
"fi.run()\n",
"while fi.state != 'complete':\n",
" print('Importing...')\n",
" fi.refresh()\n",
" time.sleep(5)\n",
"\n",
"print_query('SELECT * FROM brooklyn_poverty LIMIT 5')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Importing from a URL"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Importing...\n",
"Importing...\n",
"Importing...\n",
"Importing...\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" | \n",
" accident_type | \n",
" cause_subcategory | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" Derailment | \n",
" Wheels | \n",
"
\n",
" \n",
" 1 | \n",
" Derailment | \n",
" Track Geometry | \n",
"
\n",
" \n",
" 2 | \n",
" Derailment | \n",
" Body | \n",
"
\n",
" \n",
" 3 | \n",
" Derailment | \n",
" Brake | \n",
"
\n",
" \n",
" 4 | \n",
" Rearend collision | \n",
" Miscellaneous | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"url = 'https://builder-demo.carto.com/api/v2/sql?q=select%20*%20from%20%22builder-demo%22.railroad_data&format=gpkg&filename=railroad_data'\n",
"\n",
"fi = FileImportJob(url,carto_key)\n",
"fi.run()\n",
"while fi.state != 'complete':\n",
" print('Importing...')\n",
" fi.refresh()\n",
" time.sleep(5)\n",
"\n",
"print_query('SELECT accident_type, cause_subcategory FROM railroad_data LIMIT 5')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Manage datasets"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from carto.datasets import DatasetManager\n",
"import warnings\n",
"warnings.filterwarnings('ignore')\n",
"\n",
"dm = DatasetManager(carto_key)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Get metadata of all datasets"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"95"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"datasets = dm.all()\n",
"len(datasets)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Find datasets by their name"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"railroad_data\n",
"brooklyn_poverty\n"
]
}
],
"source": [
"for dataset in datasets:\n",
" if dataset.name.startswith('railroad') or dataset.name.startswith('brooklyn_poverty'):\n",
" print(dataset.name)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Remove datasets"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Deleting railroad_data...\n",
"Deleting brooklyn_poverty...\n"
]
}
],
"source": [
"for dataset in datasets:\n",
" if dataset.name.startswith('railroad') or dataset.name.startswith('brooklyn_poverty'):\n",
" print(\"Deleting {}...\".format(dataset.name))\n",
" dataset.delete()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Edit dataset metadata, save, restore"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"LINK\n"
]
}
],
"source": [
"places_table = dm.get('populated_places')\n",
"print(places_table.description)\n",
"print(places_table.privacy)"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import carto.permissions\n",
"places_table.description = 'Populated places dataset (desc set from the SDK)'\n",
"places_table.privacy = carto.permissions.PRIVATE\n",
"places_table.save()"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Populated places dataset (desc set from the SDK)\n",
"PRIVATE\n"
]
}
],
"source": [
"places_table = dm.get('populated_places')\n",
"print(places_table.description)\n",
"print(places_table.privacy)"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"places_table.description = ''\n",
"places_table.privacy = carto.permissions.LINK\n",
"places_table.save()"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"LINK\n"
]
}
],
"source": [
"places_table = dm.get('populated_places')\n",
"print(places_table.description)\n",
"print(places_table.privacy)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Batch SQL API\n"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from carto.sql import BatchSQLClient\n",
"batch_client = BatchSQLClient(carto_key)"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" | \n",
" count | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 395633 | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"print_query('SELECT COUNT(*) from flights')"
]
},
{
"cell_type": "code",
"execution_count": 40,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Job a22f74cf-b58f-43ef-bf1a-d18e82924b4a created at 2017-09-14T14:30:00.125Z\n",
"pending\n",
"done\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" | \n",
" count | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 97371 | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"job = batch_client.create([\n",
" '''CREATE TABLE flights_batch AS SELECT * FROM flights TABLESAMPLE SYSTEM(25)''',\n",
" '''SELECT CDB_CartoDBfytable('jsanz','flights_batch')'''\n",
"])\n",
"\n",
"job_id = job['job_id']\n",
"\n",
"print(\"Job {} created at {}\".format(job_id,job['created_at']))\n",
"\n",
"# Let's check the status\n",
"while job['status'] == 'pending':\n",
" print(job['status'])\n",
" time.sleep(5)\n",
" job = batch_client.read(job_id)\n",
"\n",
"print(job['status'])\n",
"\n",
"print_query('SELECT COUNT(*) FROM flights_batch')"
]
},
{
"cell_type": "code",
"execution_count": 53,
"metadata": {},
"outputs": [],
"source": [
"try:\n",
" dm.get('flights_batch').delete()\n",
"except Exception as e:\n",
" print('Something happened!')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Manage maps"
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from carto.visualizations import VisualizationManager\n",
"vm = VisualizationManager(carto_key)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Get all the account maps metadata"
]
},
{
"cell_type": "code",
"execution_count": 43,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"vizs = vm.all()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Show the names of the first 10"
]
},
{
"cell_type": "code",
"execution_count": 44,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Geoinquietos\n",
"Test boundaries aggregation\n",
"torque frown\n",
"Mapping Sax\n",
"Test named map\n",
"NYC Tree Map\n",
"groups\n",
"Cow Map\n",
"Foursquare Checkins\n",
"CARTO team map\n"
]
}
],
"source": [
"for viz in vizs[0:10]:\n",
" print(viz.name)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Show the description from one of them"
]
},
{
"cell_type": "code",
"execution_count": 45,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'This map is updated every day with last Flickr [most interesting photos](https://www.flickr.com/explore).'"
]
},
"execution_count": 45,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"flickr = vm.get('Flickr Great Shots 2016')\n",
"flickr.description"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Get the map download link"
]
},
{
"cell_type": "code",
"execution_count": 46,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'http://s3.amazonaws.com/com.cartodb.imports.production/e7be05bc424f730853dd/Flickr%20Great%20Shots%202016%20%28on%202017-09-14%20at%2014.30.34%29.carto?AWSAccessKeyId=AKIAJUI5EFFJIRZMEEMA&Expires=1505406635&Signature=vHTPqMdoFKYt2GUGX4JrVNCy78Y%3D&response-content-disposition=attachment%3Bfilename%3D%22Flickr%2520Great%2520Shots%25202016%2520%28on%25202017-09-14%2520at%252014.30.34%29.carto%22%3Bfilename%2A%3Dutf-8%27%27Flickr%2520Great%2520Shots%25202016%2520%28on%25202017-09-14%2520at%252014.30.34%29.carto'"
]
},
"execution_count": 46,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"flickr.export()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Named maps\n"
]
},
{
"cell_type": "code",
"execution_count": 48,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from carto.maps import NamedMapManager\n",
"nm = NamedMapManager(carto_key)"
]
},
{
"cell_type": "code",
"execution_count": 49,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"named_maps = nm.all()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Print the custom templates in the account (those that don't start with `tpl`)"
]
},
{
"cell_type": "code",
"execution_count": 50,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"named_map_test_in\n",
"cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom1\n",
"cartoframes_ver20170406_layers2_time0_baseid1_labels0_zoom0\n",
"test_named_map\n",
"populated_places_1491426149_69\n",
"test_literal\n",
"cartoframes_ver20170406_layers2_time0_baseid1_labels0_zoom1\n",
"populated_places_1491425943_37\n",
"cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom0\n",
"populated_places_1491425172_65\n"
]
}
],
"source": [
"for named_map in named_maps:\n",
" template_id = named_map.template_id\n",
" if not template_id.startswith('tpl'):\n",
" print(named_map.template_id)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Print some details from one of the templates"
]
},
{
"cell_type": "code",
"execution_count": 51,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"HTTP Layer\n",
"https://cartodb-basemaps-{s}.global.ssl.fastly.net/light_all/{z}/{x}/{y}.png\n",
"\n",
"CARTO Layer\n",
"SELECT * FROM (SELECT * \\\n",
"FROM nycpluto_all \\\n",
"WHERE ST_Intersects(\\\n",
" ST_Buffer(\\\n",
" ST_SetSRID(\\\n",
" ST_GeomFromText('POINT(-73.988371 40.736)'),\\\n",
" 4326\\\n",
" )::geography,\\\n",
" 1000)::geometry,\\\n",
" the_geom) ) AS wrapped_query WHERE <%= layer0 %>=1\n"
]
}
],
"source": [
"test_named = nm.get('test_named_map')\n",
"for layer in test_named.layergroup['layers']:\n",
" print()\n",
" if layer['type'] == 'http':\n",
" print('HTTP Layer')\n",
" print(layer['options']['urlTemplate'])\n",
" elif layer['type'] == 'cartodb':\n",
" print('CARTO Layer')\n",
" print(layer['options']['sql'])"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.5.2+"
}
},
"nbformat": 4,
"nbformat_minor": 2
}