Oracle Database#
This tutorial will show you how to get an Oracle instance up and running locally to test JupySQL. You can run this in a Jupyter notebook.
Pre-requisites#
To run this tutorial, you need to install following Python packages:
%pip install oracledb --quiet
Note: you may need to restart the kernel to use updated packages.
Start Oracle instance#
We use the non-official image gvenzl/oracle-xe to initial the instance, and database users (this will take 1-2 minutes):
%%bash
docker run --name oracle \
-e ORACLE_PASSWORD=ploomber_app_admin_password \
-e APP_USER=ploomber_app \
-e APP_USER_PASSWORD=ploomber_app_password \
-p 1521:1521 -d gvenzl/oracle-xe
cc531fdc8802c40aa666a8b3eb52debda71fc0e64bc00ef956da22314dc9b971
Our database is running, let’s load some data!
Load sample data#
Now, let’s fetch some sample data. We’ll be using the iris.csv:
import pandas as pd
df = pd.read_csv(
"https://github.com/Muhd-Shahid/Write-Raw-File-into-Database-Server/raw/main/iris.csv", # noqa: E501
index_col=False,
)
df.shape
(150, 5)
from sqlalchemy import create_engine
from sqlalchemy.types import Float
engine = create_engine(
"oracle+oracledb://ploomber_app:ploomber_app_password@localhost:1521/?service_name=XEPDB1" # noqa: E501
)
df.to_sql(
name="iris",
con=engine,
chunksize=1000,
if_exists="replace",
index=False,
dtype={
"sepal_length": Float(),
"sepal_width": Float(),
"petal_length": Float(),
"petal_width": Float(),
},
)
engine.dispose()
Query#
Now, let’s start JupySQL, authenticate, and start querying the data!
%load_ext sql
%sql oracle+oracledb://ploomber_app:ploomber_app_password@localhost:1521/?service_name=XEPDB1
List the tables in the database:
%sqlcmd tables
Name |
---|
iris |
Query some data in iris table
%sql SELECT * FROM iris FETCH NEXT 5 ROWS ONLY
* oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=XEPDB1
0 rows affected.
sepal_length | sepal_width | petal_length | petal_width | species |
---|---|---|---|---|
6.7 | 2.5 | 5.8 | 1.8 | virginica |
7.2 | 3.6 | 6.1 | 2.5 | virginica |
6.5 | 3.2 | 5.1 | 2.0 | virginica |
6.4 | 2.7 | 5.3 | 1.9 | virginica |
6.8 | 3.0 | 5.5 | 2.1 | virginica |
Query our data:
%%sql
SELECT COUNT(*) FROM iris
* oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=XEPDB1
0 rows affected.
COUNT(*) |
---|
150 |
Parametrize queries#
threshold = 5.0
%%sql
SELECT COUNT(*) FROM iris
WHERE sepal_length < {{threshold}}
* oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=XEPDB1
0 rows affected.
COUNT(*) |
---|
22 |
CTEs#
%%sql --save saved_cte --no-execute
SELECT * FROM iris
WHERE sepal_length > 6.0
* oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=XEPDB1
Skipping execution...
%sql --with saved_cte SELECT * FROM saved_cte FETCH NEXT 5 ROWS ONLY
* oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=XEPDB1
0 rows affected.
sepal_length | sepal_width | petal_length | petal_width | species |
---|---|---|---|---|
6.7 | 2.5 | 5.8 | 1.8 | virginica |
7.2 | 3.6 | 6.1 | 2.5 | virginica |
6.5 | 3.2 | 5.1 | 2.0 | virginica |
6.4 | 2.7 | 5.3 | 1.9 | virginica |
6.8 | 3.0 | 5.5 | 2.1 | virginica |
This is what JupySQL executes:
query = %sqlrender saved_cte
print(query)
WITH
SELECT * FROM iris
WHERE sepal_length > 6.0
Clean up#
To stop and remove the container:
! docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cc531fdc8802 gvenzl/oracle-xe "container-entrypoin…" 7 minutes ago Up 7 minutes 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp oracle
%%capture out
! docker container ls --filter name=oracle --quiet
container_id = out.stdout.strip()
print(f"Container id: {container_id}")
Container id: cc531fdc8802
Remove the container
! docker container stop {container_id}
! docker container rm {container_id}
cc531fdc8802
cc531fdc8802
! docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES