SQLAlchemy PGSpider Dialect

Common errors

• sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:pgspider

  cause The `sqlalchemy-pgspider` package is not installed or its entry point for registering the dialect with SQLAlchemy is not recognized.
  fix Ensure the package is correctly installed: `pip install sqlalchemy-pgspider`.

• ModuleNotFoundError: No module named 'psycopg2'

  cause The Python database driver for PostgreSQL (`psycopg2-binary` or `psycopg`), which `sqlalchemy-pgspider` relies on for actual connectivity, is not installed.
  fix Install a compatible PostgreSQL driver: `pip install psycopg2-binary` (or `pip install psycopg` if you prefer).

• sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (...) failed: FATAL: password authentication failed for user "postgres"

  cause The connection parameters (host, port, username, password, or database) in the `pgspider://` URI are incorrect, or the PGSpider server is not running or accessible at the specified address.
  fix Verify your `pgspider://` connection string details (user, password, host, port, dbname) and ensure your PGSpider instance is running and reachable from your application.

Warnings

• gotcha A separate PostgreSQL database driver (e.g., `psycopg2-binary` or `psycopg`) must be installed for `sqlalchemy-pgspider` to function correctly, as PGSpider is PostgreSQL-compatible. The dialect registers itself, but the underlying driver handles the actual network communication.
  Fix: Install a suitable driver: `pip install psycopg2-binary` or `pip install psycopg`.
• gotcha As a 0.1.0 release, `sqlalchemy-pgspider` is in its early stages. This means that advanced SQLAlchemy features (e.g., specific ORM capabilities, complex schema reflection) or unique PGSpider features might have limited or no direct support, requiring raw SQL.
  Fix: Review the official GitHub repository for updates on feature support. For unsupported features, consider using `connection.execute(text(...))` for raw SQL queries.
• gotcha The `pgspider://` connection string must be precisely configured to match your PGSpider server's host, port, database name, username, and password. Incorrect parameters are a common source of `OperationalError`.
  Fix: Double-check all components of your `pgspider://user:password@host:port/database` URI against your PGSpider server's configuration and ensure the server is running and accessible.

Install

• pip install sqlalchemy-pgspider Install core package
• pip install psycopg2-binary Install PostgreSQL driver (recommended)

Imports

• create_engine

  from sqlalchemy import create_engine

  The `sqlalchemy-pgspider` package registers the 'pgspider' dialect with SQLAlchemy upon installation. You typically don't import symbols directly from `sqlalchemy_pgspider`; instead, you use `create_engine` with a 'pgspider://' URI.

Quickstart

This quickstart demonstrates how to establish a connection to a PGSpider database using `create_engine` with the 'pgspider://' URI and execute a simple query. Ensure you have the `PGSPIDER_CONN_STR` environment variable set or update the placeholder directly.

from sqlalchemy import create_engine, text
import os

# Replace with your PGSpider connection details
# For example: "pgspider://user:password@host:port/database"
PGSPIDER_CONN_STR = os.environ.get('PGSPIDER_CONN_STR', 'pgspider://postgres:password@localhost:5432/pgspider_db')

try:
    engine = create_engine(PGSPIDER_CONN_STR)

    with engine.connect() as connection:
        result = connection.execute(text("SELECT 1"))
        print(f"PGSpider connection successful. Result: {result.scalar()}")

except Exception as e:
    print(f"Error connecting to PGSpider: {e}")