micropython-cratedb - A CrateDB Driver for MicroPython

Introduction

micropython-cratedb is a CrateDB driver for the MicroPython language. It connects to CrateDB using the HTTP Endpoint.

To use this, you'll need a CrateDB database cluster. Sign up for our cloud free tier here or get started with Docker here.

Want to learn more about CrateDB? Take our free Fundamentals course at the CrateDB Academy. You can also watch this video from the Notts IoT Meetup where Simon Prickett, CrateDB's Developer Advocate, demonstrates how to use an early version of this driver with various sensors attached to Raspberry Pi Pico W devices.

Installation

There are two ways to install this driver.

Install with mpremote

Install the driver with mpremote like this:

mpremote mip install github:crate/micropython-cratedb

This will install the driver into /lib on the device, along with the base64 module from micropython-lib.

Install with mip

You can also install the driver into /lib on the device by running the following commands at the MicroPython REPL on the device:

import network
import mip
wlan = network.WLAN(network.STA_IF)
wlan.active(True)
wlan.connect("<your wifi SSID>", "<your wifi password>")
wlan.isconnected() # Run this until it returns True
mip.install("github:crate/micropython-cratedb")

Using the Driver in a MicroPython Script

Import the driver like this:

import cratedb

Connecting to CrateDB

Connect to a CrateDB Cloud cluster using SSL, by providing hostname, username, and password:

crate = cratedb.CrateDB(
    host="host", 
    user="user", 
    password="password"
)

The driver uses SSL by default.

If you're running CrateDB on your workstation (with Docker for example, by using docker run --rm -it --publish=4200:4200 crate), connect like this:

crate = cratedb.CrateDB(
    host="hostname", 
    use_ssl=False
)

The driver will connect to port 4200 unless you provide an alternative value:

crate = cratedb.CrateDB(
    host="host", 
    user="user", 
    port=4201,
    password="password"
)

Interacting with CrateDB

CrateDB is a SQL database: you'll store, update and retieve data using SQL statements. The examples that follow assume a table schema that looks like this:

CREATE TABLE temp_humidity (
  sensor_id TEXT, 
  ts TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS current_timestamp,
  temp DOUBLE PRECISION, 
  humidity DOUBLE PRECISION
);

Assume that the table contains a few sample rows.

Retrieving Data

The execute method sends a SQL statement to the database for execution, and returns the result:

response = crate.execute(
    "SELECT sensor_id, ts, temp, humidity FROM temp_humidity ORDER BY ts DESC"
)

You can also use parameterized queries:

response = crate.execute(
    """
        SELECT sensor_id, ts, temp, humidity
        FROM temp_humidity WHERE sensor_id = ?
        ORDER BY ts DESC
    """,
    [
        "a01"
    ]
)

Data is returned as a dictionary that looks like this:

{
    'rows': [
        ['a01', 1728473302619, 22.8, 59.1], 
        ['a02', 1728473260880, 3.3, 12.9], 
        ['a02', 1728473251188, 3.2, 12.7], 
        ['a03', 1728473237365, 28.4, 65.7], 
        ['a01', 1728473223332, 22.3, 58.6]
    ], 
    'rowcount': 5, 
    'cols': [
        'sensor_id', 
        'ts', 
        'temp', 
        'humidity'
    ], 
    'duration': 18.11329
}

Use the with_types parameter to have CrateDB return information about the data type of each column in the resultset. This feature is off by defaault to minimize network bandwidth.

response = crate.execute(
    "SELECT sensor_id, ts, temp FROM temp_humidity WHERE sensor_id = ? ORDER BY ts DESC",
    [
        "a01"
    ],
    with_types=True
)

The resultset then contains an extra key, col_types:

{
    'col_types': [
        4, 
        11, 
        6
    ], 
    'cols': [
        'sensor_id', 
        'ts', 
        'temp'
    ], 
    'rowcount': 2, 
    'rows': [
        ['a01', 1728473302619, 22.8], 
        ['a01', 1728473223332, 22.3]
    ], 
    'duration': 7.936583
}

Constants are provided for each type. For example type 11 is CRATEDB_TYPE_TIMESTAMP_WITH_TIME_ZONE.

Inserting / Updating Data

Here's an example insert statement:

response = crate.execute(
    "INSERT INTO temp_humidity (sensor_id, temp, humidity) VALUES (?, ?, ?)",
    [
        "a01",
        22.8,
        60.1
    ]
)

The response from CrateDB looks like this:

{
    'rows': [
        []
    ], 
    'rowcount': 1, 
    'cols': [], 
    'duration': 38.615707
}

If you don't need a response, set the return_response parameter to False (default is True). This will save a small amount of time that the driver normally spends on processing the response.

response = crate.execute(
    "INSERT INTO temp_humidity (sensor_id, temp, humidity) VALUES (?, ?, ?)",
    [
        "a01",
        22.9,
        60.3
    ],
    return_response=False
)

response will be None.

You can add multiple records in a single network round trip using a bulk insert:

response = crate.execute(
    "INSERT INTO temp_humidity (sensor_id, temp, humidity) VALUES (?, ?, ?)",
    [
        [
            "a01",
            22.7,
            60.1
        ],
        [
            "a02",
            3.3,
            12.9
        ]
    ]
)

The response looks like this, note that you can expect to receive multiple results each containing their own rowcount:

{
    'results': [
        {
            'rowcount': 1
        }, 
        {
            'rowcount': 1
        }
    ], 
    'cols': [], 
    'duration': 32.546875
}

Existing rows can also be updated:

response = crate.execute(
    "UPDATE temp_humidity SET sensor_id = ? WHERE sensor_id = ?",
    [
        "a04",
        "a01"
    ]
)

The response includes the number of rows affected by the update:

{
    'rows': [
        []
    ], 
    'rowcount': 5, 
    'cols': [], 
    'duration': 696.36975
}

Working with Objects and Arrays

CrateDB supports flexible storage and indexing of objects / JSON data. To learn more about this, check out our blog post that explains the different ways objects can be stored.

Here are some basic examples showing how to store objects with micropython-cratedb and retrieve desired fields from them.

Assume a table with the following definition having a dynamic object column:

CREATE TABLE driver_object_test (
    id TEXT PRIMARY KEY, 
    data OBJECT(DYNAMIC)
)

Objects of arbitrary structure are inserted like this:

response = crate.execute(
    "INSERT INTO driver_object_test (id, data) VALUES (?, ?)",
    [
        "2cae54",
        {
            "sensor_readings": {
                "temp": 23.3,
                "humidity": 61.2
            },
            "metadata": {
                "software_version": "1.19",
                "battery_percentage": 57,
                "uptime": 2851200
            }
        }
    ]
)

And values contained in objects can be retrieved selectively like this:

response = crate.execute(
    """SELECT 
            id,
            data['metadata']['uptime'] AS uptime, 
            data['sensor_readings'] AS sensor_readings 
        FROM driver_object_test 
        WHERE id = ?""",
    [
        "2cae54"
    ]
)

response contains the matching records like this:

{
    'rows': [
        [2851200, {'humidity': 61.2, 'temp': 23.3}]  
    ], 
    'rowcount': 1, 
    'cols': [
        'uptime', 'sensor_readings'
    ], 
    'duration': 4.047666
}

For more examples, see the object_examples.py script in the examples folder.

Deleting Data

Delete queries work like any other SQL statement:

response = crate.execute(
    "DELETE FROM temp_humidity WHERE sensor_id = ?",
    [
        "a02"
    ]
)

And the response from the above looks like this, again including the number of rows affected:

{
    'rows': [
        []
    ], 
    'rowcount': 3, 
    'cols': [], 
    'duration': 66.81604
}

Errors / Exceptions

The driver can throw the following types of exception:

• NetworkError: when there is a network level issue, for example the hostname cannot be resolved.
• CrateDBError: errors returned by the CrateDB cluster, for example when invalid SQL is submitted.

Here's an example showing how to catch a network error:

crate = cratedb.CrateDB("nonexist", use_ssl=False)

try:
    response = crate.execute(
        "SELECT sensor_id, ts, temp FROM temp_humidity WHERE sensor_id = ? ORDER BY ts DESC",
        [
            "a01"
        ],
        with_types=True
    )
except cratedb.NetworkError as e:
    print("Network error:")
    print(e)

Output:

Network error:
[addrinfo error 8]

This example shows a CrateDBError:

try:
    response = crate.execute(
        "SELECT nonexist FROM temp_humidity"
    )
except cratedb.CrateDBError as e:
    print("CrateDB error:")
    print(e)

Output:

CrateDB error:
{
    'error': {
        'message': 'ColumnUnknownException[Column nonexist unknown]', 
        'code': 4043
    }
}

Constants for each value of code are provided. For example 4043 is CRATEDB_ERROR_UNKNOWN_COLUMN .

Examples

The examples folder contains example MicroPython scripts, some of which are for specific microcontroller boards, including the popular Raspberry Pi Pico W. Hardware-independent example programs also work well on CPython, and the MicroPython UNIX and Windows port, see Running on CPython and Running on MicroPython.

Testing

This driver library has been tested using the following MicroPython versions:

• 1.24.0
  • macOS/darwin (install with Homebrew package manager)
  • Raspberry Pi Pico W (download)
• 1.23.0
  • macOS/darwin (install with Homebrew package manager)
  • Raspberry Pi Pico W (download)
• 1.23.0 (Pimoroni build)
  • Raspberry Pi Pico W (download)

If you have other microcontroller boards that you can test the driver with or provide examples for, we'd love to receive a pull request!

Need Help?

If you need help, have a bug report or feature request, or just want to show us your project that uses this driver then we'd love to hear from you!

For bugs or feature requests, please raise an issue on GitHub. We also welcome pull requests!

If you have a project to share with us, or a more general question about this driver or CrateDB, please post in our community forum.