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   lt pyshell 1 gt    line 1  in  lt module gt      import cx Oracle ImportError  DLL load failed  The specified module could not be found    I will be grateful for any help

User · Answer

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 Python Lib site-packages into the Lib site-packages from your virtual env  Of course  also here  architecture and version are important

User · Answer

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

User · Answer

Ensure these two and it should work -    Python  Oracle instantclient and cx Oracle are 32 bit  Set the environment variables    Fixes this issue on windows like a charm

User · Answer

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      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

User · Answer

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

User · Answer

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    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 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 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  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

User · Answer

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

User · Answer

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

User · Answer

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

[python] How can I access Oracle from Python?

Examples related to python

Examples related to oracle

Examples related to database-connection

Examples related to cx-oracle