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