"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