.. _index:

##############################
SQLAlchemy dialect for CrateDB
##############################

.. rubric:: Table of contents

.. contents::
    :local:
    :depth: 1


************
Introduction
************

The :ref:`CrateDB dialect <using-sqlalchemy>` for `SQLAlchemy`_ provides adapters
for `CrateDB`_ and SQLAlchemy. The supported versions are 1.3, 1.4, and 2.0.

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`_.


*************
Documentation
*************

Please consult 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.

.. toctree::
    :titlesonly:

    install

SQLAlchemy
==========

Install package from PyPI.

.. code-block:: shell

    pip install sqlalchemy-cratedb

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`_.

.. toctree::
    :maxdepth: 2

    overview

Install package from PyPI with DB API and SQLAlchemy support.

.. code-block:: shell

    pip install sqlalchemy-cratedb pandas

Connect to CrateDB instance running on ``localhost``.

.. code-block:: python

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

.. code-block:: python

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

.. code-block:: python

    # 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 :ref:`CrateDB's data types
<crate-reference:data-types>` to different degrees. For more information,
please consult the :ref:`data-types` and :ref:`SQLAlchemy extension types
<using-extension-types>` documentation pages.

.. toctree::
    :maxdepth: 2

    data-types


.. _examples:
.. _by-example:
.. _sqlalchemy-by-example:

Examples
========

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

.. toctree::
    :maxdepth: 1

    getting-started
    crud
    working-with-types
    advanced-querying
    inspection-reflection
    dataframe


See also
--------
- 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.



*******************
Project information
*******************

Resources
=========
- `Source code <https://github.com/crate/crate-python>`_
- `Documentation <https://crate.io/docs/python/>`_
- `Python Package Index (PyPI) <https://pypi.org/project/crate/>`_

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 :ref:`index-all`.

License
=======
The project is licensed under the terms of the Apache 2.0 license, like
`CrateDB itself <CrateDB source_>`_, see `LICENSE`_.


.. _Apache Superset: https://github.com/apache/superset
.. _Crash CLI: https://crate.io/docs/crate/crash/
.. _CrateDB: https://crate.io/products/cratedb
.. _CrateDB Cloud: https://console.cratedb.cloud/
.. _CrateDB source: https://github.com/crate/crate
.. _Create an issue: https://github.com/crate/crate-python/issues
.. _development sandbox: https://github.com/crate/crate-python/blob/master/DEVELOP.rst
.. _cratedb-examples repository: https://github.com/crate/cratedb-examples/tree/main/by-language
.. _FIWARE QuantumLeap data historian: https://github.com/orchestracities/ngsi-timeseries-api
.. _GeoJSON: https://geojson.org/
.. _GeoJSON geometry objects: https://tools.ietf.org/html/rfc7946#section-3.1
.. _LICENSE: https://github.com/crate/crate-python/blob/master/LICENSE
.. _managed on GitHub: https://github.com/crate/crate-python
.. _pandas: https://pandas.pydata.org/
.. _PEP 249: https://peps.python.org/pep-0249/
.. _PyPy: https://www.pypy.org/
.. _sample application: https://github.com/crate/crate-sample-apps/tree/main/python-flask
.. _sample application documentation: https://github.com/crate/crate-sample-apps/blob/main/python-flask/documentation.md
.. _SQLAlchemy: https://www.sqlalchemy.org/
.. _SQLAlchemy documentation: https://docs.sqlalchemy.org/
.. _SQLAlchemy tutorial: https://docs.sqlalchemy.org/en/latest/tutorial/
.. _Use CrateDB with pandas: https://github.com/crate/crate-qa/pull/246
