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
