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
# 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');
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
response = cdb.sql(sql, do_post=True)
print "Response: " + response.__str__()
print "\tDatabase: " + response['rows'][0]['current_database']
print "\tUser: " + response['rows'][0]['current_user']
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'
if 'georss_box' in e:
bbox = e['georss_box']
latmin, lonmin, latmax, lonmax = bbox.split(' ')
# compose polygon
points = [(lonmin, latmin), (lonmin, latmax), (lonmax, latmax),
(lonmax, latmin), (lonmin, latmin)]
#insert into cartodb
sql.append(
"insert into osm_updates (the_geom, osm_id) values(GeomFromText('MULTIPOLYGON(((%s)))',4326), '%s')"
% (','.join([' '.join(x) for x in points]), e.id))
# insert in bacth
if sql:
try:
cl.sql(';'.join(sql))
except CartoDBException as e:
print("some error ocurred inserting data into cartodb", e)
time.sleep(2)
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')