How to set a query timeout in sqlalchemy using Oracle database?

Question

I want to create a query timeout in sqlalchemy. I have an oracle database.

I have tried following code:

import sqlalchemy
engine = sqlalchemy.create_engine('oracle://db', connect_args={'querytimeout': 10})

I got following error:

TypeError: 'querytimeout' is an invalid keyword argument for this function

I would like a solution looking like:

connection.execute('query').set_timeout(10)

Maybe it is possible to set timeout in sql query? I found how to do it in pl/sql, but i need just sql.

How could i set a query timeout?

Answer 1

timeout decorator

Get your session handle as you normally would. (Notice that the session has not actually connected yet.) Then, test the session in a function that is decorated with wrapt_timeout_decorator.timeout.

#!/usr/bin/env python3


from time import time
from cx_Oracle import makedsn
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text
from wrapt_timeout_decorator import timeout


class ConnectionTimedOut(Exception):
    pass


class Blog:

    def __init__(self):
        self.port = None

    def connect(self, connection_timeout):
        @timeout(connection_timeout, timeout_exception=ConnectionTimedOut)
        def test_session(session):
            session.execute(text('select dummy from dual'))

        session = sessionmaker(bind=self.engine())()
        test_session(session)
        return session

    def engine(self):
        return create_engine(
            self.connection_string(),
            max_identifier_length=128
        )

    def connection_string(self):
        driver = 'oracle'
        username = 'USR'
        password = 'solarwinds123'
        return '%s://%s:%s@%s' % (
            driver,
            username,
            password,
            self.dsn()
        )

    def dsn(self):
        host = 'hn.com'
        dbname = 'ORCL'
        print('port: %s expected: %s' % (
            self.port,
            'success' if self.port == 1530 else 'timeout'
        ))
        return makedsn(host, self.port, dbname)

    def run(self):
        self.port = 1530
        session = self.connect(connection_timeout=4)
        for r in session.execute(text('select status from v$instance')):
            print(r.status)
        self.port = 1520
        session = self.connect(connection_timeout=4)
        for r in session.execute(text('select status from v$instance')):
            print(r.status)


if __name__ == '__main__':
    Blog().run()

In this example, the network is firewalled with port 1530 open. Port 1520 is blocked and leads to a TCP connection timeout. Output:

port: 1530 expected: success
OPEN
port: 1520 expected: timeout
Traceback (most recent call last):
  File "./blog.py", line 68, in <module>
    Blog().run()
  File "./blog.py", line 62, in run
    session = self.connect(connection_timeout=4)
  File "./blog.py", line 27, in connect
    test_session(session)
  File "/home/exagriddba/lib/python3.8/site-packages/wrapt_timeout_decorator/wrapt_timeout_decorator.py", line 123, in wrapper
    return wrapped_with_timeout(wrap_helper)
  File "/home/exagriddba/lib/python3.8/site-packages/wrapt_timeout_decorator/wrapt_timeout_decorator.py", line 131, in wrapped_with_timeout
    return wrapped_with_timeout_process(wrap_helper)
  File "/home/exagriddba/lib/python3.8/site-packages/wrapt_timeout_decorator/wrapt_timeout_decorator.py", line 145, in wrapped_with_timeout_process
    return timeout_wrapper()
  File "/home/exagriddba/lib/python3.8/site-packages/wrapt_timeout_decorator/wrap_function_multiprocess.py", line 43, in __call__
    self.cancel()
  File "/home/exagriddba/lib/python3.8/site-packages/wrapt_timeout_decorator/wrap_function_multiprocess.py", line 51, in cancel
    raise_exception(self.wrap_helper.timeout_exception, self.wrap_helper.exception_message)
  File "/home/exagriddba/lib/python3.8/site-packages/wrapt_timeout_decorator/wrap_helper.py", line 178, in raise_exception
    raise exception(exception_message)
__main__.ConnectionTimedOut: Function test_session timed out after 4.0 seconds

Caution

Do not decorate the function that calls sessionmaker, or you will get:

_pickle.PicklingError: Can't pickle <class 'sqlalchemy.orm.session.Session'>: it's not the same object as sqlalchemy.orm.session.Session

SCAN

This implementation is a "connection timeout" without regard to underlying cause. The client could time out before trying all available SCAN listeners.

Answer 2

The only way how you can set connection timeout for the Oracle engine from the Sqlalchemy is create and configure the sqlnet.ora

Linux

Create file sqlnet.ora in folder

/opt/oracle/instantclient_19_9/network/admin

Windows

For windows please create such folder as \network\admin

C:\oracle\instantclient_19_9\network\admin

Example sqlnet.ora file

SQLNET.INBOUND.CONNECT_TIMEOUT = 120
SQLNET.SEND_TIMEOUT = 120
SQLNET.RECV_TIMEOUT = 120

More parameters you can find here https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm

Answer 3

The way to do it in Oracle is via resource manager. Have a look here