Jupyter Lab installation on Fedora to access an Oracle database

Preamble

The first time I had a brief look to Python was 10-15 years ago when one of my colleague who got a late diploma (in the frame of his current job within a company) told me he had a class on Python. I told him that was dead technology and very soon the language will be replaced by something else. How wrong I was !!

With the recent rise of Hadoop to store huge amount of data on commodity hardware we have seen emergence of a new way of processing in a *clever* way this new huge amount of data. The new buzz words are Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL).

The traditional language of this new technologies and new way of working as well as processing statistical and financial operations is Python. So the recent rise of this very old language (1991) !!

I have started to learn it a bit using free Youtube video of Academind and even bought their course on Udemy. Needless to say that recent blog post of Dominic Gilles and release in June of Python 3.7 has finally decided me to give it a try. I wanted to have a focus on making graphics using Matplotlib and even if Dominic Gilles’ post is using Altair I will test it in a second post…

To be honest if, like me, you start from nothing your are at free steps from being able to use Dominic Gilles’ post as you will have to setup your environment first…

If you connect to your server where you have installed Python using a ssh client like Putty you will not been able to display any graphics. This is why IPython has been developed, based on it Jupyter Notebook has emerged to finally produce Jupyter Lab. As they say “IPython itself is focused on interactive Python, part of which is providing a Python kernel for Jupyter”.

This blog post has been written using server edition of Fedora release 28 (Twenty Eight), Fedora has always been known to provide latest packages of Linux world product as well as being one of the most well known Linux…

Goal is to setup Jupyter lab with Python 3.7. Dong this will provide a low footprint virtual machine that you can use to connect to any database server inside your company…

Python 3.7 installation

Installing Fedora 28 (64 bits server release) in VirtualBox is quite straightforward and if you don’t know how to start you will find plenty of article on how to do this on internet. So I assue you have a running Fedora virtual machine.

As expected latest 3.7 Python release is already available on Fedora:

[root@fedora1 ~]# dnf list python37
Last metadata expiration check: 0:26:26 ago on Mon 02 Jul 2018 11:20:14 AM CEST.
Available Packages
python37.i686                                                                                   3.7.0-0.20.rc1.fc28                                                                                  updates
python37.x86_64                                                                                 3.7.0-0.20.rc1.fc28                                                                                  updates

Install it with:

[root@fedora1 ~]# dnf -y install python37.x86_64
Last metadata expiration check: 0:26:38 ago on Mon 02 Jul 2018 11:20:14 AM CEST.
Dependencies resolved.
============================================================================================================================================================================================================
 Package                                                   Arch                                     Version                                                 Repository                                 Size
============================================================================================================================================================================================================
Installing:
 python37                                                  x86_64                                   3.7.0-0.20.rc1.fc28                                     updates                                    20 M
Installing dependencies:
 aajohan-comfortaa-fonts                                   noarch                                   3.001-2.fc28                                            fedora                                    147 k
 dwz                                                       x86_64                                   0.12-7.fc28                                             fedora                                    107 k
 fontconfig                                                x86_64                                   2.13.0-4.fc28                                           updates                                   253 k
 fontpackages-filesystem                                   noarch                                   1.44-21.fc28                                            fedora                                     15 k
 fpc-srpm-macros                                           noarch                                   1.1-4.fc28                                              fedora                                    7.5 k
 ghc-srpm-macros                                           noarch                                   1.4.2-7.fc28                                            fedora                                    8.2 k
 gnat-srpm-macros                                          noarch                                   4-5.fc28                                                fedora                                    8.8 k
 go-srpm-macros                                            noarch                                   2-16.fc28                                               fedora                                     13 k
 libX11                                                    x86_64                                   1.6.5-7.fc28                                            fedora                                    622 k
 libX11-common                                             noarch                                   1.6.5-7.fc28                                            fedora                                    167 k
 libXau                                                    x86_64                                   1.0.8-11.fc28                                           fedora                                     34 k
 libXft                                                    x86_64                                   2.3.2-8.fc28                                            fedora                                     65 k
 libXrender                                                x86_64                                   0.9.10-5.fc28                                           fedora                                     32 k
 libxcb                                                    x86_64                                   1.13-1.fc28                                             fedora                                    228 k
 nim-srpm-macros                                           noarch                                   1-1.fc28                                                fedora                                    7.6 k
 ocaml-srpm-macros                                         noarch                                   5-2.fc27                                                fedora                                    7.8 k
 openblas-srpm-macros                                      noarch                                   2-2.fc27                                                fedora                                    6.6 k
 perl-srpm-macros                                          noarch                                   1-25.fc28                                               fedora                                    9.7 k
 python-srpm-macros                                        noarch                                   3-29.fc28                                               updates                                    11 k
 qt5-srpm-macros                                           noarch                                   5.10.1-1.fc28                                           fedora                                    9.6 k
 redhat-rpm-config                                         noarch                                   108-1.fc28                                              updates                                    77 k
 rust-srpm-macros                                          noarch                                   5-2.fc28                                                fedora                                    8.1 k
 tcl                                                       x86_64                                   1:8.6.8-1.fc28                                          fedora                                    1.1 M
 tk                                                        x86_64                                   1:8.6.8-1.fc28                                          fedora                                    1.6 M
 
Transaction Summary
============================================================================================================================================================================================================
Install  25 Packages
 
Total download size: 24 M
Installed size: 98 M

To specifically execute it you have to specify the complete name:

[root@fedora1 ~]# python3.7
Python 3.7.0rc1 (default, Jun 12 2018, 12:42:02)
[GCC 8.1.1 20180502 (Red Hat 8.1.1-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>

For legacy reason the default Python of Fedora/RedHat is still Python 2.7.5, this is going to change in Fedora 29 but till that time the best way I have seen is to make what they call a virtual environment.

Virtual environment creation is as simple as, and then Python 3.7 is default Python in your virtual environment:

[root@fedora1 ~]# python3.7 -m venv python37_venv
[root@fedora1 ~]# source python37_venv/bin/activate
(python37_venv) [root@fedora1 ~]# python
Python 3.7.0rc1 (default, Jun 12 2018, 12:42:02)
[GCC 8.1.1 20180502 (Red Hat 8.1.1-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>

Jupyter Lab installation

After Python 3.7 has been installed the standard tool to install Python packages is pip. First try failed for an obvious company proxy configuration:

(python37_venv) [root@fedora1 ~]# pip install jupyterlab
Collecting jupyter
  Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError('<pip._vendor.urllib3.connection.VerifiedHTTPSConnection object at 0x7ff03147e470>: Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/jupyter/
  Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError('<pip._vendor.urllib3.connection.VerifiedHTTPSConnection object at 0x7ff03147e400>: Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/jupyter/
  Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError('<pip._vendor.urllib3.connection.VerifiedHTTPSConnection object at 0x7ff03147e3c8>: Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/jupyter/
  Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError('<pip._vendor.urllib3.connection.VerifiedHTTPSConnection object at 0x7ff03147e128>: Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/jupyter/
  Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError('<pip._vendor.urllib3.connection.VerifiedHTTPSConnection object at 0x7ff03147e630>: Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/jupyter/
  Could not find a version that satisfies the requirement jupyter (from versions: )
No matching distribution found for jupyter

Which I solved with a simple:

(python37_venv) [root@fedora1 ~]# export https_proxy='http://proxy_account:proxy_password@proxy_server:proxy_port'
(python37_venv) [root@fedora1 ~]# echo $https_proxy
http://proxy_account:proxy_password@proxy_server:proxy_port

Second try failed for a https certificate verification (due to my company proxy architecture):

(python37_venv) [root@fedora1 ~]# pip install jupyterlab
Collecting jupyter
  Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1045)'))': /simple/jupyter/
  Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1045)'))': /simple/jupyter/
  Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1045)'))': /simple/jupyter/
  Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1045)'))': /simple/jupyter/
  Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1045)'))': /simple/jupyter/
  Could not fetch URL https://pypi.org/simple/jupyter/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/jupyter/ (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1045)'))) - skipping
  Could not find a version that satisfies the requirement jupyter (from versions: )
No matching distribution found for jupyter
Could not fetch URL https://pypi.org/simple/pip/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/pip/ (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1045)'))) - skipping

Which I solved by creating /etc/pip.conf and inserting my list of trusted hosts:/

(python37_venv) [root@fedora1 ~]# cat /etc/pip.conf
[global]
trusted-host = pypi.python.org
               pypi.org
               files.pythonhosted.org

Finally installation of Jupyter Lab wen well this time but launching it with jupyter-lab command failed for:

ImportError: libzmq.so.5: cannot open shared object file: No such file or directory

Which can be solved with:

(python37_venv) [root@fedora1 ~]# dnf -y install zeromq-devel.x86_64

When executing Jupyter Lab as root it failed for:

[C 14:13:49.036 LabApp] Running as root is not recommended. Use --allow-root to bypass.

We know that launching tools as root is never a good idea but if you want to bypass it generate a config file with:

(python37_venv) [root@fedora1 ~]# jupyter-lab --generate-config
Writing default config to: /root/.jupyter/jupyter_notebook_config.py

In generated file I also changed allowable IP to connect to and default listening IP:

c.NotebookApp.allow_origin = '*'
c.NotebookApp.allow_root = True
c.NotebookApp.ip = '192.168.56.105'

Deactivate SELinux (/etc/selinux/config configuration file to modify) and stop/disable firewall:

(python37_venv) [root@fedora1 ~]# systemctl stop firewalld
(python37_venv) [root@fedora1 ~]# systemctl disable firewalld

Run command should now work and you can access Jupyter Lab using provided url of the run command:

jupyter_lab01
jupyter_lab01

Then I wanted to install cx_Oracle Python package to access Oracle database but it failed for:

error: command 'gcc' failed with exit status 1

Which can be solved by installing gcc compiler:

(python37_venv) [root@fedora1 ~]# dnf -y install gcc

I have also installed below Python packaged:

  • cx_Oracle
  • numpy
  • altair
  • ipython-sql

Jupyter Lab testing

The first error I had in Jupyter Lab is:

(cx_Oracle.DatabaseError) DPI-1047: 64-bit Oracle Client library cannot be loaded: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help (Background on this error at: http://sqlalche.me/e/4xp6)
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

Obviously you have to install the Oracle instant client (or a normal client). I have chosen the RPM download and installed oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm RPM. The recommended configuration did not help:

sudo sh -c "echo /usr/lib/oracle/12.2/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf"
sudo ldconfig

So I had to create the symbolic link in Oracle instant client directory:

[root@fedora1 lib]# ln -s libclntsh.so.12.1 libclntsh.so

then I had below error:

(cx_Oracle.DatabaseError) ORA-01017: invalid username/password; logon denied (Background on this error at: http://sqlalche.me/e/4xp6)
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

That I have solved with:

[root@fedora1 ~]# dnf install libnsl.x86_64

Finally the testing proposed by Dominic Gilles finally worked well…

In below the:

alt.data_transformers.enable('default', max_rows=1000000)

is to bypass limitation of 5000 rows per graph…

And finally Dominic Gilles’ example is working well and I have produced my first Python graphic:

import cx_Oracle
import keyring
import pandas as pd
import altair as alt
import getpass
pwd = getpass.getpass('Please enter your password: ')
%load_ext sql
%sql oracle+cx_oracle://account:$pwd@server1.domain.com:1521/sid
%%sql result <<
select table_name, owner, num_rows, blocks, avg_row_len, trunc(last_analyzed)
from all_tables
where num_rows  > 0
and tablespace_name is not null
AND owner NOT IN ('ANONYMOUS','DBSNMP','WMSYS','XDB','APPQOSSYS','GSMADMIN_INTERNAL','GSMCATUSER','SYSBACKUP','OUTLN',
                  'DIP','SYSDG','ORACLE_OCM','OJVMSYS','SYSKM','XS$NULL','GSMUSER','AUDSYS','SYSTEM','SYS')
result_df = result.DataFrame()
result_df.head()
chart1=alt.Chart(result_df).mark_circle().encode(
    x = alt.X('blocks', scale=alt.Scale(type='log')),
    y = alt.Y('num_rows',scale=alt.Scale(type='log')),
    tooltip=['owner','table_name','blocks','num_rows']
).properties(width=800,height=400).interactive()
chart1.save('/tmp/chart1.html')
chart1
jupyter_lab02
jupyter_lab02
jupyter_lab03
jupyter_lab03

If you want to store the password for a common account use keyring, it might not be more secure as if you share the notebook with others the password can be got by everyone very easily. At first try I have got below error:

(python37_venv) [root@fedora1 ~]# keyring set dwhte yjaquier
Password for 'yjaquier' in 'dwhte':
Traceback (most recent call last):
  File "/root/python37_venv/bin/keyring", line 11, in <module>
    sys.exit(main())
  File "/root/python37_venv/lib64/python3.7/site-packages/keyring/cli.py", line 111, in main
    return cli.run(argv)
  File "/root/python37_venv/lib64/python3.7/site-packages/keyring/cli.py", line 72, in run
    set_password(service, username, password)
  File "/root/python37_venv/lib64/python3.7/site-packages/keyring/core.py", line 47, in set_password
    _keyring_backend.set_password(service_name, username, password)
  File "/root/python37_venv/lib64/python3.7/site-packages/keyring/backends/fail.py", line 23, in get_password
    raise RuntimeError(msg)
RuntimeError: No recommended backend was available. Install the keyrings.alt package if you want to use the non-recommended backends. See README.rst for details.

Which I have solved by installing keyrings.alt package:

(python37_venv) [root@fedora1 ~]# pip install keyrings.alt
Collecting keyrings.alt
  Downloading https://files.pythonhosted.org/packages/f7/db/202fe99c9f6d75c7810cb3af7d791479df0dd942f2bac2425646c0ad3db8/keyrings.alt-3.1-py2.py3-none-any.whl
Requirement already satisfied: six in ./python37_venv/lib/python3.7/site-packages (from keyrings.alt) (1.11.0)
Installing collected packages: keyrings.alt
Successfully installed keyrings.alt-3.1

I have then been able to store the password for my personal account:

(python37_venv) [root@fedora1 ~]# keyring set dwhte yjaquier
Password for 'yjaquier' in 'dwhte':

References

Yannick Jaquier on LinkedinYannick Jaquier on RssYannick Jaquier on Twitter
Yannick Jaquier
Find more about me on social media.
This entry was posted in Oracle, Python and tagged . Bookmark the permalink.

One thought on “Jupyter Lab installation on Fedora to access an Oracle database

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>