SQLAlchemy dialect for CrateDB

Table of contents

About

The CrateDB dialect for SQLAlchemy provides adapters for CrateDB and SQLAlchemy. The supported versions are 1.3, 1.4, and 2.0. The package is available from PyPI at sqlalchemy-cratedb.

The connector can be used to connect to both CrateDB and CrateDB Cloud, and is verified to work on Linux, macOS, and Windows. It is used by pandas, Dask, and many other libraries and applications connecting to CrateDB from the Python ecosystem. It is verified to work with CPython, but it has also been tested successfully with PyPy.

Note

If you are upgrading from crate[sqlalchemy] to sqlalchemy-cratedb, please read this section carefully.

Introduction

Please consult the SQLAlchemy tutorial, and the general SQLAlchemy documentation. For more detailed information about how to install the dialect package, how to connect to a CrateDB cluster, and how to run queries, consult the resources referenced below.

Installation

Install package from PyPI.

pip install --upgrade sqlalchemy-cratedb

More installation details can be found over here.

Features

The CrateDB dialect for SQLAlchemy offers convenient ORM access and supports CrateDB’s container data types OBJECT and ARRAY, its vector data type FLOAT_VECTOR, and geospatial data types using GeoJSON, supporting different kinds of GeoJSON geometry objects.

Synopsis

Connect to CrateDB instance running on localhost.

# Connect using SQLAlchemy Core.
import sqlalchemy as sa
from pprint import pp

dburi = "crate://localhost:4200"
query = "SELECT country, mountain, coordinates, height FROM sys.summits ORDER BY country;"

engine = sa.create_engine(dburi, echo=True)
with engine.connect() as connection:
    with connection.execute(sa.text(query)) as result:
        pp(result.mappings().fetchall())

Connect to CrateDB Cloud.

# Connect using SQLAlchemy Core.
import sqlalchemy as sa
dburi = "crate://admin:<PASSWORD>@example.aks1.westeurope.azure.cratedb.net:4200?ssl=true"
engine = sa.create_engine(dburi, echo=True)

Load results into pandas DataFrame.

pip install pandas

# Connect using SQLAlchemy Core and pandas.
import pandas as pd
import sqlalchemy as sa

dburi = "crate://localhost:4200"
query = "SELECT * FROM sys.summits ORDER BY country;"

engine = sa.create_engine(dburi, echo=True)
with engine.connect() as connection:
    df = pd.read_sql(sql=sa.text(query), con=connection)
    df.info()
    print(df)

Data Types

The DB API driver and the SQLAlchemy dialect support CrateDB’s data types to different degrees. For more information, please consult the Data types and SQLAlchemy extension types documentation pages.

Support Utilities

The package bundles a few support and utility functions that try to fill a few gaps you will observe when working with CrateDB, when compared with other databases. Due to its distributed nature, CrateDB’s behavior and features differ from those found in other RDBMS systems.

Examples

This section enumerates concise examples demonstrating the use of the SQLAlchemy dialect.

See also

Project information

Resources

Contributions

The SQLAlchemy dialect for CrateDB is an open source project, and is managed on GitHub. Every kind of contribution, feedback, or patch, is much welcome. Create an issue or submit a patch if you think we should include a new feature, or to report or fix a bug.

Development

In order to setup a development environment on your workstation, please head over to the development sandbox documentation. When you see the software tests succeed, you should be ready to start hacking.

Page index

The full index for all documentation pages can be inspected at CrateDB SQLAlchemy dialect – all pages.

License

The project is licensed under the terms of the Apache 2.0 license, like CrateDB itself, see LICENSE.