[enhancement] Support for OracleDB (sql widget)

[selepo]

Would it be possible to add support for Oracle DB, in the sql widget, given that it is widely used, at least in the busines world ?

[janezd]

Database support is in development and there's a lot of prototyping going on, so we would prefer to concentrate on a single db at the moment. I would like to say that contributions are welcome, but I fear that they would soon get outdated...

[kernc]

How about introducing a new dependency, a database abstraction layer such as pyDAL or SQLAlchemy Core (not ORM)? In addition to pythonic SQL statements, we get, with pyDAL at least, seamless support for just about all SQL dialects and some NoSQL eventually as well.

[oyvinrog]

how about ODBC or JDBC? Then people would just have to use the odbc administrator and add a source of their own need. Including Oracle, Microsoft, etc.....

[ajdapretnar]

@janezd @kernc Is anyone working on this or will anyone be working on this in the next 6 months? If not, I suggest we close the issue.

[janezd]

AFAIK, no.

[ajdapretnar]

Closed for the same reason as in #28.

[kernc]

One mechanism of triaging open issues are issue labels. Look into them.

[ajdapretnar]

I am getting seriously frustrated over this. :angry: Unless we're employing 3 new people on the project in the near future, this is absolutely pointless.

[lazykat]

I would really love to see ODBC or something similar for connection to an Oracle DB via SQL Table. Thanks for taking the request.

[Juriell]

I just built a widget for my personal use to select data from oracle database. Ir uses cx_oracle and returns the result as pandas dataframe and orange table.

[AlexS12]

@juriel how could I have a look? Is it in a fork?

[ajdapretnar]

@Juriell Would you consider submitting a PR against this repo? I am sure many people would appreciate your contribution. :) And we're always happy to review the code!

[Juriell]

Ok, i will. I just want to say that it has no error handlers and is very primitive

[ajdapretnar]

Then perhaps you could submit against orange3-prototypes? What are your thoughts, @astaric?

[Juriell]

Added a pull request: https://github.com/biolab/orange3/pull/2551

[anzenmusaigai]

Hello Juriell

I am a beginner of Python and Orange. I want to use your 'Oracle SQL widget', and try to install it. I put 2files in each directory as follows. Orange/widgets/data/oraclesql.py Orange/widgets/data/icons/ORACLE_SQL.svg And I start Orange3 from Anaconda Navigator, but there is no new widget 'Oracle SQL'. Do I have to do something else?

best regard

P.S. about My PC 　　Windows7 64bit Core i7 16GB mem Python 3.6 Orange3 3.9.0

[Juriell]

I had almost no time to continue this widget. I just made an Orange3-prototypes fork and put it there: https://github.com/Juriell/orange3-prototypes/tree/oracle_sql_widget. Install it from .zip file and it should work. But remember: you must have cx_oracle installed and working to be able to run this widget. Also don't select billions of columns - i selcected 100 and the orange "Data Table" widget broke.

[anzenmusaigai]

Thank you, Juriell I'll try it.

[anzenmusaigai]

Thank you, Juriell I tried it and succeeded. Thank you very much.

[Juriell]

@anzenmusaigai great to hear that.

[anzenmusaigai]

Hello Juriell I installed Orange3-prototypes in my 64bit PC yesterday successfully. So,today, I try to install it in my windows7 32bit PC. I succeed installation. But some widgets don't appear in Prototypes. For example, Oracle SQL and IPython, Stacking, Line Plot, etc. There are 22 widget icons in 64bit PC, but there are 13 widget icons in 32bitPC. Today I installed in 32bit PC as follows. 1) On Git CMD,

git clone https://github.com/biolab/orange3-prototypes.git cd orange3-prototypes pip install -e . 2) Conformation Orange3 widgets There is not Oracle SQL Widget. 3) Then I remember about .ZIP file. So, I download ZIP file and unzip. I overwrite files in Orange3-prototypes folder by unziped files. Again, pip install -e . Conformation Orange3 widgets -> There is not Oracle SQL Widget etc. Did I do the wrong way? I would appreciate it if you give me advice.

[Juriell]

I don't know about the other widgets and why you can't see them. I can only tell you that cx_oracle package runs only on 64 bit windows.

[ajdapretnar]

Also on Windows, you probably won't see Webcam, Face Detector and so on. You'd need OpenCV package that is not supported on Windows (unless you try with Anaconda).

[anzenmusaigai]

Dear Juriell Thank you for your response. "cx_oracle package runs only on 64 bit windows." -> I did not know about this matter. Sorry. For a while, I use only 64bit PC. Thank you for helping me.

[anzenmusaigai]

Dear ajdapretnar Thank you for your response. I can see Webcam, Face Detector Icons. OpenCV package version is 3.3.1.

There is something to worry about. In command prompt, type "orange-canvas", there are some warning messages about PyQt4. I have installed PyQt 5.6.0. Is this wrong? And Orange3 version is 3.4.1. in 32bit PC. (Warning messages) C:\Users**\orange3-prototypes\orangecontrib\prototypes\widgets\owclock.p y:3: RuntimeWarning: Loaded module AnyQt._backport as a substitute for PyQt4 from PyQt4.QtCore import Qt, QPoint, QDateTime, QSize, QRegExp C:\Users**\orange3-prototypes\orangecontrib\prototypes\widgets\owclock.p y:3: RuntimeWarning: Loaded module AnyQt._backport.QtCore as a substitute for Py Qt4.QtCore from PyQt4.QtCore import Qt, QPoint, QDateTime, QSize, QRegExp C:\Users****\orange3-prototypes\orangecontrib\prototypes\widgets\owclock.p y:4: RuntimeWarning: Loaded module AnyQt._backport.QtGui as a substitute for PyQ t4.QtGui from PyQt4.QtGui import QApplication, QCalendarWidget, QBrush, \ qt.network.ssl: QSslSocket: cannot resolve TLSv1_1_client_method qt.network.ssl: QSslSocket: cannot resolve TLSv1_2_client_method qt.network.ssl: QSslSocket: cannot resolve TLSv1_1_server_method qt.network.ssl: QSslSocket: cannot resolve TLSv1_2_server_method qt.network.ssl: QSslSocket: cannot resolve SSL_select_next_proto qt.network.ssl: QSslSocket: cannot resolve SSL_CTX_set_next_proto_select_cb qt.network.ssl: QSslSocket: cannot resolve SSL_get0_next_proto_negotiated An error occurred while fetching help inventory 'PyQt5.QtCore.QUrl('http://orang e3-prototypes.readthedocs.io/en/latest/')'

---

On windows7 64bit PC, there is following warning. OpenCV 3.3.0 PyQt 5.6.0 Orange3 3.9.0 (Warning messages) C:\Users**\Anaconda3\lib\site-packages\gensim\utils.py:860: UserWarning: detected Windows; aliasing chunkize to chunkize_serial warnings.warn("detected Windows; aliasing chunkize to chunkize_serial") C:\Users**\orange3-prototypes\orangecontrib\prototypes\widgets\owclock.p y:3: RuntimeWarning: Loaded module AnyQt._backport as a substitute for PyQt4 from PyQt4.QtCore import Qt, QPoint, QDateTime, QSize, QRegExp C:\Users**\orange3-prototypes\orangecontrib\prototypes\widgets\owclock.p y:3: RuntimeWarning: Loaded module AnyQt._backport.QtCore as a substitute for Py Qt4.QtCore from PyQt4.QtCore import Qt, QPoint, QDateTime, QSize, QRegExp C:\Users**\orange3-prototypes\orangecontrib\prototypes\widgets\owclock.p y:4: RuntimeWarning: Loaded module AnyQt._backport.QtGui as a substitute for PyQ t4.QtGui from PyQt4.QtGui import QApplication, QCalendarWidget, QBrush, \ [nltk_data] Downloading package wordnet to [nltk_data] C:\Users**\AppData\Roaming\Orange\nltk_data... [nltk_data] Package wordnet is already up-to-date! [nltk_data] Downloading package stopwords to [nltk_data] C:\Users**\AppData\Roaming\Orange\nltk_data... qt.network.ssl: QSslSocket: cannot resolve TLSv1_1_client_method qt.network.ssl: QSslSocket: cannot resolve TLSv1_2_client_method qt.network.ssl: QSslSocket: cannot resolve TLSv1_1_server_method qt.network.ssl: QSslSocket: cannot resolve TLSv1_2_server_method qt.network.ssl: QSslSocket: cannot resolve SSL_select_next_proto qt.network.ssl: QSslSocket: cannot resolve SSL_CTX_set_next_proto_select_cb qt.network.ssl: QSslSocket: cannot resolve SSL_get0_next_proto_negotiated [nltk_data] Package stopwords is already up-to-date! [nltk_data] Downloading package punkt to [nltk_data] C:\Users**\AppData\Roaming\Orange\nltk_data... [nltk_data] Package punkt is already up-to-date! [nltk_data] Downloading package opinion_lexicon to [nltk_data] C:\Users**\AppData\Roaming\Orange\nltk_data... [nltk_data] Package opinion_lexicon is already up-to-date! [nltk_data] Downloading package vader_lexicon to [nltk_data] C:\Users**\AppData\Roaming\Orange\nltk_data... [nltk_data] Package vader_lexicon is already up-to-date! [nltk_data] Downloading package averaged_perceptron_tagger to [nltk_data] C:\Users**\AppData\Roaming\Orange\nltk_data... [nltk_data] Package averaged_perceptron_tagger is already up-to- [nltk_data] date! [nltk_data] Downloading package maxent_treebank_pos_tagger to [nltk_data] C:\Users****\AppData\Roaming\Orange\nltk_data... [nltk_data] Package maxent_treebank_pos_tagger is already up-to- [nltk_data] date! An error occurred while fetching help inventory 'PyQt5.QtCore.QUrl('http://orang e3-prototypes.readthedocs.io/en/latest/')' An error occurred while fetching help inventory 'PyQt5.QtCore.QUrl('http://spark .com/my-widget/docs/')'

[ajdapretnar]

These warnings are normal. Also, for issues with Prototypes add-on, please report issues to the corresponding issue tracker.

[ajdapretnar]

Oracle widget is now available in Prototypes.

I would close this issue for now. Currently, no new support for databases is planned.