{"id":1879,"library":"sqlalchemy-redshift","title":"Amazon Redshift Dialect for SQLAlchemy","description":"sqlalchemy-redshift is an active project that provides an Amazon Redshift dialect for SQLAlchemy. It extends SQLAlchemy to support Redshift-specific syntax and operations, allowing Python applications to interact seamlessly with Amazon Redshift data warehouses. The library maintains a regular release schedule, with version 0.8.14 being the latest as of April 2023, indicating frequent patch and minor updates.","status":"active","version":"0.8.14","language":"en","source_language":"en","source_url":"https://github.com/sqlalchemy-redshift/sqlalchemy-redshift","tags":["SQLAlchemy","Redshift","AWS","database","ORM","dialect","data warehouse"],"install":[{"cmd":"pip install sqlalchemy-redshift","lang":"bash","label":"Base installation"},{"cmd":"pip install sqlalchemy-redshift psycopg2-binary","lang":"bash","label":"With Psycopg2 (common)"},{"cmd":"pip install sqlalchemy-redshift redshift_connector","lang":"bash","label":"With Redshift Connector"}],"dependencies":[{"reason":"Core dependency for database interaction and ORM capabilities.","package":"sqlalchemy","optional":false},{"reason":"Required DBAPI driver to connect to Redshift. User must choose this or 'redshift_connector'.","package":"psycopg2","optional":true},{"reason":"Required DBAPI driver to connect to Redshift. User must choose this or 'psycopg2'. Provides native IAM auth and Redshift-specific data types (>= 0.8.6).","package":"redshift_connector","optional":true}],"imports":[{"note":"The dialect is loaded implicitly via the connection string prefix (e.g., 'redshift+psycopg2://').","symbol":"create_engine","correct":"import sqlalchemy as sa\nengine = sa.create_engine(...)"}],"quickstart":{"code":"import sqlalchemy as sa\nimport os\n\n# Ensure either 'redshift_connector' or 'psycopg2' is installed\n# e.g., pip install sqlalchemy-redshift psycopg2-binary\n\n# Redshift connection details from environment variables\nREDSHIFT_USER = os.environ.get(\"REDSHIFT_USER\", \"your_username\")\nREDSHIFT_PASSWORD = os.environ.get(\"REDSHIFT_PASSWORD\", \"your_password\")\nREDSHIFT_HOST = os.environ.get(\"REDSHIFT_HOST\", \"your_redshift_host.amazonaws.com\")\nREDSHIFT_PORT = os.environ.get(\"REDSHIFT_PORT\", \"5439\")\nREDSHIFT_DB = os.environ.get(\"REDSHIFT_DB\", \"your_database_name\")\n\n# Construct connection string using psycopg2 driver (can be 'redshift_connector')\nconnection_string = (\n    f\"redshift+psycopg2://{REDSHIFT_USER}:{REDSHIFT_PASSWORD}@\"\n    f\"{REDSHIFT_HOST}:{REDSHIFT_PORT}/{REDSHIFT_DB}\"\n)\n\ntry:\n    engine = sa.create_engine(connection_string)\n    with engine.connect() as connection:\n        # Perform a simple query to verify connection\n        result = connection.execute(sa.text(\"SELECT 1 as id, 'hello' as message\")).fetchone()\n        if result:\n            print(f\"Connection successful! Result: ID={result.id}, Message='{result.message}'\")\n        else:\n            print(\"Connection successful, but no result returned for test query.\")\nexcept Exception as e:\n    print(f\"Error connecting to Redshift: {e}\")","lang":"python","description":"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`."},"warnings":[{"fix":"Update `pip install redshift-sqlalchemy` to `pip install sqlalchemy-redshift` and adjust any direct imports if they were used.","message":"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.","severity":"breaking","affected_versions":"0.4.0 and earlier"},{"fix":"Upgrade your Python environment to Python 3.4 or newer.","message":"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`.","severity":"breaking","affected_versions":"0.8.8+"},{"fix":"Install `psycopg2-binary` (for convenience) or `redshift_connector` explicitly: `pip install sqlalchemy-redshift psycopg2-binary` or `pip install sqlalchemy-redshift redshift_connector`.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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]}`.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-09T00:00:00.000Z","next_check":"2026-07-08T00:00:00.000Z"}