SQLAlchemy не может подключиться к базе данных Oracle, используя URL базы данных

Question

Я пытаюсь создать контейнер Docker, который подключается к VPN, а затем проверяет, может ли он подключаться к некоторым базам данных. В настоящее время я использую следующий код:

DB_URI_PREFIX = {
    'mssql': 'mssql+pymssql://', 'oracle': 'oracle+cx_oracle://', 'postgres': 'postgres://',
}


def connect_to_db(db_type: str, host: str, username: str, password: str, database: str):
    db_uri = '{}{}:{}@{}/{}'.format(DB_URI_PREFIX[db_type], username, password, host, database)
    engine = create_engine(db_uri)
    connection = engine.connect()
    print('Successfully connected to {}/{}'.format(host, database))
    connection.close()

db_uri в этом случае оказывается mssql+pymssql://user:pass@host:port_num/database_name для базы данных SQL Server и oracle+cx_oracle://user:pass@host:port_num/database_name для базы данных Oracle.

Вышесказанное прекрасно работает для SQL-сервера и баз данных Postgres, но для Oracle я получаю эту ошибку:

 Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 782, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 1186, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 1183, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 667, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 410, in connect
    return self.dbapi.connect(*cargs, **cparams)
cx_Oracle.DatabaseError: ORA-12545: Connect failed because target host or object does not exist

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

Traceback (most recent call last):
  File "connect_to_db.py", line 37, in <module>
    connect_to_db(args.db_type, args.host, args.username, args.password, args.database)
  File "connect_to_db.py", line 16, in connect_to_db
    connection = engine.connect()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2102, in connect
    return self._connection_cls(self, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2188, in raw_connection
    self.pool.unique_connection, _connection)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 782, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 1186, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 1183, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/pool.py", line 667, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 410, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-12545: Connect failed because target host or object does not exist (Background on this error at: http://sqlalche.me/e/4xp6)

Первоначально я думал, что это означает, что хост не может быть найден, но он отвечает при проверке связи. Кроме того, как ни странно, если я соединяюсь с помощью cx_Oracle напрямую со строкой соединения "Oracle style" и передаю это SQLAlchemy, я могу подключиться:

def get_oracle_connection(conn_str: str):
    return cx_Oracle.connect(conn_str)


def connect_to_db(host: str, username: str, password: str, database: str):
    conn_str = '{}/{}@{}/{}'.format(username, password, host, database)
    conn_creator = partial(get_oracle_connection, conn_str=conn_str)
    engine = create_engine('oracle+cx_oracle://', creator=conn_creator)

    connection = engine.connect()
    print('Successfully connected to {}'.format(conn_str))
    connection.close()

В этом случае conn_str было бы user/pass@host/database_name,

В моем контейнере Docker я использую Python 3 и подключаюсь к VPN с помощью openconnect. Я также установил моментальный клиент Oracle в свой контейнер.

requirements.txt:

SQLAlchemy==1.2.2
pymssql==2.1.3
cx-Oracle==6.1
psycopg2==2.7.4

Я где-то здесь ошибаюсь или это ошибка в SQLAlchemy?

Answer 1

Код ошибки имеет '{}{}:{}@{}/{}' но рабочий код имеет '{}/{}@{}/{}' В первом отсутствует косая черта между именем пользователя и паролем, и неясно, где установлен номер порта (после двоеточия).