How can I access Oracle from Python?

Question

How can I access Oracle from Python? I have downloaded a cx_Oracle msi installer, but Python can't import the library.

I get the following error:

import cx_Oracle

Traceback (most recent call last):
  File "<pyshell#1>", line 1, in <module>
    import cx_Oracle
ImportError: DLL load failed: The specified module could not be found.

I will be grateful for any help.

Solution 1

Here's what worked for me. My Python and Oracle versions are slightly different from yours, but the same approach should apply. Just make sure the cx_Oracle binary installer version matches your Oracle client and Python versions.

My versions:

• Python 2.7
• Oracle Instant Client 11G R2
• cx_Oracle 5.0.4 (Unicode, Python 2.7, Oracle 11G)
• Windows XP SP3

Steps:

1. Download the Oracle Instant Client package. I used instantclient-basic-win32-11.2.0.1.0.zip. Unzip it to C:\your\path\to\instantclient_11_2
2. Download and run the cx_Oracle binary installer. I used cx_Oracle-5.0.4-11g-unicode.win32-py2.7.msi. I installed it for all users and pointed it to the Python 2.7 location it found in the registry.
3. Set the ORACLE_HOME and PATH environment variables via a batch script or whatever mechanism makes sense in your app context, so that they point to the Oracle Instant Client directory. See oracle_python.bat source below. I'm sure there must be a more elegant solution for this, but I wanted to limit my system-wide changes as much as possible. Make sure you put the targeted Oracle Instant Client directory at the beginning of the PATH (or at least ahead of any other Oracle client directories). Right now, I'm only doing command-line stuff so I just run oracle_python.bat in the shell before running any programs that require cx_Oracle.
4. Run regedit and check to see if there's an NLS_LANG key set at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. If so, rename the key (I changed it to NLS_LANG_OLD) or unset it. This key should only be used as the default NLS_LANG value for Oracle 7 client, so it's safe to remove it unless you happen to be using Oracle 7 client somewhere else. As always, be sure to backup your registry before making changes.
5. Now, you should be able to import cx_Oracle in your Python program. See the oracle_test.py source below. Note that I had to set the connection and SQL strings to Unicode for my version of cx_Oracle.

Source: oracle_python.bat

@echo off
set ORACLE_HOME=C:\your\path\to\instantclient_11_2
set PATH=%ORACLE_HOME%;%PATH%

Source: oracle_test.py

import cx_Oracle

conn_str = u'user/password@host:port/service'
conn = cx_Oracle.connect(conn_str)
c = conn.cursor()
c.execute(u'select your_col_1, your_col_2 from your_table')
for row in c:
    print row[0], "-", row[1]
conn.close()

Possible Issues:

• "ORA-12705: Cannot access NLS data files or invalid environment specified" - I ran into this before I made the NLS_LANG registry change.
• "TypeError: argument 1 must be unicode, not str" - if you need to set the connection string to Unicode.
• "TypeError: expecting None or a string" - if you need to set the SQL string to Unicode.
• "ImportError: DLL load failed: The specified procedure could not be found." - may indicate that cx_Oracle can't find the appropriate Oracle client DLL.

Solution 2

You can use any of the following way based on Service Name or SID whatever you have.

With SID:

import cx_Oracle
dsn_tns = cx_Oracle.makedsn('server', 'port', 'sid')
conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from TABLE_NAME')
for row in c:
   print(row)
conn.close()

OR

With Service Name:

import cx_Oracle
dsn_tns = cx_Oracle.makedsn('server', 'port', service_name='service_name')
conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from TABLE_NAME')
for row in c:
   print(row)
conn.close()

Solution 3

Here is how my code looks like. It also shows an example of how to use query parameters using a dictionary. It works on using Python 3.6:

import cx_Oracle

CONN_INFO = {
    'host': 'xxx.xx.xxx.x',
    'port': 12345,
    'user': 'SOME_SCHEMA',
    'psw': 'SECRETE',
    'service': 'service.server.com'
}

CONN_STR = '{user}/{psw}@{host}:{port}/{service}'.format(**CONN_INFO)

QUERY = '''
    SELECT
        *
    FROM
        USER
    WHERE
        NAME = :name
'''


class DB:
    def __init__(self):
        self.conn = cx_Oracle.connect(CONN_STR)

    def query(self, query, params=None):
        cursor = self.conn.cursor()
        result = cursor.execute(query, params).fetchall()
        cursor.close()
        return result


db = DB()
result = db.query(QUERY, {'name': 'happy'})

Solution 4

Note if you are using pandas you can access it in following way:

import pandas as pd
import cx_Oracle
conn= cx_Oracle.connect('username/pwd@host:port/service_name')
try:
    query = '''
         SELECT * from dual
             '''
    df = pd.read_sql(con = conn, sql = query)
finally:
    conn.close()
df.head()

Solution 5

import cx_Oracle
   dsn_tns = cx_Oracle.makedsn('host', 'port', service_name='give service name') 
   conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns) 
   c = conn.cursor()
   c.execute('select count(*) from schema.table_name')
for row in c:
   print row
conn.close()

---

Note :

1. In (dsn_tns) if needed, place an 'r' before any parameter in order to address any special character such as '\'.

2. In (conn) if needed, place an 'r' before any parameter in order to address any special character such as '\'. For example, if your user name contains '\', you'll need to place 'r' before the user name: user=r'User Name' or password=r'password'

3. use triple quotes if you want to spread your query across multiple lines.

View more solutions

Comments

[Venu Murthy]

that was not intentional, not sure how that came in though.

[The Red Pea]

Does anyone know if this works with other versions (i.e. 3.4? and 64 bit) as long as all the version numbers and platforms are aligned?

[Honinbo Shusaku]

@TheRedPea This has worked for me for version 3.5 and 64 bit.

[Akshay]

I am still struggling with the ImportError: DLL load failed: The specified procedure could not be found. while I am trying to import the cx_Oracle library.

[Abhi]

Thanks for detailed notes. Very helpful!! My understanding is that I need to install Oracle client before I install cx_oracle and connect to database. I am trying to connect to Oracle db in our corporate cluster from my machine, but could not install cx_oracle, shows up this error message: distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation.

[Alex Pi]

Do I really need the Oracle client to have this working? Isn't as simple as a direct JDBC connection? Ty.