在Python中设置数据库连接超时

发布于 2024-08-23 20:55:17 字数 664 浏览 14 评论 0原文

我正在创建一个需要访问数据库的 RESTful API。我正在使用 Restish、Oracle 和 SQLAlchemy。不过，我会尝试尽可能笼统地提出我的问题，而不考虑 Restish 或其他 Web API。

我希望能够为执行查询的连接设置超时。这是为了确保放弃长时间运行的查询，并丢弃（或回收）连接。此查询超时可以是全局值，这意味着我不需要在每次查询或连接创建时更改它。

给出以下代码：

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()

如何修改上面的代码来设置查询超时？此超时也适用于连接创建吗？

这类似于 Java 中 java.sql.Statement 的 setQueryTimeout(int secondary) 方法的作用。

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

别忘他 2024-08-30 20:55:17

对于查询，您可以查看计时器和 conn.cancel() 调用。

这些行中的一些内容：

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

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()

回复收藏 0 原文

感性 2024-08-30 20:55:17

在linux下查看/etc/oracle/sqlnet.ora，

sqlnet.outbound_connect_timeout=value

还有选项：

tcp.connect_timeout和sqlnet.expire_time，祝你好运！

回复收藏 0 原文

若水般的淡然安静女子 2024-08-30 20:55:17

您可以查看设置 PROFILEs 在经过一定数量的 Logical_reads_per_call 和/或 cpu_per_call 后终止查询

回复收藏 0 原文

箹锭⒈辈孓 2024-08-30 20:55:17

对于 Windows 11； sqlnet.ora 中的以下行给了我连接时的超时时间。

tcp.connect_timeout=3

如 betaru 所示

For Windows 11; The below line in sqlnet.ora gave me the said timeout while connecting.

tcp.connect_timeout=3

as indicated by betaru

回复收藏 0 原文

如此安好 2024-08-30 20:55:17

系统警报超时

以下是如何使用操作系统超时来执行此操作。它是通用的，适用于 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!!

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!!

回复收藏 0 原文

~没有更多了~