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?

3 Answers 3


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

  • 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 Commented May 5, 2014 at 19:11

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


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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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