Python同一函数调用双数据库查询，第二个查询报‘表或视图不存在’

DevOps 小满

2026-6-23

问题描述

尝试在一个函数中合并来自两个不同数据库的查询结果，单独执行两个查询函数时均能正常运行，但在同一函数中先后调用它们时，无论执行顺序如何，第二个执行的查询总会返回TABLE OR VIEW DOES NOT EXIST错误。

相关代码如下：

def execute_query1():
    pool_AVECOM = get_pool(IP_AVECOM, PORT_AVECOM, SERVICENAME_AVECOM, USERNAME_AVECOM, PW_AVECOM)
    conn1 = pool_AVECOM.acquire()
    cursor = conn1.cursor()
    query = """
    SELECT
        TEC.COD_RESPONSAVEL COD_TECNICO,
        TEC.NOM_RESPONSAVEL NOME_TECNICO,
        GRA.COD_CRIADOR_PROPRIETARIO COD_CRIADOR,
        GRA.NOM_GRANJA NOME_GRANJA,
        REPLACE(TRANSLATE(GRA.CGC_CPF_CRIADOR , '. - /' , ' '), ' ','') CGC_CPF_CRIADOR,
        LOT.NRO_LOTE LOTE,
        (CASE WHEN LOT.DAT_INICIAL_ALOJAMENTO > SYSDATE
            THEN NULL
            ELSE LOT.DAT_INICIAL_ALOJAMENTO
            END ) DAT_INICIAL_ALOJAMENTO,
        DECODE(F.TIPMOV,NULL,'ABERTO','FECHADO') STATUS,
        LOT.QTD_AVES_ALOJADAS,
        NVL(RPI.QTD_AVES_MORTAS, 0) MORTALIDADE,
        LOT.DAT_FECHAMENTO,
        LOT.COD_SEXO SEXO
    FROM AVECOM.FCFSLOT0 LOT
    JOIN AVECOM.FCFSGRA0 GRA ON GRA.COD_GRANJA = LOT.COD_GRANJA AND GRA.COD_CRIADOR_PROPRIETARIO = LOT.COD_CRIADOR AND LOT.COD_LOCAL = GRA.COD_LOCAL
    JOIN AVECOM.FCFSRES0 TEC ON TEC.COD_RESPONSAVEL = GRA.COD_RESPONSAVEL
    LEFT JOIN AVECOM.FCFSRPI0 RPI ON LOT.NRO_LOTE = RPI.NRO_LOTE
    LEFT JOIN AVECOM.FCFSPAV0 P ON LOT.NRO_LOTE = P.NRO_LOTE
    LEFT JOIN AVECOM.AVECOM_MIT F ON LOT.NRO_LOTE = F.CLOTE AND F.TIPMOV = 'P01016'
    WHERE 1 = 1
    AND LOT.DAT_INICIAL_ALOJAMENTO >= ( SYSDATE - 130 )
    AND GRA.FLG_ATIVA = 1
    GROUP BY
    TEC.COD_RESPONSAVEL,
    TEC.NOM_RESPONSAVEL,
    GRA.COD_CRIADOR_PROPRIETARIO,
    GRA.NOM_GRANJA,
    REPLACE(TRANSLATE(GRA.CGC_CPF_CRIADOR , '. - /' , ' '), ' ',''),
    LOT.NRO_LOTE,
    (CASE WHEN LOT.DAT_INICIAL_ALOJAMENTO > SYSDATE
            THEN NULL
            ELSE LOT.DAT_INICIAL_ALOJAMENTO
            END ),
    DECODE(F.TIPMOV,NULL,'ABERTO','FECHADO'),
    LOT.QTD_AVES_ALOJADAS,
    NVL(RPI.QTD_AVES_MORTAS, 0),
    LOT.DAT_FECHAMENTO,   
    LOT.COD_SEXO
    """
    cursor.execute(query)
    result = cursor.fetchall()
    print(result)
    cursor.close()
    conn1.close()
    return result

# Função para executar a segunda query e retornar o resultado como um dicionário
def execute_query2():
    pool_TOTVS = get_pool(IP_TOTVS, PORT_TOTVS, SERVICENAME_TOTVS, USERNAME_TOTVS, PW_TOTVS)
    conn2 = pool_TOTVS.acquire()
    cursor = conn2.cursor()
    query = """
    SELECT
        A2_COD,
        A2_CGC,
        A2_NOME
    FROM
        U_C0OLTW_PR.SA2010
    WHERE D_E_L_E_T_ <> '*'
    """
    cursor.execute(query)
    result = {row[1]: (row[0], row[2]) for row in cursor.fetchall()}
    print(result)
    cursor.close()
    conn2.close()
    return result

调用代码：

def mainTEST():
    # Conectar ao primeiro banco de dados (substitua os detalhes da conexão conforme necessário)

    # Conectar ao segundo banco de dados (substitua os detalhes da conexão conforme necessário)

    # Executar a primeira consulta
    result1 = execute_query1()
    print(result1)
    teste.sleep(10)

    # Executar a segunda consulta
    result2 = execute_query2()
    print(result2)

    # Realizar a junção dos resultados
    # merged_result = merge_results(result1, result2)

    # Fechar as conexões
    # conn1.close()
    # conn2.close()
    # for row in merged_result:
    #     print(row)
    # Retornar o resultado
    # return merged_result

解决方案

以下是几种可行的排查和修复思路：

隔离连接池实例
检查get_pool函数是否为两个数据库创建了独立的连接池实例。如果存在全局共享的连接池变量，会导致第二个查询复用第一个数据库的连接上下文，从而找不到对应表。确保每个数据库的连接池是独立初始化的，没有状态共享。

使用with语句自动管理资源
手动调用cursor.close()和conn.close()可能存在异常情况下资源未正确释放的问题，导致后续连接被污染。改用with语句自动处理连接和游标生命周期：

def execute_query1():
    pool_AVECOM = get_pool(IP_AVECOM, PORT_AVECOM, SERVICENAME_AVECOM, USERNAME_AVECOM, PW_AVECOM)
    with pool_AVECOM.acquire() as conn1:
        with conn1.cursor() as cursor:
            query = """你的SQL语句"""
            cursor.execute(query)
            result = cursor.fetchall()
            print(result)
            return result

def execute_query2():
    pool_TOTVS = get_pool(IP_TOTVS, PORT_TOTVS, SERVICENAME_TOTVS, USERNAME_TOTVS, PW_TOTVS)
    with pool_TOTVS.acquire() as conn2:
        with conn2.cursor() as cursor:
            query = """你的SQL语句"""
            cursor.execute(query)
            result = {row[1]: (row[0], row[2]) for row in cursor.fetchall()}
            print(result)
            return result

显式验证连接上下文
在第二个查询执行前，打印当前连接的数据库信息，确认连接指向正确的数据库：

def execute_query2():
    pool_TOTVS = get_pool(IP_TOTVS, PORT_TOTVS, SERVICENAME_TOTVS, USERNAME_TOTVS, PW_TOTVS)
    conn2 = pool_TOTVS.acquire()
    cursor = conn2.cursor()
    # 验证当前连接的schema
    cursor.execute("SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL")
    print("当前连接的schema:", cursor.fetchone()[0])
    # 执行原查询
    query = """你的SQL语句"""
    cursor.execute(query)
    result = {row[1]: (row[0], row[2]) for row in cursor.fetchall()}
    print(result)
    cursor.close()
    conn2.close()
    return result

如果输出的schema不是U_C0OLTW_PR，说明连接上下文错误，需要排查连接池的初始化逻辑。

检查数据库驱动的会话隔离
部分数据库驱动（如cx_Oracle）可能会在进程内共享会话状态。尝试在获取连接后显式设置会话属性，比如：
```
# 在execute_query2中添加
cursor.execute("ALTER SESSION SET CURRENT_SCHEMA = U_C0OLTW_PR")
```

内容的提问来源于stack exchange，提问作者pedro.curti