Consider higher level interface using lists of dicts for each row of data?

[simonprickett]

No description provided.

[amotl]

Hi Simon,

because your issue description lacks additional information, I can only try to extrapolate.

dataset is my favourite higher level database interface for Python. Are you referring to something like this, or do you have different things in mind?

With kind regards,
Andreas.

[simonprickett]

I was thinking something higher level, optional (so you can still just use execute) and light as we don't want to be too clever on microcontrollers that have limited memory etc.

I'll take a look at dataset, I was thinking something like this.

Given a table:

CREATE TABLE some_table (id TEXT, temp DOUBLE PRECISION, humidity DOUBLE PRECISION);

Then something like this:

sensor_reading = {
    "id": "2abcde",
    "temp": 23.2,
    "humidity": 58.7
}

Use something like this to save that dict in the database, the driver assumes the key names in the dict are the column names in the table:

crate.insert("some_table". sensor_reading)

[amotl]

That sounds good!

Concerning data model design, If that resonates with you to provide an implementation that works beyond a specific DDL, so that the high-level interface you are aiming for can be implemented in a generic way, I'd recommend to have a look how Kotori is doing it, emulating the InfluxDB data model, but also how the CTK MongoDB I/O-, and CTK DynamoDB I/O-subsystems are doing it.

Other than that those are generic variants, they also work around other obstacles quickly discovered with CrateDB. For example, the venerable impossibility to store column names with leading underscores, i.e. _foo is invalid, becomes a non-issue without much ado.

Kotori/InfluxDB

time TIMESTAMP
tags OBJECT(DYNAMIC)
fields OBJECT(DYNAMIC)

MongoDB

oid TEXT
data OBJECT(DYNAMIC)

DynamoDB

pk OBJECT(STRICT)
data OBJECT(DYNAMIC)
aux OBJECT(IGNORED)

[amotl]

Concerning interface design, looking at it from a developer's perspective, it would be nice if we could pull in your driver without much ado into a downstream package like the Terkin Multi-Protocol Datalogger, roughly like its TelemetryTransportHTTP.

[amotl]

dataset is my favourite higher level database interface for Python.

• I had some obstacles unlocking it: dataset: databases for lazy people crate-clients-tools#48
• So I considered giving the Records library a try: Records: SQL for Humans™ crate-clients-tools#161
• It works well: records: Add examples about library and application use cratedb-examples#764

I was thinking something higher level, optional (so you can still just use execute) and light as we don't want to be too clever on microcontrollers that have limited memory etc.

Records is certainly not suitable for microcontrollers, because it is based on SQLAlchemy, but I am sharing it here, because it still might inspire you designing your own micro database wrapper, and also may spark your interest to use it within other demos. Hands-on examples how to use Records with CrateDB can be found at cratedb-examples:/framework/records.

CRUD operations are not mapped to individual methods, but it is all just db.query() instead.

docker run -it --rm --publish=4200:4200 crate

import records

db = records.Database("crate://")

# Basic DQL and DDL.
db.query("SELECT * FROM sys.summits ORDER BY height DESC LIMIT 3").all()
db.query("CREATE TABLE testdrive.example (id INT, text TEXT)")

# DML, single-record insert.
data = {"id": 42, "text": "foobar"}
db.query("INSERT INTO testdrive.example (id, text) VALUES (:id, :text)", **data)

# DML, bulk insert.
data = [{"id": 42, "text": "foobar"}, {"id": 43, "text": "bazqux"}]
db.bulk_query("INSERT INTO testdrive.example (id, text) VALUES (:id, :text)", data)

Nice, isn't it?

/cc @karynzv, @surister, @hlcianfagna, @hammerhead, @wierdvanderhaar