Pinot DB-API and SQLAlchemy Dialect

9.1.1 verified Tue May 12 auth: no python install: verified

pinotdb is a Python library providing a DB-API 2.0 interface and a SQLAlchemy dialect for Apache Pinot. It enables Python applications to connect to and query Pinot clusters using standard SQL. The library is actively maintained with frequent releases, currently at version 9.1.1, and requires Apache Pinot server version 0.3.0 or later for its SQL API functionality.

pip install pinotdb==9.1.1

Common errors

error AttributeError: 'NoneType' object has no attribute '_username' ↓

cause This error occurs when pinotdb attempts to use authentication details for a Pinot cluster but the 'auth' object is unexpectedly None, often indicating missing credentials when authentication is enabled on the Pinot server.

fix

Provide explicit 'username' and 'password' parameters in the pinotdb.connect() call if your Pinot cluster requires authentication. For example: conn = connect(host='localhost', port=8000, path='/query/sql', username='your_username', password='your_password').

error requests.exceptions.ConnectionError: HTTPConnectionPool(...) Failed to establish a new connection: [Errno 111] Connection refused ↓

cause The pinotdb client failed to establish a network connection to the specified Pinot broker host and port, likely due to the Pinot broker not running, running on a different address, or network/firewall issues.

fix

Verify that the Pinot broker is running and accessible at the specified 'host' and 'port' in your pinotdb.connect() call. Check network connectivity and ensure no firewall is blocking the connection to the Pinot broker port (commonly 8000 or 8099 for queries).

error pinotdb.exceptions.DatabaseError: {'errorCode': 410, 'message': 'BrokerResourceMissingError'} ↓

cause The Pinot broker successfully received the query but could not find the necessary resources (e.g., the specified table) to process it, typically because the table does not exist or is not properly configured and available in the cluster.

fix

Ensure that the table named in your SQL query exists in the Pinot cluster and is correctly configured and online. Check the Pinot controller UI or logs for table status and broker assignments.

error ModuleNotFoundError: No module named 'pinotdb' ↓

cause The 'pinotdb' Python package is not installed in the current Python environment, or the Python interpreter being used is not the one where the package was installed.

fix

Install the 'pinotdb' package using pip: pip install pinotdb. If using virtual environments, ensure you are activating the correct environment before running your Python script.

Warnings

breaking Python 3.9 support was dropped in `pinotdb` version 7.0.0. ↓

fix Upgrade your Python environment to 3.10 or newer (current requirement: >=3.10, <4).

breaking Python 3.8 support was dropped in `pinotdb` version 6.0.0. ↓

fix Upgrade your Python environment to 3.9 or newer before upgrading to `pinotdb` 6.0.0, or 3.10+ for later versions.

breaking `pinotdb` version 8.0.0 introduced compatibility with SQLAlchemy 2.0. If you are using SQLAlchemy 1.x, you may need to adjust your code or pin to an older `pinotdb` version. ↓

fix Review SQLAlchemy 2.0 migration guides and update your SQLAlchemy-dependent code. Alternatively, pin `pinotdb` to a version prior to 8.0.0 if you must remain on SQLAlchemy 1.x.

gotcha Versions prior to 9.1.1 had a potential issue with clear-text logging of sensitive information. ↓

fix Upgrade to `pinotdb` 9.1.1 or newer to benefit from the fix addressing clear-text logging of sensitive data.

gotcha `pinotdb` versions >= 0.3.2 use the Pinot SQL API and require Apache Pinot server version >= 0.3.0. Older `pinotdb` versions (0.2.x) use the deprecated PQL API. ↓

fix Ensure your Apache Pinot cluster is running a version that supports the SQL API (0.3.0 or later) when using recent `pinotdb` clients. For older Pinot clusters, consider using `pinotdb` 0.2.x if PQL is necessary, but be aware of missing newer features.

gotcha `pinotdb` requires an active Apache Pinot broker to establish a connection. A `Connection refused` error indicates the client could not reach the broker at the specified address and port, possibly because the broker is not running, is misconfigured, or a network firewall is blocking access. ↓

fix Ensure your Apache Pinot broker is running and accessible from the client's network. Verify the host and port configuration used by `pinotdb` and check network connectivity and firewall rules.

Install compatibility verified last tested: 2026-05-12 v9.1.1 (up to date)

python os / libc status wheel install import disk mem side effects

3.10 alpine (musl) wheel - 0.21s 23.2M 8.2M clean

3.10 alpine (musl) - - 0.23s 23.1M 8.1M -

3.10 slim (glibc) wheel 2.4s 0.15s 24M 8.2M clean

3.10 slim (glibc) - - 0.18s 24M 8.1M -

3.11 alpine (musl) wheel - 0.39s 25.6M 9.4M clean

3.11 alpine (musl) - - 0.39s 25.5M 9.4M -

3.11 slim (glibc) wheel 2.4s 0.31s 26M 9.4M clean

3.11 slim (glibc) - - 0.30s 26M 9.4M -

3.12 alpine (musl) wheel - 0.51s 17.3M 9.4M clean

3.12 alpine (musl) - - 0.58s 17.2M 9.4M -

3.12 slim (glibc) wheel 2.2s 0.56s 18M 9.4M clean

3.12 slim (glibc) - - 0.55s 18M 9.4M -

3.13 alpine (musl) wheel - 0.54s 16.7M 9.8M clean

3.13 alpine (musl) - - 0.58s 16.5M 9.8M -

3.13 slim (glibc) wheel 2.1s 0.50s 17M 9.8M clean

3.13 slim (glibc) - - 0.53s 17M 9.8M -

3.9 alpine (musl) build_error - - - - - -

3.9 alpine (musl) - - - - - -

3.9 slim (glibc) build_error - 1.6s - - - -

3.9 slim (glibc) - - - - - -

Imports

connect
```
from pinotdb import connect
```
For direct DB-API 2.0 connections to Pinot.
create_engine
```
from sqlalchemy.engine import create_engine
```
For using the SQLAlchemy dialect to connect to Pinot.

Quickstart last tested: 2026-04-24

This example demonstrates how to connect to an Apache Pinot broker using the DB-API 2.0 interface and execute a simple SQL query. It fetches the count of records from a hypothetical 'baseballStats' table and prints the results and query statistics. Authentication details are optional and can be provided via environment variables.

import os
from pinotdb import connect

# Configure connection details (replace with your Pinot broker host and port)
PINOT_HOST = os.environ.get('PINOT_BROKER_HOST', 'localhost')
PINOT_PORT = int(os.environ.get('PINOT_BROKER_PORT', '8000')) # Default Pinot QuickStart broker port
PINOT_PATH = os.environ.get('PINOT_QUERY_PATH', '/query/sql')
PINOT_SCHEME = os.environ.get('PINOT_SCHEME', 'http')
PINOT_USERNAME = os.environ.get('PINOT_USERNAME', '')
PINOT_PASSWORD = os.environ.get('PINOT_PASSWORD', '')

try:
    if PINOT_USERNAME and PINOT_PASSWORD:
        conn = connect(
            host=PINOT_HOST, 
            port=PINOT_PORT, 
            path=PINOT_PATH, 
            scheme=PINOT_SCHEME,
            username=PINOT_USERNAME,
            password=PINOT_PASSWORD
        )
    else:
        conn = connect(host=PINOT_HOST, port=PINOT_PORT, path=PINOT_PATH, scheme=PINOT_SCHEME)

    curs = conn.cursor()
    curs.execute("""
        SELECT count(*) FROM baseballStats LIMIT 10
    """)

    print("Query Results:")
    for row in curs:
        print(row)

    print(f"Time taken: {curs.timeUsedMs} ms")
    print(f"Query Stats: {curs.query_stats}")

except Exception as e:
    print(f"An error occurred: {e}")
    print("Please ensure a Pinot broker is running and accessible at {PINOT_SCHEME}://{PINOT_HOST}:{PINOT_PORT}")