Example of querying an Oracle database using Python, SQLAlchemy, and Pandas

oracle-query.org

Query Oracle databases with Python and SQLAlchemy

N.B. SQLAlchemy now incorporates all of this information in its documentation; I’m leaving this post here, but recommend referring to SQLAlchemy instead of these instructions.

Install requirements

1. We’ll assume you already have SQLAlchemy and Pandas installed; these are included by default in many Python distributions.
2. Install the cx_Oracle package in your Python environment, using either pip or conda, for example:

   pip install cx_Oracle
       

3. Install the ODPI-C libraries as described at https://oracle.github.io/odpi/doc/installation.html. This, naturally, requires that you create a free Oracle developer account to get access to the libraries. I put the libraries at $HOME/opt/oracle/… and linked them to $HOME/lib, which seems to work fine. I looked for a Homebrew recipe to install these but didn’t see one, and the conda package for cx_oracle didn’t seem to include these libraries.

Example use

The easy case

Oracle databases apparently have something called an SID, and they might also have something called a service name. (More about the difference.) If you can use an SID to connect to your Oracle database, then you can use the simple connection string as shown in the SQLAlchemy docs. In this example, your SID corresponds to the database parameter.

import pandas as pd
from sqlalchemy import create_engine

oracle_connection_string = 'oracle+cx_oracle://{username}:{password}@{hostname}:{port}/{database}'

engine = create_engine(
    oracle_connection_string.format(
        username='CALCULATING_CARL',
        password='12345',
        hostname='all.thedata.com',
        port='1521',
        database='everything',
    )
)

data = pd.read_sql("SELECT * FROM …", engine)

The annoying case

Sometimes, an Oracle database will require you to connect using a service name instead of an SID. In this case, the connection string is more complicated, but the cx_Oracle module has an undocumented function that will build it for you. (Thanks to this StackOverflow answer for this tip.)

import pandas as pd
from sqlalchemy import create_engine
import cx_Oracle

oracle_connection_string = (
    'oracle+cx_oracle://{username}:{password}@' +
    cx_Oracle.makedsn('{hostname}', '{port}', service_name='{service_name}')
)

engine = create_engine(
    oracle_connection_string.format(
        username='CALCULATING_CARL',
        password='12345',
        hostname='all.thedata.com',
        port='1521',
        service_name='every.piece.ofdata',
    )
)

data = pd.read_sql("SELECT * FROM …", engine)

Thanks a lot - annoying case using service name worked for me.

Hey,
I am getting the following error even after I trying the annoying case.
DatabaseError: (cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: "C:\app\Seva_Mahapatra\product\oci.dll is not the correct architecture". See https://oracle.github.io/odpi/doc/installation.html#windows for help
(Background on this error at: http://sqlalche.me/e/4xp6)

This post has been extremely helpful over the past few months for me as I've had to connect to multiple databases in that time with all the listed variations of connection types and more. Well written and straight to the point.

thanks, this is very useful.

I came across another handy option if you already have a connection string that works with cx_Oracle. It lets SQLAlchemy use the underlying connection directly. Leaving it here for next time I need it!

import pandas as pd
from sqlalchemy import create_engine
import cx_Oracle

connection_string = 'me/my_pass@//host:port/name'
conn_factory = lambda: cx_Oracle.connect(connection_string)

engine = create_engine(
    "oracle://", 
    creator=conn_factory
)

pd.read_sql("select sys_context('userenv', 'current_schema') as schema from dual", engine)

Just a small correction. makedsn is a well documented function of cx_Oracle. Please check the cx_Oracle documentation link.