superset-sqlalchemy-gizmosql-adbc-dialect 0.0.9
An Apache Superset compatible SQLAlchemy dialect for connecting to a [GizmoSQL](https://github.com/gizmodata/gizmosql) server with ADBC
Requires Python: >=3.10
Keywords: apache, superset, sqlalchemy, flight-sql, gizmosql, adbc, dialect

# Superset SQLAlchemy [GizmoSQL](https://github.com/gizmodata/GizmoSQL) ADBC Dialect 

[<img src="https://img.shields.io/badge/GitHub-gizmodata%2Fsuperset--sqlalchemy--gizmosql--adbc--dialect-blue.svg?logo=Github">](https://github.com/gizmodata/superset-sqlalchemy-gizmosql-adbc-dialect)
[![superset-sqlalchemy-gizmosql-adbc-dialect-ci](https://github.com/gizmodata/superset-sqlalchemy-gizmosql-adbc-dialect/actions/workflows/ci.yml/badge.svg)](https://github.com/gizmodata/superset-sqlalchemy-gizmosql-adbc-dialect/actions/workflows/ci.yml)
[![Supported Python Versions](https://img.shields.io/pypi/pyversions/superset--sqlalchemy--gizmosql--adbc--dialect)](https://pypi.org/project/superset-sqlalchemy-gizmosql-adbc-dialect/)
[![PyPI version](https://badge.fury.io/py/superset-sqlalchemy-gizmosql-adbc-dialect.svg)](https://badge.fury.io/py/superset-sqlalchemy-gizmosql-adbc-dialect)
[![PyPI Downloads](https://img.shields.io/pypi/dm/superset-sqlalchemy-gizmosql-adbc-dialect.svg)](https://pypi.org/project/superset-sqlalchemy-gizmosql-adbc-dialect/)

Basic Apache Superset compatible SQLAlchemy dialect for [GizmoSQL](https://github.com/gizmodata/GizmoSQL)

This package uses a version of SQLAlchemy which is compatible with Apache Superset 5.0.*

> [!NOTE]
> The term "dialect" is used in SQLAlchemy to refer to a specific database backend.  See: https://docs.sqlalchemy.org/en/20/dialects/ for more details.
> This package uses a DuckDB SQL dialect when sending SQL commmands to the GizmoSQL server.

## Installation

### Option 1 - from PyPi
```sh
$ pip install superset-sqlalchemy-gizmosql-adbc-dialect
```

### Option 2 - from source - for development
```shell
git clone https://github.com/gizmodata/superset-sqlalchemy-gizmosql-adbc-dialect

cd superset-sqlalchemy-gizmosql-adbc-dialect

# Create the virtual environment
python3 -m venv .venv

# Activate the virtual environment
. .venv/bin/activate

# Upgrade pip, setuptools, and wheel
pip install --upgrade pip setuptools wheel

# Install Superset SQLAlchemy GizmoSQL ADBC Dialect - in editable mode with dev dependencies
pip install --editable .[dev]
```

### Note
For the following commands - if you are running from source and using `--editable` mode (for development purposes) - you will need to set the PYTHONPATH environment variable as follows:
```shell
export PYTHONPATH=$(pwd)/src
```

## Usage

Once you've installed this package, you should be able to just use it, as SQLAlchemy does a python path search

### Start a GizmoSQL Server - example below - see https://github.com/gizmodata/GizmoSQL for more details
```bash
docker run --name gizmosql \
           --detach \
           --rm \
           --tty \
           --init \
           --publish 31337:31337 \
           --env TLS_ENABLED="1" \
           --env GIZMOSQL_PASSWORD="gizmosql_password" \
           --env PRINT_QUERIES="1" \
           --pull missing \
           gizmodata/gizmosql:latest
```

> [!IMPORTANT]
> The GizmoSQL server must be started with the DuckDB (default) back-end.  The SQLite back-end is not supported.

### Connect with the SQLAlchemy GizmoSQL ADBC Dialect
```python
import os
import logging

from sqlalchemy import create_engine, MetaData, Table, select, Column, text, Integer, String, Sequence
from sqlalchemy.orm import Session
from sqlalchemy.orm import declarative_base
from sqlalchemy.engine.url import URL

# Setup logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)


Base = declarative_base()


class FakeModel(Base):  # type: ignore
    __tablename__ = "fake"

    id = Column(Integer, Sequence("fakemodel_id_sequence"), primary_key=True)
    name = Column(String)


def main():
    # Build the URL
    url = URL.create(drivername="gizmosql",
                     host="localhost",
                     port=31337,
                     username=os.getenv("GIZMOSQL_USERNAME", "gizmosql_username"),
                     password=os.getenv("GIZMOSQL_PASSWORD", "gizmosql_password"),
                     query={"disableCertificateVerification": "True",
                            "useEncryption": "True"
                            }
                     )

    print(f"Database URL: {url}")

    engine = create_engine(url=url)
    Base.metadata.create_all(bind=engine)

    metadata = MetaData()
    metadata.reflect(bind=engine)

    for table_name in metadata.tables:
        print(f"Table name: {table_name}")

    with Session(bind=engine) as session:

        # Try ORM
        session.add(FakeModel(id=1, name="Joe"))
        session.commit()

        joe = session.query(FakeModel).filter(FakeModel.name == "Joe").first()

        assert joe.name == "Joe"

        # Execute some raw SQL
        results = session.execute(statement=text("SELECT * FROM fake")).fetchall()
        print(results)

        # Try a SQLAlchemy table select
        fake: Table = metadata.tables["fake"]
        stmt = select(fake.c.name)

        results = session.execute(statement=stmt).fetchall()
        print(results)


if __name__ == "__main__":
    main()
```

### Superset - connection URL example
Here is a example connection URL (for use with Apache Superset):
```text
gizmosql://gizmosql_username:gizmosql_password@localhost:31337?disableCertificateVerification=True&useEncryption=True
```

To specify a catalog other than the default - you can specify the `catalog` argument - example:
```text
gizmosql://gizmosql_username:gizmosql_password@localhost:31337?disableCertificateVerification=True&useEncryption=True&catalog=test
```


### Credits
Much code and inspiration was taken from repo: https://github.com/Mause/duckdb_engine