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

lplenkalplenka Member Posts: 11 Contributor II
edited June 2019 in Help

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 :

err.pngError 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:

 

Best Answer

  • lplenkalplenka Member Posts: 11 Contributor II
    Solution Accepted

    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

Answers

  • Thomas_OttThomas_Ott RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,761 Unicorn

    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?

  • lplenkalplenka Member Posts: 11 Contributor II

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

    python.png

     

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

  • Pavithra_RaoPavithra_Rao Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 123 RM Data Scientist

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

  • lplenkalplenka Member Posts: 11 Contributor II

    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.

    connect.png

  • Pavithra_RaoPavithra_Rao Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 123 RM Data Scientist

    @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

     

Sign In or Register to comment.