Python cartodb.CartoDBAPIKey类代码示例

    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 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

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 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 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 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'))

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 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 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 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 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 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