def get_auth_client(username=None,
api_key=None,
baseurl=None,
cdb_client=None):
"""Instantiates a SQL Client from the CARTO Python SDK (v1.0.0)
:param username: CARTO username
:type username: string
:param api_key: API key of CARTO user ``username``
:type api_key: string
:param baseurl: Base URL for CARTO instance (usually suitable for on prem)
:type baseurl: string
:param cdb_client: CARTO Python SDK Authentication client
:type cdb_client: object
:returns: Authenticated SQL client with user credentials
:rtype: sql auth object
"""
from carto.sql import SQLClient
from carto.auth import APIKeyAuthClient
if cdb_client:
sql = SQLClient(cdb_client)
elif username is not None and api_key is not None:
baseurl = get_baseurl(username=username, baseurl=baseurl)
auth_client = APIKeyAuthClient(baseurl, api_key)
sql = SQLClient(auth_client)
else:
raise Exception("`username` and `api_key` or `cdb_client` has to be "
"specified.")
return sql
def clean(self):
domain = self.cleaned_data['domain']
table_name = self.cleaned_data['table_name']
name_col = self.cleaned_data['name_col']
pcode_col = self.cleaned_data['pcode_col']
parent_code_col = self.cleaned_data['parent_code_col']
auth_client = EtoolsCartoNoAuthClient(
base_url="https://{}.carto.com/".format(str(domain)))
sql_client = SQLClient(auth_client)
try:
sites = sql_client.send(
'select * from {} limit 1'.format(table_name))
except CartoException:
logger.exception("CartoDB exception occured")
raise ValidationError(
"Couldn't connect to CartoDB table: {}".format(table_name))
else:
row = sites['rows'][0]
if name_col not in row:
raise ValidationError(
'The Name column ({}) is not in table: {}'.format(
name_col, table_name))
if pcode_col not in row:
raise ValidationError(
'The PCode column ({}) is not in table: {}'.format(
pcode_col, table_name))
if parent_code_col and parent_code_col not in row:
raise ValidationError(
'The Parent Code column ({}) is not in table: {}'.format(
parent_code_col, table_name))
return self.cleaned_data
def __init__(self, user, api_key, options={}):
super().__init__(options)
self.do_post = options.get('do_post', False)
self.parse_json = options.get('parse_json', True)
self.format = options.get('format', 'json')
self.base_url_option = options.get('base_url', '')
self.api_version = options.get('api_version', self.DEFAULT_API_VERSION)
self.batch = options.get('batch', False)
self.user = user
self.api_key = api_key
self.base_url = self._generate_base_url(user, self.base_url_option)
# Carto Context for DataFrame handling
self._carto_context = None
# Carto client for COPYs
self._copy_client = None
self._auth_client = APIKeyAuthClient(api_key=api_key,
base_url=self.base_url)
self._sql_client = SQLClient(self._auth_client,
api_version=self.api_version)
self._batch_client = None
if self.batch:
self._batch_client = BatchSQLClient(self._auth_client)
def update_asset_on_carto(asset_dict, fields):
auth_client = APIKeyAuthClient(api_key=CARTO_API_KEY,
base_url=USR_BASE_URL)
sql = SQLClient(auth_client)
#values_tuple_strings = [make_values_tuple_string_from_model(r, fields) for r in [asset]]
# OR POSSIBLY
#values_tuple_strings = [make_values_tuple_string_from_model(asset, fields)]
#q = f"UPDATE {TABLE_NAME} SET {values_tuple_strings} WHERE asset_id = {asset.id};"
#values_tuple_strings = [values_string_from_model(asset, fields)]
#q = f"UPDATE {TABLE_NAME} SET ({', '.join(fields + ['the_geom', 'the_geom_webmercator'])}) " \
# f"VALUES {', '.join(map(lambda x: x + 1, values_tuple_strings))};" # This is throwing an
# error, and it's really not clear why it's trying to map a math function over strings.
# Let's ignore the the_geom* fields for now and do the update the simple way:
# Single updates can be done like this:
# UPDATE election_results SET votes=52, pro=24 WHERE county_id = 1;
other_fields = copy.deepcopy(fields)
other_fields.remove('id')
q = f"UPDATE {TABLE_NAME} SET {set_string_from_model(asset_dict, other_fields)} WHERE id = {asset_dict['asset'].id};"
assert len(q) < 16384
print(q)
results = sql.send(q)
def clean(self):
domain = self.cleaned_data['domain']
api_key = self.cleaned_data['api_key']
table_name = self.cleaned_data['table_name']
client = get_carto_client(api_key, domain)
sql = SQLClient(client)
try:
sites = sql.send(
'select * from {} limit 1'.format(table_name)
)
except CartoException:
logging.exception("CartoDB exception occured", exc_info=True)
raise ValidationError(
"Couldn't connect to CartoDB table: " + table_name)
else:
row = sites['rows'][0]
if 'name' not in row:
raise ValidationError(
'The Name column ({}) is not in table: {}'.format(
'name', table_name))
if 'pcode' not in row:
raise ValidationError(
'The PCode column ({}) is not in table: {}'.format(
'pcode', table_name))
if self.cleaned_data['parent'] and 'parent_pcode' not in row:
raise ValidationError(
'The Parent Code column ({}) is not in table: {}'.format(
'parent_pcode', table_name))
return self.cleaned_data
def fix_carto_geofields(asset_id=None):
auth_client = APIKeyAuthClient(api_key=CARTO_API_KEY,
base_url=USR_BASE_URL)
sql = SQLClient(auth_client)
# Now the problem with pushing this data through SQL calls is that Carto does not rerun the
# processes that add values for the_geom and the_geom_webmercator. So it kind of seems like
# we have to do this ourselves as documented at
# https://gis.stackexchange.com/a/201908
q = f"UPDATE {TABLE_NAME} SET the_geom = ST_SetSRID(st_makepoint(longitude, latitude),4326)"
if asset_id is not None:
q += f" WHERE id = {asset_id}" # This can significantly speed up Carto geofield updates
# when saving a single model instance.
# This works because 'longitude' and 'latitude' are the names of the corresponding fields in the CSV file.
results1 = sql.send(q) # This takes 12 seconds to run for 100,000 rows.
# Exporting the data immediately after this is run oddly leads to the same CSV file as exporting before
# it is run, but waiting a minute and exporting again gives something with the_geom values in the same
# rows as the table on the Carto site. Basically, the exported CSV file can lag the view on the Carto
# web site by a minute or two.
q = f"SELECT ST_Transform(ST_SetSRID(st_makepoint(longitude, latitude),4326),3857) as the_geom_webmercator FROM {TABLE_NAME}"
results2 = sql.send(q) # This one ran much faster.
# One improvement is that you can replace ST_SetSRID(st_makepoint(lon, lat)) with CDB_LatLng(lat, lon)
# though I don't know if it leads to any performance improvement.
print(
f"Tried to add values for the the_geom and the_geom_webmercator fields in {TABLE_NAME}. The requests completed in {results1['time']} s and {results2['time']} s."
)
def setUp(self):
if (os.environ.get('APIKEY') is None
or os.environ.get('USERNAME') is None):
try:
creds = json.loads(open('test/secret.json').read())
self.apikey = creds['APIKEY']
self.username = creds['USERNAME']
except: # noqa
warnings.warn('Skipping CartoContext tests. To test it, '
'create a `secret.json` file in test/ by '
'renaming `secret.json.sample` to `secret.json` '
'and updating the credentials to match your '
'environment.')
self.apikey = None
self.username = None
else:
self.apikey = os.environ['APIKEY']
self.username = os.environ['USERNAME']
if self.username and self.apikey:
self.baseurl = 'https://{username}.carto.com/'.format(
username=self.username)
self.auth_client = APIKeyAuthClient(base_url=self.baseurl,
api_key=self.apikey)
self.sql_client = SQLClient(self.auth_client)
# sets skip value
WILL_SKIP = self.apikey is None or self.username is None # noqa: F841
has_mpl = 'mpl' if os.environ.get('MPLBACKEND') else 'nonmpl'
pyver = sys.version[0:3].replace('.', '_')
# for writing to carto
self.test_write_lnglat_table = (
'cartoframes_test_write_lnglat_table_{ver}_{mpl}'.format(
ver=pyver, mpl=has_mpl))
def setUp(self):
if (os.environ.get('APIKEY') is None
or os.environ.get('USERNAME') is None):
try:
creds = json.loads(open('test/secret.json').read())
self.apikey = creds['APIKEY']
self.username = creds['USERNAME']
except: # noqa: E722
warnings.warn("Skipping CartoContext tests. To test it, "
"create a `secret.json` file in test/ by "
"renaming `secret.json.sample` to `secret.json` "
"and updating the credentials to match your "
"environment.")
self.apikey = None
self.username = None
else:
self.apikey = os.environ['APIKEY']
self.username = os.environ['USERNAME']
self.user_url = self.user_url()
if self.username and self.apikey:
self.baseurl = self.user_url.format(username=self.username)
self.auth_client = APIKeyAuthClient(base_url=self.baseurl,
api_key=self.apikey)
self.sql_client = SQLClient(self.auth_client)
# sets skip value
WILL_SKIP = self.apikey is None or self.username is None # noqa: F841
self.points = 'tweets_obama'
self.polys = 'nat'
self.local = 'cb_2013_us_csa_500k'
def setUp(self):
if (os.environ.get('APIKEY') is None
or os.environ.get('USERNAME') is None):
try:
creds = json.loads(open('test/secret.json').read())
self.apikey = creds['APIKEY']
self.username = creds['USERNAME']
except:
warnings.warn("Skipping CartoContext tests. To test it, "
"create a `secret.json` file in test/ by "
"renaming `secret.json.sample` to `secret.json` "
"and updating the credentials to match your "
"environment.")
self.apikey = None
self.username = None
else:
self.apikey = os.environ['APIKEY']
self.username = os.environ['USERNAME']
if self.username and self.apikey:
self.baseurl = 'https://{username}.carto.com/'.format(
username=self.username)
self.auth_client = APIKeyAuthClient(base_url=self.baseurl,
api_key=self.apikey)
self.sql_client = SQLClient(self.auth_client)
# sets skip value
WILL_SKIP = self.apikey is None or self.username is None
# table naming info
has_mpl = 'mpl' if os.environ.get('MPLBACKEND') else 'nonmpl'
pyver = sys.version[0:3].replace('.', '_')
# test tables
self.test_read_table = 'cb_2013_us_csa_500k'
self.valid_columns = set([
'affgeoid', 'aland', 'awater', 'created_at', 'csafp', 'geoid',
'lsad', 'name', 'the_geom', 'updated_at'
])
# for writing to carto
self.test_write_table = 'cartoframes_test_table_{ver}_{mpl}'.format(
ver=pyver, mpl=has_mpl)
# for batch writing to carto
self.test_write_batch_table = (
'cartoframes_test_batch_table_{ver}_{mpl}'.format(ver=pyver,
mpl=has_mpl))
self.test_write_lnglat_table = (
'cartoframes_test_write_lnglat_table_{ver}_{mpl}'.format(
ver=pyver, mpl=has_mpl))
# for queries
self.test_query_table = ('cartoframes_test_query_'
'table_{ver}_{mpl}'.format(ver=pyver,
mpl=has_mpl))
self.test_delete_table = ('cartoframes_test_delete_'
'table_{ver}_{mpl}').format(ver=pyver,
mpl=has_mpl)
def test_sql_unverified_fails_with_auth_client(wrong_onprem_auth_client):
if wrong_onprem_auth_client is None:
assert True is True
return
sql = SQLClient(wrong_onprem_auth_client)
with pytest.raises(CartoException):
data = sql.send('select version()')
def __init__(self, credentials):
self.credentials = credentials or get_default_credentials()
check_credentials(self.credentials)
self.auth_client = _create_auth_client(self.credentials)
self.sql_client = SQLClient(self.auth_client)
self.copy_client = CopySQLClient(self.auth_client)
self.batch_sql_client = BatchSQLClient(self.auth_client)
def show_remap_table(self, request, pk):
carto_table = CartoDBTable.objects.get(pk=pk)
sql_client = SQLClient(
LocationsCartoNoAuthClient(
base_url=f"https://{carto_table.domain}.carto.com/"))
old2new, to_deactivate = get_remapping(sql_client, carto_table)
template = loader.get_template('admin/location_remap.html')
context = {'old2new': old2new, 'to_deactivate': to_deactivate}
return HttpResponse(template.render(context, request))
def clean(self):
domain = self.cleaned_data['domain']
table_name = self.cleaned_data['table_name']
name_col = self.cleaned_data['name_col']
pcode_col = self.cleaned_data['pcode_col']
parent_code_col = self.cleaned_data['parent_code_col']
remap_table_name = self.cleaned_data['remap_table_name']
auth_client = LocationsCartoNoAuthClient(
base_url="https://{}.carto.com/".format(str(domain)))
sql_client = SQLClient(auth_client)
try:
sites = sql_client.send(
'select * from {} limit 1'.format(table_name))
except CartoException:
logger.exception("CartoDB exception occured")
raise ValidationError(
"Couldn't connect to CartoDB table: {}".format(table_name))
else:
row = sites['rows'][0]
if name_col not in row:
raise ValidationError(
'The Name column ({}) is not in table: {}'.format(
name_col, table_name))
if pcode_col not in row:
raise ValidationError(
'The PCode column ({}) is not in table: {}'.format(
pcode_col, table_name))
if parent_code_col and parent_code_col not in row:
raise ValidationError(
'The Parent Code column ({}) is not in table: {}'.format(
parent_code_col, table_name))
if remap_table_name:
try:
remap_table = sql_client.send(
'select * from {} limit 1'.format(remap_table_name))
except CartoException: # pragma: no-cover
logger.exception("CartoDB exception occured")
raise ValidationError(
"Couldn't connect to the CartoDB remap table: {}".format(
remap_table_name))
else:
row = remap_table['rows'][0]
if 'old_pcode' not in row.keys():
raise ValidationError(
'The Old PCode column ({}) is not in table: {}'.format(
'old_pcode', remap_table_name))
if 'new_pcode' not in row.keys():
raise ValidationError(
'The New PCode column ({}) is not in table: {}'.format(
'new_pcode', remap_table_name))
return self.cleaned_data
def get_carto_asset_ids(id_to_check=None):
auth_client = APIKeyAuthClient(api_key=CARTO_API_KEY,
base_url=USR_BASE_URL)
sql = SQLClient(auth_client)
if id_to_check is None:
results = sql.send(f"SELECT id FROM {TABLE_NAME}")
else:
results = sql.send(
f"SELECT id FROM {TABLE_NAME} WHERE id = {id_to_check}")
ids = [r['id'] for r in results['rows']]
return ids
def validate_locations_in_use(carto_table_pk):
try:
carto_table = CartoDBTable.objects.get(pk=carto_table_pk)
except CartoDBTable.DoesNotExist as e:
logger.exception('Cannot retrieve CartoDBTable with pk: %s',
carto_table_pk)
raise e
database_pcodes = []
for row in Location.objects.all_locations().filter(
gateway=carto_table.location_type).values('p_code'):
database_pcodes.append(row['p_code'])
auth_client = LocationsCartoNoAuthClient(
base_url="https://{}.carto.com/".format(carto_table.domain))
sql_client = SQLClient(auth_client)
try:
qry = sql_client.send(
'select array_agg({}) AS aggregated_pcodes from {}'.format(
carto_table.pcode_col,
carto_table.table_name,
))
new_carto_pcodes = qry['rows'][0]['aggregated_pcodes'] \
if len(qry['rows']) > 0 and "aggregated_pcodes" in qry['rows'][0] else []
remapped_pcode_pairs = []
if carto_table.remap_table_name:
remap_qry = 'select old_pcode::text, new_pcode::text from {}'.format(
carto_table.remap_table_name)
remapped_pcode_pairs = sql_client.send(remap_qry)['rows']
except CartoException as e:
logger.exception(
"CartoDB exception occured during the data validation.")
raise e
remap_old_pcodes = [
remap_row['old_pcode'] for remap_row in remapped_pcode_pairs
]
orphaned_pcodes = set(database_pcodes) - (set(new_carto_pcodes)
| set(remap_old_pcodes))
orphaned_location_ids = Location.objects.all_locations().filter(
p_code__in=list(orphaned_pcodes))
# if location ids with no remap in use are found, do not continue the import
location_ids_bnriu = get_location_ids_in_use(orphaned_location_ids)
if location_ids_bnriu:
msg = "Location ids in use without remap found: {}".format(','.join(
[str(iu) for iu in location_ids_bnriu]))
logger.exception(msg)
raise NoRemapInUseException(msg)
return True
def test_sql(api_key_auth_client_usr, mock_requests, do_post=True):
with mock_requests.mocker:
sql = SQLClient(api_key_auth_client_usr)
data = sql.send('select * from ' + EXISTING_POINT_DATASET,
do_post=do_post)
assert data is not None
assert 'rows' in data
assert 'total_rows' in data
assert 'time' in data
assert len(data['rows']) > 0
def is_public(self, query):
# Used to detect public tables in queries in the publication,
# because privacy only works for tables.
public_auth_client = _create_auth_client(self.credentials, public=True)
public_sql_client = SQLClient(public_auth_client)
exists_query = 'EXPLAIN {}'.format(query)
try:
public_sql_client.send(exists_query, do_post=False)
return True
except CartoException:
return False
def test_sql_unverified(non_verified_auth_client):
if non_verified_auth_client is None:
assert True is True
return
sql = SQLClient(non_verified_auth_client)
data = sql.send('select version()')
assert data is not None
assert 'rows' in data
assert 'total_rows' in data
assert 'time' in data
assert len(data['rows']) > 0
def create_table(self, table_name=None, cartodbfy=False):
table_name = table_name or self.carto_table_name
client = SQLClient(self.carto_auth_client)
client.send(
"CREATE TABLE IF NOT EXISTS {table_name} ({columns})".format(
table_name=table_name,
columns=",".join((name + " " + type
for (name, type) in self.carto_fields))))
if cartodbfy is True:
client.send(
"SELECT CDB_CartodbfyTable('{schema}', '{table_name}')".format(
schema=self.carto_auth_client.username,
table_name=table_name))
def __init__(self, base_url=None, api_key=None, session=None, verbose=0):
self.api_key, self.base_url = _process_credentials(api_key,
base_url)
self.auth_client = APIKeyAuthClient(base_url=self.base_url,
api_key=self.api_key,
session=session)
self.sql_client = SQLClient(self.auth_client)
self.username = self.auth_client.username
self.is_org = self._is_org_user()
self._map_templates = {}
self._srcdoc = None
self._verbose = verbose
def query(self, sql_query, parse_json=True, do_post=True, format=None, write_qry=False):
try:
if not write_qry and self.__is_write_query(sql_query):
raise CartoModelException("Aborted query. No write queries allowed.")
auth_client = self.__get_auth_client(self.__carto_api_key, self.__carto_user)
sql = SQLClient(auth_client, api_version='v2')
res = sql.send(sql_query, parse_json, do_post, format)
return res['rows']
except Exception as err:
self.__logger.error("Error sending query to Carto: {0}\n{1}".format(err, sql_query))
raise CartoModelException(err)
def __init__(self, CARTO_USER, CARTO_API_URL, CARTO_ORG, CARTO_API_KEY,
USER_QUOTA):
self.CARTO_USER = CARTO_USER
self.CARTO_ORG = CARTO_ORG
self.USER_QUOTA = USER_QUOTA
### CARTO clients
auth_client = APIKeyAuthClient(CARTO_API_URL, CARTO_API_KEY, CARTO_ORG)
self.sql = SQLClient(auth_client)
self.vm = VisualizationManager(auth_client)
self.dm = DatasetManager(auth_client)
### logger, variables and CARTO clients
self.logger = logging.getLogger('carto_report')
self.logger.addHandler(logging.NullHandler())
def __init__(self):
self.carto_api_key = os.environ['API_KEY']
self.carto_account = os.environ['ACCOUNT']
USR_BASE_URL = "https://{user}.carto.com/".format(
user=self.carto_account)
self.auth_client = APIKeyAuthClient(api_key=self.carto_api_key,
base_url=USR_BASE_URL)
#this mimics the carto docs, leave it this way
self.sql = SQLClient(self.auth_client)
self.dataset_manager = DatasetManager(self.auth_client)
def initialize(self):
if not self.api_url and self.user_name:
self.api_url = "https://{}.carto.com/api/".format(self.user_name)
elif not self.api_url and not self.user_name:
raise Exception(
'Not enough data provided to initialize the client')
if self.org_name:
self.client = APIKeyAuthClient(self.api_url, self.api_key,
self.org_name)
else:
self.client = APIKeyAuthClient(self.api_url, self.api_key)
self.sql_client = SQLClient(self.client)
self.batch_client = BatchSQLClient(self.client)
def test_sql_additional_params(api_key_auth_client_usr):
sql = SQLClient(api_key_auth_client_usr)
request_args = {"skipfields": "the_geom_webmercator"}
data = sql.send('select * from ' + EXISTING_POINT_DATASET,
do_post=True,
**request_args)
assert data is not None
assert 'rows' in data
assert 'total_rows' in data
assert 'time' in data
assert len(data['rows']) > 0
assert "the_geom_webmercator" not in data['rows'][0]
data = sql.send('select * from ' + EXISTING_POINT_DATASET, do_post=True)
assert "the_geom_webmercator" in data['rows'][0]
def conn(self):
if self._conn is None:
self.logger.info('Making connection to Carto {} account...'.format(
self.user))
try:
api_key = self.api_key
base_url = USR_BASE_URL.format(user=self.user)
auth_client = APIKeyAuthClient(api_key=api_key,
base_url=base_url)
conn = SQLClient(auth_client)
self._conn = conn
self.logger.info('Connected to Carto.\n')
except CartoException as e:
self.logger.error(
'Failed making connection to Carto {} account...'.format(
self.user))
raise e
return self._conn
def get_carto_username(self):
"""
Returns the user name for the client passed
Returns:
String with CARTO account name
Raises:
CartoException: an error thrown by the CARTO request
Exception: some error in the client happened
"""
self.logger.debug("Getting the CARTO user name...")
sql = SQLClient(self.auth_client)
query = "SELECT CDB_Username()"
q = sql.send(query)
self.logger.debug(q)
if "rows" in q and len(q["rows"]) == 1:
return q["rows"][0]["cdb_username"]
else:
raise Exception("Your client is not valid")
def dumpToCarto(eleList, table_name):
# I am using my CARTO account
USERNAME = "******"
USR_BASE_URL = "https://{user}.carto.com/".format(user=USERNAME)
auth_client = APIKeyAuthClient(
api_key="53bb19efc968a08f7bdc2c1ffc29c31659240b39",
base_url=USR_BASE_URL)
sql = SQLClient(auth_client)
table_name = 'strava_segments_' + table_name
for segment in eleList:
try:
query = "UPDATE {table} SET cartodb_id={id}, the_geom=ST_SetSRID(ST_MakePoint({long}, {lat}),4326), name='{name}', value={value}, date=now() WHERE cartodb_id={id}". \
format(table=table_name,id=segment[0],long=segment[7],lat=segment[8],name=segment[1],value=segment[2])
logger.info(query)
sql.send(query)
except CartoException as e:
logger.error(e)
def __init__(self, csv_file_path, **kwargs):
self.__set_max_csv_length()
self.__set_defaults()
for key, value in kwargs.items():
try:
setattr(self, key, int(value))
except (ValueError, TypeError):
if value in ("true", "True"):
setattr(self, key, True)
elif value in ("false", "False"):
setattr(self, key, False)
else:
setattr(self, key, value)
self.__trim_columns()
self.csv_file_path = csv_file_path
if self.api_key:
self.api_auth = APIKeyAuthClient(self.base_url, self.api_key)
self.sql = SQLClient(self.api_auth)
self.bsql = BatchSQLClient(self.api_auth)
def query(sql_query, parse_json=True, do_post=True, format=None, retries=5):
log.debug(f"Query: {sql_query}")
sql = SQLClient(_get_auth_client(), api_version="v2")
res = None
for retry_number in range(retries):
try:
res = sql.send(sql_query, parse_json, do_post, format)
if res:
break
except CartoException as carto_exception:
if retry_number == retries - 1:
raise carto_exception
else:
time.sleep(5)
continue
if format is None:
return res["rows"]
return res