googleapis / googleapis/google-cloud-python

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

Open
#16,467 0 comments 0 reactions 0 assignees View on GitHub
priority: p2 type: bug
Dominant language
Python
Stars
5.4k
Forks
1.8k
Avg merge
3d 4h
Merged PRs (30d)
122

Description

## 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

```python
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: googleapis/google-cloud-python#16492

## Related

- DBAPI issue: googleapis/google-cloud-python#16492
- `_SnapshotBase.execute_sql()` has accepted `timeout=` since November 2018 (PR #6536 in the original monorepo)
- This code was previously in `googleapis/python-spanner-sqlalchemy` (now archived) and moved to `googleapis/google-cloud-python/packages/sqlalchemy-spanner`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.