How to use python-oracledb in AWS Glue

[MominRaza]

I'm using python-oracledb with
--additional-python-modules = s3://.../oracledb-1.4.0.whl

Code sample

import oracledb

connection = oracledb.connect(
    f"{username}/{password}@{host}:{port}/{dbname}
)
query =  "SELECT * FROM BOOKS"
connection.cursor().execute(query)
connection.commit()

Error that I'm getting

2023-09-04 11:34:06,830 ERROR [main] glue.ProcessLauncher (Logging.scala:logError(77)): Error from Python:Traceback (most recent call last):
  File "src/oracledb/impl/thin/connection.pyx", line 328, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 215, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src/oracledb/impl/thin/connection.pyx", line 186, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src/oracledb/impl/thin/connection.pyx", line 127, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src/oracledb/impl/thin/protocol.pyx", line 246, in oracledb.thin_impl.Protocol._connect_phase_two
  File "src/oracledb/impl/thin/protocol.pyx", line 381, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 359, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 430, in oracledb.thin_impl.Protocol._receive_packet
  File "src/oracledb/impl/thin/packet.pyx", line 561, in oracledb.thin_impl.ReadBuffer.receive_packet
  File "src/oracledb/impl/thin/packet.pyx", line 360, in oracledb.thin_impl.ReadBuffer._receive_packet_helper
  File "src/oracledb/impl/thin/packet.pyx", line 206, in oracledb.thin_impl.ReadBuffer._get_data_from_socket
  File "/home/spark/.local/lib/python3.10/site-packages/oracledb/errors.py", line 127, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.DatabaseError: DPY-4011: the database or network closed the connection

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/tmp/ES-Test.py", line 313, in <module>
    insert_audit_log(
  File "/tmp/ES-Test.py", line 168, in insert_audit_log
    sqlconn = _get_db_connection(config)
  File "/tmp/ES-Test.py", line 260, in _get_db_connection
    connection = oracledb.connect(
  File "/home/spark/.local/lib/python3.10/site-packages/oracledb/connection.py", line 1020, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/home/spark/.local/lib/python3.10/site-packages/oracledb/connection.py", line 130, in __init__
    impl.connect(params_impl)
  File "src/oracledb/impl/thin/connection.pyx", line 337, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 235, in oracledb.thin_impl.ThinConnImpl._force_close
  File "src/oracledb/impl/thin/protocol.pyx", line 353, in oracledb.thin_impl.Protocol._force_close
OSError: [Errno 9] Bad file descriptor

Please help

[cjbj]

You wouldn't want to use cx_Oracle since that is just the old name/version of python-oracledb.

Googling showed some people hacking Instant Client to make it work, but with python-oracledb you generally won't need Instant Client. Just make sure you do not call init_oracle_client() in your code.

Try python-oracledb and share more information about what you are doing, and what behavior you see. Then perhaps someone who has used AWS Glue will be able to comment.

[MominRaza]

I have uploaded the question with more details please check

[cjbj]

Check the doc about NNE in the Troubleshooting Errors.

[lourivalcs]

I am also facing the same problem.
Changing the encryption is not an option, so all that remains is to use thick mode

AWS Glue Job Details
Does not allow changing files outside the /tmp/ folder
Does not allow pre-configuration of the instance that will run the job (installing apt packages for example or running bash scripts)
Does not allow setting environment variables

What I noticed is why the oracledb lib doesn't work properly
It behaves differently between Windows and Linux, Windows accepts a lib_dir in the oracledb.init_oracle_client() method, on Linux this option does not work or is not implemented.
It has dependencies on operating system packages, making it necessary to install libaio1

Given the explanation above, both Glue and the oracledb lib make them practically impossible to run without a lot of headaches.

Here I quote the pymssql lib from SqlServer, no additional steps are necessary, simply add and pass the connection parameters and everything works beautifully.

[HL-RamonZubiate]

[cjbj]

This is a different question and, as was pointed out on stack overflow, is due to a networking issue you need to resolve.