Make CrateDB work with dataset

[amotl]

Hi there,

back in a while, I've tried to use the sweet dataset package with CrateDB.

Being built on top of SQLAlchemy, dataset works with all major databases, such as SQLite, PostgreSQL and MySQL.

For exercising it, and to provide a common ground for others to experiment with, I've created the cratedb-dataset-demo.py gist.

Within this meta issue, all related issues will be tracked which are needed to make the demo program work completely.

With kind regards,
Andreas.

References

• Optimize single primary key usage patterns crate#11020
• Expose UUIDs.base64UUID as scalar function crate#11039
• Add a gen_random_text_uuid scalar crate#11165
• Regression in information_schema.key_column_usage? crate#13102
• Improve SQLAlchemy's CrateDialect.get_pk_constraint for CrateDB>=5.1 crate-python#459
• PostgreSQL compatibility: Support LIMIT ALL OFFSET 0 crate#13104
• SQLParseException[line 4:8: no viable alternative at input 'LIMIT -'] sqlalchemy-cratedb#113
• SQLAlchemy/DDL: Support server_default column definition option sqlalchemy-cratedb#106
• Populate primary key value for the row just inserted into DBAPI's lastrowid and SQLAlchemy's inserted_primary_key attributes sqlalchemy-cratedb#94

[amotl]

With recent improvements, most notably crate/crate#11165, which added the gen_random_text_uuid() scalar function, primary key values can be automatically generated when inserting new records. This was essential to make INSERT operations work like table.insert(dict(name="John Doe", age=37)).

The corresponding SQL DDL statement looks like:

CREATE TABLE IF NOT EXISTS "doc"."testdrive" (
    "id" TEXT DEFAULT gen_random_text_uuid() NOT NULL,
    "name" TEXT,
    "age" INTEGER,
    "gender" TEXT,
    PRIMARY KEY ("id")
);

Currently, the schema has to be provided manually, maybe because dataset itself only handles automatic provisioning of autoincrement-like columns, i.e. one of Types.integer, Types.bigint ¹². It would be a nice-to-have to make the automatic schema creation work, like it works on other databases as well. Maybe it will be enough to add Types.{string,text} at ²?

One of the main features of dataset is to automatically create tables and columns as data is inserted. This behaviour can optionally be disabled via the ensure_schema argument. It can also be overridden in a lot of the data manipulation methods using the ensure flag.

-- https://dataset.readthedocs.io/en/latest/api.html#connecting

Footnotes

1. https://github.com/pudo/dataset/blob/1.5.2/dataset/table.py#L338-L343 ↩

2. https://github.com/pudo/dataset/blob/1.5.2/dataset/table.py#L48 ↩ ↩²