CrateDB Python Client

2.1.2 · active · verified Fri Apr 17

The `crate` library is the official Python client for CrateDB, a distributed SQL database. It provides a DB-API 2.0 compliant interface for connecting to CrateDB clusters, executing SQL queries, and managing data. The current version is 2.1.2, and it typically sees regular updates aligned with CrateDB releases and Python ecosystem changes.

Common errors

```
OperationalError: Failed to establish a new connection: [Errno 111] Connection refused
```
cause The CrateDB server is not running, is not accessible at the specified host/port, or a firewall is blocking the connection.

fix Ensure CrateDB is running and listening on the correct address (default: `localhost:4200`). Verify firewall rules and the `CRATEDB_HOST` environment variable or hardcoded connection string.
```
TypeError: Object of type datetime is not JSON serializable
```
cause Attempting to insert a Python `datetime` object directly into a CrateDB `TIMESTAMP` column without proper serialization.

fix Before inserting, convert the `datetime` object to an integer representing milliseconds since epoch, or an ISO 8601 string. Example: `cursor.execute("INSERT INTO my_table (ts_col) VALUES (?) ", (int(my_datetime.timestamp() * 1000),))`.
```
AttributeError: module 'crate.client' has no attribute 'SQLAlchemyDialect'
```
cause Trying to import the SQLAlchemy dialect from the main `crate` package after it was moved to a separate `crate-sqlalchemy` package.

fix Install the dedicated SQLAlchemy package: `pip install crate-sqlalchemy`. Then, import the dialect from `crate_sqlalchemy.dialect`.

Warnings

breaking The SQLAlchemy dialect for CrateDB was extracted into a separate package, `crate-sqlalchemy`. Users relying on ORM integration will need to install this new package.
Fix: Install `crate-sqlalchemy` (`pip install crate-sqlalchemy`) and import the dialect from `crate_sqlalchemy.dialect`.
gotcha The `connect()` function expects a full HTTP URL for the CrateDB host, including the `http://` or `https://` schema. Failing to include the schema (e.g., just `localhost:4200`) will result in a connection error.
Fix: Ensure the connection string starts with `http://` or `https://`, e.g., `http://localhost:4200`.
gotcha Python `datetime` objects are not directly JSON serializable by default, which can lead to `TypeError` when inserting into `TIMESTAMP` columns without explicit conversion. CrateDB expects timestamps as Unix milliseconds or ISO 8601 strings.
Fix: Convert `datetime` objects to Unix milliseconds (integer) or ISO 8601 strings before passing them in parameters, e.g., `int(dt_object.timestamp() * 1000)`.

Install

pip install crate Install core client

Imports

connect
```
from crate.client import connect
```

Quickstart

Connects to a CrateDB instance, creates a table, inserts two rows, and then queries them. Requires a running CrateDB instance (default: localhost:4200).

import os
from crate.client import connect

CRATEDB_HOST = os.environ.get('CRATEDB_HOST', 'http://localhost:4200')

try:
    # Connect to CrateDB
    connection = connect(CRATEDB_HOST)
    cursor = connection.cursor()

    # Create a table if it doesn't exist
    cursor.execute("CREATE TABLE IF NOT EXISTS my_data (id INTEGER, name TEXT)")
    print("Table 'my_data' ensured.")

    # Insert data
    cursor.execute("INSERT INTO my_data (id, name) VALUES (?, ?)", (1, "Alice"))
    cursor.execute("INSERT INTO my_data (id, name) VALUES (?, ?)", (2, "Bob"))
    connection.commit()
    print("Data inserted.")

    # Query data
    cursor.execute("SELECT * FROM my_data ORDER BY id")
    results = cursor.fetchall()

    print("\nQuery Results:")
    for row in results:
        print(row)

except Exception as e:
    print(f"An error occurred: {e}")
finally:
    if 'connection' in locals() and connection:
        connection.close()
        print("Connection closed.")

view raw JSON →