Add SQLAlchemy Engine Support for Teradata Connectivity, Pooling, Legacy Cursors, and teradataml Compatibility

[remi-td]

Currently, our MCP server uses the native teradatasql driver for all Teradata database interactions. While this provides minimal overhead and direct control, it requires us to implement and maintain custom connection pooling, retry logic, and reconnection handling. The introduction of teradataml also added a second connection stream, specifically for the teradataml API.

By adopting SQLAlchemy as our unified engine, we will:

• Leverage its mature connection pool (e.g. QueuePool) with built-in health checks and auto-reconnect (pool_pre_ping).
• Provide a consistent engine interface for both our SQL queries and integration with the teradataml library (so we use a single connection).
• Retain full compatibility with existing “classic” tools that call .cursor() by unwrapping raw DBAPI connections.

Proposal

1. Introduce SQLAlchemy dependency
2. Create a factory function (e.g. get_engine(config)) that returns a configured SQLAlchemy Engine for Teradata, with: pool_size, max_overflow, pool_recycle, pool_pre_ping (these can be exposed as application parameters).
3. Support for legacy cursor-based code
   • Document two patterns:
   1. engine.raw_connection()––obtain a DBAPI connection and call .cursor() exactly as before.
   2. with engine.connect() as conn: raw = conn.connection; cursor = raw.cursor()––use pooled checkout + raw cursor.
      • This ensures existing tools using connection.cursor() can remain unchanged.
   3. Refactor data-access layer
      • Replace direct teradatasql.connect() calls with engine.connect() or scoped sessions.
      • Gradually migrate raw SQL execution to SQLAlchemy Core constructs where it reduces verbosity.
   4. Integrate with teradataml
      • Supply our new engine into teradataml.create_context(engine=...) to unify session management for both SQL and teradataml APIs.

[dtehan-td]

I vote for the change as it would also address #68 which would allow us to address the larger PR #58

[dtehan-td]

We need to revise the EVS connection code, and move evs_connect back into the evs module.

[dtehan-td]

@xuhaibintd As you look at your revisions, can you consider the changes in approach of TD connection