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
}