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)

This clarified a lot for me. sqlalchemy should just link to this. Thanks.

---

Empty Traceback (most recent call last)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _do_get(self)
1121 wait = use_overflow and self._overflow >= self._max_overflow
-> 1122 return self._pool.get(wait, self._timeout)
1123 except sqla_queue.Empty:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\queue.py in get(self, block, timeout)
144 if self._empty():
--> 145 raise Empty
146 elif timeout is None:

Empty:

During handling of the above exception, another exception occurred:

DatabaseError Traceback (most recent call last)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _wrap_pool_connect(self, fn, connection)
2146 try:
-> 2147 return fn()
2148 except dialect.dbapi.Error as e:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in connect(self)
386 if not self._use_threadlocal:
--> 387 return _ConnectionFairy._checkout(self)
388

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _checkout(cls, pool, threadconns, fairy)
765 if not fairy:
--> 766 fairy = _ConnectionRecord.checkout(pool)
767

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in checkout(cls, pool)
515 def checkout(cls, pool):
--> 516 rec = pool._do_get()
517 try:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _do_get(self)
1137 with util.safe_reraise():
-> 1138 self._dec_overflow()
1139 else:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py in exit(self, type_, value, traceback)
65 if not self.warn_only:
---> 66 compat.reraise(exc_type, exc_value, exc_tb)
67 else:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py in reraise(tp, value, tb, cause)
186 raise value.with_traceback(tb)
--> 187 raise value
188

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _do_get(self)
1134 try:
-> 1135 return self._create_connection()
1136 except:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _create_connection(self)
332
--> 333 return _ConnectionRecord(self)
334

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in init(self, pool, connect)
460 if connect:
--> 461 self.__connect(first_connect_check=True)
462 self.finalize_callback = deque()

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in __connect(self, first_connect_check)
650 self.starttime = time.time()
--> 651 connection = pool._invoke_creator(self)
652 pool.logger.debug("Created new connection %r", connection)

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\strategies.py in connect(connection_record)
104 return connection
--> 105 return dialect.connect(*cargs, **cparams)
106

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\dialects\oracle\cx_oracle.py in connect(self, *cargs, **cparams)
793 return super(OracleDialect_cx_oracle, self).connect(
--> 794 *cargs, **cparams)
795

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py in connect(self, *cargs, **cparams)
392 def connect(self, *cargs, **cparams):
--> 393 return self.dbapi.connect(*cargs, **cparams)
394

DatabaseError: DPI-1047: 64-bit Oracle Client library cannot be loaded: "The specified module could not be found". See https://oracle.github.io/odpi/doc/installation.html#windows for help

The above exception was the direct cause of the following exception:

DatabaseError Traceback (most recent call last)
in ()
9 )
10
---> 11 data = pd.read_sql("SELECT * FROM CUST_ALERT_DET_TBL", engine)

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
414 sql, index_col=index_col, params=params,
415 coerce_float=coerce_float, parse_dates=parse_dates,
--> 416 chunksize=chunksize)
417
418

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize)
1085 args = _convert_params(sql, params)
1086
-> 1087 result = self.execute(*args)
1088 columns = result.keys()
1089

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
976 def execute(self, *args, **kwargs):
977 """Simple passthrough to SQLAlchemy connectable"""
--> 978 return self.connectable.execute(*args, **kwargs)
979
980 def read_table(self, table_name, index_col=None, coerce_float=True,

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in execute(self, statement, *multiparams, **params)
2061 """
2062
-> 2063 connection = self.contextual_connect(close_with_result=True)
2064 return connection.execute(statement, *multiparams, **params)
2065

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in contextual_connect(self, close_with_result, **kwargs)
2110 return self._connection_cls(
2111 self,
-> 2112 self._wrap_pool_connect(self.pool.connect, None),
2113 close_with_result=close_with_result,
2114 **kwargs)

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _wrap_pool_connect(self, fn, connection)
2149 if connection is None:
2150 Connection._handle_dbapi_exception_noconnection(
-> 2151 e, dialect, self)
2152 else:
2153 util.reraise(*sys.exc_info())

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _handle_dbapi_exception_noconnection(cls, e, dialect, engine)
1463 util.raise_from_cause(
1464 sqlalchemy_exception,
-> 1465 exc_info
1466 )
1467 else:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py in raise_from_cause(exception, exc_info)
201 exc_type, exc_value, exc_tb = exc_info
202 cause = exc_value if exc_value is not exception else None
--> 203 reraise(type(exception), exception, tb=exc_tb, cause=cause)
204
205 if py3k:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py in reraise(tp, value, tb, cause)
184 value.cause = cause
185 if value.traceback is not tb:
--> 186 raise value.with_traceback(tb)
187 raise value
188

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _wrap_pool_connect(self, fn, connection)
2145 dialect = self.dialect
2146 try:
-> 2147 return fn()
2148 except dialect.dbapi.Error as e:
2149 if connection is None:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in connect(self)
385 """
386 if not self._use_threadlocal:
--> 387 return _ConnectionFairy._checkout(self)
388
389 try:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _checkout(cls, pool, threadconns, fairy)
764 def _checkout(cls, pool, threadconns=None, fairy=None):
765 if not fairy:
--> 766 fairy = _ConnectionRecord.checkout(pool)
767
768 fairy._pool = pool

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in checkout(cls, pool)
514 @classmethod
515 def checkout(cls, pool):
--> 516 rec = pool._do_get()
517 try:
518 dbapi_connection = rec.get_connection()

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _do_get(self)
1136 except:
1137 with util.safe_reraise():
-> 1138 self._dec_overflow()
1139 else:
1140 return self._do_get()

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py in exit(self, type_, value, traceback)
64 self._exc_info = None # remove potential circular references
65 if not self.warn_only:
---> 66 compat.reraise(exc_type, exc_value, exc_tb)
67 else:
68 if not compat.py3k and self._exc_info and self._exc_info[1]:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py in reraise(tp, value, tb, cause)
185 if value.traceback is not tb:
186 raise value.with_traceback(tb)
--> 187 raise value
188
189 else:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _do_get(self)
1133 if self._inc_overflow():
1134 try:
-> 1135 return self._create_connection()
1136 except:
1137 with util.safe_reraise():

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in _create_connection(self)
331 """Called by subclasses to create a new ConnectionRecord."""
332
--> 333 return _ConnectionRecord(self)
334
335 def _invalidate(self, connection, exception=None):

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in init(self, pool, connect)
459 self.__pool = pool
460 if connect:
--> 461 self.__connect(first_connect_check=True)
462 self.finalize_callback = deque()
463

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool.py in __connect(self, first_connect_check)
649 try:
650 self.starttime = time.time()
--> 651 connection = pool._invoke_creator(self)
652 pool.logger.debug("Created new connection %r", connection)
653 self.connection = connection

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\strategies.py in connect(connection_record)
103 if connection is not None:
104 return connection
--> 105 return dialect.connect(*cargs, **cparams)
106
107 creator = pop_kwarg('creator', connect)

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\dialects\oracle\cx_oracle.py in connect(self, *cargs, **cparams)
792 else:
793 return super(OracleDialect_cx_oracle, self).connect(
--> 794 *cargs, **cparams)
795
796 def initialize(self, connection):

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py in connect(self, *cargs, **cparams)
391
392 def connect(self, *cargs, **cparams):
--> 393 return self.dbapi.connect(*cargs, **cparams)
394
395 def create_connect_args(self, url):

DatabaseError: (cx_Oracle.DatabaseError) DPI-1047: 64-bit Oracle Client library cannot be loaded: "The specified module could not be found". See https://oracle.github.io/odpi/doc/installation.html#windows for help

this is the error i keep getting even after trying your solution

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.