Oracle via oracleDB¶
Description:
- Work with data in OracleDB
Resources:
- [GitHub] https://oracle.github.io/python-oracledb/
- [Docs] https://python-oracledb.readthedocs.io/en/latest/
- [Tutorial] https://oracle.github.io/python-oracledb/samples/tutorial/Python-and-Oracle-Database-The-New-Wave-of-Scripting.html
Notes:
- These examples use
python-oracledbin Thin mode, which connects directly to Oracle Database without Oracle Client libraries. python-oracledbis the successor to thecx_Oracledriver.
Source and license:
- Some of these examples are adapted from the python-oracledb samples (https://github.com/oracle/python-oracledb/tree/main/samples), Copyright (c) 2016, 2026 Oracle and/or its affiliates, licensed under UPL-1.0 OR Apache-2.0. That code stays under its own license; see THIRD_PARTY_NOTICES.md.
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
ORACLE_HOST: hostname of your Oracle DatabaseORACLE_PORT: optional, defaults to1521ORACLE_USER: database user nameORACLE_PASSWORD: password for that userORACLE_SERVICE_NAME: service name of the database (for exampleFREEPDB1)
Example 1:¶
Connect to a remote oracle database in Thin Mode using oracledb, insert a table and data, and read data from the table
In [ ]:
Copied!
import json
import os
import platform
# import getpass
import oracledb
# userpwd = getpass.getpass("Enter password: ")
userpwd = os.environ["ORACLE_PASSWORD"]
# Create an example Building Object we'll use in our Oracle Database
class Building(object):
def __init__(self, building_id, description, num_floors):
self.building_id = building_id
self.description = description
self.num_floors = num_floors
def __repr__(self):
return "<Building %s: %s>" % (self.building_id, self.description)
def __eq__(self, other):
if isinstance(other, Building):
return other.building_id == self.building_id \
and other.description == self.description \
and other.num_floors == self.num_floors
return NotImplemented
def to_json(self):
return json.dumps(self.__dict__)
@classmethod
def from_json(cls, value):
result = json.loads(value)
return cls(**result)
# Convert a Python building object to SQL JSON type that can be read as a string
def building_in_converter(value):
return value.to_json()
def input_type_handler(cursor, value, num_elements):
if isinstance(value, Building):
return cursor.var(oracledb.STRING, arraysize=num_elements,
inconverter=building_in_converter)
# Instantiate a Building object
building = Building(1, "The First Building", 5) # Python object
# Connect to the Oracle DB, create a table, insert data into the table, and query data from the table
with oracledb.connect(
user=os.environ["ORACLE_USER"],
password=userpwd,
host=os.environ["ORACLE_HOST"],
port=int(os.environ.get("ORACLE_PORT", "1521")),
service_name=os.environ["ORACLE_SERVICE_NAME"],
) as conn:
print("Connected to the db")
with conn.cursor() as cur:
cur.execute(
"""create table BuildingTable (
ID number(9) not null,
BuildingDetails varchar2(400),
constraint TestTempTable_pk primary key (ID))"""
)
cur.execute("truncate table BuildingTable")
cur.inputtypehandler = input_type_handler
cur.execute("insert into BuildingTable (ID, BuildingDetails) values (:1, :2)",
(building.building_id, building))
conn.commit()
print("Querying the row just inserted...")
cur.execute("select ID, BuildingDetails from BuildingTable")
for (int_col, string_col) in cur:
print("Building ID:", int_col)
print("Building Details in JSON format:", string_col)
import json
import os
import platform
# import getpass
import oracledb
# userpwd = getpass.getpass("Enter password: ")
userpwd = os.environ["ORACLE_PASSWORD"]
# Create an example Building Object we'll use in our Oracle Database
class Building(object):
def __init__(self, building_id, description, num_floors):
self.building_id = building_id
self.description = description
self.num_floors = num_floors
def __repr__(self):
return "<Building %s: %s>" % (self.building_id, self.description)
def __eq__(self, other):
if isinstance(other, Building):
return other.building_id == self.building_id \
and other.description == self.description \
and other.num_floors == self.num_floors
return NotImplemented
def to_json(self):
return json.dumps(self.__dict__)
@classmethod
def from_json(cls, value):
result = json.loads(value)
return cls(**result)
# Convert a Python building object to SQL JSON type that can be read as a string
def building_in_converter(value):
return value.to_json()
def input_type_handler(cursor, value, num_elements):
if isinstance(value, Building):
return cursor.var(oracledb.STRING, arraysize=num_elements,
inconverter=building_in_converter)
# Instantiate a Building object
building = Building(1, "The First Building", 5) # Python object
# Connect to the Oracle DB, create a table, insert data into the table, and query data from the table
with oracledb.connect(
user=os.environ["ORACLE_USER"],
password=userpwd,
host=os.environ["ORACLE_HOST"],
port=int(os.environ.get("ORACLE_PORT", "1521")),
service_name=os.environ["ORACLE_SERVICE_NAME"],
) as conn:
print("Connected to the db")
with conn.cursor() as cur:
cur.execute(
"""create table BuildingTable (
ID number(9) not null,
BuildingDetails varchar2(400),
constraint TestTempTable_pk primary key (ID))"""
)
cur.execute("truncate table BuildingTable")
cur.inputtypehandler = input_type_handler
cur.execute("insert into BuildingTable (ID, BuildingDetails) values (:1, :2)",
(building.building_id, building))
conn.commit()
print("Querying the row just inserted...")
cur.execute("select ID, BuildingDetails from BuildingTable")
for (int_col, string_col) in cur:
print("Building ID:", int_col)
print("Building Details in JSON format:", string_col)