Database API

Use the Database API to create a database, describe a table, list tables, and execute a query against a database. Site admins can execute a query against a database using custom connection parameters.

import json, requests

table_api = 'https://www.instabase.com/api/v1/databases'
root = '/<username>/<workspace>/databases'

See Instabase API authorization and response conventions for authorization and error convention details.

Create a database

Use this API to create a database.

Request

The request must be:

headers = {'Authorization': 'Bearer {0}'.format(token)}
data = '{"name": "testdb", "action": "create", "dbType": "mysql"}'
resp = requests.post(table_api + root, headers=headers, data=data).json()

JSON arguments must define the action, database name, and database type:

  • name: Database name

  • action: create to create and mount a new database

  • dbType: The dialect of the database. Creatable dialects include mysql and postgres

Response

{
  "status": "OK"
}

List all tables

Use this API to list all database tables.

Request

The request must be:

headers = {'Authorization': 'Bearer {0}'.format(token)}
resp = requests.get(table_api + root + '/testdb1', headers=headers).json()

Response

{
  "status": "OK",
  "tables": [
    {
      "columns": [
        {
          "name": "year",
          "type": "string"
        },
        {
          "name": "recipients",
          "type": "string"
        },
        {
          "name": "citation",
          "type": "string"
        }
      ],
      "db_type": "mysql",
      "escape_char": "`",
      "full_path": "/anantb/workspace/databases/testdb1/turing_winners",
      "name": "turing_winners",
      "type": "table"
    }
  ]
}

Describe a table

Use this API to describe a database table.

Request

The request must be:

headers = {'Authorization': 'Bearer {0}'.format(token)}
resp = requests.get(table_api + root + '/testdb1/turing_winners', headers=headers).json()

Response

{
  "status": "OK",
  "table": {
    "columns": [
      {
        "name": "year",
        "type": "string"
      },
      {
        "name": "recipients",
        "type": "string"
      },
      {
        "name": "citation",
        "type": "string"
      }
    ],
    "name": "turing_winners"
  }
}

Bulk insert into a table

Request

The request must be:

headers = {'Authorization': 'Bearer {0}'.format(token)}
data = dict(
    values=[
        ["2014", "Mike Stonebraker", "Building database systems"],
        ["2020", "Jeff Dean", "MapReduce"]
    ]
)
resp = requests.post(
    table_api + root + '/testdb1/turing_winners',
    data=json.dumps(data), headers=headers
).json()

Response

{
  "num_rows_inserted": 2,
  "status": "OK"
}

Execute a query

Use this API to execute a query against a database.

Request

The request must be:

headers = {'Authorization': 'Bearer {0}'.format(token)}
data = '{"query": "SELECT ip, count FROM apache;"}'
resp = requests.post(table_api + root + '/testdb1', data=data, headers=headers).json()

Response

{
  "status": "OK",
  "table_spec": {
    "columns": [
      {"type": "string", "name": "ip"},
      {"type": "long", "name": "count"}
    ]
  },
  "table_content": [
    ["64.242.88.10", "47"],
    ["64.242.85.11", "3"]
  ]
}

Execute a query with custom connection

This API is callable only by Site Admins.

This API allows Site Admins to execute a query against a database using custom connection parameters. The supported database dialects include MySQL, Oracle, Microsoft SQL Server, and Postgres.

Request

The request must be in this format:

headers = {'Authorization': 'Bearer {0}'.format(token)}
data = '{"query": "select * from people", "dialect": "mysql", "database_name": "<database_name>", "host": "<host_url>", "username": "<username>", "password": "<password>", "port": <port>}'
resp = requests.post(table_api + '/execute', data=data, headers=headers).json()

Fields:

  • query (required) - A database query

  • dialect (required) - A database dialect. One of mysql, oracle, mssql, or postgresql.

  • database_name (optional) - The name of the database (leave empty if using service_name)

  • oracle_service_name (optional) - The service name of the database instance, if registered

  • host (optional) - The host url of the database

  • username (optional) - The username of your database credentials

  • password (optional) - The password of your database credentials

  • port (optional) - The port number used to connect to the database

Response

If successful, the response contains data that was returned by the database query.

{
  "status": "OK",
  "table_spec": {
    "columns": [
      {"type": "int", "name": "id"},
      {"type": "string", "name": "name"},
      {"type": "int", "name": "age"}
    ]
  },
  "table_data": {
    "table_spec": {
      "columns": [
        {"name": "id", "type": "int"},
        {"name": "name", "type": "string"},
        {"name": "age", "type": "int"}
      ]
    },
    "table_content": [
      ["1", "joe", "26"],
      ["2", "sarah", "35"]
    ]
  }
}