def insert_into_cartodb(sql_query): cl = CartoDBAPIKey(var_api_key, var_cartodb_domain) try: # your CartoDB account: print cl.sql(sql_query) except CartoDBException as e: print ("some error ocurred", e)
def create( table_name, columns, api_key=CARTODB_API_KEY, cartodb_domain=cartodb_domain): cl = CartoDBAPIKey(api_key, cartodb_domain) try: resp = cl.sql("drop table {}".format(table_name)) except CartoDBException as e: print ("unable to drop table", e) # Do not add column 'cartodb_id' as this is created by the later call to cdb_cartodbfytable() # Though if it were needed, it would most likely look like this - # cartodb_id int4 default nextval('untitled_table_cartodb_id_seq1'::regclass) not null # Columns created_at, updated_at, the_geometry and the_geom_webmercator are also created automatically if absent. # created_at timestamptz default now() not null, updated_at timestamptz default now() not null # the_geometry, the_geom_webmercator geometry try: resp = cl.sql("create table {} ({})".format(table_name, columns)) except CartoDBException as e: print ("unable to create table", e) print resp try: resp = cl.sql("select cdb_cartodbfytable('{}')".format(table_name)) except CartoDBException as e: print ("unable to cartodbfy table", e) return
def purgeCartoDBMpas(mpas=mpas, dryrun=False): '''Execute Mpa remove statements using the CartoDB API via the cartodb module for mpas in the CartoDB mpatlas table that are not found in the passed mpas queryset. mpas = Mpa queryset [default is all non-rejected MPAs with geom boundaries] dryrun = [False] if true, just return list of mpa_ids to be purged but don't run SQL. Returns list of mpa.mpa_ids that were removed, empty list if none removed. ''' cl = CartoDBAPIKey(API_KEY, cartodb_domain) nummpas = mpas.count() local_ids = mpas.values_list('mpa_id', flat=True) cartodb_idsql = ''' SELECT mpa_id FROM mpatlas ORDER BY mpa_id; ''' try: result = cl.sql(cartodb_idsql) except CartoDBException as e: error_ids.extend(step_ids) print('CartoDB Error for getting mpa_ids', e) cartodb_ids = [i['mpa_id'] for i in result['rows']] missing = list(set(cartodb_ids) - set(local_ids)) missing.sort() deletesql = ''' DELETE FROM mpatlas WHERE mpa_id IN %(missing)s; ''' % ({'missing': adaptParam(tuple(missing))}) if not dryrun: try: cl.sql(deletesql) except CartoDBException as e: error_ids.extend(step_ids) print 'CartoDB Error deleting %s mpas:' % len(missing), e return missing
class CartoDBConnector(object): def __init__(self): self.user = os.environ.get('CARTODB_EMAIL') self.api_key = os.environ.get('CARTODB_APIKEY') self.cartodb_domain = os.environ.get('CARTODB_DOMAIN') self.cl = CartoDBAPIKey(self.api_key, self.cartodb_domain) def newResponse(self, response_data): try: sqlValues = { "survey" : response_data.get('survey'), "surveyid" : response_data.get('survey_id'), "path":response_data.get('path'), "choose_a_road" : response_data.get('choose_a_road'), "how_do_you_feel" : response_data.get('how_do_you_feel'), "link_to_destinations" : response_data.get('link_to_destinations'), "is_the_gradient_amenable_to_cycling" : response_data.get('is_the_gradient_amenable_to_cycling'), "street_offers_priority_through_intersections" : response_data.get("street_offers_priority_through_intersections"), "type_of_cyclist" : response_data.get('type_of_cyclist') } sqlStr = "INSERT INTO dynamicconnections_bmwguggenheimlab \ (survey, surveyid, type_of_cyclist, the_geom, choose_a_road, how_do_you_feel, link_to_destinations,is_the_gradient_amenable_to_cycling,street_offers_priority_through_intersections) \ values \ ('%(survey)s', '%(surveyid)s', '%(type_of_cyclist)s', ST_GeomFromText('MULTILINESTRING((%(path)s))',4326), '%(choose_a_road)s', '%(how_do_you_feel)s', '%(link_to_destinations)s','%(is_the_gradient_amenable_to_cycling)s', '%(street_offers_priority_through_intersections)s')" % sqlValues app.logger.debug('cartodb insert sql') app.logger.debug(sqlStr) query = self.cl.sql(sqlStr.encode('utf-8','replace')) app.logger.debug('query results') app.logger.debug(query) return query except CartoDBException as e: print ("some error occurred",e) return e def test(self): try: sqlstr = "INSERT INTO dynamicconnections_bmwguggenheimlab \ (survey, surveyid, the_geom, choose_a_road, how_do_you_feel, link_to_destinations,is_the_gradient_amenable_to_cycling,street_offers_priority_through_intersections) \ values \ ('None', 'None', ST_GeomFromText('MULTILINESTRING((13.40568 52.51951, 13.40669 52.51879, 13.40726 52.51843, 13.40835 52.51758, 13.40918 52.51698, 13.40998 52.5164, 13.41032 52.51623, 13.41057 52.51616, 13.41177 52.51596, 13.41234 52.51586, 13.41315 52.51576, 13.41348 52.51575))',4326), '%(street)s', 'stressed', 'yes','yes', 'no')" % {'street':'Spandauer Straße and Stralauer Straße'} query = self.cl.sql(sqlstr.encode('utf-8','replace')) return query except CartoDBException as e: print ("some error ocurred", e) return e
def geodata(): # Query: INSERT INTO geopaths (the_geom) VALUES (ST_SetSRID(ST_Point(" + coords[0].toString() + ", " + coords[1].toString() + "),4326)) cl = CartoDBAPIKey(keys.cartodb_key, keys.cartodb_user) geodata = request.json for geo in geodata: try: cl.sql("INSERT INTO geopaths (the_geom) VALUES (ST_SetSRID(ST_Point(" + str(geo[0]) + ", " + str(geo[1]) + "),4326))") except CartoDBException as e: print("some error ocurred", e) return redirect(url_for('index'))
def updateMpa(m): '''Executes Mpa update/insert statements using the CartoDB API via the cartodb module. Returns mpa.mpa_id or None if error''' if not isinstance(m, Mpa): m = Mpa.objects.get(pk=m) cl = CartoDBAPIKey(API_KEY, cartodb_domain) try: cl.sql(updateMpaSQL(m)) return m.pk except CartoDBException as e: print 'CartoDB Error for mpa_id %s:' % m.pk, e return None
def flush_and_transmit(features): print "Connecting to CartoDB..." cl = CartoDBAPIKey(API_KEY, cartodb_domain) try: print "Clearing old feature set..." cl.sql("TRUNCATE TABLE warning_geom;") print "Inserting new features..." for feat in features: cl.sql("INSERT INTO warning_geom (name, description, the_geom) VALUES ('%s','%s', ST_SetSRID(ST_GeometryFromText('%s'), 4326))" % (feat['key'], feat['desc'], feat['wkt'])) except CartoDBException as e: print ("some error ocurred", e)
def upload(property_id): """Uploads the data for the supplied integer ID""" cl = CartoDBAPIKey(API_KEY, DOMAIN) x = cleaner.carto_entry(property_id) if x['values'] is not None: query = 'INSERT INTO %s %s VALUES %s' %(TABLE, x['header'], x['values']) try: print "uploading %s" % property_id cl.sql(query) except CartoDBException as e: print ("some error ocurred", e) print query
def upload(property_id): """Uploads the data for the supplied integer ID""" cl = CartoDBAPIKey(API_KEY, DOMAIN) x = cleaner.carto_entry(property_id) if x['values'] is not None: query = 'INSERT INTO %s %s VALUES %s' % (TABLE, x['header'], x['values']) try: print "uploading %s" % property_id cl.sql(query) except CartoDBException as e: print("some error ocurred", e) print query
def flush_and_transmit(features): print "Connecting to CartoDB..." cl = CartoDBAPIKey(API_KEY, cartodb_domain) try: print "Clearing old feature set..." cl.sql("TRUNCATE TABLE warning_geom;") print "Inserting new features..." for feat in features: cl.sql( "INSERT INTO warning_geom (name, description, the_geom) VALUES ('%s','%s', ST_SetSRID(ST_GeometryFromText('%s'), 4326))" % (feat['key'], feat['desc'], feat['wkt'])) except CartoDBException as e: print("some error ocurred", e)
def index_s(id): cl = CartoDBAPIKey('',cartodb_user) try: carto_geoj = json.dumps(cl.sql("SELECT * FROM points WHERE cartodb_id= %d;" % id , format='geojson')) #TODO: Parse array of strings, not array of objects as place labels labels_resp = cl.sql("SELECT pelias_label FROM points WHERE cartodb_id= %d;" % id) labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']] last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points") last_row_id = last_row_id_resp['rows'][0]['id'] except CartoDBException as e: print("some error ocurred", e) return render_template('index.html', carto_geoj=carto_geoj, carto_places=labels, last_row_id=last_row_id, id=id)
def clean(self): api_key = self.cleaned_data['api_key'] 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'] client = CartoDBAPIKey(api_key, domain) try: sites = client.sql( 'select * from {} limit 1'.format(table_name) ) except CartoDBException as e: 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_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 updateAllMpas(mpas=mpas, step=10, limit=None): '''Execute bulk Mpa update/insert statements using the CartoDB API via the cartodb module. mpas = Mpa queryset [default is all non-rejected MPAs with geom boundaries] step = number of Mpas to update per http transaction limit = only process a subset of records, useful for testing Returns list of mpa.mpa_ids that were not processed due to errors, empty list if no errors ''' cl = CartoDBAPIKey(API_KEY, cartodb_domain) nummpas = mpas.count() if limit: nummpas = min(limit, nummpas) print 'Processing %s of %s mpa records at a time' % (step, nummpas) r = range(0,nummpas+2,step) if r and r[-1] < nummpas: r.append(nummpas) error_ids = [] start = time.time() for i in xrange(0,len(r)-1): r0 = r[i] r1 = r[i+1] print 'Records [%s - %s]' % (r0, r1-1) step_ids = [] upsert = '' for m in mpas[r0:r1]: try: upsert += updateMpaSQL(m) step_ids.append(m.pk) except: error_ids.append(m.pk) print 'Skipping Mpa', m.pk # Now update this batch of records in CartoDB try: cl.sql(upsert) except CartoDBException as e: print 'CartoDB Error for mpa_ids %s:' % step_ids, e print 'Trying single updates.' for mpa_id in step_ids: try: updateMpa(mpa_id) except CartoDBException as e: error_ids.append(mpa_id) print 'CartoDB Error for mpa_id %s:' % mpa_id, e end = time.time() print 'TOTAL', end - start, 'sec elapsed' return error_ids
def insert_data1(query): """Connect to CartoDB and insert the data contained in tye query.""" cl = CartoDBAPIKey(settings.CARTODB_API_KEY1, settings.CARTODB_DOMAIN1) try: print(cl.sql(query)) except CartoDBException as e: print("some error ocurred", e) raise
class CartoDBOutput: SQL = 'INSERT INTO {table} ({column_names}) VALUES ({column_values})' def __init__(self, apikey, domain, table): self.table = table self.cl = CartoDBAPIKey(apikey, domain) def insert_line(self, lat, lon): """ Insert a line in the configured CartoDB table. Lat/Lon are in EPSG:4326. """ formatted_sql = self.SQL.format(table=self.table, column_names='the_geom', column_values='ST_SetSRID(ST_Point(' + lon + ', ' + lat + '), 4326)') self.cl.sql(formatted_sql) def truncate_table(self): self.cl.sql("TRUNCATE TABLE {table}".format(table=self.table))
def get_api(self): """ Reads secrets from local file and connects to CartoDB API. """ with open('secrets.json') as secrets_file: secrets = json.load(secrets_file) api_key = secrets['api_key'] cartodb_domain = secrets['cartodb_domain'] api = CartoDBAPIKey(api_key, cartodb_domain) try: api.sql('SELECT * FROM energy_tweets_table') except CartoDBException: raise return api
class CartoTransaction(object): #_SQL_INSERT = "insert into {0} ( the_geom, type, happened_at, message, spotid ) values( g, t, h, m, i ) select {1} where {1} not in (select spotid from {0} where spotid = {1} _SQL_INSERT = "insert into %s ( the_geom, type, happened_at, message, spotid ) values( %s, %s, %s, %s, %s);" _SQL_DELETE = "delete from %s where spotid = %s;" def __init__(self, api_key, domain, table, debug=False): self.cl = CartoDBAPIKey(api_key, domain) self.table = table self.queries = [] self.debug = debug def commit(self): if len(self.queries) == 0: return stmts = "\n".join(self.queries) query = "BEGIN;\n" query += stmts query += "COMMIT;\n" if self.debug: print query resp = self.cl.sql(query) if self.debug: print resp def _craft_insert(self, the_geom, event_type, happened_at, message, spotid): if happened_at is None: happened_at = '' if message is None: message = '' return self._SQL_INSERT % (self.table, the_geom, quote(event_type), quote(happened_at), quote(message), quote(spotid)) def insert_point(self, point): the_geom = "ST_SetSRID(ST_Point(%s,%s), 4326)" % (point.longitude, point.latitude) insert = self._craft_insert(the_geom, "checkin", point.dateTime, point.messageContent, str(point.id)) self.queries.append(insert) def update_line(self, spotid, coords, remove_first=False): geojson = json.dumps({ "type": "MultiLineString", "coordinates": [coords] }) the_geom = "ST_SetSRID(ST_GeomFromGeoJSON('%s'), 4326)" % (geojson) insert = self._craft_insert(the_geom, "track", str(datetime.datetime.now()), None, str(spotid)) delete = self._SQL_DELETE % (self.table, quote(spotid)) self.queries.append(delete) self.queries.append(insert)
def update_sites(api_key='', domain='', username='', password='', list_name='', site_type='', name_col='', code_col='', target_list=''): carto_client = CartoDBAPIKey(api_key, domain) ai_client = ActivityInfoClient(username, password) # create an index of sites by p_code existing = dict( (site['code'], dict(site, index=i)) for (i, site) in enumerate(ai_client.get_locations(target_list)) if 'code' in site) sites = carto_client.sql('select * from {}'.format(list_name)) send_message('Starting upload of {}'.format(list_name)) bad_codes = [] updated_sites = 0 for row in sites['rows']: p_code = str(row[code_col]).strip() site_name = row[name_col].encode('UTF-8') cad = ai['Cadastral Area'].find_one({'code': str(row['cad_code'])}) if cad is None: bad_codes.append(row['cad_code']) continue caz = ai['Caza'].find_one({'id': cad['parentId']}) gov = ai['Governorate'].find_one({'id': caz['parentId']}) if p_code not in existing and site_name: payload = dict(id=int(random.getrandbits(31)), locationTypeId=int(target_list), name='{}: {}'.format(site_type, site_name)[0:40], axe='{}'.format(p_code), latitude=row['latitude'], longitude=row['longitude'], workflowstatusid='validated') payload['E{}'.format(gov['levelId'])] = gov['id'] payload['E{}'.format(caz['levelId'])] = caz['id'] payload['E{}'.format(cad['levelId'])] = cad['id'] response = ai_client.call_command('CreateLocation', **payload) if response.status_code == requests.codes.no_content: updated_sites += 1 print 'Updated {}'.format(payload['name']) else: print 'Error for {}'.format(payload['name']) print 'Bad codes: {}'.format(bad_codes) print 'Updated sites: {}'.format(updated_sites) send_message('Updated {} sites'.format(updated_sites))
def all(): cl = CartoDBAPIKey('', cartodb_user) try: carto_geoj = cl.sql("SELECT * FROM points;", format='geojson') id = "All" #TODO: Parse array of strings, not array of objects as place labels labels_resp = cl.sql("SELECT pelias_label FROM points ") labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']] last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points") last_row_id = last_row_id_resp['rows'][0]['id'] except CartoDBException as e: print("some error ocurred", e) return render_template('index.html', carto_geoj=json.dumps(carto_geoj), carto_places=labels, last_row_id=last_row_id, id=id)
def update(): cl = CartoDBAPIKey('',cartodb_user) prevRow = int(request.args.get('rowid','')) try: #TODO: user parameter binding instead of string concatenation carto_geoj = cl.sql("SELECT the_geom FROM points WHERE cartodb_id > " + str(prevRow) + ";", format='geojson') #TODO: Parse array of strings, not array of objects as place labels labels_resp = cl.sql("SELECT pelias_label FROM points WHERE cartodb_id > " + str(prevRow) + ";") labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']] last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points") last_row_id = last_row_id_resp['rows'][0]['id'] except CartoDBException as e: print("some error occurred", e) return jsonify(multipoints=carto_geoj, places=labels, lastrowid=last_row_id)
def geodata(): cl = CartoDBAPIKey(cartodb_key, cartodb_user) #TODO: validate that geoJSON is valid and nonmalicious geodata = json.dumps(request.json) try: #TODO: Store places as array of string, not array of object #TODO: user parameter binding instead of string concatenation result = json.dumps(cl.sql("DROP TABLE temp ; CREATE TABLE temp AS WITH data AS (SELECT '" + geodata + "'::json AS fc) SELECT row_number() OVER () AS gid, ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom, feat->'properties' AS properties FROM (SELECT json_array_elements(fc->'features') AS feat FROM data) AS f; INSERT INTO points (the_geom, pelias_label,session_id) SELECT ST_COLLECT(ST_SETSRID(geom, 4326)), json_agg(properties), max(gid) from temp;")) except CartoDBException as e: print("some error ocurred", e) return redirect(url_for('index'))
class CartoTransaction(object): _SQL_INSERT = "insert into %s ( the_geom, type, happened_at, message ) values( %s, %s, %s, %s);" def __init__(self, api_key, domain, table, debug=False): self.cl = CartoDBAPIKey(api_key, domain) self.table = table self.queries = [] self.debug = debug def commit(self): if len(self.queries) == 0: return stmts = "\n".join(self.queries) query = "BEGIN;\n" query += stmts query += "COMMIT;\n" if self.debug: print query resp = self.cl.sql(query) if self.debug: print resp def _craft_insert(self, the_geom, event_type, happened_at, message): if happened_at is None: happened_at = '' if message is None: message = '' def quote(s): return "'" + s + "'" return self._SQL_INSERT % (self.table, the_geom, quote(event_type), quote(happened_at), quote(message)) def insert_point(self, point): the_geom = "ST_SetSRID(ST_Point(%s,%s), 4326)" % (point.longitude, point.latitude) insert = self._craft_insert(the_geom, "checkin", point.dateTime, point.message) self.queries.append(insert) def update_line(self, trip_id, coords): geojson = json.dumps({ "type": "MultiLineString", "coordinates": [coords] }) the_geom = "ST_SetSRID(ST_GeomFromGeoJSON('%s'), 4326)" % (geojson) insert = self._craft_insert(the_geom, "track", str(datetime.datetime.now()), None) self.queries.append(insert)
def new_squat(): def value_to_db(value): if value == 'on': value = 'Yes' return "'%s'" % value.replace("'", "''") cl = CartoDBAPIKey(app.config.get('CARTODB_APIKEY'), app.config.get('CARTODB_USER')) try: keys = request.form.keys() sql = 'INSERT INTO %s (%s) VALUES (%s)' % ( app.config.get('CARTODB_TABLE'), ','.join(keys + ['needs_moderation',]), ','.join([value_to_db(request.form.get(k)) for k in keys] + ['true',]), ) cl.sql(sql) except CartoDBException: traceback.print_exc() response = app.make_response('OK') if app.config.get('DEBUG'): response.headers['Access-Control-Allow-Origin'] = '*' return response
def processed_ids(): """Returns a list of property IDs that have already been pushed to CartoDB """ cl = CartoDBAPIKey(API_KEY, DOMAIN) query = 'SELECT property_id FROM %s' % TABLE try: data = cl.sql(query) except CartoDBException as e: print("some error ocurred", e) return [x['property_id'] for x in data['rows']]
def index(): cl = CartoDBAPIKey(keys.cartodb_key, keys.cartodb_user) try: carto_geoj = cl.sql("SELECT cartodb_id," + "ST_AsGeoJSON(p1) as p1," + "ST_AsGeoJSON(p2) as p2," + "ST_AsGeoJSON(p3) as p3," + "ST_AsGeoJSON(p4) as p4," + "ST_AsGeoJSON(p5) as p5 " + "FROM geopaths;") except CartoDBException as e: print("some error ocurred", e) return render_template('index.html', carto_geoj=carto_geoj)
def processed_ids(): """Returns a list of property IDs that have already been pushed to CartoDB """ cl = CartoDBAPIKey(API_KEY, DOMAIN) query = 'SELECT property_id FROM %s' % TABLE try: data = cl.sql(query) except CartoDBException as e: print ("some error ocurred", e) return [x['property_id'] for x in data['rows']]
def synchronize_entries(self, entries): cl = CartoDBAPIKey(settings.CARTODB_SYNC['API_KEY'], settings.CARTODB_SYNC['DOMAIN']) # # Apply synchronizations in the following order: insert, update, # delete. In this way, we should hopefully avoid unsafe situations # such as deleting an entry then trying to update it. # inserts = [self.get_cartodb_mapping(e.content_object) for e in entries \ if e.status == SyncEntry.PENDING_INSERT] if inserts: insert_statement = self.get_insert_statement(inserts) try: print(cl.sql(insert_statement)) except CartoDBException as e: traceback.print_exc() print('Exception while inserting:', e) updates = [self.get_cartodb_mapping(e.content_object) for e in entries \ if e.status == SyncEntry.PENDING_UPDATE] if updates: statement = self.get_update_statement(updates) print(statement) try: print(cl.sql(statement)) except CartoDBException as e: traceback.print_exc() print('Exception while updating:', e) deletes = [e for e in entries if e.status == SyncEntry.PENDING_DELETE] if deletes: statement = self.get_delete_statement(deletes) try: print(cl.sql(statement)) except CartoDBException as e: traceback.print_exc() print('Exception while deleting:', e)
def update_sites( api_key='cad5c2fd1aa5236083743f54264b203d903f3a06', domain='unhcr', table_name='imap_v5_cadcode', site_type='IS', name_col='pcodename', code_col='p_code', ): client = CartoDBAPIKey(api_key, domain) sites = client.sql( 'select * from {}'.format(table_name) ) for row in sites['rows']: p_code = row[code_col] site_name = row[name_col].encode('UTF-8') cad = ai['Cadastral Area'].find_one({'code': row['cad_code']}) caz = ai['Caza'].find_one({'id': cad['parentId']}) gov = ai['Governorate'].find_one({'id': caz['parentId']}) location = ai.locations.find_one({'p_code': p_code}) if not location: location = { "p_code": p_code, "ai_id": int(random.getrandbits(31)) # (31-bit random key), } location["ai_name"] = '{}: {}'.format(site_type, site_name) location["name"] = site_name location["type"] = site_type location["latitude"] = row['latitude'] location["longitude"] = row['longitude'] location["adminEntities"] = { str(gov['levelId']): { "id": gov['id'], "name": gov['name'] }, str(caz['levelId']): { "id": caz['id'], "name": caz['name'] }, str(cad['levelId']): { "id": cad['id'], "name": cad['name'] }, } ai.locations.update({'p_code': p_code}, location, upsert=True) print 'Updated {}: {}'.format(site_type, site_name)
def total_value(): """Returns the total value of the unclaimed property that had already been uploaded to CartoDB """ cl = CartoDBAPIKey(API_KEY, DOMAIN) query = 'SELECT value FROM %s' % TABLE try: data = cl.sql(query) except CartoDBException as e: print("some error ocurred", e) cash = sum([x['value'] for x in data['rows']]) return '{:10,.2f}'.format(cash)
def total_value(): """Returns the total value of the unclaimed property that had already been uploaded to CartoDB """ cl = CartoDBAPIKey(API_KEY, DOMAIN) query = 'SELECT value FROM %s' % TABLE try: data = cl.sql(query) except CartoDBException as e: print ("some error ocurred", e) cash = sum([x['value'] for x in data['rows']]) return '{:10,.2f}'.format(cash)
def update_sites( api_key='cad5c2fd1aa5236083743f54264b203d903f3a06', domain='unhcr', table_name='imap_v5_cadcode', site_type='IS', name_col='pcodename', code_col='p_code', ): client = CartoDBAPIKey(api_key, domain) sites = client.sql('select * from {}'.format(table_name)) for row in sites['rows']: p_code = row[code_col] site_name = row[name_col].encode('UTF-8') cad = ai['Cadastral Area'].find_one({'code': row['cad_code']}) caz = ai['Caza'].find_one({'id': cad['parentId']}) gov = ai['Governorate'].find_one({'id': caz['parentId']}) location = ai.locations.find_one({'p_code': p_code}) if not location: location = { "p_code": p_code, "ai_id": int(random.getrandbits(31)) # (31-bit random key), } location["ai_name"] = '{}: {}'.format(site_type, site_name) location["name"] = site_name location["type"] = site_type location["latitude"] = row['latitude'] location["longitude"] = row['longitude'] location["adminEntities"] = { str(gov['levelId']): { "id": gov['id'], "name": gov['name'] }, str(caz['levelId']): { "id": caz['id'], "name": caz['name'] }, str(cad['levelId']): { "id": cad['id'], "name": cad['name'] }, } ai.locations.update({'p_code': p_code}, location, upsert=True) print 'Updated {}: {}'.format(site_type, site_name)
def get_breweries(komm): cl = CartoDBAPIKey(CDB_KEY, CDB_DOMAIN) try: res = cl.sql(''' SELECT count(*) FROM osm_breweries WHERE ST_Contains(%s, the_geom) ''' % geojson_sql(komm['geometry'])) return res['rows'][0]['count'] except CartoDBException: return -1
def update(): cl = CartoDBAPIKey('', cartodb_user) prevRow = int(request.args.get('rowid', '')) try: #TODO: user parameter binding instead of string concatenation carto_geoj = cl.sql("SELECT the_geom FROM points WHERE cartodb_id > " + str(prevRow) + ";", format='geojson') #TODO: Parse array of strings, not array of objects as place labels labels_resp = cl.sql( "SELECT pelias_label FROM points WHERE cartodb_id > " + str(prevRow) + ";") labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']] last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points") last_row_id = last_row_id_resp['rows'][0]['id'] except CartoDBException as e: print("some error occurred", e) return jsonify(multipoints=carto_geoj, places=labels, lastrowid=last_row_id)
def update(): cl = CartoDBAPIKey(keys.cartodb_key, keys.cartodb_user) prevRow = request.args.get('rowid','') try: carto_geoj = cl.sql("SELECT cartodb_id," + "ST_AsGeoJSON(p1) as p1," + "ST_AsGeoJSON(p2) as p2," + "ST_AsGeoJSON(p3) as p3," + "ST_AsGeoJSON(p4) as p4," + "ST_AsGeoJSON(p5) as p5 " + "FROM geopaths " + "WHERE cartodb_id > " + str(prevRow) + ";") except CartoDBException as e: print("some error occurred", e) return jsonify(carto_geoj)
def index(): cl = CartoDBAPIKey(keys.cartodb_key, keys.cartodb_user) try: carto_geoj = cl.sql("SELECT cartodb_id," + "ST_AsGeoJSON(p1) as p1," + "ST_AsGeoJSON(p2) as p2," + "ST_AsGeoJSON(p3) as p3," + "ST_AsGeoJSON(p4) as p4," + "ST_AsGeoJSON(p5) as p5 " + "FROM geopaths;") last_row_id = max([row['cartodb_id'] for row in carto_geoj['rows']]) print("Length of database is: ", len(carto_geoj['rows'])) except CartoDBException as e: print("some error ocurred", e) return render_template('index.html', carto_geoj=carto_geoj, last_row_id=last_row_id)
def geodata(): cl = CartoDBAPIKey(cartodb_key, cartodb_user) #TODO: validate that geoJSON is valid and nonmalicious geodata = json.dumps(request.json) try: #TODO: Store places as array of string, not array of object #TODO: user parameter binding instead of string concatenation result = json.dumps( cl.sql( "DROP TABLE temp ; CREATE TABLE temp AS WITH data AS (SELECT '" + geodata + "'::json AS fc) SELECT row_number() OVER () AS gid, ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom, feat->'properties' AS properties FROM (SELECT json_array_elements(fc->'features') AS feat FROM data) AS f; INSERT INTO points (the_geom, pelias_label,session_id) SELECT ST_COLLECT(ST_SETSRID(geom, 4326)), json_agg(properties), max(gid) from temp;" )) except CartoDBException as e: print("some error ocurred", e) return redirect(url_for('index'))
class CartoTransaction(object): _SQL_INSERT = "insert into %s ( the_geom, type, happened_at, message ) values( %s, %s, %s, %s);" def __init__(self, api_key, domain, table, debug = False): self.cl = CartoDBAPIKey(api_key, domain) self.table = table self.queries = [] self.debug = debug def commit(self): if len(self.queries) == 0: return stmts = "\n".join(self.queries) query = "BEGIN;\n" query += stmts query += "COMMIT;\n" if self.debug: print query resp = self.cl.sql(query) if self.debug: print resp def _craft_insert(self, the_geom, event_type, happened_at, message): if happened_at is None: happened_at = '' if message is None: message = '' def quote(s): return "'" + s + "'" return self._SQL_INSERT % (self.table, the_geom , quote(event_type), quote(happened_at), quote(message)) def insert_point(self, point): the_geom = "ST_SetSRID(ST_Point(%s,%s), 4326)" %(point.longitude, point.latitude) insert = self._craft_insert(the_geom, "checkin", point.dateTime, point.message) self.queries.append(insert) def update_line(self, trip_id, coords): geojson = json.dumps({ "type" : "MultiLineString", "coordinates": [coords] }) the_geom = "ST_SetSRID(ST_GeomFromGeoJSON('%s'), 4326)" % (geojson) insert = self._craft_insert(the_geom, "track", str(datetime.datetime.now()), None) self.queries.append(insert)
class CartoTransaction(object): #_SQL_INSERT = "insert into {0} ( the_geom, type, happened_at, message, spotid ) values( g, t, h, m, i ) select {1} where {1} not in (select spotid from {0} where spotid = {1} _SQL_INSERT = "insert into %s ( the_geom, type, happened_at, message, spotid ) values( %s, %s, %s, %s, %s);" _SQL_DELETE = "delete from %s where spotid = %s;" def __init__(self, api_key, domain, table, debug = False): self.cl = CartoDBAPIKey(api_key, domain) self.table = table self.queries = [] self.debug = debug def commit(self): if len(self.queries) == 0: return stmts = "\n".join(self.queries) query = "BEGIN;\n" query += stmts query += "COMMIT;\n" if self.debug: print query resp = self.cl.sql(query) if self.debug: print resp def _craft_insert(self, the_geom, event_type, happened_at, message, spotid): if happened_at is None: happened_at = '' if message is None: message = '' return self._SQL_INSERT % (self.table, the_geom , quote(event_type), quote(happened_at), quote(message), quote(spotid)) def insert_point(self, point): the_geom = "ST_SetSRID(ST_Point(%s,%s), 4326)" %(point.longitude, point.latitude) insert = self._craft_insert(the_geom, "checkin", point.dateTime, point.messageContent, str(point.id)) self.queries.append(insert) def update_line(self, spotid, coords, remove_first=False): geojson = json.dumps({ "type" : "MultiLineString", "coordinates": [coords] }) the_geom = "ST_SetSRID(ST_GeomFromGeoJSON('%s'), 4326)" % (geojson) insert = self._craft_insert(the_geom, "track", str(datetime.datetime.now()), None, str(spotid)) delete = self._SQL_DELETE % (self.table, quote(spotid)) self.queries.append(delete) self.queries.append(insert)
def describe(table_name): cl = CartoDBAPIKey(CARTODB_API_KEY, cartodb_domain) try: resp = cl.sql( "SELECT * FROM information_schema.columns WHERE table_name ='{}'". format(table_name)) except CartoDBException as e: print("some error ocurred", e) #print resp.keys() #for c in resp['fields']: # print c, resp['fields'][c] for c in resp['rows']: #print "-----------" #for d in ['column_name','udt_name','column_default']: #for d in c: # if c[d] != None: # print d, c[d] item = "{} {}".format(c['column_name'], c['udt_name']) if c['column_default']: item = item + " default " + c['column_default'] print item
def get_foot_trails(komm): cl = CartoDBAPIKey(CDB_KEY, CDB_DOMAIN) try: gj = geojson_sql(komm['geometry']) query = ''' SELECT SUM( ST_Length( ST_INTERSECTION( s.the_geom::geography, %s::geography ) ) ) / 1000 as len FROM fotrute s WHERE ST_Intersects(%s, s.the_geom); ''' % (gj, gj) res = cl.sql(query) length = res['rows'][0]['len'] return round(length) if length is not None else 0 except CartoDBException: return -1
from cartodb import CartoDBAPIKey, CartoDBException user = '******' API_KEY ='a593f743aebe095ab7c75d230c71e2fd13e5bc24' cartodb_domain = 'mik3cap' cl = CartoDBAPIKey(API_KEY, cartodb_domain) try: print cl.sql('SELECT * FROM user_info') except CartoDBException as e: print ("some error ocurred", e) try: print cl.sql('SELECT * FROM movie') except CartoDBException as e: print ("some error ocurred", e) try: print cl.sql('SELECT * FROM venue') except CartoDBException as e: print ("some error ocurred", e) try: print cl.sql('SELECT * FROM plan') except CartoDBException as e: print ("some error ocurred", e) # INSERT INTO user_info (email, fb_user_id, full_name) VALUES (%s, %s, %s)
def update_sites_from_cartodb(carto_table): client = CartoDBAPIKey(carto_table.api_key, carto_table.domain) sites_created = sites_updated = sites_not_added = 0 try: # query for cartodb qry = '' if carto_table.parent_code_col and carto_table.parent: qry = 'select st_AsGeoJSON(the_geom) as the_geom, {}, {}, {} from {}'.format( carto_table.name_col, carto_table.pcode_col, carto_table.parent_code_col, carto_table.table_name) else: qry = 'select st_AsGeoJSON(the_geom) as the_geom, {}, {} from {}'.format( carto_table.name_col, carto_table.pcode_col, carto_table.table_name) sites = client.sql(qry) except CartoDBException as e: logging.exception("CartoDB exception occured", exc_info=True) else: for row in sites['rows']: pcode = str(row[carto_table.pcode_col]).strip() site_name = row[carto_table.name_col] if not site_name or site_name.isspace(): logger.warning("No name for location with PCode: {}".format(pcode)) sites_not_added += 1 continue site_name = site_name.encode('UTF-8') parent = None parent_code = None parent_instance = None # attempt to reference the parent of this location if carto_table.parent_code_col and carto_table.parent: try: parent = carto_table.parent.__class__ parent_code = row[carto_table.parent_code_col] parent_instance = Location.objects.get(p_code=parent_code) except Exception as exp: msg = " " if exp is parent.MultipleObjectsReturned: msg = "{} locations found for parent code: {}".format( 'Multiple' if exp is parent.MultipleObjectsReturned else 'No', parent_code ) else: msg = exp.message logger.warning(msg) sites_not_added += 1 continue # create the actual location or retrieve existing based on type and code succ, sites_not_added, sites_created, sites_updated = create_location(pcode, carto_table, parent, parent_instance, site_name, row, sites_not_added, sites_created, sites_updated) return "Table name {}: {} sites created, {} sites updated, {} sites skipped".format( carto_table.table_name, sites_created, sites_updated, sites_not_added )
import time from polyline.codec import PolylineCodec from geojson import Feature, LineString ## Strava connection client = Client() client.access_token = 'd47099a29b2f3539e1c21af6d820e33a109a079e' ## CartoDB connection API_KEY = 'cad54ea0c580a0c554b9e9562157e7c9bd9f37b0' cartodb_domain = 'geodarcy' cl = CartoDBAPIKey(API_KEY, cartodb_domain) ## remove duplicate rows cl.sql( 'DELETE FROM wbstrava WHERE cartodb_id IN (SELECT cartodb_id FROM (SELECT cartodb_id, ROW_NUMBER() OVER (partition BY segment_id ORDER BY cartodb_id) AS rnum FROM wbstrava) t WHERE t.rnum > 1);' ) ## find segments with no geometry queryResult = cl.sql('select segment_id from wbstrava where the_geom is null') currentSegments = [x['segment_id'] for x in queryResult['rows']] for id in currentSegments: try: segmentGeojson = PolylineCodec().decode( client.get_segment(id).map.polyline) flippedGeojson = [[x[1], x[0]] for x in segmentGeojson] inputGeojson = '{"coordinates": ' + str( flippedGeojson ) + ', "type": "LineString", "crs":{"type":"name","properties":{"name":"EPSG:4326"}}}' cl.sql('UPDATE wbstrava SET the_geom = ST_GeomFromGeoJSON(\'' + inputGeojson + '\') WHERE segment_id=' + str(id))
sql_query = 'INSERT INTO humanitarian_response (the_geom, id, label, cluster_id, cluster_label, location_id, location_label) VALUES (' # Add our values to the statement, making sure that we wrap string values # in quotes # 4/8, EB: Fixed bug on next line that rounded x/y down to very low # precision sql_query = sql_query + "'SRID=4326; POINT (%f %f)', '%s', '%s', '%s', '%s', '%s', '%s'" % (long, lat, id, label, cluster_id, cluster_label, location_id, location_label) sql_query = sql_query + ')' return str(sql_query) def main(): # define a variable to hold the source URL urlData = "http://www.humanitarianresponse.info/api/v1.0/assessments" # Open the URL and read the data webUrl = urllib2.urlopen(urlData) if (webUrl.getcode() == 200): data = webUrl.read() # print out our customized results getResults(data) # This is where you call insert_into_cartodb() insert_into_cartodb(sql_query) else: print "Received an error from server, cannot retrieve results " + str(webUrl.getcode()) if __name__ == "__main__": main() try: cl.sql('DELETE FROM humanitarian_response WHERE cartodb_id NOT IN (SELECT MIN(cartodb_id) FROM humanitarian_response GROUP BY id)') except CartoDBException as e: print ("some error ocurred", e)
params={"api_key": API_KEY}) response_data = r.json() state = response_data["state"] print response_data if state == "failure": sys.exit(1) table_name = response_data["table_name"] print "TABLE_NAME" # 2) Append new data from temp table to real table cl = CartoDBAPIKey(API_KEY, "dcarrion") try: print cl.sql("CREATE SEQUENCE serial_%s START %s" % (current_cartodb_id, current_cartodb_id)) except CartoDBException as e: pass try: print cl.sql( "INSERT INTO dcarrion.elecciones_partidos (actor_displayname,actor_followerscount,actor_friendscount," "actor_id,actor_image,actor_languages,actor_link,actor_links,actor_listedcount,actor_location,actor_objecttype," "actor_postedtime,actor_preferredusername,actor_statusescount,actor_summary,actor_twittertimezone,actor_utcoffset," "actor_verified,body,category_name,category_terms,favoritescount,generator_displayname,generator_link,geo,gnip,id," "inreplyto_link,link,location_displayname,location_geo,location_link,location_name,location_objecttype," "location_streetaddress,object_id,object_link,object_objecttype,object_postedtime,object_summary,object_type," "postedtime,provider_displayname,provider_link,provider_objecttype,retweetcount,the_geom,twitter_entities," "twitter_filter_level,twitter_lang,verb,cartodb_id) SELECT actor_displayname,actor_followerscount,actor_friendscount," "actor_id,actor_image,actor_languages,actor_link,actor_links,actor_listedcount,actor_location,actor_objecttype," "actor_postedtime,actor_preferredusername,actor_statusescount,actor_summary,actor_twittertimezone,actor_utcoffset,"
'&fcode=AIRP&formatted=tr&lang=iata&username=demo') geoinfo = r.json() print geoinfo if geoinfo['totalResultsCount'] == 0: raise CartoDBException('code ' + iata_code + 'not geocodeable') else: lat = geoinfo['geonames'][0]['lat'] lng = geoinfo['geonames'][0]['lng'] wkt = "'POINT(" + lng + ' ' + lat + ")'" return wkt try: print "Grabbing destinations" iata_dest = cl.sql( "select distinct destination_city from athompson.flight_cube_query_09_06_15_dests" ) print "Dests grabbed!" except CartoDBException as e: print("some error occured with dest grab", e) for row in iata_dest['rows']: iata_codes_dest.append(row['destination_city']) try: print "Geocoding dests" for code in iata_codes_dest: if code in []: print "passing " + code pass else:
from cartodb import CartoDBAPIKey, CartoDBException ''' UPDATE athompson.flight_cube_query_09_06_15 SET origin_city = 'ISA' where city_pair like 'ISA%'; select * from athompson.flight_cube_query_09_06_15 where origin_city is null order by city_pair asc ''' API_KEY = 'REMOVE' cartodb_domain = 'athompson' cl = CartoDBAPIKey(API_KEY, cartodb_domain) iata_codes_origin = [] iata_codes_dest = [] try: print "Grabbing origins" pair_origin = cl.sql( "select distinct substring(city_pair from '^.{3}') from athompson.flight_cube_query_09_06_15" ) print "Origins grabbed!" except CartoDBException as e: print("some error occured with origin grab", e) for row in pair_origin['rows']: iata_codes_origin.append(row['substring']) try: print "Origins processed, pushing updates to cartodb" for code in iata_codes_origin: print "updating origin " + code update_origin = cl.sql( "update athompson.flight_cube_query_09_06_15 set origin_city = '" + code + "' where city_pair like '" + code + "%'")
print "geocoding iata airport: " + iata_code r = requests.get('http://ws.geonames.org/searchJSON?name_equals=' + iata_code + '&fcode=AIRP&formatted=tr&lang=iata&username=demo') geoinfo = r.json() print geoinfo if geoinfo['totalResultsCount'] == 0: raise CartoDBException('code ' + iata_code + 'not geocodeable') else: lat = geoinfo['geonames'][0]['lat'] lng = geoinfo['geonames'][0]['lng'] wkt = "'POINT(" + lng + ' ' + lat + ")'" return wkt try: print "Grabbing origins" iata_origin = cl.sql("select distinct origin_city from athompson.flight_cube_query_09_06_15_origins") print "Origins grabbed!" except CartoDBException as e: print ("some error occured with origin grab", e) for row in iata_origin['rows']: iata_codes_origin.append(row['origin_city']) try: print "Geocoding origins" for code in iata_codes_origin: if code in ['TEV', 'OSB']: print "passing " + code pass else: print "geocoding origin " + code
continue tmp_table_name = response_data["table_name"] # Just in case it changed during import print ("TMP_TABLE_NAME", tmp_table_name) # 2) Append new data from temp table to real table try: print (cl.sql("INSERT INTO {account_name}.{table_name} (actor_displayname,actor_followerscount,actor_friendscount," "actor_id,actor_image,actor_languages,actor_link,actor_links,actor_listedcount,actor_location,actor_objecttype," "actor_postedtime,actor_preferredusername,actor_statusescount,actor_summary,actor_twittertimezone,actor_utcoffset," "actor_verified,body,category_name,category_terms,favoritescount,generator_displayname,generator_link,geo,gnip,id," "inreplyto_link,link,location_displayname,location_geo,location_link,location_name,location_objecttype," "location_streetaddress,object_id,object_link,object_objecttype,object_postedtime,object_summary,object_type," "postedtime,provider_displayname,provider_link,provider_objecttype,retweetcount,the_geom,twitter_entities," "twitter_filter_level,twitter_lang,verb,cartodb_id) SELECT actor_displayname,actor_followerscount,actor_friendscount," "actor_id,actor_image,actor_languages,actor_link,actor_links,actor_listedcount,actor_location,actor_objecttype," "actor_postedtime,actor_preferredusername,actor_statusescount,actor_summary,actor_twittertimezone,actor_utcoffset," "actor_verified,body,category_name,category_terms,favoritescount,generator_displayname,generator_link,geo,gnip,id," "inreplyto_link,link,location_displayname,location_geo,location_link,location_name,location_objecttype," "location_streetaddress,object_id,object_link,object_objecttype,object_postedtime,object_summary,object_type," "postedtime,provider_displayname,provider_link,provider_objecttype,retweetcount,the_geom,twitter_entities," "twitter_filter_level,twitter_lang,verb,nextval('{table_name}_cartodb_id_seq') as cartodb_id " "FROM {account_name}.{tmp_table_name}".format(table_name=TABLE_NAME, tmp_table_name=tmp_table_name, account_name=ACCOUNT_NAME))) except CartoDBException as e: print ("some error ocurred", e) continue with open("{table_name}_next.conf".format(table_name=TABLE_NAME), "w") as conf: conf.write(json.dumps({"start_timestamp": end_timestamp.strftime("%Y%m%d%H%M%S")})) # 3) Delete temporary table
from cartodb import CartoDBAPIKey, CartoDBException API_KEY ='YOUR_CARTODB_API_KEY' cartodb_domain = 'jatorre' cl = CartoDBAPIKey(API_KEY, cartodb_domain) try: print cl.sql('select * from mytable') except CartoDBException as e: print ("some error ocurred", e)from cartodb import CartoDBAPIKey, CartoDBException API_KEY ='YOUR_CARTODB_API_KEY' cartodb_domain = 'jatorre' cl = CartoDBAPIKey(API_KEY, cartodb_domain) try: print cl.sql('select * from mytable') except CartoDBException as e: print ("some error ocurred", e)
#clear_table.py from cartodb import CartoDBAPIKey, CartoDBException user = '******' API_KEY ='adfa3ceeaf052aaa42e2e2ec96d4df80ea42e950' cartodb_domain = 'lauragator' cl = CartoDBAPIKey(API_KEY, cartodb_domain) sql_statement = "DELETE FROM spatial" try: print cl.sql(sql_statement) except CartoDBException as e: print ("some error ocurred", e)
from cartodb import CartoDBAPIKey, CartoDBException API_KEY ='' cartodb_domain = 'palonso0vf' cl = CartoDBAPIKey(API_KEY, cartodb_domain) #try: #except CartoDBException as e: # print("some error ocurred", e) #Peticion SQL petition = ("""SELECT nameunit, ST_Area(the_geom::geography)/1000000 area FROM ign_spanish_adm3_municipalities where nameunit like '%Valladolid%';""") catch = (cl.sql(petition)) for f in catch['rows']: print f
def truncate_table1(table=settings.CARTODB_TABLE1): query = 'TRUNCATE %s' % table cl = CartoDBAPIKey(settings.CARTODB_API_KEY1, settings.CARTODB_DOMAIN1) cl.sql(query)
def delete_for_date1(target_date, table): print("\n=== Deleting existing data for date %s... ===" % target_date) date_formatted = target_date.strftime('%Y-%m-%d') query = "DELETE FROM %s WHERE date='%s'" % (table, date_formatted) cl = CartoDBAPIKey(settings.CARTODB_API_KEY1, settings.CARTODB_DOMAIN1) cl.sql(query)
from cartodb import CartoDBAPIKey, CartoDBException import urllib2 api = slumber.API("http://ws.usig.buenosaires.gob.ar/rest/") user = '******' API_KEY = '5ebd92316d11854603dd60b67019350c7412de80' cartodb_domain = 'gcba' cl = CartoDBAPIKey(API_KEY, cartodb_domain) batch_size = 100 # # print answer['resultado']['y'] # print answer['resultado']['x'] answer = cl.sql( 'SELECT cartodb_id, latitude, longitude, coord_x, coord_y FROM censo_arbolado_recoleta' ) batch_sql = "" batch_count = 0 for row in answer['rows']: batch_count += 1 if not row['latitude']: usig_answer = api.convertir_coordenadas.get(x=row['coord_x'], y=row['coord_y'], output='lonlat') lng = usig_answer['resultado']['x'] lat = usig_answer['resultado']['y'] sql_text = "UPDATE censo_arbolado_recoleta SET longitude='%s', latitude='%s' WHERE cartodb_id = %s ;" % ( lng, lat, row['cartodb_id']) print sql_text batch_sql += sql_text
if __name__ == '__main__': # # For each row in whatever is returned from get_data(), create an SQL # INSERT statement for that data, then insert it into CartoDB. # for media in Instagram_API(min_id=get_max_id()): try: # ONLY look at pieces of media with a location if media.location: sql_query = create_sql_statement( media.location.point.longitude, media.location.point.latitude, media.images['standard_resolution'].url, media.user.username, media.id, media.link, media.created_time ) # print(sql_query) # This is where you call insert_into_cartodb() insert_into_cartodb(sql_query) except exceptions.AttributeError: pass if __name__ == '__main__': try: cl.sql('DELETE FROM instagram_media WHERE cartodb_id NOT IN (SELECT MIN(cartodb_id) FROM instagram_media GROUP BY url)') except CartoDBException as e: print ("some error ocurred", e)