POST to the CartoDB imports API from Google AppEngine / Python

Question

Creating tables using the CartoDB SQL api is a known bug/missing feature, CREATE TABLE queries sent via the SQL API create shadow tables that never show up in the dashboard. One potential way around this is using the new imports api to post a small csv file in the desired schema to create a table that then can be updated/inserted to via the sql api.

http://blog.cartodb.com/post/54101913823/got-files-weve-got-a-import-api

This works well with files sent via curl. It would be nice to know a little more about what parameters the imports api expects and can respond to. Specifically, I am attempting to post using urlfetch in AppEngine, and not having much luck.

Like this..

url = 'http://mycartodb.cartodb.com/api/v1/imports?api_key=myapikey'

with open('testtable.txt', 'r') as fh: #testtable is a simple 3 line csv
    payload = fh.read()

result = urlfetch.fetch(
    url=url,
    payload=payload,
    method=urlfetch.POST
).content

logging.info(result)

These requests successfully hit the api...

{
    "item_queue_id":"d18799a5-3849-4058-ac23-49ea7d6e194e",
    "success":true
}

...but when I query the job status it reports an error of unknown origin. One hitch may be that the "Content-Length" header cannot be set in AppEngine, and maybe a successful imports api POST request requires this?

{
    "id": "blah-blah-blah",
    "user_id": "blah-blah-blah",
    "table_id": null,
    "data_type": "file",
    "table_name": null,
    "state": "failure",
    "error_code": 99999,
    "queue_id": "blah-blah-blah",
    "get_error_text": {
        "title": "Unknown",
        "what_about": "Sorry, something went wrong and we're not sure what. Try uploading your file again, or <a href='mailto:support@cartodb.com?subject=Unknown error'>contact us</a> and we'll try to help you quickly."
    },
    "tables_created_count": null,
    "synchronization_id": null,
    "success": false
}      

Answer 1

That error corresponds typically to a non supported file, but notice that there's a way to show in the dashboard tables created via SQL API by following this guide: https://github.com/CartoDB/cartodb/wiki/creating-tables-though-the-SQL-API. There's an extra step required: to CartoDBfy the tables (this creates columns cartodb_id, the_geom, created_at, updated_at, and other details that the CartoDB Editor needs the tables to have in order to work with them).