CrateDB Python Client¶

Table of contents

Introduction
Documentation
Project information

Introduction ¶

The Python client library for CrateDB implements the Python Database API Specification v2.0 (PEP 249), and also includes the CrateDB dialect for SQLAlchemy.

The Python driver 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 the Crash CLI, as well as 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.

Please make sure to also visit the section about Other connectivity options for Python, using the PostgreSQL wire protocol interface of CrateDB.

Documentation ¶

For general help about the Python Database API, or SQLAlchemy, please consult PEP 249, the SQLAlchemy tutorial, and the general SQLAlchemy documentation. For more detailed information about how to install the client driver, how to connect to a CrateDB cluster, and how to run queries, consult the resources referenced below.

DB API¶

Install package from PyPI.

pip install crate

Connect to CrateDB instance running on localhost.

# Connect using DB API.
from crate import client
from pprint import pp

query = "SELECT country, mountain, coordinates, height FROM sys.summits ORDER BY country;"

with client.connect("localhost:4200", username="crate") as connection:
    cursor = connection.cursor()
    cursor.execute(query)
    pp(cursor.fetchall())
    cursor.close()

Connect to CrateDB Cloud.

# Connect using DB API.
from crate import client
connection = client.connect(
    servers="https://example.aks1.westeurope.azure.cratedb.net:4200",
    username="admin",
    password="<PASSWORD>")

SQLAlchemy¶

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

SQLAlchemy support

Install package from PyPI with DB API and SQLAlchemy support.

pip install 'crate[sqlalchemy]' pandas

Connect to CrateDB instance running on localhost.

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

pkg_resources.require("sqlalchemy>=2.0")

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.

# 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.

Data types
- Database API client
- SQLAlchemy

Examples¶

The By example section enumerates concise examples demonstrating the different API interfaces of the CrateDB Python client library. Those are DB API, HTTP, and BLOB interfaces, and the SQLAlchemy dialect.
Executable code examples are maintained within the cratedb-examples repository.
The sample application and the corresponding sample application documentation demonstrate the use of the driver on behalf of an example “guestbook” application.
Use CrateDB with pandas has corresponding code snippets about how to connect to CrateDB using pandas, and how to load and export data.
The Apache Superset and FIWARE QuantumLeap data historian projects.

By example
- DB API, HTTP, and BLOB interfaces
- SQLAlchemy by example

Project information ¶

Resources¶

Contributions¶

The CrateDB Python client library 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 Python Client – all pages.

License¶

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