1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
#------------------------------------------------------------------------------
# Copyright (c) 2020, 2021, Oracle and/or its affiliates. All rights reserved.
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# json_direct.py
# Shows some JSON features of Oracle Database 21c.
# See https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADJSN
#
# For JSON with older databases see json_blob.py
#------------------------------------------------------------------------------
import json
import sys
import cx_Oracle as oracledb
import sample_env
connection = oracledb.connect(sample_env.get_main_connect_string())
client_version = oracledb.clientversion()[0]
db_version = int(connection.version.split(".")[0])
# this script only works with Oracle Database 21
if db_version < 21:
sys.exit("This example requires Oracle Database 21.1 or later. "
"Try json_blob.py")
# Create a table
cursor = connection.cursor()
cursor.execute("""
begin
execute immediate 'drop table customers';
exception when others then
if sqlcode <> -942 then
raise;
end if;
end;""")
cursor.execute("""
create table customers (
id integer not null primary key,
json_data json
)""")
# Insert JSON data
data = dict(name="Rod", dept="Sales", location="Germany")
inssql = "insert into customers values (:1, :2)"
if client_version >= 21:
# Take advantage of direct binding
cursor.setinputsizes(None, oracledb.DB_TYPE_JSON)
cursor.execute(inssql, [1, data])
else:
# Insert the data as a JSON string
cursor.execute(inssql, [1, json.dumps(data)])
# Select JSON data
sql = "SELECT c.json_data FROM customers c"
if client_version >= 21:
for j, in cursor.execute(sql):
print(j)
else:
for j, in cursor.execute(sql):
print(json.loads(j.read()))
# Using JSON_VALUE to extract a value from a JSON column
sql = """SELECT JSON_VALUE(json_data, '$.location')
FROM customers
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY"""
for r in cursor.execute(sql):
print(r)
# Using dot-notation to extract a value from a JSON column
sql = """SELECT c.json_data.location
FROM customers c
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY"""
if client_version >= 21:
for j, in cursor.execute(sql):
print(j)
else:
for j, in cursor.execute(sql):
print(json.loads(j.read()))
# Using JSON_OBJECT to extract relational data as JSON
sql = """SELECT JSON_OBJECT('key' IS d.dummy) dummy
FROM dual d"""
for r in cursor.execute(sql):
print(r)
|
