Setup Oracle client and LD_LIBRARY_PATH

download from https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
and insall locally
    
test connection using sqlplus

# cd /home/luca/oracle_client/instantclient_19_10 
cd <oracle_client_home_path> # edit as neeeded
export LD_LIBRARY_PATH=$PWD
./sqlplus scott/tiger@//localhost:1521/XEPDB1


restart jupyter in order to set

export LD_LIBRARY_PATH=<oracle_client_home_path> # edit path as neeeded

"""
Explanation: SQL Magic extension for Oracle and IPython

An example of how to query Oracle from IPython using SQL magic extensions
This is a straighforward interface for running SQL
The results integrate easily into the python environment and with pandas
Dependencies: needs an Oracle client installation, cx_Oracle and ipython-sql

Setup and prerequisites
End of explanation
"""


# the following requires ipython-sql, install if neeeded using
# ! pip install ipython-sql

# loads the SQL magic extensions
%load_ext sql

# Connect to Oracle
# %sql oracle+cx_oracle://scott:tiger@dbserver:1521/?service_name=orcl.mydomain.com

%sql oracle+cx_oracle://scott:tiger@localhost:1521?service_name=XEPDB1

%%sql 
select * from emp

"""
Explanation: Connect to Oracle and run a query using %SQL magic extensions

SQL magic extensions introduce the %%sql cell magic and %sql line magic for running SQL in IPython
The connect string to the database uses sql alchemy syntax
End of explanation
"""


Employee_name="SCOTT"

%sql select * from emp where ename=:Employee_name

%sql update emp set sal=3500 where ename=:Employee_name
%sql commit
%sql select * from emp where ename=:Employee_name

"""
Explanation: Bind variables and DML
End of explanation
"""


myResultSet=%sql select ename "Employee Name", sal "Salary" from emp

import matplotlib.pyplot as plt 
plt.style.use('seaborn-darkgrid')

myResultSet.bar();

"""
Explanation: Additional example of the integration with the IPython environment
End of explanation
"""


%%sql
select e1.ename "Employee Name", e1.job "Job", e2.ename "Manager Name" 
from emp e1, emp e2
where e1.mgr = e2.empno(+)

# save result set into my_ResultSet and copy it to pandas in my_DataFrame
my_ResultSet = _

my_DataFrame=my_ResultSet.DataFrame()

my_DataFrame.head()

"""
Explanation: Integration with Pandas
This opens many additional possibilities for data analysis
End of explanation
"""


