[Proposal] Some simple high-level feature to set connection timeouts

[Don Nillo]

Hi!

Sorry for bothering, I have not enough skills to contribute yet (

But...

I think it would be great to have some feature to easily set connection timeouts in SQLAlchemy 2.0.

The reason is I faced a problem where I was unable to cancel some erroneous time-consuming queries with SQLAlchemy Core. And I guess, I am not the only one.

My straightforward nooby solution so far is this:

from threading import Timer

with engine.connect() as connection:
  timeout = Timer(MAX_EXECUTION_TIME, lambda: connection.connection.connection.cancel())
  timeout.start()
  r = connection.execute(stmt).freeze() # I just love FrozenResult)
  timeout.cancel()

The bad thing this is dialect-specific and works only due to cancel() method in psycopg2

I was also trying to benefit from handling sqlalchemy.events but failed...

One of my intentions was to modify before_execute() method to catch unintended cartesian product queries and raise error instead of throwing warning.

Unfortunately, at the moment this feels like too low-level for me.

What I wish to have is something like this:

with engine.connect(timeout=MAX_EXECUTION_TIME) as connection:

  r = connection.execute(stmt)

I hope somebody smart enough could seize time to think about it.

This would make me happy. 

Thanks in advance!

[Don Nillo]

UPD: found out cx_Oracle also has Connection.cancel() method. May be things are not that bad

[Jonathan Vanasco]

SQLAlchemy supports connection timeouts to establish a connection already.

SQLAlchemy does not, and can not, support query timeouts.  This is possible with some python database drivers, but very rare.  In every Python database program/library query timeouts are typically handled on the database server, and almost never on Python.  You can use SQLAlchemy's engine events to emit sql that will set/clear a timeout on the database server.

This has come up many times in the past, and there are several threads in the group history that explain the details and offer solutions.

[Don Nillo]

Yup. Sure. I meant "query timeouts", not "connection timeouts". 

May be a good option could be like this:

connection.execute(stmt, timeout=MAX_TIME)

Anyway, thanks.

(Admins, please, feel free to delete this conversation. I'm ashamed for not using search properly. Sorry for that)