dialect

Redshift Dialect for Sqlalchemy Integration

class simple_aws_redshift.dialect.RedshiftPostgresDialect(executemany_mode='values_only', executemany_batch_page_size=100, **kwargs)[source]

Custom SQLAlchemy dialect for AWS Redshift using psycopg2.

Limitations:

  • Table Creation Limitation: Since we have not implemented a full Redshift dialect,

    it’s not possible to use SQLAlchemy to create tables. Redshift tables have special attributes such as Distribution Keys and Sort Keys, which SQLAlchemy does not understand. Therefore, you cannot use SQLAlchemy’s metadata-based create_all() functionality. You must write raw CREATE TABLE statements manually. However, once the tables are created, you can still use SQLAlchemy’s ORM and Table objects to write queries, which remains an elegant experience.

  • Metadata Reflection Issue: Because SQLAlchemy assumes the backend is

    standard PostgreSQL, using the MetaData.reflect() function to introspect and reconstruct database DDL objects in memory doesn’t work. Redshift’s PostgreSQL-compatible schema differs from the official PostgreSQL standard, and the reflection functionality cannot accurately interpret those differences.

  • Special Syntax Support: For Redshift-specific syntax such as the COPY command,

    COPY FROM S3, and UNLOAD, you must write raw SQL — there is no ORM-level abstraction. However, you can still use SQLAlchemy’s transaction context manager to manage the transactional scope of these operations.

  • Data Type Limitation: Redshift-specific data types are not directly supported

    in the ORM. You need to either use generic data types or handle special types through raw SQL statements.

Reference:

driver: str = 'psycopg2'

identifying name for the dialect’s DBAPI

supports_statement_cache: bool = True

indicates if this dialect supports caching.

All dialects that are compatible with statement caching should set this flag to True directly on each dialect class and subclass that supports it. SQLAlchemy tests that this flag is locally present on each dialect subclass before it will use statement caching. This is to provide safety for legacy or new dialects that are not yet fully tested to be compliant with SQL statement caching.

Added in version 1.4.5.

See also

engine_thirdparty_caching

supports_server_side_cursors: bool = True

indicates if the dialect supports server side cursors

supports_sane_multi_rowcount: bool = False

Indicate whether the dialect properly implements rowcount for UPDATE and DELETE statements when executed via executemany.

returns_native_bytes: bool = False

indicates if Python bytes() objects are returned natively by the driver for SQL “binary” datatypes.

Added in version 2.0.11.