"Connecting to Oracle DB from python script in Execute Python operator"

lplenka

Hello everyone,

 

I am trying to connect to my local Oracle DB from python script in Rapidminer. It's throwing "Database Error" when I execute the process.

I have executed the same python file from my terminal and it works but it doesn't work inside Rapidminer.

 

 

This is the xml file:

<?xml version="1.0" encoding="UTF-8"?>
<process version="8.1.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="8.1.000" expanded="true" name="Process">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="SYSTEM"/>
    <process expanded="true">
      <operator activated="true" class="python_scripting:execute_python" compatibility="7.4.000" expanded="true" height="68" name="Execute Python" width="90" x="246" y="136">
        <parameter key="script" value="import pandas&#10;import cx_Oracle&#10;&#10;def rm_main():&#10;    con = cx_Oracle.connect(&quot;demo&quot;, &quot;password&quot;, &quot;XE&quot;)  &#10;    #&quot;demo&quot;- db user name&#10;    #&quot;password&quot; - db user password&#10;    #&quot;XE&quot; - data source name&#10;    print(&quot;Database version:&quot;, con.version)&#10;&#10;    return "/>
      </operator>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>

 

 

This is the error :

Error after I run the process

I have no clue why this error is occuring. If someone has succesfully connected to any database from python script in execute python you can share your thoughts on this. kindly help :smileyhappy:

 

lplenka

Well, I tried a lot to fix the Oracle config but seems the program can't find 'LD_LIBRARY_PATH' from inside Rapidminer, I also tried setting the environment using 

os.environ['LD_LIBRARY_PATH'] = '/path/to/$ORACLE_HOME/lib'

But no success. So I tried something else, I used Jpype and Jaydebeapi and it worked.

 

Here is the code:

import pandas
import os
import jpype
import jaydebeapi

os.environ['JAVA_HOME']=  '/usr/lib/jvm/java-8-openjdk-amd64'
classpath = "path/to/ojdbc6.jar"
def rm_main():
    jpype.startJVM(jpype.getDefaultJVMPath(), "-Djava.class.path=%s" % classpath)

    try:
       con = jaydebeapi.connect('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@localhost:1521:dbname', ["user","password"])
       print("Connection Successful")
    except Exception as e:
       print (e)
    return

 

Cheers,

Lalu Prasad Lenka

Thomas_Ott

I believe this error is related to not being able to load in your cx_Oracle library. Is your python installation (or correct python environment) pathed in the RapidMiner preferences?

lplenka

@Thomas_Ott thanks for your reply. I am positive the python path is correct.

 

Also I am sure the error is in connection line as only import works without error.

Pavithra_Rao

@lplenka Have you tried executing this Python code on Python apps like Spyder, IPython Jupyter etc,.

Make sure you use the same Python environment as the one you are trying to connect with RapidMiner

This helps to understand if the error is due to running in RapidMiner or code itself has an error.

 

Let us know the results here.

 

Cheers,

lplenka

yes @Pavithra_Rao as I have already mentioned in my first post (second paragraph), I have executed the same script from the terminal and it works.

I also executed the same in Jupyter Notebook, here is a screenshot.

Pavithra_Rao

@lplenka, please check this following documentation, I guess it's something to do with Oracle config and Python communicates via RapidMiner.

 

https://oracle.github.io/odpi/doc/installation.html#windows

 

lplenka

Well, I tried a lot to fix the Oracle config but seems the program can't find 'LD_LIBRARY_PATH' from inside Rapidminer, I also tried setting the environment using 

os.environ['LD_LIBRARY_PATH'] = '/path/to/$ORACLE_HOME/lib'

But no success. So I tried something else, I used Jpype and Jaydebeapi and it worked.

 

Here is the code:

import pandas
import os
import jpype
import jaydebeapi

os.environ['JAVA_HOME']=  '/usr/lib/jvm/java-8-openjdk-amd64'
classpath = "path/to/ojdbc6.jar"
def rm_main():
    jpype.startJVM(jpype.getDefaultJVMPath(), "-Djava.class.path=%s" % classpath)

    try:
       con = jaydebeapi.connect('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@localhost:1521:dbname', ["user","password"])
       print("Connection Successful")
    except Exception as e:
       print (e)
    return

 

Cheers,

Lalu Prasad Lenka