Skip to content

Commit 1914332

Browse files
cjbjcjbj
authored
Update examples for python-oracledb 2.3 (oracle-samples#378)
Signed-off-by: Christopher Jones <christopher.jones@oracle.com>
1 parent 525d7fc commit 1914332

File tree

8 files changed

+398
-7
lines changed

8 files changed

+398
-7
lines changed

‎python/python-oracledb/json_duality.py

Lines changed: 125 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,125 @@
1+
# -----------------------------------------------------------------------------
2+
# Copyright (c) 2024, Oracle and/or its affiliates.
3+
#
4+
# This software is dual-licensed to you under the Universal Permissive License
5+
# (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
6+
# 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
7+
# either license.
8+
#
9+
# If you elect to accept the software under the Apache License, Version 2.0,
10+
# the following applies:
11+
#
12+
# Licensed under the Apache License, Version 2.0 (the "License");
13+
# you may not use this file except in compliance with the License.
14+
# You may obtain a copy of the License at
15+
#
16+
# https://www.apache.org/licenses/LICENSE-2.0
17+
#
18+
# Unless required by applicable law or agreed to in writing, software
19+
# distributed under the License is distributed on an "AS IS" BASIS,
20+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
21+
# See the License for the specific language governing permissions and
22+
# limitations under the License.
23+
# -----------------------------------------------------------------------------
24+
25+
# -----------------------------------------------------------------------------
26+
# json_duality.py
27+
#
28+
# Demonstrates Oracle Database 23ai JSON-Relational Duality Views.
29+
#
30+
# Also see soda_json_duality.py
31+
#
32+
# Reference: https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADJSN
33+
# -----------------------------------------------------------------------------
34+
35+
import json
36+
import sys
37+
38+
import oracledb
39+
import sample_env
40+
41+
# determine whether to use python-oracledb thin mode or thick mode
42+
if not sample_env.get_is_thin():
43+
oracledb.init_oracle_client(lib_dir=sample_env.get_oracle_client())
44+
45+
connection = oracledb.connect(
46+
user=sample_env.get_main_user(),
47+
password=sample_env.get_main_password(),
48+
dsn=sample_env.get_connect_string(),
49+
)
50+
51+
if not connection.thin:
52+
client_version = oracledb.clientversion()[0]
53+
db_version = int(connection.version.split(".")[0])
54+
55+
# this script only works with Oracle Database 23ai
56+
if db_version < 23:
57+
sys.exit("This example requires Oracle Database 23 or later. ")
58+
59+
with connection.cursor() as cursor:
60+
61+
# Create a JSON-Relational Duality View over the SampleJRDVAuthorTab and
62+
# SampleJRDVBookTab tables
63+
sql = """
64+
create or replace json relational duality view BookDV as
65+
SampleJRDVBookTab @insert @update @delete
66+
{
67+
_id: BookId,
68+
book_title: BookTitle,
69+
author: SampleJRDVAuthorTab @insert @update
70+
{
71+
author_id: AuthorId,
72+
author_name: AuthorName
73+
}
74+
}"""
75+
cursor.execute(sql)
76+
77+
with connection.cursor() as cursor:
78+
79+
# Insert a new book and author into the Duality View and show the resulting
80+
# new records in the relational tables
81+
data = dict(
82+
_id=101,
83+
book_title="Cooking at Home",
84+
author=dict(author_id=201, author_name="Dave Smith"),
85+
)
86+
inssql = "insert into BookDV values (:1)"
87+
if connection.thin or client_version >= 21:
88+
# Take advantage of direct binding
89+
cursor.setinputsizes(oracledb.DB_TYPE_JSON)
90+
cursor.execute(inssql, [data])
91+
else:
92+
# Insert the data as a JSON string
93+
cursor.execute(inssql, [json.dumps(data)])
94+
95+
print("Authors in the relational table:")
96+
for row in cursor.execute(
97+
"select * from SampleJRDVAuthorTab order by AuthorId"
98+
):
99+
print(row)
100+
101+
print("\nBooks in the relational table:")
102+
for row in cursor.execute(
103+
"select * from SampleJRDVBookTab order by BookId"
104+
):
105+
print(row)
106+
107+
# Select from the duality view
108+
109+
with connection.cursor() as cursor:
110+
111+
print("\nDuality view query for an author's books:")
112+
sql = """select b.data.book_title, b.data.author.author_name
113+
from BookDV b
114+
where b.data.author.author_id = :1"""
115+
for r in cursor.execute(sql, [1]):
116+
print(r)
117+
118+
print("\nDuality view query of all records:")
119+
sql = """select data from BookDV"""
120+
if connection.thin or client_version >= 21:
121+
for (j,) in cursor.execute(sql):
122+
print(j)
123+
else:
124+
for (j,) in cursor.execute(sql):
125+
print(json.loads(j.read()))

‎python/python-oracledb/oracledb_upgrade.py

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -133,7 +133,7 @@
133133
# set lib_dir; instead set LD_LIBRARY_PATH or configure ldconfig before running
134134
# Python.
135135
lib_dir = None
136-
if platform.system() == "Darwin" and platform.machine() == "x86_64":
136+
if platform.system() == "Darwin":
137137
lib_dir = os.environ.get("HOME") + "/Downloads/instantclient_19_8"
138138
elif platform.system() == "Windows":
139139
lib_dir = r"C:\oracle\instantclient_19_14"

‎python/python-oracledb/plsql_rowtype.py

Lines changed: 112 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,112 @@
1+
# -----------------------------------------------------------------------------
2+
# Copyright (c) 2024, Oracle and/or its affiliates.
3+
#
4+
# This software is dual-licensed to you under the Universal Permissive License
5+
# (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
6+
# 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
7+
# either license.
8+
#
9+
# If you elect to accept the software under the Apache License, Version 2.0,
10+
# the following applies:
11+
#
12+
# Licensed under the Apache License, Version 2.0 (the "License");
13+
# you may not use this file except in compliance with the License.
14+
# You may obtain a copy of the License at
15+
#
16+
# https://www.apache.org/licenses/LICENSE-2.0
17+
#
18+
# Unless required by applicable law or agreed to in writing, software
19+
# distributed under the License is distributed on an "AS IS" BASIS,
20+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
21+
# See the License for the specific language governing permissions and
22+
# limitations under the License.
23+
# -----------------------------------------------------------------------------
24+
25+
# -----------------------------------------------------------------------------
26+
# plsql_rowtype.py
27+
#
28+
# Demonstrates how to use a PL/SQL %ROWTYPE attribute
29+
# -----------------------------------------------------------------------------
30+
31+
import oracledb
32+
import sample_env
33+
34+
# determine whether to use python-oracledb thin mode or thick mode
35+
if not sample_env.get_is_thin():
36+
oracledb.init_oracle_client(lib_dir=sample_env.get_oracle_client())
37+
38+
connection = oracledb.connect(
39+
user=sample_env.get_main_user(),
40+
password=sample_env.get_main_password(),
41+
dsn=sample_env.get_connect_string(),
42+
)
43+
44+
with connection.cursor() as cursor:
45+
46+
cursor.execute(
47+
"""
48+
begin
49+
begin
50+
execute immediate 'drop table RTSampleTable';
51+
exception
52+
when others then
53+
if sqlcode <> -942 then
54+
raise;
55+
end if;
56+
end;
57+
58+
execute immediate 'create table RTSampleTable (
59+
city varchar2(10))';
60+
61+
execute immediate
62+
'insert into RTSampleTable values (''London'')';
63+
64+
commit;
65+
66+
end;
67+
"""
68+
)
69+
70+
cursor.execute(
71+
"""
72+
create or replace function TestFuncOUT return RTSampleTable%rowtype as
73+
r RTSampleTable%rowtype;
74+
begin
75+
select * into r from RTSampleTable where rownum < 2 order by 1;
76+
return r;
77+
end;"""
78+
)
79+
if cursor.warning:
80+
print(cursor.warning)
81+
82+
cursor.execute(
83+
"""
84+
create or replace procedure TestProcIN(
85+
r in RTSampleTable%rowtype, city out varchar2) as
86+
begin
87+
city := r.city;
88+
end;"""
89+
)
90+
if cursor.warning:
91+
print(cursor.warning)
92+
93+
# Getting a %ROWTYPE from PL/SQL returns a python-oracledb DbObject record
94+
95+
rt = connection.gettype("RTSAMPLETABLE%ROWTYPE")
96+
r = cursor.callfunc("TESTFUNCOUT", rt)
97+
print("1. City is:", r.CITY)
98+
# dump_object(r) # This is defined in object_dump.py
99+
100+
# An equivalent record can be directly constructed
101+
102+
obj = rt.newobject()
103+
obj.CITY = "My Town"
104+
105+
# Passing a record to a %ROWTYPE parameter
106+
107+
c = cursor.var(oracledb.DB_TYPE_VARCHAR)
108+
cursor.callproc("TESTPROCIN", [r, c])
109+
print("2. City is:", c.getvalue())
110+
111+
cursor.callproc("TESTPROCIN", [obj, c])
112+
print("3. City is:", c.getvalue())

‎python/python-oracledb/sample_env.py

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -209,9 +209,7 @@ def get_admin_connection():
209209

210210

211211
def get_oracle_client():
212-
if (
213-
platform.system() == "Darwin" and platform.machine() == "x86_64"
214-
) or platform.system() == "Windows":
212+
if platform.system() == "Darwin" or platform.system() == "Windows":
215213
return get_value(
216214
"PYO_SAMPLES_ORACLE_CLIENT_PATH", "Oracle Instant Client Path"
217215
)

‎python/python-oracledb/soda_basic.py

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# -----------------------------------------------------------------------------
2-
# Copyright (c) 2018, 2023, Oracle and/or its affiliates.
2+
# Copyright (c) 2018, 2024, Oracle and/or its affiliates.
33
#
44
# This software is dual-licensed to you under the Universal Permissive License
55
# (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
@@ -85,7 +85,7 @@
8585

8686
# Fetch the document back
8787
doc = collection.find().key(key).getOne() # A SodaDocument
88-
content = doc.getContent() # A JavaScript object
88+
content = doc.getContent()
8989
print("Retrieved SODA document dictionary is:")
9090
print(content)
9191
content = doc.getContentAsString() # A JSON string

‎python/python-oracledb/soda_json_duality.py

Lines changed: 117 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,117 @@
1+
# -----------------------------------------------------------------------------
2+
# Copyright (c) 2024, Oracle and/or its affiliates.
3+
#
4+
# This software is dual-licensed to you under the Universal Permissive License
5+
# (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
6+
# 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
7+
# either license.
8+
#
9+
# If you elect to accept the software under the Apache License, Version 2.0,
10+
# the following applies:
11+
#
12+
# Licensed under the Apache License, Version 2.0 (the "License");
13+
# you may not use this file except in compliance with the License.
14+
# You may obtain a copy of the License at
15+
#
16+
# https://www.apache.org/licenses/LICENSE-2.0
17+
#
18+
# Unless required by applicable law or agreed to in writing, software
19+
# distributed under the License is distributed on an "AS IS" BASIS,
20+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
21+
# See the License for the specific language governing permissions and
22+
# limitations under the License.
23+
# -----------------------------------------------------------------------------
24+
25+
# -----------------------------------------------------------------------------
26+
# soda_json_duality.py
27+
#
28+
# An example of accessing Oracle Database 23ai JSON-Relational views using
29+
# Simple Oracle Document Access (SODA).
30+
#
31+
# Oracle Client must be at 23.4 or higher.
32+
# Oracle Database must be at 23.4 or higher.
33+
# The user must have been granted the SODA_APP privilege.
34+
#
35+
# Also see json_duality.py
36+
# -----------------------------------------------------------------------------
37+
38+
import sys
39+
import oracledb
40+
import sample_env
41+
42+
# this script is currently only supported in python-oracledb thick mode
43+
oracledb.init_oracle_client(lib_dir=sample_env.get_oracle_client())
44+
45+
# this script only works with Oracle Database & Client 23.4 or later
46+
if sample_env.get_server_version() < (23, 4):
47+
sys.exit("This example requires Oracle Database 23.4 or later.")
48+
if oracledb.clientversion()[:2] < (23, 4):
49+
sys.exit("This example requires Oracle Client 23.4 or later")
50+
51+
connection = oracledb.connect(
52+
user=sample_env.get_main_user(),
53+
password=sample_env.get_main_password(),
54+
dsn=sample_env.get_connect_string(),
55+
)
56+
57+
with connection.cursor() as cursor:
58+
59+
# Create a JSON-Relational Duality View over the SampleJRDVAuthorTab and
60+
# SampleJRDVBookTab tables
61+
sql = """
62+
create or replace json relational duality view BookDV as
63+
SampleJRDVBookTab @insert @update @delete
64+
{
65+
_id: BookId,
66+
book_title: BookTitle,
67+
author: SampleJRDVAuthorTab @insert @update
68+
{
69+
author_id: AuthorId,
70+
author_name: AuthorName
71+
}
72+
}"""
73+
cursor.execute(sql)
74+
75+
# Create the parent object for SODA
76+
soda = connection.getSodaDatabase()
77+
78+
# The duality view can be opened as if it were a collection
79+
collection = soda.openCollection("BOOKDV")
80+
81+
# Count all documents
82+
c = collection.find().count()
83+
print("Collection has", c, "documents")
84+
85+
# Perform a query-by-example on the duality view
86+
print("Books starting with 'The':")
87+
qbe = {"book_title": {"$like": "The%"}}
88+
for doc in collection.find().filter(qbe).getDocuments():
89+
content = doc.getContent()
90+
print(content["book_title"])
91+
92+
# Insert a document
93+
content = {
94+
"_id": 201,
95+
"book_title": "Rainbows and Unicorns",
96+
"author": {"author_id": 401, "author_name": "Merlin"},
97+
}
98+
doc = collection.insertOneAndGet(content)
99+
key = doc.key
100+
101+
# Fetch the document back and print the title
102+
doc = collection.find().key(key).getOne()
103+
content = doc.getContent()
104+
print("Retrieved SODA document title is:")
105+
print(content["book_title"])
106+
107+
# The new book can also be queried relationally from the base tables
108+
print("Relational query:")
109+
with connection.cursor() as cursor:
110+
sql = """
111+
select AuthorName, BookTitle
112+
from SampleJRDVAuthorTab, SampleJRDVBookTab
113+
where SampleJRDVAuthorTab.AuthorName = 'Merlin'
114+
and SampleJRDVAuthorTab.AuthorId = SampleJRDVBookTab.AuthorId"""
115+
116+
for r in cursor.execute(sql):
117+
print(r)

0 commit comments

Comments
 (0)