can not connect oracle using oracledb package

[sae13]

hey hi sqlalchemy supports python-oracledb which does not need oracle client oackage https://docs.sqlalchemy.org/en/20/dialects/oracle.html#module-sqlalchemy.dialects.oracle.oracledb

https://pypi.org/project/oracledb/

its connection string is like 'oracle+oracledb://user:password@host:port/pdb'

I installed it with this dockerfile

❯ cat Documents/Docker/superset/Dockerfile 
FROM apache/superset
USER root
RUN pip install -U pip
RUN pip install elasticsearch-dbapi
RUN pip install shillelagh[gsheetsapi]
RUN pip install mysqlclient
RUN pip install oracledb
RUN pip install psycopg2
RUN pip install pymssql
USER superset

but im getting ERROR: Could not load database driver: OracleEngineSpec

for now im using cx_Oracle with this docker file but I want to know how I can use python-oracledb

FROM apache/superset
USER root
RUN pip install -U pip
RUN pip install elasticsearch-dbapi
RUN pip install shillelagh[gsheetsapi]
RUN pip install mysqlclient
RUN pip install cx_Oracle
RUN pip install psycopg2
RUN pip install pymssql
RUN apt update;apt install -y curl unzip  libaio1
USER superset
RUN curl -o $HOME/instantclient-basiclite-linuxx64.zip -L https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip
RUN cd $HOME;unzip instantclient-basiclite-linuxx64.zip
ENV LD_LIBRARY_PATH=/app/superset_home/instantclient_21_11

[BipinVeefin]

use : pip install cx_oracle

[sae13]

use : pip install cx_oracle

I dont want. I want use python-oracle db which does not need oracle client

[thinkh]

@sae13 Did you find a solution to use the oracledb library?

[alex-c]

Bump - cx_oracle will not get any new major releases, as development continues under the python-oracledb package. Support for this would be nice, in order to not be stuck with an old version.

[rusackas]

Just made a similar comment on this issue: https://github.com/apache/superset/issues/20371

Perhaps we should close one or the other as a duplicate :)

In any case, has anyone tested python-oracledb to see if it works? It sounded like cx_oracle was working for some, and python-oracledb is supposed to be a drop-in replacement.

[alex-c]

I tried it recently on my work computer (cannot look up the exact error right now), and it did not work. Oracle did not show up as an option and using "Other" and using the connection string as specified in the python-oracledb docs did not work.

[tokas]

oracledb support was added in SQLAlchemy 2.0. Superset is using SQLAlchemy 1.4

[cjbj]

With SQLAlchemy 1.4 you can use python-oracledb in place of cx_Oracle by inserting this code snippet before anything loads cx_Oracle, e.g. add it to superset_config.py:

import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb
import cx_Oracle

See the python-oracledb doc: https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_c.html#python-frameworks-sql-generators-and-orms and https://levelup.gitconnected.com/using-python-oracledb-1-0-with-sqlalchemy-pandas-django-and-flask-5d84e910cb19

Don't forget to install the new driver:

python -m pip install oracledb

With this in place, the Superset console SUPPORTED DATABASES will show 'Oracle'. The SQLAlchemy URI uses the SQLAlchemy 1.4 syntax, eg. oracle://cj:mysecret password@cjdb

I posted more detail in Steps to use Apache Superset and Oracle Database.