Невозможно подключиться к базе данных Oracle 19c с помощью SSH-туннеля с использованием Python

Я не являюсь экспертом ни по Oracle, ни по Python, поэтому, возможно, ответ очевиден, но я так и не смог найти ответ на свою проблему на форумах. У меня есть SSH-соединение с базой данных Oracle 19c для разработчиков SQL и DBeaver, но я не могу подключиться к Python. Я пробовал многое, например проверять, нужен ли толстый режим, но получал разные ошибки.

В тонком режиме туннель SSH создан, но соединение Oracle вызывает ошибки.

В толстом режиме зависло.

Фрагменты кода приведены ниже.

Есть ли у вас какие-либо идеи, в чем проблема и какое может быть решение? Спасибо

Тонкий режим:

class SSHTunnel:

    def __init__(self, ssh_host, ssh_port, ssh_user, ssh_key_file, remote_bind_host, remote_bind_port,
                 local_bind_port=1522):
        self.ssh_host = ssh_host
        self.ssh_port = ssh_port
        self.ssh_user = ssh_user
        self.ssh_key_file = ssh_key_file
        self.remote_bind_host = remote_bind_host
        self.remote_bind_port = remote_bind_port
        self.local_bind_port = local_bind_port
        self.client = None
        self.forward_tunnel = None

    def __enter__(self):
        logging.debug("Setting up SSH Tunnel")
        self.client = paramiko.SSHClient()
        self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        private_key = paramiko.RSAKey.from_private_key_file(self.ssh_key_file)
        self.client.connect(self.ssh_host, port=self.ssh_port, username=self.ssh_user, pkey=private_key,timeout=3)

        # Establish the tunnel to the remote database
        self.forward_tunnel = self.client.get_transport().open_channel(
            "direct-tcpip",
            (self.remote_bind_host, self.remote_bind_port),
            ('127.0.0.1', self.local_bind_port)
        )

        logging.debug(f"SSH Tunnel established on local port {self.local_bind_port}")
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        logging.debug("Tearing down SSH Tunnel")
        if self.forward_tunnel:
            self.forward_tunnel.close()
        if self.client:
            self.client.close()


class OracleConnection:

    def __init__(self, username, password, local_bind_port, service_name):
        self.username = username
        self.password = password
        self.local_bind_port = local_bind_port
        self.service_name = service_name
        self.connection = None

    def __enter__(self):
        logging.debug("Setting up Oracle connection")
        dsn = oracledb.makedsn("127.0.0.1", self.local_bind_port, service_name=self.service_name)
        logging.debug(f"Connecting to DSN: {dsn}")
        self.connection = oracledb.connect(user=self.username, password=self.password, dsn=dsn, disable_oob=True)
        logging.debug("Oracle connection established")
        return self.connection

    def __exit__(self, exc_type, exc_val, exc_tb):
        logging.debug("Tearing down Oracle connection")
        if self.connection:
            self.connection.close()


def main():

    ssh_host = "111.111.111.111"
    ssh_port = 22
    ssh_user = "one"
    ssh_key_file = "c:\\Users\\ssh-key.key"

    remote_bind_host = "12.7.0.41"
    remote_bind_port = 1521
    oracle_user = "system"
    oracle_password = "password"
    oracle_service_name = "oracledb.sub1.vcn03.oraclevcn.com"
    local_bind_port = 1522 

    with SSHTunnel(ssh_host, ssh_port, ssh_user, ssh_key_file,remote_bind_host, remote_bind_port, local_bind_port) as ssh_tunnel:
        with OracleConnection(oracle_user, oracle_password, ssh_tunnel.local_bind_port, oracle_service_name) as connection:
           logging.debug("Successfully connected to the Oracle database!")

            cursor = connection.cursor()
            cursor.execute("SELECT * FROM some_table")
            for row in cursor:
                print(row)

            # Close the cursor
            cursor.close()

if __name__ == "__main__":
    main()

Ошибки:

oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=rid).
[WinError 10061] No connection could be made because the target machine actively refused it

Толстый:

class SSHTunnel:

    def __init__(self, ssh_host, ssh_port, ssh_user, ssh_key_file, remote_bind_host, remote_bind_port, local_bind_port=1522):
        self.ssh_host = ssh_host
        self.ssh_port = ssh_port
        self.ssh_user = ssh_user
        self.ssh_key_file = ssh_key_file
        self.remote_bind_host = remote_bind_host
        self.remote_bind_port = remote_bind_port
        self.local_bind_port = local_bind_port
        self.client = None
        self.channel = None

    def __enter__(self):
        logging.debug("Setting up SSH Tunnel")
        self.client = paramiko.SSHClient()
        self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        self.client.connect(
            self.ssh_host, port=self.ssh_port, username=self.ssh_user, key_filename=self.ssh_key_file
        )
        
        self.channel = self.client.invoke_shell()
        self.channel.send('uname -a\n')
        
        buff = ''
        while not buff.endswith('# '):
            resp = self.channel.recv(4096).decode('utf-8')
            buff += resp
        logging.debug(f"SSH Shell Prompt: {buff}")

        self.forward_tunnel = self.client.get_transport().open_channel(
            "direct-tcpip",
            (self.remote_bind_host, self.remote_bind_port),
            ('127.0.0.1', self.local_bind_port)
        )

        logging.debug(f"SSH Tunnel established on local port {self.local_bind_port}")
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        logging.debug("Tearing down SSH Tunnel")
        if self.forward_tunnel:
            self.forward_tunnel.close()
        if self.channel:
            self.channel.close()
        if self.client:
            self.client.close()

class OracleConnection:

    def __init__(self, username, password, local_bind_port, service_name):
        self.username = username
        self.password = password
        self.local_bind_port = local_bind_port
        self.service_name = service_name
        self.connection = None

    def __enter__(self):
        logging.debug("Setting up Oracle connection in thick mode")
        
        oracledb.init_oracle_client(lib_dir = "c:\\Program Files\\Oracle Client for Microsoft Tools\\t")  

        dsn = oracledb.makedsn("127.0.0.1", self.local_bind_port, service_name=self.service_name)
        logging.debug(f"Connecting to DSN: {dsn}")
        self.connection = oracledb.connect(user=self.username, password=self.password, dsn=dsn)
        logging.debug("Oracle connection established")
        return self.connection

    def __exit__(self, exc_type, exc_val, exc_tb):
        logging.debug("Tearing down Oracle connection")
        if self.connection:
            self.connection.close()

def main():

    ssh_host = "111.111.111.111"
    ssh_port = 22
    ssh_user = "one"
    ssh_key_file = "c:\\Users\\ssh-key.key"

    remote_bind_host = "12.7.0.41"
    remote_bind_port = 1521
    oracle_user = "system"
    oracle_password = "password"
    oracle_service_name = "oracledb.sub1.vcn03.oraclevcn.com"
    local_bind_port = 1522 

    with SSHTunnel(ssh_host, ssh_port, ssh_user, ssh_key_file,remote_bind_host, remote_bind_port, local_bind_port) as ssh_tunnel:
        with OracleConnection(oracle_user, oracle_password, ssh_tunnel.local_bind_port, oracle_service_name) as connection:
           logging.debug("Successfully connected to the Oracle database!")

            cursor = connection.cursor()
            cursor.execute("SELECT * FROM some_table")
            for row in cursor:
                print(row)

            # Close the cursor
            cursor.close()

if __name__ == "__main__":
    main()

Ошибки:

DEBUG
.transport:[chan 0] Sesch channel 0 request ok
DEBUG
.transport:[chan 0] Sesch channel 0 request ok
DEBUG
.transport:[chan 0] Unhandled channel request "[email protected]"
Traceback (most recent call last):
File "D:\Users\ssh.py", line 1995, in <module>
DEBUG
.transport:[chan 0] EOF received (0)
DEBUG
.transport:[chan 0] EOF sent (0)
main()
File "D:\Users\ssh.py", line 402, in main
with SSHTunnel(
File "D:\Users\ssh.py", line 333, in enter
resp = self.channel.recv(4096).decode('utf-8')
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\paramiko\channel.py", line 697, in recv
out = self.in_buffer.read(nbytes, self.timeout)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Python312\Lib\site-packages\paramiko\buffered_pipe.py", line 150, in read
self._cv.wait(timeout)
File "C:\Python312\Lib\threading.py", line 355, in wait
waiter.acquire()
KeyboardInterrupt
DEBUG.transport in transport thread

Модифицированный код с SSHTunnel:

Вот соединительная часть кода:

import oracledb
import paramiko
import sshtunnel
from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel
from sqlalchemy.orm import sessionmaker #Run pip install sqlalchemy
from sqlalchemy import create_engine, text
import psycopg2
import os
import time
import logging

logging.basicConfig(level=logging.DEBUG)


def main():
    ssh_host = "111.111.111"
    ssh_port = 22
    ssh_user = "ssh_user"
    ssh_key_file = "c:\\Users\\ssh-key.key"

    remote_bind_host = "12.5.21"
    remote_bind_port = 1521

    oracle_user = "or_user"
    oracle_password = "or_pswd"
    oracle_service_name = "db.oraclevcn.com"

    with sshtunnel.open_tunnel(
            (ssh_host, ssh_port),
            ssh_username=ssh_user,
            ssh_pkey=ssh_key_file,
            remote_bind_address=(
                    remote_bind_host,
                    remote_bind_port),
            local_bind_address=(
                    '127.0.0.1',
                    1522)) as server:

        server.start()
        print('Server connected via SSH')

        local_port = str(server.local_bind_port)
        engine = create_engine(f'oracle+oracledb://{oracle_user}:{oracle_password}@127.0.0.1:{local_port}/{oracle_service_name}')

        Session = sessionmaker(bind=engine)
        print('Connected to Oracle')
        session = Session()

        result = session.execute(text("SELECT * FROM all_users"))
        result_exists = result.fetchone()[0]
        session.close()

if __name__ == "__main__":
    main()

И завершающая часть отладки + ошибки:

Server connected via SSH
DEBUG:paramiko.transport:[chan 0] Max packet in: 32768 bytes
Connected to Oracle
DEBUG:paramiko.transport:Received global request "[email protected]"
DEBUG:paramiko.transport:Rejecting "[email protected]" global request from server.
DEBUG:paramiko.transport:Debug msg: b'/home/opc/.ssh/authorized_keys:1: key options: agent-forwarding port-forwarding pty user-rc x11-forwarding'
DEBUG:paramiko.transport:[chan 0] Max packet out: 32768 bytes
DEBUG:paramiko.transport:Secsh channel 0 opened.
DEBUG:paramiko.transport:[chan 0] EOF sent (0)
DEBUG:paramiko.transport:Dropping user packet because connection is dead.
DEBUG:paramiko.transport:Dropping user packet because connection is dead.
DEBUG:paramiko.transport:EOF in transport thread
Traceback (most recent call last):
  File "src\\oracledb\\impl/thin/connection.pyx", line 279, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src\\oracledb\\impl/thin/protocol.pyx", line 242, in oracledb.thin_impl.Protocol._connect_phase_one
  File "src\\oracledb\\impl/thin/protocol.pyx", line 400, in oracledb.thin_impl.Protocol._process_message
  File "src\\oracledb\\impl/thin/protocol.pyx", line 379, in oracledb.thin_impl.Protocol._process_message
  File "src\\oracledb\\impl/thin/messages.pyx", line 1875, in oracledb.thin_impl.ConnectMessage.process
  File "C:\Python312\Lib\site-packages\oracledb\errors.py", line 182, in _raise_err
    raise error.exc_type(error) from cause
oracledb.exceptions.OperationalError: DPY-6003: SID "db.oraclevcn.com" is not registered with the listener at host "127.0.0.1" port 1522. (Similar to ORA-12505)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 3300, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\impl.py", line 179, in _do_get
    with util.safe_reraise():
  File "C:\Python312\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\impl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 674, in __init__
    self.__connect()
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 900, in __connect
    with util.safe_reraise():
  File "C:\Python312\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Python312\Lib\site-packages\sqlalchemy\pool\base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\engine\create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 620, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\oracledb\connection.py", line 1158, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\oracledb\connection.py", line 541, in __init__
    impl.connect(params_impl)
  File "src\\oracledb\\impl/thin/connection.pyx", line 381, in oracledb.thin_impl.ThinConnImpl.connect
  File "src\\oracledb\\impl/thin/connection.pyx", line 377, in oracledb.thin_impl.ThinConnImpl.connect
  File "src\\oracledb\\impl/thin/connection.pyx", line 337, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src\\oracledb\\impl/thin/connection.pyx", line 318, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src\\oracledb\\impl/thin/connection.pyx", line 284, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "C:\Python312\Lib\site-packages\oracledb\errors.py", line 182, in _raise_err
    raise error.exc_type(error) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=53uh4nd==).
DPY-6003: SID "db.oraclevcn.com" is not registered with the listener at host "127.0.0.1" port 1522. (Similar to ORA-12505)

В документации SQLAlchemy «create_engine» я видел, что он использует cx_oracle, но он недоступен в более новой версии Python, доступен только oracledb. В других случаях они были взаимозаменяемы, возможно здесь это не работает.

Я тоже попробовал через dsn, но снова получил ошибку:

dsn = f"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT = {local_port}))(CONNECT_DATA=(SERVICE_NAME = {oracle_service_name})))"
engine = create_engine(f'oracle+oracledb://{oracle_user}:{oracle_password}@/?dsn = {dsn}')

sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) DPY-4011: the database or network closed the connection