Skip to main content

Datasette plugin for inserting and updating data

Project description

datasette-insert

PyPI Changelog License

Datasette plugin for inserting and updating data

Installation

Install this plugin in the same environment as Datasette.

$ pip install datasette-insert

This plugin should always be deployed with additional configuration to prevent unauthenticated access, see notes below.

If you are trying it out on your own local machine, you can pip install the datasette-insert-unsafe plugin to allow access without needing to set up authentication or permissions separately.

Inserting data and creating tables

Start datasette and make sure it has a writable SQLite database attached to it. If you have not yet created a database file you can use this:

datasette data.db --create

The --create option will create a new empty data.db database file if it does not already exist.

The plugin adds an endpoint that allows data to be inserted or updated and tables to be created by POSTing JSON data to the following URL:

/-/insert/name-of-database/name-of-table

The JSON should look like this:

[
    {
        "id": 1,
        "name": "Cleopaws",
        "age": 5
    },
    {
        "id": 2,
        "name": "Pancakes",
        "age": 5
    }
]

The first time data is posted to the URL a table of that name will be created if it does not aready exist, with the desired columns.

You can specify which column should be used as the primary key using the ?pk= URL argument.

Here's how to POST to a database and create a new table using the Python requests library:

import requests

requests.post("http://localhost:8001/-/insert/data/dogs?pk=id", json=[
    {
        "id": 1,
        "name": "Cleopaws",
        "age": 5
    },
    {
        "id": 2,
        "name": "Pancakes",
        "age": 4
    }
])

And here's how to do the same thing using curl:

curl --request POST \
  --data '[
      {
        "id": 1,
        "name": "Cleopaws",
        "age": 5
      },
      {
        "id": 2,
        "name": "Pancakes",
        "age": 4
      }
    ]' \
    'http://localhost:8001/-/insert/data/dogs?pk=id'

Or by piping in JSON like so:

cat dogs.json | curl --request POST -d @- \
    'http://localhost:8001/-/insert/data/dogs?pk=id'

Inserting a single row

If you are inserting a single row you can optionally send it as a dictionary rather than a list with a single item:

curl --request POST \
  --data '{
      "id": 1,
      "name": "Cleopaws",
      "age": 5
    }' \
    'http://localhost:8001/-/insert/data/dogs?pk=id'

Automatically adding new columns

If you send data to an existing table with keys that are not reflected by the existing columns, you will get an HTTP 400 error with a JSON response like this:

{
    "status": 400,
    "error": "Unknown keys: 'foo'",
    "error_code": "unknown_keys"
}

If you add ?alter=1 to the URL you are posting to any missing columns will be automatically added:

curl --request POST \
  --data '[
      {
        "id": 3,
        "name": "Boris",
        "age": 1,
        "breed": "Husky"
      }
    ]' \
    'http://localhost:8001/-/insert/data/dogs?alter=1'

Upserting data

An "upsert" operation can be used to partially update a record. With upserts you can send a subset of the keys and, if the ID matches the specified primary key, they will be used to update an existing record.

Upserts can be sent to the /-/upsert API endpoint.

This example will update the dog with ID=1's age from 5 to 7:

curl --request POST \
  --data '{
      "id": 1,
      "age": 7
    }' \
    'http://localhost:3322/-/upsert/data/dogs?pk=id'

Like the /-/insert endpoint, the /-/upsert endpoint can accept an array of objects too. It also supports the ?alter=1 option.

Permissions and authentication

This plugin defaults to denying all access, to help ensure people don't accidentally deploy it on the open internet in an unsafe configuration.

You can read about Datasette's approach to authentication in the Datasette manual.

You can install the datasette-insert-unsafe plugin to run in unsafe mode, where all access is allowed by default.

I recommend using this plugin in conjunction with datasette-auth-tokens, which provides a mechanism for making authenticated calls using API tokens.

You can then use "allow" blocks in the datasette-insert plugin configuration to specify which authenticated tokens are allowed to make use of the API.

Here's an example metadata.json file which restricts access to the /-/insert API to an API token defined in an INSERT_TOKEN environment variable:

{
    "plugins": {
        "datasette-insert": {
            "allow": {
                "bot": "insert-bot"
            }
        },
        "datasette-auth-tokens": {
            "tokens": [
                {
                    "token": {
                        "$env": "INSERT_TOKEN"
                    },
                    "actor": {
                        "bot": "insert-bot"
                    }
                }
            ]
        }
    }
}

With this configuration in place you can start Datasette like this:

INSERT_TOKEN=abc123 datasette data.db -m metadata.json

You can now send data to the API using curl like this:

curl --request POST \
  -H "Authorization: Bearer abc123" \
  --data '[
      {
        "id": 3,
        "name": "Boris",
        "age": 1,
        "breed": "Husky"
      }
    ]' \
    'http://localhost:8001/-/insert/data/dogs'

Or using the Python requests library like so:

requests.post(
    "http://localhost:8001/-/insert/data/dogs",
    json={"id": 1, "name": "Cleopaws", "age": 5},
    headers={"Authorization": "bearer abc123"},
)

Finely grained permissions

Using an "allow" block as described above grants full permission to the features enabled by the API.

The API implements several new Datasett permissions, which other plugins can use to make more finely grained decisions.

The full set of permissions are as follows:

  • insert:all - all permissions - this is used by the "allow" block described above. Argument: database_name
  • insert:insert-update - the ability to insert data into an existing table, or to update data by its primary key. Arguments: (database_name, table_name)
  • insert:create-table - the ability to create a new table. Argument: database_name
  • insert:alter-table - the ability to add columns to an existing table (using ?alter=1). Arguments: (database_name, table_name)

You can use plugins like datasette-permissions-sql to hook into these more detailed permissions for finely grained control over what actions each authenticated actor can take.

Plugins that implement the permission_allowed() plugin hook can take full control over these permission decisions.

CORS

If you start Datasette with the datasette --cors option the following HTTP headers will be added to resources served by this plugin:

Access-Control-Allow-Origin: *
Access-Control-Allow-Headers: content-type,authorization
Access-Control-Allow-Methods: POST

Development

To set up this plugin locally, first checkout the code. Then create a new virtual environment:

cd datasette-insert
python3 -m venv venv
source venv/bin/activate

Now install the dependencies and tests:

pip install -e '.[test]'

To run the tests:

pytest

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

datasette-insert-0.8.tar.gz (5.5 kB view details)

Uploaded Source

Built Distribution

datasette_insert-0.8-py3-none-any.whl (5.7 kB view details)

Uploaded Python 3

File details

Details for the file datasette-insert-0.8.tar.gz.

File metadata

  • Download URL: datasette-insert-0.8.tar.gz
  • Upload date:
  • Size: 5.5 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/4.0.1 CPython/3.10.5

File hashes

Hashes for datasette-insert-0.8.tar.gz
Algorithm Hash digest
SHA256 a6c53304f517f403729454d9673a1b4c30c0eea8a590a04cc3f28e3f7bf2f180
MD5 352d67a581df2a0308b9dd6d5f30affd
BLAKE2b-256 c31836d812ff3cb298c8a081c1b780b6f39b0995cc25ae06430af86adf0aaeb0

See more details on using hashes here.

File details

Details for the file datasette_insert-0.8-py3-none-any.whl.

File metadata

File hashes

Hashes for datasette_insert-0.8-py3-none-any.whl
Algorithm Hash digest
SHA256 66945451d5671a2e66dc9f27519ce44516dab23a18f944deb6d3ada11710ebe3
MD5 be3f31a08a76588f5a2e6b2f6652eae9
BLAKE2b-256 af150253bb3ea69f2e04897e0b02cd96c2ac23cd0be73b61b5a0c41b8f42a633

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page