Connect to Oracle DataBase with python

Scripting
, , ,
1

Hi,
Is there a way to access data from my oracle database with the Rhino Python Editor without having an Oracle Client installed?
I’ve found the DotConnect driver from Devart which allows it in Visual Studio with VB.Net but I’d like to keep using python in rhino.
Any suggestions ?
Thanks

2

Hi,

Do you mean something like this:

Using Python With Oracle Database 11g:
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/OOW11/python_db/python_db.htm

3

Hi, thanks for your reply.
I tried to use cx_Oracle and it works well with Python2.7 installed on my machine. I had few issues with version (32 or 64 bits) of the different things to install, but I did it, thanks to Google :wink:
Now I’d like to make it run inside the Rhino Python Editor which apparently works differently because I can’t find a way to set it up.
Is IronPython different from Python concerning this issue?
It refuses to load the module cx_Oracle, which I installed in my directory :
\AppData\Roaming\McNeel\Rhinoceros\5.0\Plug-ins\IronPython (814d908a-e25c-493d-97e9-ee3861957f49)\settings\lib

import cx_Oracle
Message: No module named cx_Oracle

Then I installed it here :
C:\Program Files\Rhinoceros 5 (64-bit)\Plug-ins\IronPython\Lib

Same thing …

Any advice ?

4

Yes, python is not IronPython, because IronPython runs inside the .NET framework. You may want to search specifically for how to connect from IronPython to Oracle.

5

OK,
If ironPython runs inside .NET Framework, I assumed that the previous mentionned driver dotConnect for VB.Net would be useful. And actually, by transcoding the VB connexion example to python syntax, it works !
I am now able to connect to oracle database inside Rhino without having any oracle client installed on my computer. Unfortunately, this driver is licensed, 150$ a year … fair enough

For those who might be interrested :

import clr clr.AddReference("Devart.Data.Oracle") import Devart.Data.Oracle as dv
connectionBase = dv.OracleConnection() server = "..." port = "..." sid = "..." user = "..." password = "..." connectionBase.ConnectionString = "direct=true;data source={0};port={1};sid={2};user={3};password={4}".format(server, port, sid, user, password) try: dv.OracleConnection.Open(connectionBase) print "Database connected" except dv.OracleException,e : print "Fail to connect to database\n", e SQLCommandeText = "SELECT field1, field2, field3 FROM table WHERE field1='value'" try: commande = dv.OracleCommand(SQLCommandeText, connectionBase) except Exception, e: print e try: dataReader = dv.OracleCommand.ExecuteReader(commande) except Exception, e: print e try: dataList= [] while dataReader.Read(): dataDict= {} dataDict["field1"] = dataReader["field1"] dataDict["field2"] = dataReader["field2"] dataDict["field3"] = dataReader["field3"] dataList.append(dataDict) except Exception, e: print e print dataList

6

After long search on the matter, I still don’t succeed in connecting to Oracle Database using IronPython without the use of a licensed driver.
There might be a way to connect using the Oracle.DataAccess.dll file. I tried the following structure :

import clr
clr.AddReferenceToFileAndPath("***\Oracle.DataAccess.dll")
from Oracle.DataAccess.Client import OracleConnection

ConnectionString = "data source=***;user id=***;password=***"
conn = OracleConnection(ConnectionString)

conn.Open()

But I get the error message : object reference not set to an instance of an object (about conn object)
Is there anybody to give help me on this ?
Thank you for reading

7

This is connecting here:

import clr
clr.AddReference('System.Data.OracleClient')

import System.Data.OracleClient
import Rhino
import scriptcontext as sc
import rhinoscriptsyntax as rs


conStr = "Data Source=db_name;User ID=db_user_id;Password=db_password"


con = System.Data.OracleClient.OracleConnection(conStr)
con.Open()
print con.ServerVersion
print con.State
print con.DataSource
con.Close()

I think I just installed the OracleClient and it worked after that.

8

Thank you,
I am currently trying your solution, which is encouraging if it works for you.
I am facing a new error message :
OCIEnvCreate failed with return code -1
I’ll let you know as soon as it will be fixed.

9

Could you tell me which version of OracleClient did you use?

10

11.2.0.8 is the client version. The .dll is 4.0.30319.18402. I should also say that the oracle ‘environment’ on the PC is elaborate and was set up by database people, so there may be something with that which helps it to resolve or connect.

11

Thank you,
I’ve tried many different things but can’t find a way to solve this topic.
I learnt one thing though : I hate Oracle. :angry:

I tried an OLE DB for Oracle connection but it’s not working either:

import clr
import System
clr.AddReference("System.Data")
import System.Data
 
connectstr="PROVIDER=OraOLEDB.Oracle;DATA SOURCE=***; USER ID=***; PASSWORD=***"
 
dbcon=System.Data.OleDb.OleDbConnection(connectstr)
dbcon.Open()

Error message : OraOLEDB.Oracle provider is not registered on the local machine

But when I check, the Provider exists in my 64 bits provider list:

There might be something tricky somewhere, but what ?

I would be very grateful if anyone could help me on this.

12

Hi nathancoatney

I used your code and I recive this message:

Attempting to load Oracle client libraries resulted in BadImageFormatException. This issue will occur when running in 64-bit mode with 32-bit Oracle client components installed.

My data base runs in 32 bits

Can you help me?

13

Sorry Cristiano…but I haven’t worked with oracle in quite some time now, and even less with .net, so I’m afraid I can’t be of much help. But, I’d say try the 64 bit client if your local system is 64 bit. Even if the database is 32 bit, I would think the communication between client and server should be the same.