Active10 months ago
Connect python to oracle db with pyodbc I’m only the 3 billion-th blogger to write about this but for some reason, even with the interwebs saturated with python-Oracle connection examples, this still took me pretty much the whole day to figure out.
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:
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. 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.
I will be grateful for any help.
Python is a popular general purpose dynamic scripting language. With the rise of Frameworks, Python is also becoming common for Web application development. If you want to use Python and an Oracle database, this tutorial helps you get started by giving examples. If you are new to Python review the. The process of installing Oracle drivers into Python is complex. To use Python with Oracle three components must all be inplace and be of the same version (Python. Python Spark supports the following APIs to perform read or write operations on the Oracle datastore: jdbc; format; The above APIs can be used to read data from Oracle datastore to create a DataFrame and write the DataFrame to Oracle datastore.
Gaffi3,52266 gold badges3939 silver badges7070 bronze badges
user425194user425194
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.
A Samsung expert at Best Buy will call to schedule your Galaxy S10 try out. Please share your ZIP Code to find a nearby Best Buy to try out your next phone., our Samsung expert wants to know about you so that he can assist you better. May 14, 2017 After the MobileGo app is installed, launch it and use the build-in QR code scanner to scan the code on the interface from your computer again. When Samsung Galaxy/Note is successfully connected to the computer, you can easily transfer data in between Android and PC with one-click. Samsung galaxy software for computer.
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:
- Download the Oracle Instant Client package. I used instantclient-basic-win32-11.2.0.1.0.zip. Unzip it to C:yourpathtoinstantclient_11_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.
- 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.
- 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.
- 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.
2,38011 gold badge1919 silver badges3030 bronze badges
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![Python Python](/uploads/1/2/4/8/124894291/621189130.png)
36.9k1515 gold badges157157 silver badges125125 bronze badges
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 Chhabra3,71322 gold badges3030 silver badges3737 bronze badges
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.
MikeMike
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.
TMLTML11.2k33 gold badges2929 silver badges4141 bronze badges
Ensure these two and it should work:-
![Python Oracle Driver Python Oracle Driver](/uploads/1/2/4/8/124894291/639120234.png)
- Python, Oracle instantclient and cx_Oracle are 32 bit.
- Set the environment variables.
Fixes this issue on windows like a charm.
misguided1,7231515 gold badges4141 silver badges7979 bronze badges
Venu MurthyVenu Murthy
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.
WardPure Python Oracle Driver
Ward1,92911 gold badge1515 silver badges3434 bronze badges
Note if you are using pandas you can access it in following way:
sushmitsushmit2,30511 gold badge2323 silver badges2626 bronze badges
Python Cx_oracle
Note :
- In (dsn_tns) if needed, place an 'r' before any parameter in order to address any special character such as '.
- 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'
- use triple quotes if you want to spread your query across multiple lines.
2,02211 gold badge44 silver badges1717 bronze badges
SirajSiraj