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 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 __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']
# 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