Python SDK - Examples

Import from database

Import data into your CARTO account from database.

import argparse
import json
import logging
import os
import re
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager

warnings.filterwarnings('ignore')

# python import_from_database.py --connection='{ \
#   "connector": { \
#     "provider": "hive", \
#     "connection": { \
#       "server":"YOUR_SERVER_IP", \
#       "database":"default", \
#       "username":"cloudera", \
#       "password":"cloudera" \
#     },
#     "schema": "default", \
#     "table": "order_items" \
#   }
# }'

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='External database connector')

parser.add_argument('--connection', type=str, dest='connection',
                    help='An external database connection JSON object')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# get username from base_url
substring = re.search('https://(.+?).carto.com', args.CARTO_BASE_URL)
if substring:
    username = substring.group(1)

# Dataset manager
dataset_manager = DatasetManager(auth_client)

connection = json.loads(args.connection.replace("\\", ""))
logger.info(connection)

table = dataset_manager.create(None, None, connection=connection)
logger.info(
    'Table imported: {table}'.format(table=table.name))

Import standard table

Import standard table into your CARTO account.

import argparse
import logging
import os
import re
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager

warnings.filterwarnings('ignore')

# python import_standard_table.py files/barris_barcelona_1_part_1.csv

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Create a table from a URL')

parser.add_argument('url', type=str,
                    help='Set the URL of data to load.' +
                    ' Add it in double quotes')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# get username from base_url
substring = re.search('https://(.+?).carto.com', args.CARTO_BASE_URL)
if substring:
    username = substring.group(1)

# imports the file to CARTO
dataset_manager = DatasetManager(auth_client)
table = dataset_manager.create(args.url)
logger.info('Name of table: ' + str(table.name))
print('URL of dataset: \
      https://{org}.carto.com/u/{username}/dataset/{data}'). \
      format(org=args.organization,
             username=username,
             data=str(table.name))

Import sync table

Import sync table into your CARTO account from database.

import argparse
import logging
import os
import time
import warnings

from carto.auth import APIKeyAuthClient
from carto.sync_tables import SyncTableJobManager

warnings.filterwarnings('ignore')

# python import_sync_table.py "DATASET_URL" 900

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Create a sync table from a URL')

parser.add_argument('url', type=str,
                    help='Set the URL of data to sync.' +
                    ' Add it in double quotes')

parser.add_argument('sync_time', type=int,
                    help='Set the time to sync your' +
                    ' table in seconds (min: 900s)')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL']  if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    syncTableManager = SyncTableJobManager(auth_client)
    syncTable = syncTableManager.create(args.url, args.sync_time)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# return the id of the sync
logging.debug((syncTable.get_id()))

while(syncTable.state != 'success'):
    time.sleep(5)
    syncTable.refresh()
    logging.debug(syncTable.state)
    if (syncTable.state == 'failure'):
        logging.warn('The error code is: ' + str(syncTable.error_code))
        logging.warn('The error message is: ' + str(syncTable.error_message))
        break

# force sync
syncTable.refresh()
syncTable.force_sync()

logging.debug(syncTable.state)

Import sync table as dataset

Import sync table as dataset into your CARTO account.

import argparse
import logging
import os
import re
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager

warnings.filterwarnings('ignore')

# python import_sync_table_as_dataset.py "DATASET_URL" 900

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Create a sync table from a URL')

parser.add_argument('url', type=str,
                    help='Set the URL of data to sync.' +
                    ' Add it in double quotes')

parser.add_argument('sync_time', type=int,
                    help='Set the time to sync your' +
                    ' table in seconds (min: 900s)')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    dataset_manager = DatasetManager(auth_client)
    table = dataset_manager.create(args.url, args.sync_time)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# get username from base_url
substring = re.search('https://(.+?).carto.com', args.CARTO_BASE_URL)
if substring:
    username = substring.group(1)

# return the id of the sync
logger.info('Name of table: ' + str(table.name))
print('\nURL of dataset is: \
      https://{org}.carto.com/u/{username}/dataset/{data}'). \
      format(org=args.organization,
             username=username,
             data=str(table.name))

Import and merge several datasets

Import a folder with CSV files (same structure) and merge them into one dataset.

import argparse
import glob
import logging
import os
import re
import time
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# python import_and_merge.py "files/*.csv"

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Import a folder with CSV files (same structure) and merge \
    them into one dataset')

parser.add_argument('folder_name', type=str,
                    help='Set the name of the folder where' +
                    ' you store your files and' +
                    ' the format of the files, for example:' +
                    ' "files/*.csv"')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# get username from base_url
substring = re.search('https://(.+?).carto.com', args.CARTO_BASE_URL)
if substring:
    username = substring.group(1)

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

# Dataset manager
dataset_manager = DatasetManager(auth_client)

# define path of the files
path = os.getcwd()

file_folder = glob.glob(path + '/' + args.folder_name)

# import files from the path to CARTO
table_name = []

for i in file_folder:
    table = dataset_manager.create(i)
    logger.info(
        'Table imported: {table}'.format(table=table.name))
    table_name.append(table.name)

# define base table to insert all rows from other files
base_table = table_name[0]

# select all rows from table except cartodb_id to avoid possible errors
columns_table = "select string_agg(column_name,',')" + \
    " FROM information_schema.columns" + \
    " where table_schema = '" + username + "' and table_name = '" + \
    str(table_name[0]) + "' AND column_name <> 'cartodb_id'"


result_query = sql.send(columns_table)

for k, v in result_query.items():
    if k == 'rows':
        for itr in v:
            dict_col = itr

logging.debug(dict_col['string_agg'])

# apply operation INSERT INTO SELECT with columns from previous query
index = 1
for i in table_name:

    if i == base_table:

        continue
    elif i != base_table and index <= len(table_name):

        query = "insert into " + base_table + \
            "(" + dict_col['string_agg'] + ") select " + \
            dict_col['string_agg'] + " from " + table_name[index] + ";"
        sql.send(query)
        time.sleep(2)

    else:
        break
    index = index + 1

# change name of base table
myTable = dataset_manager.get(base_table)
myTable.name = base_table + "_merged"
myTable.save()
time.sleep(2)

# remove not merged datasets
for i in table_name:
    try:
        myTable = dataset_manager.get(i)
        myTable.delete()
        time.sleep(2)
    except:
        continue

logger.info('Tables merged')
print('\nURL of dataset is: \
      https://{org}.carto.com/u/{username}/dataset/{data}'). \
      format(org=args.organization,
             username=username,
             data=(base_table + "_merged"))

Export dataset

Export a dataset from your CARTO account.

import argparse
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Exports a dataset')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG']  if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

parser.add_argument('--dataset', dest='DATASET',
                    help='The name of the dataset')

parser.add_argument('--format', dest='EXPORT_FORMAT',
                    default='csv',
                    help='The format of the file to be exported. ' +
                    'Default is `csv`')

args = parser.parse_args()


# Authenticate to CARTO account
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

query = "select * from " + args.DATASET + ""
result = sql.send(query, format=args.EXPORT_FORMAT)

filename = args.DATASET + "." + args.EXPORT_FORMAT
with open(filename, 'w') as f:
    f.write(result)
f.close()

print("File saved: " + filename)

Export map

Export a map from your CARTO account.

import argparse
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.visualizations import VisualizationManager

warnings.filterwarnings('ignore')

# python export_map.py "results_1ertour_2012_1 map"

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Export a visualization')

parser.add_argument('map_name', type=str,
                    help='The name of the map in CARTO')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    visualization = VisualizationManager(auth_client).get(args.map_name)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

url = visualization.export()

print('\nURL of .carto file is: ' + (url))

Export all datasets

Export all datasets from your CARTO account.

import argparse
import logging
import os
import warnings
from pathlib import Path

from carto.datasets import DatasetManager
from carto.auth import APIKeyAuthClient
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Exports a dataset')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG']  if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

parser.add_argument('--format', dest='EXPORT_FORMAT',
                    default='gpkg',
                    help='The format of the file to be exported. ' +
                    'Default is `gpkg`')

parser.add_argument('--save_folder', dest='SAVE_FOLDER',
                    default='.',
                    help='The folder path to download the datasets, by default is the path where the script is executes')

args = parser.parse_args()


# Authenticate to CARTO account
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

# Dataset manager
dataset_manager = DatasetManager(auth_client)

# Get all datasets from account
datasets = dataset_manager.all()

# donwload datasets from account
for tablename in datasets:
    query = 'SELECT * FROM {table_name}'.format(table_name=tablename.name) 
    try:
        result = sql.send(query, format=args.EXPORT_FORMAT)
    except Exception as e:
        logger.error(str(e))
        break
    data_folder = Path(args.SAVE_FOLDER) / "{table_name}.{format}".format(table_name=tablename.name,format=args.EXPORT_FORMAT)
    # write file to files folder
    try:
        data_folder.write_bytes(result)
    except Exception as e:
        logger.error(str(e))
        break

Export all maps

Export all maps from your CARTO account.

import argparse
import logging
import os
import warnings
import time
from pathlib import Path
import requests
# we are adding the tqdm module to create a progress bar
from tqdm import tqdm

from carto.auth import APIKeyAuthClient
from carto.visualizations import VisualizationManager

warnings.filterwarnings('ignore')

logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Exports all maps')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ.get('CARTO_ORG'),
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ.get('CARTO_BASE_URL'),
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_BASE_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ.get('CARTO_API_KEY'),
                    help='Api key of the CARTO account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()


# Authenticate to CARTO account
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
else:
    logger.error(
        '''You need to provide valid credentials,
            run with -h parameter for details''')
    import sys
    sys.exit(1)

# create output folder if it doesn't exist
if not os.path.exists('output'):
    logger.info('Creating output folder to store results')
    os.makedirs('output')

# initialize VisualizationManager manager
vis_manager = VisualizationManager(auth_client)

logger.info('Retrieving map data from {base_url}'.format(base_url=args.CARTO_BASE_URL))
# Get all maps from account
maps = vis_manager.all()

logger.info('Downloading {maps} maps'.format(maps=len(maps)))

current_path = Path.cwd()
logger.info('Data will be downloaded into {current_path}/output'.format(current_path=current_path))
# iterate over each map
'''
    The tqdm module is not needed and we could iterate directly over the maps array.
    However, we added this module to have a nicer way to see the download progress.
'''
for viz in tqdm(maps):
    # Get map object using map name
    map_obj = vis_manager.get(viz.name)
    try:
        # get URL to export map
        url = map_obj.export()
    except Exception as e:
        logger.error(str(e))
        continue
    
    logger.debug(url)
    # make request to the export URL
    r = requests.get(url)
    data_path = current_path / 'output' / "{viz_name}.carto".format(viz_name=viz.name)
    # write download data into a file
    data_path.write_bytes(r.content)

Create anonymous map

Create an anonymous map from your CARTO account.

import argparse
import json
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.maps import AnonymousMap

warnings.filterwarnings('ignore')

# python create_anonymous_map.py "files/anonymous_map.json"

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Creates an anonymous map')

parser.add_argument('anonymous_map_json', type=str,
                    help='Path to the anonymous map JSON description file')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY']  if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    anonymous = AnonymousMap(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)


with open(args.anonymous_map_json) as anonymous_map_json:
    template = json.load(anonymous_map_json)

# Create anonymous map
anonymous.instantiate(template)

print('Anonymous map created with layergroupid: ' + anonymous.layergroupid)

Create named map

Create a named map into your CARTO account.

import argparse
import json
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.maps import NamedMapManager, NamedMap

warnings.filterwarnings('ignore')

# python create_named_map.py "files/named_map.json"

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Creates a named map')

parser.add_argument('named_map_json', type=str,
                    help='Path to the named map JSON description file')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    named_map_manager = NamedMapManager(auth_client)
    n = NamedMap(named_map_manager.client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)


with open(args.named_map_json) as named_map_json:
    template = json.load(named_map_json)

# Create named map
named = named_map_manager.create(template=template)

print('Named map created with ID: ' + named.template_id)

Instantiate named map

Instantiate a named map from your CARTO account.

import argparse
import json
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.maps import NamedMapManager

warnings.filterwarnings('ignore')

# python instantiate_named_map.py "python_sdk_test_map" \
# "files/instantiate_map.json" "example_token"

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Instantiates a named map')

parser.add_argument('named_map_id', type=str,
                    help='The ID of a previously created named map')

parser.add_argument('template_json', type=str,
                    help='JSON Template file to instantiate the map with')

parser.add_argument('named_map_token', type=str,
                    help='A valid token set when the named map was created')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG']  if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    named_map_manager = NamedMapManager(auth_client)
    named_map = named_map_manager.get(args.named_map_id)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

with open(args.template_json) as template_json:
    template = json.load(template_json)

named_map.instantiate(template, args.named_map_token)

print('Done!')

Display information on maps

Return the names of all maps or display information from a specific map.

import argparse
import logging
import os
import pprint
import urllib
import warnings

from carto.auth import APIKeyAuthClient
from carto.visualizations import VisualizationManager

warnings.filterwarnings('ignore')
printer = pprint.PrettyPrinter(indent=4)

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Return the names of all maps or' +
    ' display information from a specific map')

parser.add_argument('--map', type=str, dest='map_name',
                    default=None,
                    help='Set the name of the map to explore and display its \
                    information on the console')

parser.add_argument('--export', type=str, dest='export_map',
                    default=None,
                    help='Set the name of the map and export it')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL']  if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    visualization_manager = VisualizationManager(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)



# Render map info or the name of the maps
if args.map_name is None and args.export_map is None:
    for a_map in visualization_manager.all():
        logging.info(a_map.name)
elif args.map_name is not None and args.export_map is None:
    mapa = visualization_manager.get(args.map_name)
    printer.pprint(mapa.__dict__)
    printer.pprint(mapa.table.__dict__)
elif args.map_name is None and args.export_map is not None:
    mapa_exp = visualization_manager.get(args.export_map)
    current_path = os.getcwd()
    logger.info('Map will be downloaded at {}'.format(current_path))
    f = open(current_path+'/downloaded_file.carto', 'wb')
    f.write(urllib.urlopen(mapa_exp.export()).read())
    f.close()
    logger.info('Map downloaded')
else:
    mapa = visualization_manager.get(args.map_name)
    printer.pprint(mapa.__dict__)
    printer.pprint(mapa.table.__dict__)
    mapa_exp = visualization_manager.get(args.export_map)
    current_path = os.getcwd()
    logger.info('Map will be downloaded at {}'.format(current_path))
    f = open(current_path+'/downloaded_file.carto', 'wb')
    f.write(urllib.urlopen(mapa_exp.export()).read())
    f.close()
    logger.info('Map downloaded')

Change privacy of dataset

Change the privacy of a dataset in your account.

import argparse
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager

warnings.filterwarnings('ignore')

# python change_dataset_privacy.py tornados LINK

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Changes the privacy of a dataset')

parser.add_argument('dataset_name', type=str,
                    help='The name of the dataset in CARTO')

parser.add_argument('privacy', type=str,
                    help='One of: LINK, PUBLIC, PRIVATE')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    dataset_manager = DatasetManager(auth_client)
    dataset = dataset_manager.get(args.dataset_name)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# PRIVATE, PUBLIC, LINK
dataset.privacy = args.privacy
dataset.save()

logger.info("Done!")

Check query optimization

Check if query can be easily optimized.

import argparse
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# python check_query.py "select version()"

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Check if query can be optimized')
parser.add_argument('queryUser', type=str,
                    help='Set query to analyze')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Authenticate to CARTO account
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    dataset_manager = DatasetManager(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

query = sql.send('EXPLAIN ANALYZE ' + args.queryUser)

for key, value in query.items():
    if key == 'rows':
        for itr in value:
            logger.info(itr)
    if key == 'time':
        logger.info(str(key) + ': ' + str(value))

query_arr = args.queryUser.upper().split()


for i in query_arr:
    if i == '*':
        logger.warn('Do you need all columns? ' +
                    'You can improve the performance ' +
                    'by only selecting the needed ' +
                    'columns instead of doing a \"SELECT *\" statement')
    if i == 'WHERE':
        logger.warn('Have you applied indexes on the columns ' +
                    'that you use after the WHERE statement?')
    if i == 'the_geom' or i == 'the_geom_webmercator':
        logger.warn('If the geometry is a polygon,' +
                    ' have you simplified the geometries ' +
                    'with the ST_Simplify() function?')

Export scripts to create tables

Exports the CREATE TABLE scripts of all the account datasets.

import argparse
import os
import re
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# set input arguments
parser = argparse.ArgumentParser(
    description='Exports the CREATE TABLE scripts of all the account datasets')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Authenticate to CARTO account
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    dataset_manager = DatasetManager(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

# get username from base_url
substring = re.search('https://(.+?).carto.com', args.CARTO_BASE_URL)
if substring:
    username = substring.group(1)

# check all table name of account
all_tables = []

tables = sql.send(
    "select pg_class.relname from pg_class, pg_roles, pg_namespace" +
    " where pg_roles.oid = pg_class.relowner and " +
    "pg_roles.rolname = current_user " +
    "and pg_namespace.oid = pg_class.relnamespace and pg_class.relkind = 'r'")

q = "select \
  'CREATE TABLE ' || relname || E'\n(\n' || \
  array_to_string( \
    array_agg( \
      '    ' || column_name || ' ' ||  type || ' '|| not_null \
    ) \
    , E',\n' \
  ) || E'\n);\n' as create_table \
from \
( \
  select  \
    distinct on (column_name) c.relname, a.attname AS column_name, \
    pg_catalog.format_type(a.atttypid, a.atttypmod) as type, \
    case  \
      when a.attnotnull \
    then 'NOT NULL'  \
    else 'NULL'  \
    END as not_null  \
  FROM pg_class c, \
   pg_attribute a, \
   pg_type t \
   WHERE c.relname = '{table_name}' \
   AND a.attnum > 0 \
   AND a.attrelid = c.oid \
   AND a.atttypid = t.oid \
   and a.attname not in ('cartodb_id', 'the_geom_webmercator') \
 ORDER BY column_name, a.attnum \
) as tabledefinition \
group by relname"

with open('create_table.sql', 'w') as f:
    for k, v in tables.items():
        if k == 'rows':
            for itr in v:
                try:
                    dataset_name = itr['relname']
                    print("Found dataset: " + dataset_name)
                    result = sql.send(q.format(table_name=dataset_name))
                    create_table = result['rows'][0]['create_table']
                    f.write(create_table + "\n")
                except:
                    print("Error while exporting: " + dataset_name)
                    continue
f.close()

print('\nScript exported')

Kill query

Kill a running database query.

import argparse
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Kills a running query')

parser.add_argument('pid', type=str,
                    default=None,
                    help='Set the pid of the query to kill')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL']  if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

queries = "SELECT pg_cancel_backend('" + args.pid + \
    "') from pg_stat_activity where usename=current_user;"

try:
    sql.send(queries)
    logger.info('Query killed')
except:
    logger.warn('Something went wrong')

List tables

Return graph of tables ordered by size and indicating if they are cartodbfied or not.

import argparse
import os
import re
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# set input arguments
parser = argparse.ArgumentParser(
    description='Return graph of tables ordered by size' +
    ' and indicating if they are cartodbfied or not')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Authenticate to CARTO account
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    dataset_manager = DatasetManager(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

# get username from base_url
substring = re.search('https://(.+?).carto.com', args.CARTO_BASE_URL)
if substring:
    username = substring.group(1)

# check all table name of account
all_tables = []

tables = sql.send(
    "select pg_class.relname from pg_class, pg_roles, pg_namespace" +
    " where pg_roles.oid = pg_class.relowner and " +
    "pg_roles.rolname = current_user " +
    "and pg_namespace.oid = pg_class.relnamespace and pg_class.relkind = 'r'")

for k, v in tables.items():
    if k == 'rows':
        for itr in v:
            all_tables.append(itr['relname'])


# define array to store all the table sizes
arr_size = []


# create array with values of the table sizes
for i in all_tables:
    try:
        size = sql.send("select pg_total_relation_size('" + i + "')")
        for a, b in size.items():
            if a == 'rows':
                for itr in b:
                    size_dataset = itr['pg_total_relation_size']
        arr_size.append(size_dataset)
    except:
        continue

# define variables that have the max and min values of the previous array
max_val = max(arr_size)/1048576.00
min_val = min(arr_size)/1048576.00

# define count variable
sum = 0


# define list of tuples
tupleList = []

# start iterating over array
for i in all_tables:
    # check column names
    checkCol = []

    sum = sum + 1

    # check all columns name from table
    columns_table = "select column_name, data_type FROM information_schema.columns \
        WHERE table_schema ='" + username + "' \
        AND table_name ='" + i + "';"

    # apply and get results from SQL API request
    columnAndTypes = sql.send(columns_table)
    for key, value in columnAndTypes.items():
        if key == 'rows':
            for itr in value:
                if 'cartodb_id' == itr['column_name']:
                    checkCol.append(itr['column_name'])
                elif 'the_geom' == itr['column_name']:
                    checkCol.append(itr['column_name'])
                elif 'the_geom_webmercator' == itr['column_name']:
                    checkCol.append(itr['column_name'])
    # check indexes
    checkInd = []
    # apply and get results from SQL API request
    indexes = sql.send("select indexname, indexdef from pg_indexes \
      where tablename = '" + i + "' \
      AND schemaname = '" + username + "';")
    for k, v in indexes.items():
        if k == 'rows':
            for itr in v:
                if 'the_geom_webmercator_idx' in itr['indexname']:
                    checkInd.append(itr['indexname'])
                elif 'the_geom_idx' in itr['indexname']:
                    checkInd.append(itr['indexname'])
                elif '_pkey' in itr['indexname']:
                    checkInd.append(itr['indexname'])

    # if indexes and column names exists -> table cartodbified
    if len(checkInd) >= 3 and len(checkCol) >= 3:
        cartodbfied = 'YES'
    else:
        cartodbfied = 'NO'

    # create graphs according on the table size
    try:
        table_size = sql.send("select pg_total_relation_size('" + i + "')")
        for a, b in table_size.items():
            if a == 'rows':
                for itr in b:
                    table_size = itr['pg_total_relation_size']

        # bytes to MB
        val = table_size/1048576.00

        # Normalize values
        norm = ((val-min_val)/(max_val-min_val))*100.00

        tupleList.append((i, val, norm, cartodbfied))

    except:
        print('Error at: ' + str(i))

# order list of tuples by norm size. From bigger to smaller
sorted_by_norm = sorted(tupleList, key=lambda tup: tup[2], reverse=True)

print('\n')
print('Tables of the account ordered by size:\n')
# print graphs
for z in sorted_by_norm:

    if z[2] >= 0 and z[2] <= 1:
        print('{tableName:60} {cartodbfied}:\t {space:|<1} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 1 and z[2] <= 5:
        print('{tableName:60} {cartodbfied}:\t {space:|<5} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 5 and z[2] <= 10:
        print('{tableName:60} {cartodbfied}:\t {space:|<10} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 10 and z[2] <= 20:
        print('{tableName:60} {cartodbfied}:\t {space:|<20} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 20 and z[2] <= 30:
        print('{tableName:60} {cartodbfied}:\t {space:|<30} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 30 and z[2] <= 40:
        print('{tableName:60} {cartodbfied}:\t {space:|<40} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 40 and z[2] <= 50:
        print('{tableName:60} {cartodbfied}:\t {space:|<50} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 50 and z[2] <= 60:
        print('{tableName:60} {cartodbfied}:\t {space:|<60} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 60 and z[2] <= 70:
        print('{tableName:60} {cartodbfied}:\t {space:|<70} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 70 and z[2] <= 80:
        print('{tableName:60} {cartodbfied}:\t {space:|<80} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 80 and z[2] <= 90:
        print('{tableName:60} {cartodbfied}:\t {space:|<90} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])
    elif z[2] > 90 and z[2] <= 100:
        print('{tableName:60} {cartodbfied}:\t {space:|<100} {size} {mb};'). \
            format(
                tableName=z[0], space='', size=str(round(z[1], 2)), mb='MB',
                cartodbfied=z[3])


print('\nThere are: ' + str(sum) + ' datasets in this account')

List running queries

Return a list of the running queries in the database.

import argparse
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Return the running queries of the account')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()


# Authenticate to CARTO account
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    dataset_manager = DatasetManager(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

queries = "select pid, query from pg_stat_activity \
 WHERE usename = current_user"
result = sql.send(queries)
for key, value in result.items():
    if key == 'rows':
        for itr in value:
            logger.info(itr)

SQL API batch jobs

Create and manage batch jobs for the SQL API.

import argparse
import logging
import os
import warnings

from carto.auth import APIKeyAuthClient
from carto.sql import BatchSQLClient

warnings.filterwarnings('ignore')

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Create a Batch SQL API job')

parser.add_argument('operation', type=str, default=None,
                    choices=['create', 'read', 'update', 'cancel'],
                    help='Set the batch operation that you want to apply')

parser.add_argument('--query', type=str, dest='query',
                    help='Set the query that you want to apply')

parser.add_argument('--job_id', type=str, dest='job_id',
                    help='Set the id of the job to check')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()

# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    batchSQLClient = BatchSQLClient(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# Batch SQL API operations
if args.operation == 'create':
    # create a batch api job
    createJob = batchSQLClient.create(args.query)
    for a, b in createJob.items():
        logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'read':
    readJob = batchSQLClient.read(args.job_id)
    for a, b in readJob.items():
        logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'update':
    updateJob = batchSQLClient.update(args.job_id, args.query)
    for a, b in updateJob.items():
        logger.info('{key}: {value}'.format(key=a, value=b))
elif args.operation == 'cancel':
    cancelJob = batchSQLClient.cancel(args.job_id)
    for a, b in cancelJob.items():
        logger.info('{key}: {value}'.format(key=a, value=b))
else:
    logger.info("You have not written a correct operation option")

Get table info

Return columns and its types, indexes, functions and triggers of a specific table.

import argparse
import logging
import os
from prettytable import PrettyTable
import warnings

from carto.auth import APIKeyAuthClient
from carto.datasets import DatasetManager
from carto.sql import SQLClient

warnings.filterwarnings('ignore')

# python table_info.py tornados_11

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()


# set input arguments
parser = argparse.ArgumentParser(
    description='Return columns and its types, indexes,' +
    ' functions and triggers of a specific table')

parser.add_argument('dataset_name', type=str,
                    help='Set the name of the table to explore')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL']  if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY']  if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

args = parser.parse_args()


# Authenticate to CARTO account

if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    dataset_manager = DatasetManager(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)

# SQL wrapper

sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))


# display and count all datasets of account
all_datasets = dataset_manager.all()

# set the arrays to store the values that will be used to display tables
results_col = []
results_index = []
results_func = []
results_trig = []
for i in all_datasets:
    if (i.table.name == args.dataset_name):
        print('\nGeneral information')
        table_general = PrettyTable([
            'Table name',
            'Number of rows',
            'Size of the table (MB)',
            'Privacy of the table',
            'Geometry type'
        ])

        table_general.add_row([
            i.table.name,
            i.table.row_count,
            str(round(i.table.size/1048576.00, 2)),
            str(i.table.privacy),
            str(i.table.geometry_types)
        ])

        print(table_general)

        columns_table = "select column_name, data_type FROM information_schema.columns \
        WHERE table_schema = '" + i.permission.owner.username + "'\
        AND table_name ='" + i.table.name + "';"

        # print columns_table
        print('\nThe columns and their data types are: \n')
        columnAndTypes = sql.send(columns_table)
        for key, value in columnAndTypes.items():
            if key == 'rows':
                for itr in value:
                    results_col.append([
                        itr['column_name'],
                        itr['data_type']
                    ])
        table_col = PrettyTable(
            ['Column name', 'Data type'])
        table_col.align['Column name'] = 'l'
        table_col.align['Data type'] = 'r'
        for row in results_col:
            table_col.add_row(row)

        print(table_col)
        # get all indexes of the table
        print('\nIndexes of the tables: \n')
        indexes = sql.send("select indexname, indexdef from pg_indexes \
          where tablename = '" + i.table.name + "' \
          AND schemaname = '" + i.permission.owner.username + "'")
        for k, v in indexes.items():
            if k == 'rows':
                for itr in v:
                    results_index.append([itr['indexname'], itr['indexdef']])
        table_index = PrettyTable(
            ['Index name', 'Index definition'])
        table_index.align['Index name'] = 'l'
        table_index.align['Index definition'] = 'r'
        for row_ind in results_index:
            table_index.add_row(row_ind)
        print(table_index)

        # get all functions of user account
        print('\nFunctions of the account: \n')
        functions = sql.send(
            "select pg_proc.oid as _oid, pg_proc.*, \
            pg_get_functiondef(pg_proc.oid) as definition \
            from pg_proc, pg_roles where pg_proc.proowner = pg_roles.oid \
            and pg_roles.rolname = '" + i.permission.owner.username + "'")
        for a, b in functions.items():
            if a == 'rows':
                for itr in b:
                    results_func.append(itr)
        table_func = PrettyTable(['Function name'])
        for row in results_func:
            table_func.add_row([row])

        print(table_func)

        # triggers
        print('\nTriggers of the account: \n')
        # save oid of tables in an object
        oid = sql.send(
            "select pg_class.oid as _oid, pg_class.relname from \
             pg_class, pg_roles, pg_namespace \
             where pg_roles.oid = pg_class.relowner \
             and pg_roles.rolname = current_user \
             and pg_namespace.oid = pg_class.relnamespace \
             and pg_class.relkind = 'r'")
        for c, d in oid.items():
            if c == 'rows':
                for itr in d:
                    # if the name of the table matches with the name of the
                    # input table
                    # save the oid of the table in the table_oid variable
                    if itr['relname'] == args.dataset_name:
                        table_oid = itr['_oid']

        # get triggers of the table
        triggers = sql.send(
            "SELECT tgname FROM pg_trigger WHERE tgrelid =" + str(table_oid))

        for t in triggers['rows']:
            results_trig.append(str(t['tgname']))
        table_trigger = PrettyTable(['Trigger Name'])

        for row in results_trig:
            table_trigger.add_row([row])

        print(table_trigger)

Get user info

Return information from a specific user.

import argparse
import logging
import os
from prettytable import PrettyTable
import warnings

from carto.auth import APIKeyAuthClient
from carto.sql import SQLClient
from carto.users import UserManager

warnings.filterwarnings('ignore')

# Logger (better than print)
logging.basicConfig(
    level=logging.INFO,
    format=' %(asctime)s - %(levelname)s - %(message)s',
    datefmt='%I:%M:%S %p')
logger = logging.getLogger()

# set input arguments
parser = argparse.ArgumentParser(
    description='Return information from a specific user')

parser.add_argument('--organization', type=str, dest='organization',
                    default=os.environ['CARTO_ORG'] if 'CARTO_ORG' in os.environ else '',
                    help='Set the name of the organization' +
                    ' account (defaults to env variable CARTO_ORG)')

parser.add_argument('--base_url', type=str, dest='CARTO_BASE_URL',
                    default=os.environ['CARTO_API_URL'] if 'CARTO_API_URL' in os.environ else '',
                    help='Set the base URL. For example:' +
                    ' https://username.carto.com/ ' +
                    '(defaults to env variable CARTO_API_URL)')

parser.add_argument('--api_key', dest='CARTO_API_KEY',
                    default=os.environ['CARTO_API_KEY'] if 'CARTO_API_KEY' in os.environ else '',
                    help='Api key of the account' +
                    ' (defaults to env variable CARTO_API_KEY)')

parser.add_argument('--username', dest='CARTO_USER',
                    default=os.environ['CARTO_USER'] if 'CARTO_USER' in os.environ else '',
                    help='define username of the organization' +
                    ' to check (defaults to env variable CARTO_USER)')
args = parser.parse_args()


# Set authentification to CARTO
if args.CARTO_BASE_URL and args.CARTO_API_KEY and args.CARTO_USER and args.organization:
    auth_client = APIKeyAuthClient(
        args.CARTO_BASE_URL, args.CARTO_API_KEY, args.organization)
    user_manager = UserManager(auth_client)
else:
    logger.error('You need to provide valid credentials, run with -h parameter for details')
    import sys
    sys.exit(1)


userInfo = []
print('\nThe attributes of the user are:\n')
try:
    user = user_manager.get(args.CARTO_USER)

    for i in user.__dict__:
        userInfo.append([
            i,
            str(user.__dict__[i])
        ])

    table_user = PrettyTable(['Attribute', 'Value'])
    table_user.align['Attribute'] = 'l'
    table_user.align['Value'] = 'l'
    for row in userInfo:
        table_user.add_row(row)
    print(table_user)
    # print('{name}: {value}').format(name=i,value=str(user.__dict__[i]))
except Exception as e:
    logger.warn('User has no admin of its organization')

# SQL wrapper
sql = SQLClient(APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY))

# show quota of user
results = []
print('\nThe quotas of the user are:\n')
quota = sql.send(
    "SELECT * FROM cdb_dataservices_client.cdb_service_quota_info()")
for k, v in quota.items():
    if k == 'rows':
        for itr in v:
            results.append([
                itr['service'],
                itr['used_quota'],
                itr['provider'],
                itr['soft_limit'],
                itr['monthly_quota']
            ])

table = PrettyTable(
    ['Service', 'Provider', 'Soft limit', 'Used quota', 'Monthly quota'])
table.align['Used quota'] = 'l'
table.align['Provider'] = 'r'
table.align['Soft limit'] = 'r'
table.align['Service'] = 'r'
table.align['Monthly quota'] = 'r'
for row in results:
    table.add_row(row)

print(table)

Fundamentals

Docs

Libraries

API

Python SDK v1.6.0