Teradata SQL Driver for Python

Warnings

• breaking Starting with version 20.0.0.0, the driver now maps SQL `DECIMAL` types to Python's `Decimal` objects by default, instead of `float`. This change improves precision but may require updates to existing code that expects `float` values for decimal columns.
  Fix: Update your code to handle `Decimal` objects where appropriate. If you explicitly need `float` representation for older code, you might need to convert them manually (e.g., `float(decimal_value)`), though this is generally discouraged for precision-sensitive data.
• breaking Version 20.x introduced support for Teradata Vantage connection string formats and changed some default configuration settings. While traditional keyword arguments for `teradatasql.connect` are largely supported, existing code relying on specific parameter names or default behaviors (e.g., related to `logmech` or session settings) might need review.
  Fix: Consult the `teradatasql` documentation for version 20.x to verify connection parameters and default settings. If encountering connection issues, explicitly specify parameters like `logmech` or review the new connection string format for complex configurations.
• gotcha `teradatasql` defaults to `autocommit=False` (DBAPI 2.0 standard). This means `INSERT`, `UPDATE`, `DELETE`, and `DDL` statements will not be persisted to the database until `con.commit()` is explicitly called. Forgetting to commit will result in data not being saved.
  Fix: Always call `con.commit()` after executing statements that modify the database (DML or DDL). Use `con.rollback()` to undo changes in case of an error. Wrap database operations in `try...except...finally` blocks to ensure `commit()` or `rollback()` and `close()` are called.
• gotcha It is crucial to close both the cursor (`cur.close()`) and the connection (`con.close()`) objects when you are finished with them. Failing to do so can lead to resource leaks on both the client and server sides, exhausting connection pools or consuming memory.
  Fix: Always use `try...finally` blocks to ensure `cur.close()` and `con.close()` are called, even if errors occur during database operations. For simple scripts, a `with` statement for connection is not directly supported by `teradatasql`'s connect method, so explicit closing is necessary.

Install

• pip install teradatasql Install stable version

Imports

• connect

  import teradatasql
  con = teradatasql.connect(...)

  The standard pattern is to import the module and call `teradatasql.connect()`.

Quickstart

This quickstart demonstrates how to establish a connection to a Teradata database, execute DDL and DML statements, commit changes, and query data using the `teradatasql` driver. It highlights the importance of using `os.environ.get` for sensitive credentials and proper resource management by closing cursors and connections. Note that `autocommit` is `False` by default, requiring explicit `con.commit()`.

import teradatasql
import os

# --- Configuration (replace with your Teradata connection details) ---
# It's recommended to use environment variables or a configuration management tool
HOST = os.environ.get('TERADATA_HOST', 'your_teradata_host')
USER = os.environ.get('TERADATA_USER', 'your_username')
PASSWORD = os.environ.get('TERADATA_PASSWORD', 'your_password')
DATABASE = os.environ.get('TERADATA_DATABASE', 'your_database_name') # Optional

if HOST == 'your_teradata_host':
    print("Warning: Please configure TERADATA_HOST, TERADATA_USER, and TERADATA_PASSWORD environment variables or replace placeholders.")
    exit()

con = None
cur = None
try:
    # Connect to Teradata. For Teradata Vantage, the 'host' parameter is 'DBCName'.
    # Additional parameters can be passed as keyword arguments or via a connection string.
    # For instance, if you need a specific logon mechanism: logmech='TD2'
    con = teradatasql.connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
    print("Successfully connected to Teradata.")

    cur = con.cursor()

    # Example: Create a table (if it doesn't exist)
    try:
        cur.execute("CREATE TABLE my_test_table (id INTEGER, name VARCHAR(100));")
        print("Table 'my_test_table' created.")
    except teradatasql.OperationalError as e:
        if "already exists" in str(e).lower():
            print("Table 'my_test_table' already exists.")
        else:
            raise # Re-raise other errors

    # Example: Insert data
    cur.execute("INSERT INTO my_test_table (id, name) VALUES (?, ?);", (1, 'Alice'))
    cur.execute("INSERT INTO my_test_table (id, name) VALUES (?, ?);", (2, 'Bob'))
    con.commit() # Commit the transaction as autocommit=False by default
    print("Data inserted and committed.")

    # Example: Query data
    cur.execute("SELECT id, name FROM my_test_table ORDER BY id;")
    print("\nFetched data:")
    for row in cur.fetchall():
        print(row)

    # Example: Clean up (optional)
    # cur.execute("DROP TABLE my_test_table;")
    # con.commit()
    # print("Table 'my_test_table' dropped.")

except teradatasql.Error as e:
    print(f"Teradata SQL Error: {e}")
    if con:
        con.rollback() # Rollback in case of error
except Exception as e:
    print(f"An unexpected error occurred: {e}")
finally:
    if cur:
        cur.close()
        print("Cursor closed.")
    if con:
        con.close()
        print("Connection closed.")