BritishGeologicalSurvey / etlhelper

ETL Helper is a Python ETL library to simplify data transfer into and out of databases.
https://britishgeologicalsurvey.github.io/etlhelper/
GNU Lesser General Public License v3.0
105 stars 25 forks source link

Replace cxOracle with python-oracledb #140

Closed volcan01010 closed 1 year ago

volcan01010 commented 1 year ago

Summary

As an ETL Helper user I want to use python-oracledb instead of cxOracle so that I don't need to worry about installing the Oracle Instant Client.

Description

The python-oracledb driver is a replacement for cxOracle, which we currently use. It has the big advantage that it doesn't require a separate installation of Oracle Instant Client to work. Installing Instant Client is a pain - in fact part of the reason ETLHelper exists is to make this easier.

This should be as simple as updating the OracleDbHelper class to use the new driver and confirming that the tests still work. If it does work, then we can remove the setup_oracle_client script as it will no longer be needed. (Although we may want to keep it for in case the "thick" mode of the driver still needs it).

This change may have implications for #110 and #130 as it changes the underlying Oracle driver workings. It is likely to be a breaking change. Any changes should solve #110 so that read_lob is no longer required.

If it isn't a simple update then we should consider having two separate DbHelper classes for Oracle. In the longer term, cxOracle will be deprecated to python-oracledb needs to work eventually.

Acceptance criteria

volcan01010 commented 1 year ago

Note here that cx_Oracle is not tested with Python 3.11 and releases with binary wheels are no longer available for Python 3.11 either.

https://cx-oracle.readthedocs.io/en/latest/

This is a good reason to upgrade now.

Also, cx_Oracle supports "thin" and "thick" modes. The thick mode still uses the underlying Oracle client. I don't know if the features that we use require thick mode. We will have to test and see.

volcan01010 commented 1 year ago

This has been merged into for_v1 branch.