Python CartoDBAPIKey.sql方法代码示例

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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)

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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'))

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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)

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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)   

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
    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

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
    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

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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))

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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)

# 需要导入模块: from cartodb import CartoDBAPIKey [as 别名]
# 或者: from cartodb.CartoDBAPIKey import sql [as 别名]
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'))