Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm trying to write a function in Python that will use the CartoDB API to write data to one of my tables. Does anyone have a simple example that shows how to do this that I can use to start?

share|improve this question
add comment

3 Answers 3

There is a python library for CartoDB... I think it would work with the SQL API.

share|improve this answer
add comment

Writing data through the API is pretty simple. Here is the most basic,

Suppose we have two variables already, how you get them is up to you,
username = 'cartodb-user-name'
apikey = 'MY-CARTODB-API-KEY'
Next, lets create an INSERT statement to use
insert = "INSERT INTO my_table_name (the_geom, measure) VALUES (CDB_LatLng(43, -120), 22.0)"
Create the URL endpoint for our account API
url = "https://%s.cartodb.com/api/v1/sql" % username
Create an object containing the parameters of our request
params = {
    'api_key' : apikey, # our account apikey, don't share!
    'q'       : insert  # our insert statement above
}
Send the request using urllib2
req = urllib2.Request(url, urllib.urlencode(params))
response = urllib2.urlopen(req)

SETUP: You should have a couple libraries imported in your python script

import urllib
import urllib2

BONUS: Batch inserts are wayyyy better than single line inserts.

Here is an example of a multi-row (3) insert strategy. I use an array to initially store all my row values. Each row value is a comma delimited list, wrapped in parentheses and stored to the array,

rows = [
    "(CDB_LatLng(10, 10), 1.0)",
    "(CDB_LatLng(20, 12), 1.4)",
    "(CDB_LatLng(30, 14), 1.2)"
]

insert = "INSERT INTO my_table_name (the_geom, measure) (VALUES %s)" % ','.join(rows)

Using this, I've wrapped all 3 rows into a single http request. It works faster all around and is highly recommended. From personal experience 100-250 rows at a time works really well.

share|improve this answer
1  
@mapbaker's comment below is true too! i could have swapped out a bunch of this for the cartodb library, just figured i'd go raw in this case –  andrewxhill May 5 at 19:11
add comment

Here's an example script that I wrote a while back. It basically implements what andrewxhill wrote above, but uses the "requests" library instead of urllib.

It batches together inserts to do 1000 at a time, then uses the API to do the inserts.

lasbbox2cartodb.py

The script loops over a set of Lidar files in subdirectories, and inserts the bounding boxes for the lidar files into CartoDB.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.