Mastering Oracle Python, Part 9: Jython and IronPython - JDBC and ODP.NET in Python by Przemyslaw Piotrowski, Published December 2011 This installment will cover the two most popular development environments for Python - Java and.NET - as well as Python's native implementations of these platforms. Python versions 2.5 - 2.7. MxODBC Connect is a commercial client-server product that allows connecting Python to ODBC compatible databases running on remote servers without requiring an ODBC driver on the client side. The product uses mxODBC on the server side and provides a highly portable Python library for the client side. How can I access Oracle from Python? I have downloaded a cxOracle msi installer, but Python can't import the library. If you are using virtualenv, it is not as trivial to get the driver using the installer. What you can do then: install it as described by Devon. Then copy over cxOracle.pyd and the cxOracle-XXX.egg-info folder from Python. About cxOracle. CxOracle is a Python extension module that enables access to Oracle Database. It conforms to the Python database API 2.0 specification with a considerable number of additions and a couple of exclusions. CxOracle 7 has been tested with Python version 2.7, and with versions 3.5 through 3.7. Pyodbc connecting to Oracle. Ask Question Asked 3 years, 4 months ago. Active 3 years, 4 months ago. Check if Oracle driver is aligned to bit version of Python? ODBC drivers/DSNs can be found in ODBCcad32.exe in both: C:WindowsSystem32 and C:WindowsSysWOW64. Actual Technologies Mac OS X ODBC Driver for Oracle. DataDirect ODBC Driver for Oracle. Devart Oracle ODBC Driver Works on Windows, Linux, Mac OS X 32/64 bits. Supports Oracle servers: 12c, 11g, 10g, 9i, 8i, 8.0, including Oracle Express Edition 11g and 10g. Supports both x86 and x64 versions of the following Oracle Clients: 12c, 11g, 10g, 9i.

I'm trying to connect to an oracle database with Pyodbc:

I get the following error message:

Any suggestions how to fix it would be appreciated. I'm specifically interested in getting pyodbc to work and NOT cx_Oracle.

NickpickNickpick

2 Answers

Consider specifying the DRIVER in connection string:

ParfaitParfait

You have to use the proprietary library for Oracle, cx_Oracle, and you must have the Oracle client and SDK installed.

Once this is all set up you can simply:

Then you can create a cursor with the conn object:

And then you can execute SQL:

Not the answer you're looking for? Browse other questions tagged pythonoraclepyodbc or ask your own 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:

I will be grateful for any help.

Gaffi

9 Answers

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:yourpathtoinstantclient_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_MACHINESOFTWAREORACLE. 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

Source: oracle_test.py

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.

Devon BiereDevon Biere

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:

Vlad BezdenVlad Bezden

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

With SID:

OR

With Service Name:

Sahil ChhabraSahil Chhabra

Python Oracle Driver

In addition to the Oracle instant client, you may also need to install the Oracle ODAC components and put the path to them into your system path. cx_Oracle seems to need access to the oci.dll file that is installed with them.

Also check that you get the correct version (32bit or 64bit) of them that matches your: python, cx_Oracle, and instant client versions.

Python Oracle Driver Update

In addition to cx_Oracle, you need to have the Oracle client library installed and the paths set correctly in order for cx_Oracle to find it - try opening the cx_Oracle DLL in 'Dependency Walker' (http://www.dependencywalker.com/) to see what the missing DLL is.

TMLTML

Ensure these two and it should work:-

1. Python, Oracle instantclient and cx_Oracle are 32 bit.
2. Set the environment variables.

Fixes this issue on windows like a charm.

misguided

If you are using virtualenv, it is not as trivial to get the driver using the installer. What you can do then: install it as described by Devon. Then copy over cx_Oracle.pyd and the cx_Oracle-XXX.egg-info folder from PythonLibsite-packagesinto the Libsite-packages from your virtual env. Of course, also here, architecture and version are important.

WardWard

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

sushmitsushmit

Python Oracle Connection

Note :

PostgreSQL 64-bit is a powerful, open source object-relational database system. /postgresql-odbc-windows-10.html. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

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.

DanB

Not the answer you're looking for? Browse other questions tagged pythonoracledatabase-connectioncx-oracle or ask your own question.