Cannot create connection by Sqlalchemy under 3.0.0, but it works under 2.4.1

[Jin-Yc]

import pandas as pd
import oracledb
from sqlalchemy import create_engine

lib_dir = '.\\instantclient_11_2_python'
config_dir = '.\\instantclient_11_2_python\\network\\admin'

oracledb.init_oracle_client(lib_dir=lib_dir, config_dir=config_dir)

dns = oracledb.makedsn('chn01prcluscann.enterprisenet.org', 1521, service_name='MSCH01PR_APP')

connect = create_engine('oracle+oracledb://user:password@' + dns)

Then, I use pd.read_sql(sql, connect).
It works when oracledb's version is 2.4.1, but error when 3.0.0
Here is the error:

Traceback (most recent call last):
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 3298, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\impl.py", line 179, in _do_get
    with util.safe_reraise():
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\impl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 674, in __init__
    self.__connect()
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 914, in __connect
    )._exec_w_sync_on_first_run(self.dbapi_connection, self)
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\event\attr.py", line 483, in _exec_w_sync_on_first_run
    self(*args, **kw)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\event\attr.py", line 497, in __call__
    fn(*args, **kw)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 1916, in go
    return once_fn(*arg, **kw)
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\create.py", line 752, in first_connect
    dialect.initialize(c)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\dialects\oracle\cx_oracle.py", line 1173, in initialize
    super().initialize(connection)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\dialects\oracle\base.py", line 1706, in initialize
    super().initialize(connection)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\default.py", line 540, in initialize
    max_ident_length = self._check_max_identifier_length(connection)
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\dialects\oracle\oracledb.py", line 731, in _check_max_identifier_length
    max_len = connection.connection.max_identifier_length
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 1183, in __getattr__
    return getattr(self.dbapi_connection, key)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\oracledb\connection.py", line 359, in max_identifier_length
    return self._impl.get_max_identifier_length()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "src\\oracledb\\impl/thick/connection.pyx", line 676, in oracledb.thick_impl.ThickConnImpl.get_max_identifier_length
  File "src\\oracledb\\impl/thick/utils.pyx", line 484, in oracledb.thick_impl._raise_from_odpi
  File "src\\oracledb\\impl/thick/utils.pyx", line 474, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-24315: illegal attribute type
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:\Users\jiki3001\AppData\Local\JetBrains\DataSpell 2023.2.4\plugins\python-ce\helpers\pydev\pydevconsole.py", line 364, in runcode
    coro = func()
           ^^^^^^
  File "<input>", line 1, in <module>
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\pandas\io\sql.py", line 704, in read_sql
    with pandasSQL_builder(con) as pandas_sql:
         ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\pandas\io\sql.py", line 906, in pandasSQL_builder
    return SQLDatabase(con, schema, need_transaction)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\pandas\io\sql.py", line 1636, in __init__
    con = self.exit_stack.enter_context(con.connect())
                                        ^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 3274, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 148, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 2439, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 3298, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\impl.py", line 179, in _do_get
    with util.safe_reraise():
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\impl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 674, in __init__
    self.__connect()
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 914, in __connect
    )._exec_w_sync_on_first_run(self.dbapi_connection, self)
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\event\attr.py", line 483, in _exec_w_sync_on_first_run
    self(*args, **kw)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\event\attr.py", line 497, in __call__
    fn(*args, **kw)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 1916, in go
    return once_fn(*arg, **kw)
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\create.py", line 752, in first_connect
    dialect.initialize(c)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\dialects\oracle\cx_oracle.py", line 1173, in initialize
    super().initialize(connection)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\dialects\oracle\base.py", line 1706, in initialize
    super().initialize(connection)
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\engine\default.py", line 540, in initialize
    max_ident_length = self._check_max_identifier_length(connection)
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\dialects\oracle\oracledb.py", line 731, in _check_max_identifier_length
    max_len = connection.connection.max_identifier_length
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 1183, in __getattr__
    return getattr(self.dbapi_connection, key)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jiki3001\OneDrive - NIQ\Desktop\PythonProject\btm_g2\venv\Lib\site-packages\oracledb\connection.py", line 359, in max_identifier_length
    return self._impl.get_max_identifier_length()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "src\\oracledb\\impl/thick/connection.pyx", line 676, in oracledb.thick_impl.ThickConnImpl.get_max_identifier_length
  File "src\\oracledb\\impl/thick/utils.pyx", line 484, in oracledb.thick_impl._raise_from_odpi
  File "src\\oracledb\\impl/thick/utils.pyx", line 474, in oracledb.thick_impl._raise_from_info
sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-24315: illegal attribute type
(Background on this error at: https://sqlalche.me/e/20/4xp6)

And I checked it on both Sqlalchemy and python-oracledb site, I could not find the solution.
Thank you for your help.

[anthony-tuininga]

Interesting. The calculation of max identifier length appears to be failing for you, even though there is a check that the client version must exceed 12.2 for that calculation to take place! That suggests that you may have mismatched client versions. Can you display the result of print(oracledb.clientversion())?

[cjbj]

Are you saying that connect() works fine but SQLAlchemy does not? Is this in the exact same environment / terminal window? Is the problem with the exact script you gave in your first post, or with some extra code?

What version of SQLAlchemy do you have?

What if you use an absolute path for the Instant Client library?

What happens if you do the client library initialization as part of create_engine()?

engine = create_engine(
    f'oracle+oracledb://@',
    thick_mode={"lib_dir": r".\instantclient_11_2_python"},
    connect_args={
        "user": un,
        "password": pw,
        "host": hostname,
        "port": port,
        "service_name": service_name
    }
)

Instant Client can connect to different database versions, for example Instant Client 19c can connect to Oracle DB 11.2 or later, see https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html#supported-oracle-database-versions so maybe you can upgrade your client.

[Jin-Yc]

Are you saying that connect() works fine but SQLAlchemy does not? Is this in the exact same environment / terminal window? Is the problem with the exact script you gave in your first post, or with some extra code?

What version of SQLAlchemy do you have?

What if you use an absolute path for the Instant Client library?

What happens if you do the client library initialization as part of create_engine()?

engine = create_engine(
    f'oracle+oracledb://@',
    thick_mode={"lib_dir": r".\instantclient_11_2_python"},
    connect_args={
        "user": un,
        "password": pw,
        "host": hostname,
        "port": port,
        "service_name": service_name
    }
)

Instant Client can connect to different database versions, for example Instant Client 19c can connect to Oracle DB 11.2 or later, see https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html#supported-oracle-database-versions so maybe you can upgrade your client.

1. Are you saying that connect() works fine but SQLAlchemy does not? Is this in the exact same environment / terminal window? Is the problem with the exact script you gave in your first post, or with some extra code?
   A: Exactly yes. In the same environment, I used SQLAlchemy create_engine first, and pd.read_sql(sql, connect) did not work (DatabaseError I mentioned). Then, I used connect = oracledb.connect(user=un, password=pw, dsn='chn01prcluscann.enterprisenet.org:1521/MSCH01PR_APP'), it works in pd.read_sql(sql, connect), but has Userwarning.
2. What version of SQLAlchemy do you have?
   A: 2.0.38
3. What if you use an absolute path for the Instant Client library?
   A: In my local code, I used absolute path in lib_dir and config_dir. I just hide some information.
4. What happens if you do the client library initialization as part of create_engine()?
   A: Same as Q1, Database Error
5. I will try to upgrade my client.

Thank you.

[anthony-tuininga]

You need to add to the script that @cjbj gave you:

import oracledb

lib_dir = '.\\instantclient_11_2_python'
config_dir = '.\\instantclient_11_2_python\\network\\admin'

oracledb.init_oracle_client(lib_dir=lib_dir, config_dir=config_dir)
print(oracledb.clientversion())
conn = oracledb.connect(user=un, password=pw, dsn='chn01prcluscann.enterprisenet.org:1521/MSCH01PR_APP')
print("max identifier length:", conn.max_identifier_length)

[anthony-tuininga]

It is the calculation of the max identifier length done in SQLAlchemy that is failing for some reason.

[anthony-tuininga]

FYI, this build contains pre-release development binaries that include a fix that should allow the 11.2 client to work again. That said, you are still strongly encouraged to upgrade!