Installing the Python cx_Oracle module in Windows and connecting to an Oracle database

Question

I've been trying to get this installed and setup, and despite there been several links describing how to do this, I'm just not getting this to work. I'll describe first the setup:

• My PC: Windows 8.1 Enterprise (62 Bit)
• Remote Server: Oracle DB Version 12.1.0.2
• C:\Users\Pymat\Oracle\instantclient_12_1 Here is where oraocci12.dll is located, as well as other folders and files
• C:\Users\Pymat\AppData\Local\Continuum\Anaconda3 Here is where the python.exe is located (i.e. os.path.dirname(sys.executable)) In a script, I have print (sys.version) which gives: 3.6.0 |Anaconda 4.3.0 (64-bit)| (default, Dec 23 2016, 11:57:41) [MSC v.1900 64 bit (AMD64)]

I have been referring to instructions from here and here and here and here and here.

Question 1: Which is the correct download for cx_Oracle? I went to cx_Oracle 5.2.1 from here and downloaded:

cx_Oracle-5.2.1-12c.win-amd64-py3.5.exe (md5)
cx_Oracle-5.2.1-12c.win32-py3.5.exe (md5)

It's not clear which of these is the correct .exe to download (or maybe both is correct). In any case, I use 12c to keep the versions consistent with the Oracle DB Version, although through this entire process there is no interaction with the Oracle DB on the remote server in any case, it seems.

Question 2: Is cx_Oracle correctly downloaded or actually missing?

Under the following path:

C:\Users\Pymat\AppData\Local\Continuum\Anaconda3\Lib\site-packages

I find:

cx_Oracle.cp35-win_amd64.pyd
cx_Oracle.cp35-win32.pyd

However I don't find under this directory (or any other) the cx_Oracle, like for example Verion-5.1.2-11g.win32-py2.7, as per here. I suspect there is an issue with locating the cx_Oracle module. See also Question 5

Question 3: Are my environment variables configured correctly?

(i) When I use the GUI in Windows to check the variables like here I see:

ORACLE_HOME
C:\Users\Pymat\Oracle\instantclient_12_1;
C:\Users\Pymat\Oracle\instantclient_12_1\vc12

PATH
C:\Users\Pymat\AppData\Local\Continuum\Anaconda3;
C:\Users\Pymat\AppData\Local\Continuum\Anaconda3\Scripts;
C:\Users\Pymat\AppData\Local\Continuum\Anaconda3\Library\bin;
C:\Users\Pymat\Oracle\instantclient_12_1;

NB: The semi-colon is missing at the end of ORACLE_HOME. Is that correct?

(ii) When I use C:\Users\Pymat>echo %PATH% in Command:

C:\ProgramData\Oracle\Java\javapath;
C:\WINDOWS\system32;
C:\WINDOWS;
C:\WINDOWS\System32\Wbem;
C:\WINDOWS\System32\WindowsPowerShell\v1.0\;
C:\Users\Pymat\.dnx\bin;
C:\Program Files\Microsoft DNX\Dnvm\;
C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;
C:\Users\Pymat\AppData\Local\Continuum\Anaconda3;
C:\Users\Pymat\AppData\Local\Continuum\Anaconda3\Scripts;
C:\Users\Pymat\AppData\Local\Continuum\Anaconda3\Library\bin;
C:\Users\Pymat\Oracle\instantclient_12_1;

(iii) When I go into the Python shell and use sys.path:

'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\python36.zip', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\DLLs', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\lib', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\lib\\site-packages', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\lib\\site-packages\\Sphinx-1.5.1-py3.6.egg', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\lib\\site-packages\\win32', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\lib\\site-packages\\win32\\lib', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\lib\\site-packages\\Pythonwin', 
'C:\\Users\\Pymat\\AppData\\Local\\Continuum\\Anaconda3\\lib\\site-packages\\setuptools-27.2.0-py3.6.egg'

Question 4: Instant Client Downloads, which downloads are actually essential?

After registering on Oracle, I download from here under Version 12.1.0.2 the "Instant Client Package - Basic" and "Instant Client Package - SDK". Some sites don't mention the SDK package, but I downloaded it anyway.

These are downloaded to my PC (not where the Oracle DB resides).

Question 5: In order to test for a successful installation, I go into the Python shell and use:

import os
os.chdir("C:\\Users\\Pymat\\Oracle\\instantclient_12_1")
import cx_Oracle

But then I get:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'cx_Oracle'

Why is this step not working?

Question 6: I cannot use Console correctly in Eclipse, but the Python shell in Command works, why?

When I click on "PyDev Console", then "Python Console", I then get:

'Create Interactive Console' has encountered a problem
Error initializing console

---

This is becoming a bit of a headache, and although I'm still a Python novice, I think this installation process could be a lot easier. Any advice welcome.

Answer 1

Long story short: Downgrade to Python 3.5 for now, binaries for Python 3.6 will be built with the next cx_Oracle release.

---

Long version:

In a script, I have print (sys.version) which gives: 3.6.0 [...]

I find:

cx_Oracle-5.2.1-12c.win-amd64-py3.5.exe (md5)
cx_Oracle-5.2.1-12c.win32-py3.5.exe (md5)

These installers are for Python 3.5 (see py3.5 in their name). Binary Python modules aren't necessarily API-compatible between major Python releases, that's why their filenames are version-specific.

There are no official cx_Oracle binaries for Windows for Python 3.6 yet. Once cx_Oracle 5.3 is released, it will include Windows installers for Python 3.6 as well (issue #45).

There won't be official Python 3.6 installers for current cx_Oracle version 5.2.1 (see issue #50).

Meanwhile, according to Jani Tiainen from the mailing lists, it shouldn't be difficult to build the binaries yourself -- but I've never tried this myself:

I think you only need to download Visual C++ 14 (2015) from Microsoft [1] and Instant Client libs from Oracle [2]. Then you should be able to compile it yourself.

Note that you need also SDK libs as well for Instant Client.

[1] http://landinghub.visualstudio.com/visual-cpp-build-tools

[2] http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

Answer 2

Thank you, I beleive on my way now. I installed Visuakl C++, and downgrading the Python version helped somewhat, but there were some further issues.

1. Using pip install cx_Oracle initially gave:

   Requirement already satisfied: cx_Oracle in c:\users\Pymat\appdata\local\programs\python\python35\lib\site-packages

however after using the following in my script, the problem went away:

os.chdir("C:\\Oracle\\instantclient_12_1")
import cx_Oracle

The only minor issue was the import was underlined in red, despite it now working.

2. I use in my script:

   print ("Python version: " + platform.python_version()) print ("cx_Oracle version: " + cx_Oracle.version) print ("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ','.')) print ("Oracle DB version: " + connection.version) print ("Oracle client encoding: " + connection.encoding)

Resulting in:

Python version: 3.5.3
cx_Oracle version: 5.2.1
Oracle client: (12.1.0.2.0)
Oracle DB version: 12.1.0.2.0
Oracle client encoding: US-ASCII

Further points, I deinstalled anaconda in the end. I may need some of the libraries in Anaconda, so not sure if reinstalling and using the Python there may help, at a future point. For now, it works without it.