Skip to content

Commit 1c1bb32

Browse files
cjbjgvenzl
cjbj
and
gvenzl
authored
Update python-oracledb examples for Oracle Database 23ai (oracle-samples#350)
Signed-off-by: Christopher Jones <christopher.jones@oracle.com> Co-authored-by: Gerald Venzl <gerald.venzl@oracle.com>
1 parent 150e7fc commit 1c1bb32

File tree

8 files changed

+415
-8
lines changed

8 files changed

+415
-8
lines changed

‎python/python-oracledb/create_schema.py

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,4 +54,8 @@
5454
sample_env.run_sql_script(
5555
conn, "create_schema_21", main_user=sample_env.get_main_user()
5656
)
57+
if sample_env.get_server_version() >= (23, 4):
58+
sample_env.run_sql_script(
59+
conn, "create_schema_23", main_user=sample_env.get_main_user()
60+
)
5761
print("Done.")

‎python/python-oracledb/sample_container/Dockerfile

Lines changed: 30 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,35 @@
1+
# -----------------------------------------------------------------------------
2+
# Copyright (c) 2022, 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+
#
125
# NAME
226
#
327
# Dockerfile
428
#
529
# PURPOSE
630
#
7-
# Creates a container with the Python python-oracledb samples and a running
8-
# Oracle Database so python-oracledb can be evaluated.
31+
# Creates a container with the python-oracledb samples and a running Oracle
32+
# Database 23ai Free database so you can quickly try python-oracledb.
933
#
1034
# Python-oracledb is the Python database driver for Oracle Database. See
1135
# https://oracle.github.io/python-oracledb/
@@ -36,7 +60,10 @@
3660
#
3761
# python setup.py
3862
#
39-
# Run samples like:
63+
# If this times out, wait a few minutes for the database to finish
64+
# initializing and then rerun it.
65+
#
66+
# In the container, run samples like:
4067
#
4168
# python bind_insert.py
4269
#

‎python/python-oracledb/sample_container/README.md

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,11 @@
11
# python-oracledb Samples in a Container
22

3-
This Dockerfile creates a container with python-oracledb samples and a running
4-
Oracle Database.
3+
This Dockerfile creates a container with the python-oracledb samples and a
4+
running Oracle Database 23ai Free database so you can quickly try
5+
python-oracledb.
56

6-
It has been tested in an Oracle Linux 8 environment using 'podman', but
7-
'docker' should work too.
7+
It has been tested on Oracle Linux 8 using 'podman', and on Apple Silicon with
8+
'docker' under colima.
89

910
## Usage
1011

@@ -43,6 +44,9 @@ It has been tested in an Oracle Linux 8 environment using 'podman', but
4344
python setup.py
4445
```
4546

47+
If this times out, wait a few minutes for the database to finish initializing
48+
and then rerun it.
49+
4650
The schema used can be seen in `sql/create_schema.sql`
4751

4852
- In the container, run samples like:

‎python/python-oracledb/sample_container/setup.py

Lines changed: 28 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,29 @@
11
#! /usr/bin/env python3.9
2+
3+
# -----------------------------------------------------------------------------
4+
# Copyright (c) 2022, 2024, Oracle and/or its affiliates.
5+
#
6+
# This software is dual-licensed to you under the Universal Permissive License
7+
# (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
8+
# 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
9+
# either license.
10+
#
11+
# If you elect to accept the software under the Apache License, Version 2.0,
12+
# the following applies:
13+
#
14+
# Licensed under the Apache License, Version 2.0 (the "License");
15+
# you may not use this file except in compliance with the License.
16+
# You may obtain a copy of the License at
217
#
18+
# https://www.apache.org/licenses/LICENSE-2.0
19+
#
20+
# Unless required by applicable law or agreed to in writing, software
21+
# distributed under the License is distributed on an "AS IS" BASIS,
22+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
23+
# See the License for the specific language governing permissions and
24+
# limitations under the License.
25+
# -----------------------------------------------------------------------------
26+
327
# NAME
428
#
529
# setup.py
@@ -9,9 +33,12 @@
933
# Creates the python-oracledb sample schema after waiting for the database to
1034
# open.
1135
#
36+
# If this times out, wait a few minutes for the database to finish
37+
# initializing and then rerun it.
38+
#
1239
# USAGE
1340
#
14-
# ./setup.py
41+
# python setup.py
1542

1643
import oracledb
1744
import os

‎python/python-oracledb/sql/create_schema_23.sql

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
/*-----------------------------------------------------------------------------
2+
* Copyright 2023, 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+
* create_schema_23.sql
27+
*
28+
* Performs the actual work of creating and populating the schemas with the
29+
* database objects used by the python-oracledb samples that require Oracle
30+
* Database 23.4 or higher. It is executed by the Python script
31+
* create_schema.py.
32+
*---------------------------------------------------------------------------*/
33+
34+
create table &main_user..SampleVectorTab (
35+
v32 vector(3, float32),
36+
v64 vector(3, float64),
37+
v8 vector(3, int8)
38+
)
39+
/

‎python/python-oracledb/vector.py

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
# -----------------------------------------------------------------------------
2+
# Copyright (c) 2023, 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+
# vector.py
27+
#
28+
# Demonstrates how to use the Oracle Database 23ai VECTOR data type.
29+
# -----------------------------------------------------------------------------
30+
31+
import array
32+
import sys
33+
34+
import oracledb
35+
import sample_env
36+
37+
# determine whether to use python-oracledb thin mode or thick mode
38+
if not sample_env.get_is_thin():
39+
oracledb.init_oracle_client(lib_dir=sample_env.get_oracle_client())
40+
41+
connection = oracledb.connect(
42+
user=sample_env.get_main_user(),
43+
password=sample_env.get_main_password(),
44+
dsn=sample_env.get_connect_string(),
45+
)
46+
47+
# this script only works with Oracle Database 23.4 or later
48+
if sample_env.get_server_version() < (23, 4):
49+
sys.exit("This example requires Oracle Database 23.4 or later.")
50+
51+
# this script works with thin mode, or with thick mode using Oracle Client 23.4
52+
# or later
53+
if not connection.thin and oracledb.clientversion()[:2] < (23, 4):
54+
sys.exit(
55+
"This example requires python-oracledb thin mode, or Oracle Client"
56+
" 23.4 or later"
57+
)
58+
59+
with connection.cursor() as cursor:
60+
# Single-row insert
61+
vector1_data_32 = array.array("f", [1.625, 1.5, 1.0])
62+
vector1_data_64 = array.array("d", [11.25, 11.75, 11.5])
63+
vector1_data_8 = array.array("b", [1, 2, 3])
64+
65+
cursor.execute(
66+
"insert into SampleVectorTab (v32, v64, v8) values (:1, :2, :3)",
67+
[vector1_data_32, vector1_data_64, vector1_data_8],
68+
)
69+
70+
# Multi-row insert
71+
vector2_data_32 = array.array("f", [2.625, 2.5, 2.0])
72+
vector2_data_64 = array.array("d", [22.25, 22.75, 22.5])
73+
vector2_data_8 = array.array("b", [4, 5, 6])
74+
75+
vector3_data_32 = array.array("f", [3.625, 3.5, 3.0])
76+
vector3_data_64 = array.array("d", [33.25, 33.75, 33.5])
77+
vector3_data_8 = array.array("b", [7, 8, 9])
78+
79+
rows = [
80+
(vector2_data_32, vector2_data_64, vector2_data_8),
81+
(vector3_data_32, vector3_data_64, vector3_data_8),
82+
]
83+
84+
cursor.executemany(
85+
"insert into SampleVectorTab (v32, v64, v8) values (:1, :2, :3)",
86+
rows,
87+
)
88+
89+
# Query
90+
cursor.execute("select * from SampleVectorTab")
91+
92+
# Each vector is represented as an array.array type
93+
for row in cursor:
94+
print(row)

‎python/python-oracledb/vector_numpy.py

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,123 @@
1+
# -----------------------------------------------------------------------------
2+
# Copyright (c) 2023, 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+
# vector_numpy.py
27+
#
28+
# Demonstrates how to use the Oracle Database 23ai VECTOR data type with NumPy
29+
# types.
30+
# -----------------------------------------------------------------------------
31+
32+
import sys
33+
import array
34+
import numpy
35+
import oracledb
36+
import sample_env
37+
38+
# determine whether to use python-oracledb thin mode or thick mode
39+
if not sample_env.get_is_thin():
40+
oracledb.init_oracle_client(lib_dir=sample_env.get_oracle_client())
41+
42+
connection = oracledb.connect(
43+
user=sample_env.get_main_user(),
44+
password=sample_env.get_main_password(),
45+
dsn=sample_env.get_connect_string(),
46+
)
47+
48+
# this script only works with Oracle Database 23.4 or later
49+
if sample_env.get_server_version() < (23, 4):
50+
sys.exit("This example requires Oracle Database 23.4 or later.")
51+
52+
# this script works with thin mode, or with thick mode using Oracle Client 23.4
53+
# or later
54+
if not connection.thin and oracledb.clientversion()[:2] < (23, 4):
55+
sys.exit(
56+
"This example requires python-oracledb thin mode, or Oracle Client"
57+
" 23.4 or later"
58+
)
59+
60+
61+
# Convert from NumPy ndarray types to array types when inserting vectors
62+
def numpy_converter_in(value):
63+
if value.dtype == numpy.float64:
64+
dtype = "d"
65+
elif value.dtype == numpy.float32:
66+
dtype = "f"
67+
else:
68+
dtype = "b"
69+
return array.array(dtype, value)
70+
71+
72+
def input_type_handler(cursor, value, arraysize):
73+
if isinstance(value, numpy.ndarray):
74+
return cursor.var(
75+
oracledb.DB_TYPE_VECTOR,
76+
arraysize=arraysize,
77+
inconverter=numpy_converter_in,
78+
)
79+
80+
81+
connection.inputtypehandler = input_type_handler
82+
83+
84+
# Convert from array types to NumPy ndarray types when fetching vectors
85+
def numpy_converter_out(value):
86+
if value.typecode == "b":
87+
dtype = numpy.int8
88+
elif value.typecode == "f":
89+
dtype = numpy.float32
90+
else:
91+
dtype = numpy.float64
92+
return numpy.array(value, copy=False, dtype=dtype)
93+
94+
95+
def output_type_handler(cursor, metadata):
96+
if metadata.type_code is oracledb.DB_TYPE_VECTOR:
97+
return cursor.var(
98+
metadata.type_code,
99+
arraysize=cursor.arraysize,
100+
outconverter=numpy_converter_out,
101+
)
102+
103+
104+
connection.outputtypehandler = output_type_handler
105+
106+
107+
with connection.cursor() as cursor:
108+
# Insert
109+
vector_data_32 = numpy.array([1.625, 1.5, 1.0])
110+
vector_data_64 = numpy.array([11.25, 11.75, 11.5])
111+
vector_data_8 = numpy.array([1, 2, 3])
112+
113+
cursor.execute(
114+
"insert into SampleVectorTab (v32, v64, v8) values (:1, :2, :3)",
115+
[vector_data_32, vector_data_64, vector_data_8],
116+
)
117+
118+
# Query
119+
cursor.execute("select * from SampleVectorTab")
120+
121+
# Each vector is represented as a numpy.ndarray type
122+
for row in cursor:
123+
print(row)

0 commit comments

Comments
 (0)