Python: Read Data from Oracle Database

The most commonly used Python package to connect to Oracle database is cx_Oracle. This package is now renamed to python-oracledb. By default, this driver package uses 'Thin' mode which connects directly to Oracle Database without Oracle client libraries. This mode does not need Oracle Client libraries. This thin mode can work with Oracle Database 12c, 18c, 19c and 21c.

This article provides an example to connect to Oracle using thin mode and then read data as pandas DataFrame.

Install the package

If you have not installed the package in your Python environment, install it using the following command:

pip install oracledb --upgrade

If you don't have permissions to write into system directories, you can add --user argument.

pip install oracledb --upgrade --user

*Image from https://oracle.github.io/python-oracledb/python-oracledb-arch.png

Connect to Oracle database

Now we can connect to Oracle using this package.

# connect_to_oracle.py

import oracledb
import os

user = 'OralceUser'
password = 'MyPassword'
port = 1984
service_name = 'orclpdb'
conn_string = "oracle_server_addr:{port}/{service_name}".format(port=port, service_name=service_name)

with oracledb.connect(user=user, password=password, dsn=conn_string) as conn:
    with conn.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):
            print(r)

The above Python script establishes a connection to Oracle database and then create a cursor to execute the SQL statement.

Read data as pandas DataFrame

Now we can also read data as pandas DataFrame after the connection is established. 

# connect_to_oracle.py

import oracledb
import pandas as pd 

user = 'OralceUser'
password = 'MyPassword'
port = 1521
service_name = 'orclpdb'
conn_string = "oracle_server_addr:{port}/{service_name}".format(port=port, service_name=service_name)

with oracledb.connect(user=user, password=password, dsn=conn_string) as conn:
	sql="""
		select current_timestamp as ts, 1 as id from dual
	"""
        df = pd.read_sql(sql=sql, con=conn)
	print(df)

The created DataFrame has two columns named ts and id respectively. 

Connect with Oracle native client

If you have tnsnames.ora created with Oracle native client, you can directly use the configured service name as dsn parameter when establish the connection. 

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_server_addr)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

The the following code snippet uses the service name 'ORCLPDB' directly:

with oracledb.connect(user=user, password=password, dsn='ORCLPDB') as conn:

References

python-oracledb - Python Driver for Oracle Database