Skip to content

Commit d3a85fb

Browse files
zzzeekGerrit Code Review
zzzeek
authored and
Gerrit Code Review
committed
Merge "Add JSON type support for Oracle dialect" into main
2 parents bda3c30 + 7d3b4f9 commit d3a85fb

21 files changed

Lines changed: 595 additions & 45 deletions

File tree

‎doc/build/changelog/unreleased_21/10375.rst‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
.. change::
2+
:tags: feature, oracle
3+
:tickets: 10375
4+
5+
Added support for the :class:`_sqltypes.JSON` datatype when using the
6+
Oracle database with the oracledb dialect. JSON values are serialized and
7+
deserialized using configurable strategies that accommodate Oracle's native
8+
JSON type available as of Oracle 21c. Pull request courtesy Abdallah
9+
Alhadad.
10+
11+
.. seealso::
12+
13+
:class:`_oracle.JSON` - Oracle-specific JSON class that includes
14+
implementation and platform notes.
15+
16+
:ref:`oracledb_json`

‎doc/build/dialects/oracle.rst‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,9 @@ construction arguments, are as follows:
6262
.. autoclass:: INTERVAL
6363
:members: __init__
6464

65+
.. autoclass:: JSON
66+
:members: __init__
67+
6568
.. autoclass:: NCLOB
6669
:members: __init__
6770

‎lib/sqlalchemy/dialects/mysql/base.py‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1177,6 +1177,7 @@ class MyClass(Base):
11771177
from ...sql.functions import random
11781178
from ...sql.functions import rollup
11791179
from ...sql.functions import sysdate
1180+
from ...sql.sqltypes import _JSON_VALUE
11801181
from ...sql.type_api import TypeEngine
11811182
from ...sql.visitors import ExternallyTraversible
11821183
from ...util.typing import TupleAny
@@ -2807,8 +2808,8 @@ class MySQLDialect(_mariadb_shim.MariaDBShim, default.DefaultDialect):
28072808

28082809
def __init__(
28092810
self,
2810-
json_serializer: Optional[Callable[..., Any]] = None,
2811-
json_deserializer: Optional[Callable[..., Any]] = None,
2811+
json_serializer: Callable[[_JSON_VALUE], str] | None = None,
2812+
json_deserializer: Callable[[str], _JSON_VALUE] | None = None,
28122813
is_mariadb: Optional[bool] = None,
28132814
**kwargs: Any,
28142815
) -> None:

‎lib/sqlalchemy/dialects/oracle/__init__.py‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@
3636
from .base import VECTOR
3737
from .base import VectorIndexConfig
3838
from .base import VectorIndexType
39+
from .json import JSON
3940
from .vector import SparseVector
4041
from .vector import VectorDistanceType
4142
from .vector import VectorStorageFormat
@@ -80,4 +81,5 @@
8081
"VectorStorageFormat",
8182
"VectorStorageType",
8283
"SparseVector",
84+
"JSON",
8385
)

‎lib/sqlalchemy/dialects/oracle/base.py‎

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1000,8 +1000,14 @@
10001000
from functools import lru_cache
10011001
from functools import wraps
10021002
import re
1003+
from typing import Any
1004+
from typing import Callable
1005+
from typing import TYPE_CHECKING
10031006

10041007
from . import dictionary
1008+
from .json import JSON
1009+
from .json import JSONIndexType
1010+
from .json import JSONPathType
10051011
from .types import _OracleBoolean
10061012
from .types import _OracleDate
10071013
from .types import BFILE
@@ -1036,6 +1042,7 @@
10361042
from ...sql import and_
10371043
from ...sql import bindparam
10381044
from ...sql import compiler
1045+
from ...sql import elements
10391046
from ...sql import expression
10401047
from ...sql import func
10411048
from ...sql import null
@@ -1045,6 +1052,7 @@
10451052
from ...sql import sqltypes
10461053
from ...sql import util as sql_util
10471054
from ...sql import visitors
1055+
from ...sql.base import NO_ARG
10481056
from ...sql.compiler import AggregateOrderByStyle
10491057
from ...sql.visitors import InternalTraversal
10501058
from ...types import BLOB
@@ -1057,6 +1065,9 @@
10571065
from ...types import REAL
10581066
from ...types import VARCHAR
10591067

1068+
if TYPE_CHECKING:
1069+
from ...sql.sqltypes import _JSON_VALUE
1070+
10601071
RESERVED_WORDS = set(
10611072
"SHARE RAW DROP BETWEEN FROM DESC OPTION PRIOR LONG THEN "
10621073
"DEFAULT ALTER IS INTO MINUS INTEGER NUMBER GRANT IDENTIFIED "
@@ -1079,6 +1090,9 @@
10791090
sqltypes.Interval: INTERVAL,
10801091
sqltypes.DateTime: DATE,
10811092
sqltypes.Date: _OracleDate,
1093+
sqltypes.JSON: JSON,
1094+
sqltypes.JSON.JSONIndexType: JSONIndexType,
1095+
sqltypes.JSON.JSONPathType: JSONPathType,
10821096
}
10831097

10841098
ischema_names = {
@@ -1106,6 +1120,7 @@
11061120
"ROWID": ROWID,
11071121
"BOOLEAN": BOOLEAN,
11081122
"VECTOR": VECTOR,
1123+
"JSON": JSON,
11091124
}
11101125

11111126

@@ -1278,6 +1293,18 @@ def visit_VECTOR(self, type_, **kw):
12781293
)
12791294
return f"VECTOR({dim},{storage_format},{storage_type})"
12801295

1296+
def visit_JSON(self, type_: JSON, **kw: Any) -> str:
1297+
use_blob = (
1298+
not self.dialect._supports_oracle_json
1299+
if getattr(type_, "use_blob", NO_ARG) is NO_ARG
1300+
else type_.use_blob
1301+
)
1302+
1303+
if use_blob:
1304+
return "BLOB"
1305+
else:
1306+
return "JSON"
1307+
12811308

12821309
class OracleCompiler(compiler.SQLCompiler):
12831310
"""Oracle compiler modifies the lexical structure of Select
@@ -1321,6 +1348,23 @@ def visit_true(self, expr, **kw):
13211348
def visit_false(self, expr, **kw):
13221349
return "0"
13231350

1351+
def visit_cast(self, cast, **kwargs):
1352+
# Oracle requires VARCHAR2 to have a length in CAST expressions
1353+
# Adapt String types to VARCHAR2 with appropriate length
1354+
type_ = cast.typeclause.type
1355+
if isinstance(type_, sqltypes.String) and not isinstance(
1356+
type_, (sqltypes.Text, sqltypes.CLOB)
1357+
):
1358+
adapted = VARCHAR2._adapt_string_for_cast(type_)
1359+
type_clause = self.dialect.type_compiler_instance.process(adapted)
1360+
else:
1361+
type_clause = cast.typeclause._compiler_dispatch(self, **kwargs)
1362+
1363+
return "CAST(%s AS %s)" % (
1364+
cast.clause._compiler_dispatch(self, **kwargs),
1365+
type_clause,
1366+
)
1367+
13241368
def get_cte_preamble(self, recursive):
13251369
return "WITH"
13261370

@@ -1790,6 +1834,57 @@ def visit_bitwise_lshift_op_binary(self, binary, operator, **kw):
17901834
def visit_bitwise_not_op_unary_operator(self, element, operator, **kw):
17911835
raise exc.CompileError("Cannot compile bitwise_not in oracle")
17921836

1837+
def _render_json_extract_from_binary(self, binary, operator, **kw):
1838+
literal_kw = kw.copy()
1839+
literal_kw["literal_binds"] = True
1840+
1841+
left = self.process(binary.left, **kw)
1842+
right = self.process(binary.right, **literal_kw)
1843+
1844+
if binary.type._type_affinity is sqltypes.Boolean:
1845+
# RETURNING clause doesn't handle true/false to 1/0
1846+
# mapping, so use CASE expression for boolean
1847+
return (
1848+
f"CASE JSON_VALUE({left}, {right})"
1849+
f" WHEN 'true' THEN 1"
1850+
f" WHEN 'false' THEN 0"
1851+
f" ELSE CAST(JSON_VALUE({left}, {right})"
1852+
f" AS NUMBER(1)) END"
1853+
)
1854+
elif binary.type._type_affinity is sqltypes.Integer:
1855+
json_value_returning = "INTEGER"
1856+
elif binary.type._type_affinity in (
1857+
sqltypes.Numeric,
1858+
sqltypes.Float,
1859+
):
1860+
if isinstance(binary.type, sqltypes.Float):
1861+
json_value_returning = "FLOAT"
1862+
else:
1863+
json_value_returning = (
1864+
f"NUMBER({binary.type.precision}, {binary.type.scale})"
1865+
)
1866+
elif binary.type._type_affinity is sqltypes.String:
1867+
json_value_returning = "VARCHAR2(4000)"
1868+
else:
1869+
# binary.type._type_affinity is sqltypes.JSON
1870+
# or other
1871+
return f"JSON_QUERY({left}, {right})"
1872+
1873+
return (
1874+
f"JSON_VALUE({left}, {right}"
1875+
f" RETURNING {json_value_returning} ERROR ON ERROR)"
1876+
)
1877+
1878+
def visit_json_getitem_op_binary(
1879+
self, binary: elements.BinaryExpression[Any], operator: Any, **kw: Any
1880+
) -> str:
1881+
return self._render_json_extract_from_binary(binary, operator, **kw)
1882+
1883+
def visit_json_path_getitem_op_binary(
1884+
self, binary: elements.BinaryExpression[Any], operator: Any, **kw: Any
1885+
) -> str:
1886+
return self._render_json_extract_from_binary(binary, operator, **kw)
1887+
17931888

17941889
class OracleDDLCompiler(compiler.DDLCompiler):
17951890

@@ -2023,6 +2118,8 @@ class OracleDialect(default.DefaultDialect):
20232118
supports_empty_insert = False
20242119
supports_identity_columns = True
20252120

2121+
_supports_oracle_json = True
2122+
20262123
aggregate_order_by_style = AggregateOrderByStyle.WITHIN_GROUP
20272124

20282125
statement_compiler = OracleCompiler
@@ -2077,6 +2174,8 @@ def __init__(
20772174
use_nchar_for_unicode=False,
20782175
exclude_tablespaces=("SYSTEM", "SYSAUX"),
20792176
enable_offset_fetch=True,
2177+
json_serializer: Callable[[_JSON_VALUE], str] | None = None,
2178+
json_deserializer: Callable[[str], _JSON_VALUE] | None = None,
20802179
**kwargs,
20812180
):
20822181
default.DefaultDialect.__init__(self, **kwargs)
@@ -2087,6 +2186,8 @@ def __init__(
20872186
self.enable_offset_fetch = self._supports_offset_fetch = (
20882187
enable_offset_fetch
20892188
)
2189+
self._json_serializer = json_serializer
2190+
self._json_deserializer = json_deserializer
20902191

20912192
def initialize(self, connection):
20922193
super().initialize(connection)
@@ -2102,6 +2203,7 @@ def initialize(self, connection):
21022203
self.colspecs.pop(sqltypes.Interval)
21032204
self.use_ansi = False
21042205

2206+
self._supports_oracle_json = self.server_version_info >= (21,)
21052207
self.supports_native_boolean = self.server_version_info >= (23,)
21062208
self.supports_identity_columns = self.server_version_info >= (12,)
21072209
self._supports_offset_fetch = (

‎lib/sqlalchemy/dialects/oracle/cx_oracle.py‎

Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -470,13 +470,15 @@ def _remove_clob(inputsizes, cursor, statement, parameters, context):
470470
from __future__ import annotations
471471

472472
import decimal
473+
import json
473474
import random
474475
import re
475476

476477
from . import base as oracle
477478
from .base import OracleCompiler
478479
from .base import OracleDialect
479480
from .base import OracleExecutionContext
481+
from .json import JSON
480482
from .types import _OracleDateLiteralRender
481483
from ... import exc
482484
from ... import util
@@ -485,12 +487,71 @@ def _remove_clob(inputsizes, cursor, statement, parameters, context):
485487
from ...engine import processors
486488
from ...sql import sqltypes
487489
from ...sql._typing import is_sql_compiler
490+
from ...sql.base import NO_ARG
488491
from ...sql.sqltypes import Boolean
489492

490493
# source:
491494
# https://github.com/oracle/python-cx_Oracle/issues/596#issuecomment-999243649
492495
_CX_ORACLE_MAGIC_LOB_SIZE = 131072
493496

497+
# largest JSON we can deserialize if we are not using
498+
# DB_TYPE_JSON
499+
_CX_ORACLE_MAX_JSON_CONVERTED = 32767
500+
501+
502+
class _OracleJson(JSON):
503+
def get_dbapi_type(self, dbapi):
504+
return dbapi.DB_TYPE_JSON
505+
506+
def _should_use_blob(self, dialect):
507+
use_blob = (
508+
not dialect._supports_oracle_json
509+
if self.use_blob is NO_ARG
510+
else self.use_blob
511+
)
512+
513+
return use_blob
514+
515+
def bind_processor(self, dialect):
516+
517+
if self._should_use_blob(dialect):
518+
519+
DBAPIBinary = dialect.dbapi.Binary
520+
521+
def string_process(value):
522+
if value is not None:
523+
# utf-8 is standard for oracledb
524+
# https://python-oracledb.readthedocs.io/en/latest/user_guide/globalization.html#setting-the-client-character-set # noqa: E501
525+
return DBAPIBinary(value.encode("utf-8"))
526+
else:
527+
return None
528+
529+
else:
530+
string_process = None
531+
532+
json_serializer = dialect._json_serializer or json.dumps
533+
534+
return self._make_bind_processor(string_process, json_serializer)
535+
536+
def result_processor(self, dialect, coltype):
537+
if self._should_use_blob(dialect):
538+
# for plain BLOB, use traditional binary decode + json.loads()
539+
string_process = self._str_impl.result_processor(dialect, coltype)
540+
json_deserializer = dialect._json_deserializer or json.loads
541+
542+
def process(value):
543+
if value is None:
544+
return None
545+
if string_process:
546+
value = string_process(value)
547+
return json_deserializer(value)
548+
549+
return process
550+
551+
else:
552+
# for JSON, json decoder is set as an outputtypehandler
553+
return None
554+
494555

495556
class _OracleInteger(sqltypes.Integer):
496557
def get_dbapi_type(self, dbapi):
@@ -1041,6 +1102,10 @@ class OracleDialect_cx_oracle(OracleDialect):
10411102
update_executemany_returning = True
10421103
delete_executemany_returning = True
10431104

1105+
supports_native_json_serialization = False
1106+
supports_native_json_deserialization = False
1107+
dialect_injects_custom_json_deserializer = True
1108+
10441109
bind_typing = interfaces.BindTyping.SETINPUTSIZES
10451110

10461111
driver = "cx_oracle"
@@ -1053,6 +1118,7 @@ class OracleDialect_cx_oracle(OracleDialect):
10531118
sqltypes.Float: _OracleFloat,
10541119
oracle.BINARY_FLOAT: _OracleBINARY_FLOAT,
10551120
oracle.BINARY_DOUBLE: _OracleBINARY_DOUBLE,
1121+
sqltypes.JSON: _OracleJson,
10561122
sqltypes.Integer: _OracleInteger,
10571123
oracle.NUMBER: _OracleNUMBER,
10581124
sqltypes.Date: _CXOracleDate,
@@ -1123,6 +1189,9 @@ def __init__(
11231189
dbapi_module.FIXED_NCHAR,
11241190
dbapi_module.FIXED_CHAR,
11251191
dbapi_module.TIMESTAMP,
1192+
# we dont make use of Oracle's JSON serialization; does not
1193+
# handle "none as null"
1194+
# dbapi_module.DB_TYPE_JSON,
11261195
int, # _OracleInteger,
11271196
# _OracleBINARY_FLOAT, _OracleBINARY_DOUBLE,
11281197
dbapi_module.NATIVE_FLOAT,
@@ -1357,6 +1426,16 @@ def output_type_handler(
13571426
_CX_ORACLE_MAGIC_LOB_SIZE,
13581427
cursor.arraysize,
13591428
)
1429+
elif (
1430+
default_type is cx_Oracle.DB_TYPE_JSON
1431+
and dialect._json_deserializer is not None
1432+
):
1433+
return cursor.var(
1434+
cx_Oracle.DB_TYPE_VARCHAR,
1435+
_CX_ORACLE_MAX_JSON_CONVERTED,
1436+
cursor.arraysize,
1437+
outconverter=dialect._json_deserializer,
1438+
)
13601439

13611440
return output_type_handler
13621441

0 commit comments

Comments
 (0)