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 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 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 __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 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 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 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 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 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 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 __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):
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 __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 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)
class DotCartoFile(object):
replacements = []
visualization_id = None
json = None
def __init__(self, dotcarto_file, endpoint_base_url, api_key):
self.dotcarto_file = dotcarto_file
self.sql = SQLClient(APIKeyAuthClient(endpoint_base_url, api_key))
def replace_dataset(self, original_dataset_name, new_dataset_name):
self.replacements.append({
"original_dataset_name": original_dataset_name,
"new_dataset_name": new_dataset_name
})
def replace_datasets_in_dotcarto_file(self, zip_buffer):
for replacement in self.replacements:
original_dataset_name = replacement["original_dataset_name"]
new_dataset_name = replacement["new_dataset_name"]
self.json = self.json.replace(original_dataset_name,
new_dataset_name)
new_dataset = self.sql.send("select * from {dataset}".format(
dataset=replacement["new_dataset_name"]),
format="gpkg")
zip_buffer.writestr(
join(self.visualization_id,
replacement["new_dataset_name"] + ".gpkg"), new_dataset)
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: # 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 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 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 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 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)
class DotCartoFile(object):
replacements = []
visualization_id = None
json = None
def __init__(self, dotcarto_file, endpoint_base_url, api_key):
self.dotcarto_file = dotcarto_file
self.sql = SQLClient(APIKeyAuthClient(endpoint_base_url, api_key))
def replace_dataset(self, original_dataset_name, new_dataset_name):
self.replacements.append({
"original_dataset_name": original_dataset_name,
"new_dataset_name": new_dataset_name
})
def replace_datasets_in_dotcarto_file(self, zip_buffer):
for replacement in self.replacements:
original_dataset_name = replacement["original_dataset_name"]
new_dataset_name = replacement["new_dataset_name"]
self.json = self.json.replace(original_dataset_name,
new_dataset_name)
new_dataset = self.sql.send("select * from {dataset}".format(
dataset=replacement["new_dataset_name"]),
format="gpkg")
zip_buffer.writestr(
join(self.visualization_id,
replacement["new_dataset_name"] + ".gpkg"), new_dataset)
def get_new(self, destination_path=None):
with ZipFile(self.dotcarto_file) as original_dotcarto_file:
self.visualization_id = original_dotcarto_file.namelist()[0][:-1]
json_file_relative_path = join(
self.visualization_id, self.visualization_id + ".carto.json")
self.json = original_dotcarto_file.read(json_file_relative_path)
if destination_path is not None:
with ZipFile(destination_path, mode='w') as new_dotcarto_file:
self.replace_datasets_in_dotcarto_file(new_dotcarto_file)
new_dotcarto_file.writestr(json_file_relative_path,
self.json.encode('utf-8'))
else:
new_dotcarto_buffer = StringIO()
with ZipFile(new_dotcarto_buffer, mode='w') as new_dotcarto_file:
self.replace_datasets_in_dotcarto_file(new_dotcarto_file)
new_dotcarto_file.writestr(json_file_relative_path,
self.json.encode('utf-8'))
new_dotcarto_buffer.seek(0)
return new_dotcarto_buffer
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
' (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'")
# 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',
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.get('CARTO_API_KEY', ''),
help=('Api key of the account'
' (defaults to env variable CARTO_API_KEY)'))
args = parser.parse_args()
if not args.CARTO_BASE_URL or not args.CARTO_API_KEY:
sys.exit(parser.print_usage())
auth_client = APIKeyAuthClient(args.CARTO_BASE_URL, args.CARTO_API_KEY)
sql_client = SQLClient(auth_client)
copy_client = CopySQLClient(auth_client)
# Create a table suitable to receive the data
logger.info('Creating table nexrad_copy_example...')
sql_client.send("""CREATE TABLE IF NOT EXISTS nexrad_copy_example (
the_geom geometry(Geometry,4326),
reflectivity numeric
)""")
sql_client.send(
"SELECT CDB_CartodbfyTable(current_schema, 'nexrad_copy_example')")
logger.info('Done')
logger.info('Trying to connect to the THREDDS radar query service')
rs = RadarServer(
'http://thredds.ucar.edu/thredds/radarServer/nexrad/level2/IDD/')
args = parser.parse_args()
TABLE_NAME = args.TABLE_NAME
# Set authentification to CARTO
auth_src_client = APIKeyAuthClient(
SRC_URL,
SRC_API_KEY
)
auth_dst_client = APIKeyAuthClient(
DST_URL,
DST_API_KEY
)
# Get SQL API clients
sql_src_client = SQLClient(auth_src_client)
sql_dst_client = SQLClient(auth_dst_client)
# Create a SQL utility function to extract source table structure.
with open('generate_create_table_statement.sql', 'r') as f:
query_generate_create_table_statement = f.read()
logger.info('Creating function generate_create_table_statement...')
res = sql_src_client.send(query_generate_create_table_statement)
logger.info('Response: {}'.format(res))
# Get the table structure
logger.info('Getting the CREATE TABLE statement for %s' % TABLE_NAME)
query = (
"SELECT generate_create_table_statement('%s')"
" AS create_table" % TABLE_NAME
)
' (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:
auth_client = APIKeyAuthClient(
args.CARTO_BASE_URL, args.CARTO_API_KEY)
else:
logger.error('You need to provide valid credentials, run with '
'-h parameter for details')
sys.exit(1)
# Create and cartodbfy a table
sqlClient = SQLClient(auth_client)
sqlClient.send("""
CREATE TABLE IF NOT EXISTS copy_example (
the_geom geometry(Geometry,4326),
name text,
age integer
)
""")
sqlClient.send("SELECT CDB_CartodbfyTable(current_schema, 'copy_example')")
copyClient = CopySQLClient(auth_client)
# COPY FROM example
logger.info("COPY'ing FROM file...")
query = ('COPY copy_example (the_geom, name, age) '
'FROM stdin WITH (FORMAT csv, HEADER true)')
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')
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']
' (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'")
# 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(
def test_no_auth_sql_error_get(no_auth_client):
sql = SQLClient(no_auth_client)
with pytest.raises(CartoException):
sql.send('select * from non_existing_dataset', {'do_post': False})
' (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 == '*':
def test_sql_error(api_key_auth_client_usr):
sql = SQLClient(api_key_auth_client_usr)
with pytest.raises(CartoException):
sql.send('select * from non_existing_dataset')
'(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)
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()
# loop over all 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
