oracledb

import oracledb
import os

###############################################################
### Activate Oracle Thick mode if the database enforces:
# * Oracle Native Network Encryption 
# * checksumming
###############################################################
client_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if client_dir:
    oracledb.init_oracle_client(lib_dir=client_dir)
else:
    oracledb.init_oracle_client()

###############################################################
### DSN connection
###############################################################
dsnStr = oracledb.makedsn(ORACLE_HOST, ORACLE_PORT, ORACLE_SID)
# dsnStr = oracledb.makedsn(ORACLE_HOST, ORACLE_PORT, service_name=ORACLE_SERVICE)
connection = oracledb.connect(user=ORACLE_USER, password=ORACLE_PASS, dsn=dsnStr)
connection.autocommit = False

cursor = connection.cursor()
dataset=cursor.execute("select * from dual")
print(dataset.fetchall())
cursor.close()


###############################################################
### Parameter connection
###############################################################
params = oracledb.ConnectParams(
    host=ORACLE_HOST,
    port=ORACLE_PORT,
    sid=ORACLE_SID
    # service_name=ORACLE_SID
)

with oracledb.connect(
    user=ORACLE_USER,
    password=ORACLE_PASS,
    params=params,
) as connection:
    with connection.cursor() as cursor:
        dataset=cursor.execute("select * from dual")
        print(dataset.fetchall())

oracle_cx

Download client

# check your glibc version 
ldd --version
# download "BasicLite Package Zip" from from 
# x-www-browser https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
# wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip
# # alternative: wget https://download.oracle.com/otn_software/linux/instantclient/2340000/instantclient-basiclite-linux.x64-23.4.0.24.05.zip

# create destination folder
ORACLE_DEST_FOLDER=/home/soft/oracle
mkdir -p $ORACLE_DEST_FOLDER
cd $ORACLE_DEST_FOLDER

mv ~/Downloads/instantclient-basic-linux*.zip .
unzip instantclient-basiclite-linux*.zip 
ls -l
# check unzipped version
ORACLE_CLIENT_VERSION=instantclient_23_4

echo "
export ORACLE_HOME=$ORACLE_DEST_FOLDER/$ORACLE_CLIENT_VERSION
export LD_LIBRARY_PATH=$ORACLE_DEST_FOLDER/$ORACLE_CLIENT_VERSION:\$LD_LIBRARY_PATH
" >> ~/.bashrc

ll $LD_LIBRARY_PATH
ll $ORACLE_HOME

install python package

pip3 install --break-system-packages cx_Oracle

## upgrade 
 python3 -m pip install --break-system-packages cx_Oracle --upgrade

## force install 
pip3 install --break-system-packages cx_Oracle  # python3 -m pip install --break-system-packages cx_Oracle --upgrade

## !!! works only till 3.12 !!!
# pyenv shell 3.12.10 && python -m pip install --upgrade pip setuptools wheel && python -m pip install cx_Oracle

issue with installation

# Cannot locate a 64-bit Oracle Client library: "/home/soft/oracle/instantclient_23_7/lib/libclntsh.so
# install basiclite of another version
# or just copy all the files from `/home/soft/oracle/instantclient_23_7` to `/home/soft/oracle/instantclient_23_7/lib`

#  Cannot locate a 64-bit Oracle Client library: "libaio.so.1: cannot open shared object file: No such file or directory"
# sudo ln -s /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1

github workflow step for oracle installation

      - name: Download Oracle Instant Client
        if: steps.cache-ext-file.outputs.cache-hit != 'true'
        run: |
          sudo apt-get update
          sudo apt-get install libaio1 wget unzip
          wget https://download.oracle.com/otn_software/linux/instantclient/2340000/instantclient-basiclite-linux.x64-23.4.0.24.05.zip
          # unzip instantclient-basiclite-linux.x64-23.4.0.24.05.zip
          # sudo mv instantclient_23_4 /opt/oracle  # libclntsh.so
          echo ">>>"$(pwd)
          # echo "export LD_LIBRARY_PATH=$(pwd)/instantclient_23_4:$LD_LIBRARY_PATH" >> $GITHUB_ENV

          rm -rf instantclient_23_4
          unzip -o instantclient-basiclite-linux.x64-23.4.0.24.05.zip

          python -m pip install --upgrade pip
          pip install -r db-to-csv/requirements.txt  # cx_Oracle