Metadata-Version: 2.4
Name: superset-sqlalchemy-gizmosql-adbc-dialect
Version: 0.0.7
Summary: An Apache Superset compatible SQLAlchemy dialect for connecting to a [GizmoSQL](https://github.com/gizmodata/gizmosql) server with ADBC
Author-email: Philip Moore <philip@gizmodata.com>
License-Expression: Apache-2.0
Project-URL: Homepage, https://github.com/gizmodata/superset-sqlalchemy-gizmosql-adbc-dialect
Keywords: apache,superset,sqlalchemy,flight-sql,gizmosql,adbc,dialect
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: apache-superset==5.0.*
Requires-Dist: adbc-driver-flightsql==1.8.*
Requires-Dist: psycopg2-binary==2.9.*
Requires-Dist: importlib-metadata==8.7.*
Provides-Extra: test
Requires-Dist: pytest; extra == "test"
Requires-Dist: pytest-snapshot; extra == "test"
Requires-Dist: pytest-mock; extra == "test"
Requires-Dist: filelock; extra == "test"
Requires-Dist: duckdb; extra == "test"
Requires-Dist: dask; extra == "test"
Requires-Dist: distributed; extra == "test"
Requires-Dist: pins[gcs]; extra == "test"
Requires-Dist: docker; extra == "test"
Provides-Extra: dev
Requires-Dist: bumpver; extra == "dev"
Requires-Dist: pip-tools; extra == "dev"
Requires-Dist: pytest; extra == "dev"
Dynamic: license-file

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

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

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