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

[Anonymous]

Я не являюсь экспертом ни в 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 
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:
Вот соединительная часть кода:< /p>

Код: Выделить всё

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
schema origi:  JOGADMIN
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)

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

Подробнее здесь: https://stackoverflow.com/questions/786 ... ing-python