def setup_cartodb_table(self, table_name): """ Set up the schema we need on CartoDB table 'table_name' """ print "Logging in to CartoDB using the settings in 'cartodb.json'." try: cartodb_settings = simplejson.loads( codecs.open('cartodb.json', encoding='utf-8').read(), encoding='utf-8') except Exception as ex: logging.error("Could not load CartoDB setting file 'cartodb.json': %s" % ex) exit(1) print "Updating table %s." % table_name cdb = CartoDB( cartodb_settings['CONSUMER_KEY'], cartodb_settings['CONSUMER_SECRET'], cartodb_settings['user'], cartodb_settings['password'], cartodb_settings['cartodb_domain'] ) for field in self.schema.keys(): column_schema = self.schema[field] cdb.sql("ALTER TABLE %s ADD COLUMN %s %s %s" % ( table_name, field, column_schema['type'] if column_schema['type'] else "TEXT", "NOT NULL" if column_schema['required'] else "NULL" )) print "\tField '%s' created." % field return
def setUp(self): self.client = CartoDB(CONSUMER_KEY, CONSUMER_SECRET, user, password, user)
# TO RUN # > virtualenv env # > . env/bin/activate # > pip install oauth2 # > pip install cartodb # # FILL IN THINGS BELOW # > python oauth_test.py from cartodb import CartoDB, CartoDBException import httplib2 import oauth2 as oauth if __name__ == '__main__': user = '' password = '' CONSUMER_KEY= '' CONSUMER_SECRET= '' cl = CartoDB(CONSUMER_KEY, CONSUMER_SECRET, user, password, 'simon') try: print cl.sql('select * from do_not_exist') except CartoDBException as e: print ("some error ocurred", e) print cl.sql('select * from table');
from cartodb import CartoDB import codecs import simplejson settings = simplejson.loads( codecs.open('cartodb.json', encoding='utf-8').read(), encoding='utf-8' ) cdb = CartoDB( settings['CONSUMER_KEY'], settings['CONSUMER_SECRET'], settings['user'], settings['password'], settings['user'], host=settings['domain'] ) print "Which database and user are we running under with GET?" sql = "SELECT current_database(), user;" response = cdb.sql(sql, do_post=False) print "Response: " + response.__str__() print "\tDatabase: " + response['rows'][0]['current_database'] print "\tUser: "******"SELECT current_database(), user;" print "Which database and user are we running under with POST?" print "Sending POST request: " + sql
from collections import deque from cartodb import CartoDB, CartoDBException import secret user = secret.user password = secret.password CONSUMER_KEY = secret.CONSUMER_KEY CONSUMER_SECRET = secret.CONSUMER_SECRET cartodb_domain = user OSM_CHANGES_RSS = 'http://www.openstreetmap.org/browse/changesets/feed' # connect to cartodb cl = CartoDB(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) etag = None modified = None ids = deque(maxlen=100) while True: d = feedparser.parse(OSM_CHANGES_RSS, etag=etag, modified=modified) modified = modified etag = d.etag sql = [] for e in d.entries: if e.id not in ids: ids.append(e.id) # u'45.2126441 -0.3701032 45.2126441 -0.3701032'
def uploadGeoJSONEntry(entry, table_name): """Uploads a single GeoJSON entry to any URL capable of accepting SQL statements. We convert the GeoJSON into a single INSERT SQL statement and send it. Arguments: entry: A GeoJSON row entry containing geometry and field information for upload. table_name: The name of the table to add this GeoJSON entry to. query_string: A URL format string containing a '%s', which will be replaced with a uri-encoded SQL string. Returns: none. """ global cartodb_settings cdb = CartoDB( cartodb_settings['CONSUMER_KEY'], cartodb_settings['CONSUMER_SECRET'], cartodb_settings['user'], cartodb_settings['password'], cartodb_settings['cartodb_domain'] ) # Get the fields and values ready to be turned into an SQL statement properties = entry['properties'] fields = properties.keys() # oauth2 has cannot currently send UTF-8 data in the URL. So we go # back to ASCII at this point. This can be fixed by waiting for oauth2 # to be fixed (https://github.com/simplegeo/python-oauth2/pull/91 might # be a fix), or we can clone our own python-oauth2 and fix that. # Another alternative would be to use POST and multipart/form-data, # which is probably the better long term solution anyway. values = [unicode(v).encode('ascii', 'replace') for v in properties.values()] # 'values' will be in the same order as 'fields' # as long as there are "no intervening modifications to the # dictionary" [http://docs.python.org/library/stdtypes.html#dict] # Determine the geometry for this object, by converting the GeoJSON # geometry representation into WKT. # geometry = "SRID=4326;" + shapely.geometry.asShape(entry['geometry']).wkb geometry = shapely.geometry.asShape(entry['geometry']).wkb.encode('hex') # We can use SRID=4326 because we loaded it in that SRID from # ogr2ogr. # Generate a 'tag', by calculating a SHA-1 hash of the concatenation # of the current time (in seconds since the epoch) and the string # representation of the property values in the order that Python is # using on our system. The 40-hexadecimal character hash digest so # produced is prepended with the string 'tag_', since only a valid # identifier (starting with a character) may be a tag. # # So as to have smaller requests, we use 8 character tags (from # position 20-28 of the SHA-1 hexdigest). tag = "$tag_" + hashlib.sha1( time.time().__str__() + properties.values().__str__() ).hexdigest()[20:28] + "$" # Turn the fields and values into an SQL statement. sql = "INSERT INTO %(table_name)s (the_geom, %(cols)s) VALUES (%(st_multi)s(GeomFromWKB(decode(%(geometry)s, 'hex'), 4326)), %(values)s)" % { 'table_name': table_name, 'geometry': tag + geometry + tag, 'cols': ", ".join(fields), 'st_multi': "ST_Multi" if (entry['geometry']['type'] == 'Polygon') else "", 'values': tag + (tag + ", " + tag).join(values) + tag } print "Sending SQL: [%s]" % sql print cdb.sql(sql)
# TO RUN # > virtualenv env # > . env/bin/activate # > pip install oauth2 # > pip install cartodb # # FILL IN THINGS BELOW # > python oauth_test.py from cartodb import CartoDB, CartoDBException import httplib2 import oauth2 as oauth if __name__ == '__main__': user = '' password = '' CONSUMER_KEY = '' CONSUMER_SECRET = '' cl = CartoDB(CONSUMER_KEY, CONSUMER_SECRET, user, password, 'simon') try: print cl.sql('select * from do_not_exist') except CartoDBException as e: print("some error ocurred", e) print cl.sql('select * from table')