sample.py.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Welcome to the CARTO Python SDK workshop!!\n",
    "\n",
    "This is a self-contained tutorial for educational purposes on how to use the CARTO Python SDK to work with the CARTO APIs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## [The Jupyter Notebook](https://jupyter-notebook.readthedocs.io/en/latest/)\n",
    "\n",
    "For this workshop we will use [The Jupyter Notebook](https://jupyter-notebook.readthedocs.io/en/latest/), a web application that allows executing live Python code (among others) along with equations, graphs, explanatory text, etc.\n",
    "\n",
    "You don't need to install anything for the workshop, just play around with **your** notebook.\n",
    "\n",
    "In case you want to install additional libraries, you still can use the `pip` command from your notebook:\n",
    "\n",
    "`!pip install carto==1.0.1`\n",
    "\n",
    "_But be aware that the server is shared among all the attendees to the workshop and bad things may happen._\n",
    "\n",
    "The mechanics of a Jupyter Notebook are very intuitive:\n",
    "\n",
    "- Just click over cells and run them to execute their code to load it into memory.\n",
    "- You have to run each cell sequentially but at any moment you can go to a previous cell and re-run it.\n",
    "- You only can run one cell at a time so if a cell runs forever, stop it and review the code, you may have introduced a `bug`.\n",
    "- Once a cell has been run, double click it to edit it and be able to re-run it.\n",
    "\n",
    "**You can use the shortcut `Shift+Enter` to run the current cell and jump to the next one.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A Markdown cell\n",
    "\n",
    "We are going to use two different types of cells: `Markdown` and `Code`\n",
    "\n",
    "This is a [Markdown](https://daringfireball.net/projects/markdown/syntax) cell and it is for writing text with marks.\n",
    "\n",
    "###### This is an title H6\n",
    "\n",
    "This is a list:\n",
    "\n",
    "*   Red\n",
    "*   Green\n",
    "*   Blue\n",
    "\n",
    "**bolds** _italics_ [links](https://carto.com) and inline images ![images](https://cartodb-libs.global.ssl.fastly.net/cartodb.com/files/img/layout/gallery/trees/thumbnail.b793e4f7.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# This a `Code` cell. Since this is a `Python` notebook you can run only `Python` code\n",
    "\n",
    "print('Hello world!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# <<<<< See the * while running this cell. The * means that the cell is running, be aware when you run cells infinitelly\n",
    "# This is an Infinite loop. The cell will run forever and will make other cells to stop working.\n",
    "# To finish the execution you have to stop this cell manually, by clicking on the cell and then the stop button in the top bar\n",
    "from time import sleep\n",
    "while True:\n",
    "    sleep(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## [CARTO Python SDK documentation](http://carto-python.readthedocs.io/en/latest/)\n",
    "\n",
    "You can find the latest documentation about the `carto-python` library [here](http://carto-python.readthedocs.io/en/latest/), please keep this document open for further reference during the workshop.\n",
    "\n",
    "A part from the docs, the `carto-python` library is free and the source coude is available in [this Github repository](https://github.com/CartoDB/carto-python).\n",
    "\n",
    "If during the workshop you find anything that does not work as expected or you have a suggestion or new feature that is missing, don't be shy and report it [here](https://github.com/CartoDB/carto-python/issues/new), we'll be glad to improve the `carto-python` for your needs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Local [Installation](http://carto-python.readthedocs.io/en/latest/installation.html#)\n",
    "\n",
    "**You don't need to do this for the workshop** but when working on your own projects you should install `carto-python` on your local machine.\n",
    "\n",
    "The recommended Python environment is:\n",
    "\n",
    "- Python 2.7+ or Python 3.0+\n",
    "- [pip](https://pip.pypa.io/en/stable/installing/) as a package manager\n",
    "- (optionally) [virtualenv](https://virtualenv.pypa.io/en/stable/installation/) for isolated development environments\n",
    "\n",
    "How to install `carto-python` locally:\n",
    "\n",
    "- Create a new Python virtual environment (optional):\n",
    "\n",
    "```\n",
    "virtualenv env\n",
    "source env/bin/activate\n",
    "```\n",
    "\n",
    "- Install carto\n",
    "\n",
    "```\n",
    "pip install carto\n",
    "```\n",
    "\n",
    "And you are ready to go!!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quickstart\n",
    "\n",
    "Let's run our first Python script with the Carto Python SDK!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Let's import some system packages\n",
    "import os\n",
    "import sys\n",
    "import logging\n",
    "import warnings\n",
    "from pprint import pprint as _print\n",
    "\n",
    "# We don't want warnings during the workshop (but you should take care of them in production)\n",
    "logging.captureWarnings(True)\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup your user credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# FILL UP YOUR CREDENTIALS!!\n",
    "ORGANIZATION = 'YOUR_ORGANIZATION'\n",
    "USERNAME = 'YOUR_USER_NAME'\n",
    "# WARNING: Don't share your API_KEY, you should always keep it private in your projects.\n",
    "API_KEY = 'YOUR_API_KEY'\n",
    "\n",
    "# We prepare two different URLs, one for the organization (only for CARTO enterprise accounts)\n",
    "BASE_URL = \"https://{organization}.carto.com/user/{user}/\". \\\n",
    "    format(organization=ORGANIZATION,\n",
    "           user=USERNAME)\n",
    "    \n",
    "# And one for the user. Usually on non-enterprise accounts you'll use this. \n",
    "USR_BASE_URL = \"https://{user}.carto.com/\".format(user=USERNAME)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Let's import the classes we are using\n",
    "from carto.auth import APIKeyAuthClient\n",
    "from carto.exceptions import CartoException\n",
    "from carto.sql import SQLClient\n",
    "\n",
    "# Requests are authenticated, so we create a new APIKeyAuthClient with your credentials\n",
    "auth_client = APIKeyAuthClient(USR_BASE_URL, API_KEY)\n",
    "\n",
    "# SQLClient is used to send SQL queries\n",
    "sql = SQLClient(auth_client)\n",
    "\n",
    "# let's send a simple SQL query\n",
    "try:\n",
    "    data = sql.send('select version()')\n",
    "except CartoException as e:\n",
    "    print(\"some error ocurred\", e)\n",
    "\n",
    "# now we have the data object, let's see what it has\n",
    "print data['rows']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exceptions\n",
    "\n",
    "All errors while using the `carto-python` library are wrapped into a `CartoException` object. Always use `try-except` clauses when calling to `carto-python` functions.\n",
    "\n",
    "Let's write a wrong SQL query, that will throw an error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# A wrong SQL query throws a CartoException\n",
    "try:\n",
    "    data = sql.send('select version_fuckkk()')\n",
    "except CartoException as e:\n",
    "    print(\"some error ocurred\", e.message)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CARTO APIs\n",
    "\n",
    "The CARTO Python SDK implements the official CARTO APIs.\n",
    "\n",
    "- [Import API](https://carto.com/docs/carto-engine/import-api)\n",
    "- [SQL API](https://carto.com/docs/carto-engine/sql-api)\n",
    "- [Maps API](https://carto.com/docs/carto-engine/maps-api)\n",
    "\n",
    "Additionally, you have access to other [non-public APIs](http://carto-python.readthedocs.io/en/latest/non_public_apis.html) but be aware that those APIs may change without prior notice, so its usage is not recommended\n",
    "\n",
    "Let's walk through the different APIs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import API\n",
    "\n",
    "You can import local or remote datasets into CARTO via the Import API in several ways."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### DatasetManager\n",
    "\n",
    "The `DatasetManager` allows an organization-enabled user to manage `datasets`. A `dataset` in CARTO means there is a table in the PostgreSQL server associated to this object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.datasets import DatasetManager\n",
    "from carto.auth import APIKeyAuthClient\n",
    "from carto.exceptions import CartoException\n",
    "\n",
    "# write here the path to a local file (local to the server...) or remote URL\n",
    "LOCAL_FILE_OR_URL = \"https://academy.cartodb.com/d/tornadoes.zip\"\n",
    "\n",
    "# to use the DatasetManager you need an enterprise account\n",
    "auth_client = APIKeyAuthClient(BASE_URL, API_KEY)\n",
    "\n",
    "dataset_manager = DatasetManager(auth_client)\n",
    "dataset_manager.create(LOCAL_FILE_OR_URL)\n",
    "\n",
    "# the create method will wait up to 10 minutes until the dataset is uploaded.\n",
    "# wait some seconds while the dataset is being uploaded..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### FileImport"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.file_import import FileImportJobManager\n",
    "import time\n",
    "\n",
    "# write here the URL for the dataset or the path to a local file (local to the server...)\n",
    "LOCAL_FILE_OR_URL = \"https://academy.cartodb.com/d/tornadoes.zip\"\n",
    "\n",
    "file_import_manager = FileImportJobManager(auth_client)\n",
    "file_import = file_import_manager.create(LOCAL_FILE_OR_URL)\n",
    "\n",
    "# return the id of the import\n",
    "file_id = file_import.get_id()\n",
    "\n",
    "file_import.run()\n",
    "while(file_import.state != \"complete\" and file_import.state != \"created\"\n",
    "            and file_import.state != \"success\"):\n",
    "    time.sleep(5)\n",
    "    file_import.refresh()\n",
    "    if (file_import.state == 'failure'):\n",
    "        print('The error code is: ' + str(file_import))\n",
    "        break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List current file import jobs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.file_import import FileImportJobManager\n",
    "\n",
    "file_import_manager = FileImportJobManager(auth_client)\n",
    "file_imports = file_import_manager.all()\n",
    "print(file_imports)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List all datasets (this requires an organization-enabled user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.datasets import DatasetManager\n",
    "\n",
    "dataset_manager = DatasetManager(auth_client)\n",
    "datasets = dataset_manager.all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Work with the dataset object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.datasets import DatasetManager\n",
    "\n",
    "# write here the ID of the dataset to retrieve\n",
    "DATASET_ID = \"tornados\"\n",
    "\n",
    "dataset_manager = DatasetManager(auth_client)\n",
    "dataset = dataset_manager.get(DATASET_ID)\n",
    "dataset.privacy = PUBLIC\n",
    "dataset.save()\n",
    "\n",
    "dataset.delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sync datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.datasets import DatasetManager\n",
    "\n",
    "# how often to sync the dataset (in seconds)\n",
    "SYNC_TIME = 900\n",
    "# write here the URL for the dataset to sync\n",
    "URL_TO_DATASET = \"https://academy.cartodb.com/d/tornadoes.zip\"\n",
    "\n",
    "dataset_manager = DatasetManager(auth_client)\n",
    "dataset = dataset_manager.create(URL_TO_DATASET, SYNC_TIME)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.sync_tables import SyncTableJobManager\n",
    "import time\n",
    "\n",
    "# how often to sync the dataset (in seconds)\n",
    "SYNC_TIME = 900\n",
    "# write here the URL for the dataset to sync\n",
    "URL_TO_DATASET = \"https://academy.cartodb.com/d/tornadoes.zip\"\n",
    "\n",
    "sync_table_manager = SyncTableJobManager(auth_client)\n",
    "sync_table = sync_table_manager.create(URL_TO_DATASET, SYNC_TIME)\n",
    "\n",
    "# return the id of the sync\n",
    "sync_id = sync_table.get_id()\n",
    "sync_table.run()\n",
    "while(sync_table.state != \"complete\" and sync_table.state != \"created\"\n",
    "            and sync_table.state != \"success\"):\n",
    "    time.sleep(5)\n",
    "    sync_table.refresh()\n",
    "    if (syncTable.state == 'failure'):\n",
    "        print('The error code is: ' + str(sync_table.error_code))\n",
    "        print('The error message is: ' + str(sync_table.error_message))\n",
    "        break\n",
    "\n",
    "# force sync\n",
    "sync_table.refresh()\n",
    "sync_table.force_sync()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.sql import SQLClient\n",
    "from carto.exceptions import CartoException\n",
    "\n",
    "# Requests are authenticated, so we create a new APIKeyAuthClient with your credentials\n",
    "auth_client = APIKeyAuthClient(USR_BASE_URL, API_KEY)\n",
    "\n",
    "sql = SQLClient(auth_client)\n",
    "\n",
    "try:\n",
    "    data = sql.send('select * from tornados limit 10;')\n",
    "except CartoException as e:\n",
    "    print(\"some error ocurred\", e)\n",
    "\n",
    "_print(data['rows'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Export as CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = \"select * from tornados limit 10\"\n",
    "result = sql.send(query, format='csv')\n",
    "\n",
    "print(result)\n",
    "\n",
    "filename = \"tornados_\" + USERNAME + \".csv\"\n",
    "with open(filename, 'w') as f:\n",
    "    f.write(result)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# play a bit with the SQL API\n",
    "# add here other SQL queries, try updating, inserting or deleting rows\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Batch SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.sql import BatchSQLClient\n",
    "\n",
    "LIST_OF_SQL_QUERIES = [__WRITE_HERE_LONG_UPDATES_OR_INSERTS__]\n",
    "\n",
    "batchSQLClient = BatchSQLClient(auth_client)\n",
    "createJob = batchSQLClient.create(LIST_OF_SQL_QUERIES)\n",
    "\n",
    "print(createJob['job_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "readJob = batchSQLClient.read(createJob['job_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(readJob)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Maps API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "EXISTING_POINT_DATASET=\"tornados\"\n",
    "NAMED_MAP_AUTH_TOKEN = \"ugfhfdyt5re8treu\"\n",
    "NAMED_MAP_DEFINITION = {\n",
    "    \"version\": \"0.0.1\",\n",
    "    \"name\": \"python_sdk_test_map\",\n",
    "    \"auth\": {\n",
    "        \"method\": \"token\",\n",
    "        \"valid_tokens\": [\n",
    "            NAMED_MAP_AUTH_TOKEN\n",
    "        ]\n",
    "    },\n",
    "    \"placeholders\": {\n",
    "        \"color\": {\n",
    "            \"type\": \"css_color\",\n",
    "            \"default\": \"red\"\n",
    "        }\n",
    "    },\n",
    "    \"layergroup\": {\n",
    "        \"version\": \"1.0.1\",\n",
    "        \"layers\": [\n",
    "            {\n",
    "                \"type\": \"cartodb\",\n",
    "                \"options\": {\n",
    "                    \"cartocss_version\": \"2.1.1\",\n",
    "                    \"cartocss\": \"#layer { marker-fill: <%= color %>; }\",\n",
    "                    \"sql\": \"select * from {dataset}\".format(dataset=EXISTING_POINT_DATASET)\n",
    "                }\n",
    "            }\n",
    "        ]\n",
    "    },\n",
    "    \"view\": {\n",
    "        \"zoom\": 4,\n",
    "        \"center\": {\n",
    "            \"lng\": 0,\n",
    "            \"lat\": 0\n",
    "        },\n",
    "        \"bounds\": {\n",
    "            \"west\": -45,\n",
    "            \"south\": -45,\n",
    "            \"east\": 45,\n",
    "            \"north\": 45\n",
    "        }\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.maps import NamedMapManager, NamedMap\n",
    "import json\n",
    "\n",
    "named_map_manager = NamedMapManager(auth_client)\n",
    "named_map = NamedMap(named_map_manager.client)\n",
    "\n",
    "# Create named map\n",
    "named = named_map_manager.create(template=NAMED_MAP_DEFINITION)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(named)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.maps import NamedMapManager, NamedMap\n",
    "import json\n",
    "\n",
    "# write the path to a local file with a JSON named map template\n",
    "JSON_TEMPLATE = { \n",
    "    \"color\": \"#ff6600\" \n",
    "}\n",
    "\n",
    "# write here the ID of the named map\n",
    "NAMED_MAP_ID = \"python_sdk_test_map\"\n",
    "\n",
    "# write here the token you set to the named map when created\n",
    "NAMED_MAP_TOKEN = \"ugfhfdyt5re8treu\"\n",
    "\n",
    "named_map_manager = NamedMapManager(auth_client)\n",
    "named_map = named_map_manager.get(NAMED_MAP_ID)\n",
    "\n",
    "named_map.instantiate(JSON_TEMPLATE, NAMED_MAP_TOKEN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tile = named_map.get_tile_url(0, 0, 0, layer_id=\"all\")\n",
    "print(tile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from carto.maps import NamedMapManager, NamedMap\n",
    "\n",
    "# write here the ID of the named map\n",
    "NAMED_MAP_ID = \"python_sdk_test_map\"\n",
    "\n",
    "# get the named map created\n",
    "named_map = named_map_manager.get(NAMED_MAP_ID)\n",
    "\n",
    "# update named map\n",
    "named_map.view = None\n",
    "named_map.save()\n",
    "\n",
    "# delete named map\n",
    "#named_map.delete()\n",
    "\n",
    "# list all named maps\n",
    "named_maps = named_map_manager.all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examples\n",
    "\n",
    "Try by yourself some of the `carto-python` [examples](https://github.com/CartoDB/carto-python/tree/master/examples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}