When executing queries against SQL Server via SQL Alchemy (and Pandas), how do I set the timeout for queries?

Question

I am using Python's Pandas and SQLAlchemy packages to query a MS SQL Server 2019, and I want to set the timeout value for queries, but I can't get it working. I have been testing with a three second query timeout against a SQL query that will run for 15 seconds. What is the syntax to achieve this behavior?

import datetime

import pandas as pd
import sqlalchemy

connection_str = "mssql+pyodbc://@ITDEV/FACETS?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server"
engine = sqlalchemy.create_engine(connection_str)
with engine.begin() as conn:
    print(datetime.datetime.now())
    pd.read_sql("WAITFOR DELAY '00:00:15'; SELECT TOP 1 * FROM CMC_SBSB_SUBSC", conn)
    print(datetime.datetime.now())

Answer 1

You're looking for the connection event handler on the SQLAlchemy Connection Pool so that you can set the timeout property of the DBAPI connection object. In brief that looks like this:

@event.listens_for(engine, "connect")
def receive_connect(dbapi_connection, connection_record):
    # This will throw:
    # pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired (0) (SQLExecDirectW)')
    dbapi_connection.timeout = 5
    # dbapi_connection.timeout = 20 # Succeeds

A more complete example of this would be something like:

import datetime
import os
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import URL
from sqlalchemy import event

query_dict = {
    "driver": "ODBC Driver 17 for SQL Server",
    "trustServerCertificate": "yes"
}

connection_str = URL.create(
    "mssql+pyodbc",
    username = os.environ["DB_USER"],
    password = os.environ["DB_PWD"],
    host = os.environ["DB_SERVER"],
    port = 1433,
    database = "msdb",
    query = query_dict,
)

print(f"Connection String: {connection_str}");
engine = create_engine(connection_str)

@event.listens_for(engine, "connect")
def receive_connect(dbapi_connection, connection_record):
    # # This will throw:
    # # pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired (0) (SQLExecDirectW)')
    # dbapi_connection.timeout = 5
    dbapi_connection.timeout = 20 # Succeeds

with engine.begin() as conn:
    print(datetime.datetime.now())
    print(pd.read_sql("WAITFOR DELAY '00:00:15'; SELECT GETDATE() AS NOW", conn).to_string())
    print(datetime.datetime.now())