sqlalchemy: login failure when password contains a reserved character?

Describe the bug Unable to connect to Oracle when the password has special characters. (Many passwords are auto-generated when an account is set up and are required to have a certain number of special characters.)

Expected behavior Expect successful connection: the connection is successful through other tools and through SQL Alchemy when the password is changed to something with special characters at the end.

To Reproduce Connection string: r'oracle+cx_oracle://someuser::%24jE?hpJ@SOME_TNS' The password randomly assigned was :%24jE?hpJ Note the leading colon character ‘:’ in the password.

import sqlalchemy as sa
env = r'oracle+cx_oracle://someuser::%24jE?hpJ@SOME_TNS'
dbcon = sa.create_engine(env)
dbcon.execute('select 1 from dual')

Error

Traceback (most recent call last):
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\base.py", line 2345, in _wrap_pool_connect
    return fn()
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
    rec = pool._do_get()
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\impl.py", line 140, in _do_get
    self._dec_overflow()
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\impl.py", line 137, in _do_get
    return self._create_connection()
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\default.py", line 490, in connect
    return self.dbapi.connect(*cargs, **cparams)
cx_Oracle.DatabaseError: ORA-01017: invalid username/password; logon denied

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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\base.py", line 2243, in execute
    connection = self._contextual_connect(close_with_result=True)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\base.py", line 2311, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\base.py", line 2348, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\base.py", line 1590, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\base.py", line 2345, in _wrap_pool_connect
    return fn()
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
    rec = pool._do_get()
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\impl.py", line 140, in _do_get
    self._dec_overflow()
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\impl.py", line 137, in _do_get
    return self._create_connection()
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Miniconda3\envs\r20\lib\site-packages\sqlalchemy\engine\default.py", line 490, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01017: invalid username/password; logon denied
(Background on this error at: http://sqlalche.me/e/4xp6)

Versions.

• OS: Windows 10
• Python: 3.8.5
• SQLAlchemy: 1.3.17
• Database: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
• DBAPI: ? cx_Oracle 7.2.3

Additional context cx_oracle is the lower library: that package project checked out the problem here and confirmed it was higher up the stack: https://github.com/oracle/python-cx_Oracle/issues/494 This is a minor bug with an easy workaround: change the password in Oracle with: ALTER USER YOUR_USER IDENTIFIED BY NewPassword replace OldPasswordWithSpecialChars But I’d hate for someone else to spend the same length of time figuring this out as I did.

Have a nice day! Thanks for all you guys do.