Set database connection timeout in Python

12
votes

I'm creating a RESTful API which needs to access the database. I'm using Restish, Oracle, and SQLAlchemy. However, I'll try to frame my question as generically as possible, without taking Restish or other web APIs into account.

I would like to be able to set a timeout for a connection executing a query. This is to ensure that long running queries are abandoned, and the connection discarded (or recycled). This query timeout can be a global value, meaning, I don't need to change it per query or connection creation.

Given the following code:

import cx_Oracle
import sqlalchemy.pool as pool

conn_pool = pool.manage(cx_Oracle)
conn = conn_pool.connect("username/p4ss@dbname")
conn.ping()

try:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM really_slow_query")
    print cursor.fetchone()
finally:
    cursor.close()

How can I modify the above code to set a query timeout on it? Will this timeout also apply to connection creation?

This is similar to what java.sql.Statement's setQueryTimeout(int seconds) method does in Java.

Thanks

4
pythondatabaseoraclecx-oraclepython-db-api
Hi, I see that none off answers is accepted did u find any other solution for this? - Ib33X
No, I haven't been able to make this work. The solutions suggested thus far simply do not work. - oneself
Why doesn't conn.cancel() work? What happens when you try Dmitry's solution? - alldayremix
I haven't had an opportunity to test out Dmitry's solution. (I moved on to other things since.) If someone else adds a comment that they've tested it, I can accept that solution. - oneself
Dmitry's solution worked like a charm! After the timeout I get a "ORA-01013: user requested cancel of current operation". So I would accept that anwser - Fabian76

4 Answers

16
votes

for the query, you can look on timer and conn.cancel() call.

something in those lines:

t = threading.Timer(timeout,conn.cancel)
t.start()
cursor = conn.cursor()
cursor.execute(query)
res =  cursor.fetchall()
t.cancel()
4
votes

In linux see /etc/oracle/sqlnet.ora,

sqlnet.outbound_connect_timeout= value

also have options:

tcp.connect_timeout and sqlnet.expire_time, good luck!

0
votes

You could look at setting up PROFILEs in Oracle to terminate the queries after a certain number of logical_reads_per_call and/or cpu_per_call

-3
votes

Timing Out with the System Alarm

Here's how to use the operating system timout to do this. It's generic, and works for things other than Oracle.

import signal
class TimeoutExc(Exception):
    """this exception is raised when there's a timeout"""
    def __init__(self): Exception.__init__(self)
def alarmhandler(signame,frame):
    "sigalarm handler.  raises a Timeout exception"""
    raise TimeoutExc()

nsecs=5
signal.signal(signal.SIGALRM, alarmhandler)  # set the signal handler function
signal.alarm(nsecs)                          # in 5s, the process receives a SIGALRM
try:
    cx_Oracle.connect(blah blah)             # do your thing, connect, query, etc
    signal.alarm(0)                          # if successful, turn of alarm
except TimeoutExc:
    print "timed out!"                       # timed out!!