Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set database connection timeout in Python

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

like image 414
oneself Avatar asked Mar 03 '10 18:03

oneself


2 Answers

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()
like image 79
Dmitry Khrisanov Avatar answered Sep 28 '22 09:09

Dmitry Khrisanov


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

sqlnet.outbound_connect_timeout= value

also have options:

tcp.connect_timeout and sqlnet.expire_time, good luck!

like image 25
BetarU Avatar answered Sep 28 '22 09:09

BetarU