firebolt-sqlalchemy

The Firebolt dialect for SQLAlchemy. firebolt-sqlalchemy uses Firebolt's Python SDK which implements PEP 249.

• SQLAlchemy Dialects
• PyPI Package

Installation

Requires Python >=3.7.

pip install firebolt-sqlalchemy

Connecting

Connection strings use the following structure:

firebolt://{client_id}:{client_secret}@{database}[/{engine_name}]?account_name={name}

engine_name is optional.

account_name is required.

Examples:

firebolt://aaa-bbb-ccc-222:$ecret@sample_database?account_name=my_account
firebolt://aaa-bbb-ccc-222:$ecret@sample_database/sample_engine?account_name=my_account

To override the API URL (e.g. for dev testing):

export FIREBOLT_BASE_URL=<your_url>

If your secret contains % or / characters they need to be sanitised as per https://docs.sqlalchemy.org/en/20/core/engines.html#database-urls

my_secret = "0920%/2"
import urllib.parse
new_secret = urllib.parse.quote_plus(my_secret)

Connecting with Firebolt Core

Firebolt Core is free self-hosted version of Firebolt.

In order to connect to it you can provide a simplified version of the connection string:

firebolt://{database}?url={url}

{database} is you Firebolt Core database. By default this is firebolt

{url} is a fully qualified URL (with port) where your Firebolt Core is hosted. By default it's http://localhost:3473

If you are running Firebolt Core locally with defaults the connection string will be firebolt://firebolt?url=http://localhost:3473

Quick Start

import urllib.parse
from sqlalchemy import create_engine

secret = urllib.parse.quote_plus("your_secret_here")
engine = create_engine("firebolt://aaa-bbb-ccc-222:" + secret + "@sample_database/sample_engine?account_name=my_account")
connection = engine.connect()

connection.execute("CREATE FACT TABLE example(dummy int) PRIMARY INDEX dummy")
connection.execute("INSERT INTO example(dummy) VALUES (11)")
result = connection.execute("SELECT * FROM example")
for item in result.fetchall():
    print(item)

AsyncIO extension

import urllib.parse
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine

secret = urllib.parse.quote_plus("your_secret_here")
engine = create_async_engine("asyncio+firebolt://aaa-bbb-ccc-222:" + secret + "@sample_database/sample_engine?account_name=my_account")

async with engine.connect() as conn:

    await conn.execute(
        text(f"INSERT INTO example(dummy) VALUES (11)")
    )

    result = await conn.execute(
        text(f"SELECT * FROM example")
    )
    print(result.fetchall())

await engine.dispose()

Limitations

1. Transactions are not supported since Firebolt database does not support them at this time.
2. Parametrised calls to execute and executemany are not implemented.

Contributing

See: CONTRIBUTING.MD