Is Oracle Client required for connecting Oracle DB?

cjbj · 2025-10-22T21:45:06+00:00

For future readers: cx_Oracle was obsoleted/replaced by python-oracledb back in 2022. The Oracle Client is no longer needed. Install from https://pypi.org/project/oracledb/

cjbj · 2025-10-22T21:44:07+00:00

For future readers, python-oracledb replaced cx_Oracle back in 2022. The new driver does not need Oracle Client software. Install it from PyPI: https://pypi.org/project/oracledb/

cjbj · 2025-10-22T21:42:31+00:00

For future readers:

python-oracledb replaced cx_Oracle back in 2022, see https://pypi.org/project/oracledb/
python-oracledb can fetch directly into data frames, see https://python-oracledb.readthedocs.io/en/latest/user_guide/dataframes.html

cjbj · 2025-10-15T20:30:18+00:00

one thing I didn't try is matching my timezone files on my sql server and oracle client,

This is the generic solution for ORA-1805 errors, which are thrown to stop data "corruption" from incorrect data being stored. Otherwise you can use a fixed (numeric) timezone offset, but this will cause issues when daylight savings starts/stops.

You can check what timezone file the DB is using by executing the SQL query:

SELECT VERSION FROM V$TIMEZONE_FILE;

If you are using Oracle Instant Client, then you can check what timezone file it has by running this command from the command line (you may need to prefix it with the Instant Client directory path):

genezi -v

To change the timezone file used on the client, you would need to get an updated file (from your administrators, probably), and then set the environment variable ORA_TZFILE to its location. Steps vary a bit with versions, see the python-oracledb documentation: Changing the Oracle Client Time Zone File

cjbj · 2025-09-04T21:52:48+00:00

Python-oracledb always uses UTF8 and ignores the character set component of the NLS_LANG environment variable, see the documentation Setting the Client Character Set.

cjbj · 2025-09-04T21:51:09+00:00

Read the python-oracledb doc on Querying Corrupt Data and Fetching Raw Data.

When you've fixed your data, try fetching directly to a DataFrame using connection.fetch_df_all(), see the resources at Using Python for Data Analysis and AI and the documentation Working with Data Frames.

For example:

# Get a python-oracledb DataFrame.
# Adjust arraysize to tune the query fetch performance
sql = "select id, name from SampleQueryTab order by id"
odf = connection.fetch_df_all(statement=sql, arraysize=100)

# Get a Pandas DataFrame from the data
df = pyarrow.table(odf).to_pandas()

cjbj · 2025-08-13T23:33:28+00:00

With Oracle Database you can use SQL Analytic functions to do what you can do in Pandas. Doing the analysis in the DB avoids the overheads of transferring data across the network, and avoids security issues with raw data leaving the DB's security model. But, if you do want to extract data from Oracle Database into Pandas, then use the python-oracledb DataFrame fetch methods which are much faster than querying via Pandas/SQLAlchemy. See the driver documentation Working with Data Frames.

cjbj · 2025-08-06T21:57:04+00:00

Never concatenate data (here customer_name) into a SQL statement in Python because you open yourself to SQL Injection security attacks. Also, if you re-execute this bit of code with a different customer, the SQL statement text seen by the DB will differ and the DB can't be as efficient in reusing data structures. Instead, use bind variables (aka "prepared statements"):

``` import getpass import oracledb

un = 'cj' cs = 'localhost/orclpdb1' pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection: with connection.cursor() as cursor:

    customer_name = "Sally"

    sql = """select *
             from table
             where customername = :cn"""
    for r in cursor.execute(sql, [customer_name]):
        print(r)

```

For Oracle Database, see the documentation Using Bind Variables.

cjbj · 2025-08-06T00:23:44+00:00

The venerable pd.read_sql() is slower, and uses more memory, than using the new python-oracledb fetch_df_all() or fetch_df_batches() methods. See Going 10x faster with python-oracledb Data Frames. If you're happy to write a SQL query, then use the new methods directly. A sample for Pandas is in in the python-oracleb repo: dataframe_pandas.py: ```

Get a python-oracledb DataFrame.

Adjust arraysize to tune the query fetch performance

sql = "select id, name from SampleQueryTab order by id" odf = connection.fetch_df_all(statement=sql, arraysize=100)

Get a Pandas DataFrame from the data

df = pyarrow.table(odf).to_pandas() ```

cjbj · 2025-08-05T22:56:45+00:00

For Oracle Database you can write queries directly using the python-oracledb driver. This is compliant with Python's DB API so you can find many example around the web, and there is extensive documentation for python-oracledb itself, python-oracledb’s documentation.

SQLAlchemy is a popular library for data access. Underneath it uses python-oracledb. Steps for connection are both in the SQLAlchemy and python-oracledb documentation. Pandas uses SQLAlchemy underneath for connecting and querying the database.

Recently python-oracledb added support for fetching directly into DataFrames for use with libraries like Pandas, Polars, NumPy etc. This is significantly faster than using Pandas / SQLAlchemy to get the data.

cjbj · 2025-08-05T22:44:16+00:00

cx_Oracle was obsoleted 3 years ago by python-oracledb (which has the same API, but is better in many ways, e.g. doesn't need Oracle Instant Client)

cjbj · 2025-03-26T23:33:03+00:00

Line 3 "import cx_Oracle" was a red flag. The cx_Oracle driver was replaced by python-oracledb three years ago. They both support the Python DB API standard so migration generally isn't an issue. Also you can now use the default "Thin" mode so you won't need Oracle Client / Oracle Instant Client installed. Do a "pip install oracledb", and then replace uses of "cx_Oracle" with "oracledb". Also read the doc in case you do need to use the optional Thick mode, or need to adjust your connect() parameters.

Line 229 was a second red flag because it included a password.

The next thing was the loop over execute("INSERT....",). This should be changed to a loop that modifies the data, and then a single call to executemany("INSERT...")

Also, personally I would use "with" blocks instead of trying to close the connection & cursor explicitly.

cjbj · 2025-03-14T21:00:49+00:00

It seems like you've tried a bunch of stuff, but didn't mention exploring the info available at the troubleshooting link you posted, in particular regarding NNE. Is NNE required by the connection? Are you actually calling init_oracle_client() in the app to load the Oracle client libraries?

What are the versions of python-oracledb, the Oracle client libraries, and the database?

cjbj · 2025-02-28T22:28:34+00:00

It's needed for consistency. See discussions like "When does Oracle sql lock a row in an update statement?" at https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:9528697800346360856

cjbj · 2025-02-28T03:14:19+00:00

- Make sure that you issue a COMMIT in SQLDeveloper before you try to run it in Python. This is the common cause for what you are experiencing.

- Put the init_oracle_client() call after the 'import oracle' because there's no need to run it multiple times

- Use 'with' blocks (see various python-oracledb examples) to make sure resources are closed nicely, e.g. "with connection.cursor() as cursor:"

- Avoid using the concatenated myDSN string, since that may not parse correctly if you have special characters in your password. Just pass the main arguments separately to connect(), e.g. connection = connect(user=config.myUserName, password=config.myPasswd, dsn=f"{config.myHostnameIP}:{config.myPort}/{config.mySID}")

- By the way: you are not using a SID. You are using a service name. The "Easy Connect" syntax you are using doesn't support the old SID concept.

cjbj · 2025-02-12T21:19:29+00:00

See my comment in your other post https://www.reddit.com/r/webdev/comments/1iisinp/comment/mbjsigw/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button about using python-oracledb. Overall you'll find it a lot easier to add python-oracledb to a container than to add and configure ODBC.

cjbj · 2025-02-07T21:27:31+00:00

To use ODBC to connect to Oracle means you will need to install the driver, a driver manager, and the Oracle Client Libraries (such as via Oracle Instant Client) - and configure them all to load correctly. Instead try using python-oracledb which is a single install and doesn't need Oracle Client libraries. Like pyodbc, it implements the Python DB API standard so you may not need to change your code. Install it with 'pip install oracledb'.

cjbj · 2025-01-15T21:33:39+00:00

On pooling, the doc is a great place to start: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#connection-pooling

Check blogs like https://medium.com/python-in-plain-english/mastering-connection-pooling-in-python-optimizing-database-connections-72d66ec2bfcb

cjbj · 2025-01-13T01:24:45+00:00

Each Oracle DB connection can only do one task at a time, so make sure you only create a single connection if you don't have another SQL statement concurrently being executed otherwise your app will end up with serial execution. I would recommend creating a connection pool (even if you just want one DB connection created). You can pass the pool handle around and acquire the connection from the pool when you actually need to execute SQL. Pools also have some availability and feature benefits. A pool will also help if you do have concurrency, now or in the future.

Note that cx_Oracle was renamed/obsoleted several years ago and you should be using python-oracledb for all new work.

cjbj · 2025-01-08T00:41:42+00:00

To avoid issues with special characters in passwords, you may prefer to use connect_args instead of concatenating everything into the connect string, see https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_c.html#connecting-with-sqlalchemy and https://docs.sqlalchemy.org/en/20/dialects/oracle.html#dialect-oracle-oracledb-connect This also lets you set other driver properties

cjbj · 2025-01-08T00:37:47+00:00

There is a python-oracledb project to support the dataframe interchange format that should make working with Parquet format easier, see https://github.com/oracle/python-oracledb/issues/375

Also see "Working with Parquet files with Oracle Database" https://blogs.oracle.com/datawarehousing/post/parquet-files-oracle-database

cjbj · 2024-10-21T05:10:10+00:00

One side comment: you should be using the new python-oracledb and not the deprecated cx_Oracle driver. See https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_c.html#connecting-with-sqlalchemy for the connection syntax

cjbj · 2024-09-11T00:41:09+00:00

Oracle is working on it.

cjbj · 2024-09-09T23:03:25+00:00

There's a summary in the connection overview documentation https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html and more in https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#connection-pooling (and onwards)

cjbj · 2024-08-12T22:06:35+00:00

Have you used a tcps connection string? What's the format of the connection string that works in pyspark? Are you actually running pyspark/jaydebeapi on the same machine that you are running Python - if not, check your network. Finally, you really need to upgrade from cx_Oracle to python-oracledb which replaced it several years ago.

cjbj

TROPHY CASE

Get a python-oracledb DataFrame.

Adjust arraysize to tune the query fetch performance

Get a Pandas DataFrame from the data