MS-SQL/PyODBC/Linux: How to set a query timeout?

[Daniel Haude]

Occasionally my Flask/WSGI application gets stuck while executing a query against an SQL Server database. I have no control over that server, so maybe I'll never be able to fix the issue, but at least I'd like to be able to present my users with a meaningful error message rather than a "500 Internal server error" caused by the web server's eventually giving up on the request. So I'd like to limit the SQL query execution to a reasonable timeout which, upon running out, would raise an exception which I could catch and deal with.

It seems the database query goes through several layers: SQLAlchemy -> PyODBC -> unixodbc -> MS ODBC Driver -> database. I have no idea which of those elements would be responsible for enforcing a query timeout, nor how to specify one. My connection URI looks like this:

mssql+pyodbc://user@host/db?driver=ODBC+Driver+17+for+SQL+Server

I don't need to specify a per-query timeout. Just once for the whole application.

Thanks!

[Mike Bayer]

it looks like pyodbc has a timeout attribute on connection:

https://code.google.com/archive/p/pyodbc/wikis/Connection.wiki

but no init argument for that so youd need to use an event

engine = create_engine("mssql+pyodbc:// ...")

from sqlalchemy import event

@event.listens_for(engine, "connect")

def receive_connect(dbapi_connection, connection_record):

    dbapi_connection.timeout = 30

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3b716143-633e-4c94-ba4d-b1f3d1c38f6co%40googlegroups.com.