pymssql - query_timeout

[charles pierre]

Hi,

Thank you for your fantastic work. It is really easy and efficient to work with your library. Most of the time we are using it to work with postgres and it works like a charm but one of our client is using a SQL server and so we have to work with pymssql - we would like to set a query_timeout (http://www.pymssql.org/en/latest/ref/_mssql.html?highlight=query_timeout#_mssql.MSSQLConnection.query_timeout) so we added the following code:

engine = create_engine(cls.db_uri, query_timeout=2)
with engine.begin() as transaction:
      query = cls.get_query(*args, **kwargs)
      result_proxy = transaction.execute(query)

The URI we are using is the following: mssql+pymssql://username:password@localhost:port/DB
The query_timeout parameter seems to be recognized by create_engine but is not active when we execute the query. Is it a bug or did we miss something ?

[Mike Bayer]

per pymssql's documentation, "query_timeout" is not available on the connect() function; I'd recommend you raise the issue with them that query_timeout should be made available on connect().

in the meantime, to set an attribute upon connect use the connect event (http://docs.sqlalchemy.org/en/rel_1_0/core/events.html?highlight=connectionevents#sqlalchemy.events.PoolEvents.connect):

from sqlalchemy import event

engine = create_engine(...)

@event.listens_for(engine, 'connect')
def receive_connect(dbapi_connection, connection_record):
    dbapi_connection.query_timeout = 30

[charles pierre]

Thank you for the quick reply. See https://github.com/pymssql/pymssql/issues/410 for the issue on pymssql side.
For the sake of correctness and other people that may stumble on this thread, the correct way to activate query_timeout with sqlalchemy is using dbapi_connection._conn.query_timeout = 30 on the connect event (and not dbapi_connection.query_timeout = 30 as stated above).

Thanks again @zzzeek

[Mike Bayer]

there's no "_conn" attribute in SQLAlchemy, are you sure that's not also a bug in pymssql ?