What is the simplest way to write data to CartoDB using Python?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.