{ "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namepop_maxwkt
0Madrid5567000POINT(-3.669245 40.429913)
1Barcelona4920000POINT(2.159592 41.400347)
2Seville1212045POINT(-5.97997 37.383606)
3Bilbao875552POINT(-2.937123 43.258534)
4Valencia808000POINT(-0.363258 39.464773)
" ], "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
cartodb_idcommuters_16_over_2011_2015geoidpop_determined_poverty_status_2011_2015poverty_countpoverty_per_popthe_geomthe_geom_webmercatortotal_pop_2011_2015total_populationwalked_to_work_2011_2015
0597933.78512236047076000316593.199780289.3893280.2004080106000020E61000000100000001030000000100000008...0106000020110F00000100000001030000000100000008...25611.88581614440.011213
19612422.37972936047038100123171.502272277.5171230.2341920106000020E61000000100000001030000000100000007...0106000020110F00000100000001030000000100000007...31835.05327611850.006302
2977998.99914136047038700321176.235374104.1678320.1354590106000020E61000000100000001030000000100000007...0106000020110F00000100000001030000000100000007...24021.3104917690.007314
3116NaN360470702030NaNNaNNaNNoneNoneNaN0NaN
41275464.20610636047003900215644.554430181.8849560.3608830106000020E61000000100000001030000000100000009...0106000020110F00000100000001030000000100000009...10386.5074745040.016299
" ], "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
accident_typecause_subcategory
0DerailmentWheels
1DerailmentTrack Geometry
2DerailmentBody
3DerailmentBrake
4Rearend collisionMiscellaneous
" ], "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
0395633
" ], "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
097371
" ], "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 }