def __init__(self, api_key, domain, table, debug=False): self.cl = CartoDBAPIKey(api_key, domain) self.table = table self.queries = [] self.debug = debug
time.sleep(5) r = requests.get(IMPORT_API_ENDPOINT + item_queue_id, 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,"
#!/usr/bin/env python from cartodb import CartoDBAPIKey, CartoDBException import requests from my_keys import CARTODB_API_KEY, cartodb_domain, DATAPOINT_API_KEY cl = CartoDBAPIKey(CARTODB_API_KEY, cartodb_domain) srid = 4326 # Spatial ref ID EPSG:4326 DATAPOINT_URL = 'http://datapoint.metoffice.gov.uk' datapoint_obs_path = 'public/data/val/wxobs/all/json/all' datapoint_fcs_path = 'public/data/val/wxfcs/all/json/all' col_names_obs = { 'W': 'weather_type', 'V': 'visibility', 'T': 'temperature', 'S': 'wind_speed', 'P': 'pressure', 'G': 'wind_gust', 'D': 'wind_direction', 'Pt': 'pressure_tendency', 'Dp': 'dew_point', 'H': 'screen_relative_humidity'} col_names_fcs = { 'W': 'weather_type', 'V': 'visibility', 'T': 'temperature', 'S': 'wind_speed', 'U': 'max_uv_index', 'G': 'wind_gust',
def cartodb_cursor(): return CartoDBAPIKey(API_KEY, CARTODB_DOMAIN)
def __init__(self, apikey, domain, table): self.table = table self.cl = CartoDBAPIKey(apikey, domain)
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 )
def __init__(self, user_name=None, api_key=None): self.client = CartoDBAPIKey(api_key, user_name) self.user_name = user_name
def setUp(self): self.client = CartoDBAPIKey(API_KEY, user, api_version='v2')
import os import dateutil.parser from datetime import datetime, timedelta from powertrack.api import * from cartodb import CartoDBAPIKey, CartoDBException, FileImport config = ConfigParser.RawConfigParser() config.read("champions.conf") ACCOUNT_NAME = config.get('cartodb', 'account_name') API_KEY = config.get('cartodb', 'api_key') TABLE_NAME = config.get('cartodb', 'table_name') p = PowerTrack(api="search") cdb = CartoDBAPIKey(API_KEY, ACCOUNT_NAME) categories = [ [ "#HalaMadrid", "#APorLaUndecima", "#RMUCL", "#RMFans", "#RMCity", "#RmHistory", "@realmadrid", "@realmadriden", "@readmadridfra", "@realmadridarab", "@realmadridjapan" ], ["#LaUndecima"], ["Cristiano Ronaldo", "@Cristiano"], ["Gareth Bale", "@GarethBale11"], ["@jamesdrodriguez"], ["Sergio Ramos", "@SergioRamos"], ["Keylor Navas", "@NavasKeylor"], ["@MarceloM12"], ["Benzema", "@Benzema"],
def cartoCred(): cl = CartoDBAPIKey(API_KEY, cartodb_domain) return cl
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))
if not API_KEY or not DOMAIN: raise EnvironmentError( 'Please set both CARTODB_API_KEY and CARTODB_DOMAIN environment variables.' ) def quote(value): return "'%s'" % value values = [] with open('../data/pred_pelagia.csv', 'rb') as csvfile: reader = csv.DictReader(csvfile) for row in reader: value = ','.join( [row['long'], row['lat'], row['pelagia'], quote(row['the_geom'])]) values.append('(' + value + ')') cl = CartoDBAPIKey(API_KEY, DOMAIN) query = """ INSERT INTO pred_pelagia (long, lat, pelagia, the_geom) VALUES %s; """ % ', '.join(values) try: print cl.sql(query) except CartoDBException as e: print("some error ocurred", e)
def drop( table_name, 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)
#!/home/eleonore/virtenvs/cartodb/bin/python2.7 # -*- coding: utf-8 -*- import sys from cartodb import CartoDBAPIKey, CartoDBException if __name__ == "__main__": api_key, cartodb_domain = sys.argv[1], sys.argv[2] cartodb_api = CartoDBAPIKey(api_key, cartodb_domain) import_data = cartodb_api.req("https://" + cartodb_domain + ".cartodb.com/api/v2/sql?q=", http_method="GET", files="ebola_outbreaks_before_2014") import_data.run() print import_data.success
def truncate_table(table=settings.CARTODB_TABLE): query = 'TRUNCATE %s' % table cl = CartoDBAPIKey(settings.CARTODB_API_KEY, settings.CARTODB_DOMAIN) cl.sql(query)
def setUp(self): self.client = CartoDBAPIKey(API_KEY, user)
def delete_for_date(target_date, table=settings.CARTODB_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_KEY, settings.CARTODB_DOMAIN) cl.sql(query)
from _settings import * from cartodb import CartoDBAPIKey, CartoDBException, FileImport # gitignored secret info from _secret_info import cartodb_domain, API_KEY cl = CartoDBAPIKey(API_KEY, cartodb_domain) def cdbRunSQL(sqlStatement): #https://github.com/CartoDB/cartodb-python try: print(cl.sql(sqlStatement)) except CartoDBException as e: print("some error ocurred", e) outTable = 'nycb2010_treesdata' sql_statement = "CREATE TABLE " + outTable + """ AS SELECT nycb2010.the_geom, nycb2010.bctcb2010, cb2010tree.count1995, cb2010tree.count2005, cb2010tree.count2015, cb2010tree.pctchange9505cnt, cb2010tree.pctchange0515cnt, cb2010tree.pctchange9515cnt, cb2010tree.cbareasqft, cb2010tree.cbareasqmi, cb2010tree.treedensqmi1995, cb2010tree.treedensqmi2005, cb2010tree.treedensqmi2015, cb2010tree.pctchange9505den, cb2010tree.pctchange0515den,
import time import urllib2 import json from cartodb import CartoDBAPIKey, CartoDBException from secret import API_KEY URL = 'http://services.redbullstratos.com/LiveData/Get' cl = CartoDBAPIKey(API_KEY, 'javi') #create_table(); positions = {} while True: try: data = json.loads(urllib2.urlopen(URL).read()) telemetry = data['Telemetry'] if telemetry: telemetry.sort(lambda x, y: x['Id'] - y['Id']) for t in telemetry: if t['Id'] not in positions: positions[t['Id']] = t t['the_geom'] = "ST_GeomFromText('POINT(%s %s)', 4326)" % ( t['Longitude'], t['Latitude']) del t['Latitude'] del t['Longitude'] del t['CompassDirection'] cl.sql("insert into stratos (%s) values (%s)" % (','.join( map(str, t.keys())), ','.join(map(str, t.values())))) except Exception, e: print e pass