Amazon Redshift Dialect for SQLAlchemy

Warnings

• breaking The package name was changed from `redshift_sqlalchemy` to `sqlalchemy_redshift` in version 0.4.0. Older applications may need to update their `pip install` commands and import statements.
  Fix: Update `pip install redshift-sqlalchemy` to `pip install sqlalchemy-redshift` and adjust any direct imports if they were used.
• breaking Support for Python 2.7 was dropped in version 0.8.8. Users on Python 2.7 will need to upgrade to Python 3.4+ or use an older version of `sqlalchemy-redshift`.
  Fix: Upgrade your Python environment to Python 3.4 or newer.
• gotcha This dialect requires an external DBAPI driver (`psycopg2` or `redshift_connector`) to function, but it does not install either of them automatically. You must install one separately.
  Fix: Install `psycopg2-binary` (for convenience) or `redshift_connector` explicitly: `pip install sqlalchemy-redshift psycopg2-binary` or `pip install sqlalchemy-redshift redshift_connector`.
• gotcha When using SQLAlchemy's ORM with Redshift tables, a primary key is required for mapped classes, even if the underlying Redshift table lacks one. Attempting to reflect a table without a primary key will cause issues.
  Fix: When defining your SQLAlchemy model for a Redshift table without a primary key, you can designate a column to act as the primary key using `__mapper_args__ = {'primary_key': [your_column_name]}`.
• gotcha When executing raw SQL statements that involve schema or table names (e.g., `GRANT SELECT ON schema.table TO user`), SQLAlchemy's default parameter binding might quote these identifiers, leading to syntax errors in Redshift.
  Fix: Use f-strings or `sqlalchemy.text()` with direct string formatting (if identifiers are trusted) for such statements, or explicitly use `sqlalchemy.sql.text()` with `.bindparams()` and then use the `AsIs` construct from `psycopg2` if dealing directly with DBAPI. Be cautious of SQL injection risks when directly formatting SQL strings.

Install

• pip install sqlalchemy-redshift Base installation
• pip install sqlalchemy-redshift psycopg2-binary With Psycopg2 (common)
• pip install sqlalchemy-redshift redshift_connector With Redshift Connector

Imports

• create_engine

  import sqlalchemy as sa
  engine = sa.create_engine(...)

  The dialect is loaded implicitly via the connection string prefix (e.g., 'redshift+psycopg2://').

Quickstart

This quickstart demonstrates how to establish a connection to an Amazon Redshift database using `sqlalchemy-redshift` and perform a simple query. It uses environment variables for credentials and connects via the `psycopg2` driver. Remember to install an underlying DBAPI driver like `psycopg2-binary` or `redshift_connector` alongside `sqlalchemy-redshift`.

import sqlalchemy as sa
import os

# Ensure either 'redshift_connector' or 'psycopg2' is installed
# e.g., pip install sqlalchemy-redshift psycopg2-binary

# Redshift connection details from environment variables
REDSHIFT_USER = os.environ.get("REDSHIFT_USER", "your_username")
REDSHIFT_PASSWORD = os.environ.get("REDSHIFT_PASSWORD", "your_password")
REDSHIFT_HOST = os.environ.get("REDSHIFT_HOST", "your_redshift_host.amazonaws.com")
REDSHIFT_PORT = os.environ.get("REDSHIFT_PORT", "5439")
REDSHIFT_DB = os.environ.get("REDSHIFT_DB", "your_database_name")

# Construct connection string using psycopg2 driver (can be 'redshift_connector')
connection_string = (
    f"redshift+psycopg2://{REDSHIFT_USER}:{REDSHIFT_PASSWORD}@"
    f"{REDSHIFT_HOST}:{REDSHIFT_PORT}/{REDSHIFT_DB}"
)

try:
    engine = sa.create_engine(connection_string)
    with engine.connect() as connection:
        # Perform a simple query to verify connection
        result = connection.execute(sa.text("SELECT 1 as id, 'hello' as message")).fetchone()
        if result:
            print(f"Connection successful! Result: ID={result.id}, Message='{result.message}'")
        else:
            print("Connection successful, but no result returned for test query.")
except Exception as e:
    print(f"Error connecting to Redshift: {e}")