Teradata SQL Dialect for SQLAlchemy

20.0.0.9 · active · verified Thu Apr 09

teradatasqlalchemy provides a SQL dialect for SQLAlchemy, enabling Python applications to connect to and interact with the Teradata Database. It offers an abstraction layer over the Teradata DBAPI, allowing users to leverage SQLAlchemy's ORM or Core features. The library supports 64-bit Python 3.4 and later. The current version is 20.0.0.9, released on December 15, 2025, with frequent updates to support new Teradata datatypes and features.

Warnings

breaking Older versions of `teradatasqlalchemy` (prior to `17.20.0.0`) are not compatible with SQLAlchemy 2.0 or newer. Attempting to use them together will result in errors.
Fix: Upgrade `teradatasqlalchemy` to version `17.20.0.0` or higher to ensure compatibility with SQLAlchemy 2.0+. If upgrading is not feasible, downgrade SQLAlchemy to a 1.x version.
gotcha There are two main SQLAlchemy dialects for Teradata: `teradatasqlalchemy` (uses `teradatasql://` in connection string) and the older `sqlalchemy-teradata` (uses `teradata://`). `teradatasqlalchemy` is the recommended package for new development as it directly integrates with the `teradatasql` driver and does NOT require a separate ODBC driver installation. The `sqlalchemy-teradata` dialect, which depends on an ODBC driver, is less actively maintained and can lead to connection issues if ODBC is not correctly configured.
Fix: For new projects, always use `teradatasqlalchemy` with the `teradatasql://` connection prefix. If you encounter connection issues, verify which dialect you are attempting to use and ensure the correct underlying driver/ODBC setup is in place for that dialect.
gotcha The `teradatasqlalchemy` library requires 64-bit Python 3.4 or later. 32-bit Python installations are explicitly not supported and will lead to errors.
Fix: Ensure your Python environment is a 64-bit installation compatible with Python 3.4+.
gotcha Incorrect or unescaped connection string parameters are a common source of connection failures. Special characters in `user`, `password`, `database`, or `host` components of the URL should be URL-encoded.
Fix: Carefully construct your connection string, verifying `host`, `user`, `password`, `database`, and `port` (default 1025). URL encode any special characters within these parameters using `urllib.parse.quote_plus` for robust connections.
gotcha When upgrading `teradatasqlalchemy` to a new version, `pip` might use a cached older version, preventing the update. This can lead to unexpected behavior or an inability to access new features/fixes.
Fix: Use the `--no-cache-dir` option with `pip install` to force the download of the new version: `pip install --no-cache-dir teradatasqlalchemy`.

Install

pip install teradatasqlalchemy Install latest version

Imports

create_engine
```
from sqlalchemy import create_engine
```
The dialect is automatically registered and instantiated when 'teradatasql://' is used in the connection string, so explicit import of a dialect class is typically not needed.

Quickstart

This quickstart demonstrates how to establish a connection to a Teradata database using teradatasqlalchemy and SQLAlchemy's `create_engine` function. It uses environment variables for secure credential management and executes a basic query to verify the connection. The `teradatasql://` dialect is recommended for direct connections without ODBC.

import os
from sqlalchemy import create_engine, text

# Get connection details from environment variables for security and flexibility
td_host = os.environ.get("TD_HOST", "your_teradata_host")
td_user = os.environ.get("TD_USER", "your_username")
td_password = os.environ.get("TD_PASSWORD", "your_password")
td_database = os.environ.get("TD_DATABASE", "your_database") # Optional, can be empty

# Construct the connection string using the 'teradatasql' dialect
# This dialect (teradatasql://) does NOT require an ODBC driver.
# If td_database is provided, include it in the connection string.
if td_database:
    connection_string = f"teradatasql://{td_user}:{td_password}@{td_host}/?database={td_database}"
else:
    connection_string = f"teradatasql://{td_user}:{td_password}@{td_host}"

print(f"Attempting to connect to Teradata at {td_host}...")

try:
    # Create the SQLAlchemy engine
    engine = create_engine(connection_string)

    # Establish a connection and execute a simple query
    with engine.connect() as connection:
        # Example: Execute a simple SELECT 1 query to verify connection
        result = connection.execute(text("SELECT 1 AS test_column")).scalar()
        print(f"Connection successful! Query result: {result}")

        # Example: Execute a query to fetch data from a table
        # Make sure 'your_table' and 'your_column' exist in your Teradata database
        # try:
        #     sample_data = connection.execute(text("SELECT TOP 5 column1 FROM your_table")).fetchall()
        #     print("Sample data:", sample_data)
        # except Exception as query_e:
        #     print(f"Error executing sample query: {query_e}")

except Exception as e:
    print(f"Error connecting to Teradata: {e}")
    print("Please ensure environment variables (TD_HOST, TD_USER, TD_PASSWORD, TD_DATABASE) are set correctly ")
    print("and network connectivity to the Teradata host on port 1025 (default) is available.")

view raw JSON →