Home Original page

feat(sqlalchemy-spanner): wire timeout execution option through to DBAPI Connection.timeout

Summary

The SQLAlchemy Spanner dialect does not handle timeout in execution_options. Users cannot set a per-statement gRPC deadline through SQLAlchemy, causing all queries to use the gRPC default timeout of 3600 seconds.

Background

The Spanner SQLAlchemy dialect (SpannerExecutionContext.pre_exec()) reads execution options and forwards them to the DBAPI connection. Currently handled options: read_only, staleness, request_priority, transaction_tag, request_tag. The timeout option is not handled.

The dialect's reset_connection() method resets read_only, staleness, request_priority, transaction_tag, and request_tag on the DBAPI connection when returning connections to the pool. timeout is not reset.

Timeline

Date Commit Event
Apr 2020 Initial commit Dialect created — pre_exec() handles read_only and staleness
Oct 2021 d976fda (PR googleapis/python-spanner#269) request_priority added to pre_exec()
Jan 2023 5bd5076 (PR googleapis/python-spanner#494) transaction_tag and request_tag added to pre_exec()
Jun 2023 66c32a4 (PR googleapis/python-spanner#503) ignore_transaction_warnings added to pre_exec()
None N/A timeout was never added to pre_exec() or reset_connection()

Each new execution option was added incrementally. timeout was not included in any of these additions.

Proposed Change

Add timeout handling to SpannerExecutionContext.pre_exec() following the existing pattern for request_priority:

  1. Read self.execution_options.get("timeout")
  2. Set self._dbapi_connection.connection.timeout = timeout
  3. Reset timeout to None in reset_connection()

Files changed

  1. google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py — Add timeout handling to pre_exec() and reset_connection()

Usage

from sqlalchemy import create_engine

engine = create_engine("spanner:///...")

# Per-connection timeout
with engine.connect().execution_options(timeout=60) as conn:
    conn.execute(text("SELECT * FROM my_table"))

# Engine-level default timeout
engine = create_engine("spanner:///...", execution_options={"timeout": 60})

Prerequisites

This change depends on the DBAPI Connection supporting a timeout property: #16492

Related