Python代码取消正在运行的Oracle SQL查询 [英] Python code to cancel a running Oracle SQL Query

问题描述

我有以下python代码，可在Oracle数据库中运行多个SQL查询并将它们组合为一个数据帧.

I have the following python code which runs multiple SQL Queries in Oracle database and combines them into one dataframe.

查询存在于txt文件中，每一行都是单独的SQL查询.循环按顺序运行查询.我想取消所有运行超过10秒的SQL查询，以免在数据库中造成开销. 以下代码实际上并没有给出我想要的结果.更具体地说，这段代码确实对我的问题有帮助:

The queries exist in a txt file and every row is a separate SQL query. The loop runs sequentially the queries. I want to cancel any SQL queries that run for more than 10 secs so as not to create an overhead in the database. The following code doesnt actually me give the results that i want. More specifically this bit of the code really help me on my issue:

        if (time.time() - start) > 10: 
        connection.cancel()

完整的python代码如下.可能是可以调用Oracle函数来取消查询.

Full python code is the following. Probably it is an oracle function that can be called so as to cancel the query.

import pandas as pd
import cx_Oracle
import time

ip = 'XX.XX.XX.XX'
port = XXXX
svc  = 'XXXXXX'
dsn_tns = cx_Oracle.makedsn(ip, port, service_name = svc)

connection = cx_Oracle.connect(user='XXXXXX'
                           , password='XXXXXX'
                           , dsn=dsn_tns
                           , encoding = "UTF-8"   
                           , nencoding = "UTF-8"  
                           )

filepath = 'C:/XXXXX'

appended_data = []

with open(filepath + 'sql_queries.txt') as fp:  
    line = fp.readline()
    while line:
        start = time.time()
        df = pd.read_sql(line, con=connection)
        if (time.time() - start) > 10: 
            connection.cancel()
            print("Cancel")         
        appended_data.append(df)
        df_combined = pd.concat(appended_data, axis=0)
        line = fp.readline()
        print(time.time() - start)
    fp.close()

推荐答案

查看cx_Oracle 7的 Connection.callTimeout 设置.您将需要使用Oracle客户端库18+. (这些将连接到Oracle DB 11.2+).等效的node-oracledb参数的 doc 解释了打印Oracle行为和往返行程.

Look at cx_Oracle 7's Connection.callTimeout setting. You'll need to be using Oracle client libraries 18+. (These will connect to Oracle DB 11.2+). The doc for the equivalent node-oracledb parameter explains the fine print behind the Oracle behavior and round trips.

这篇关于Python代码取消正在运行的Oracle SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！