| 1 |
""" |
| 2 |
Oracle database backend for Django. |
| 3 |
|
| 4 |
Requires oracledb: https://oracle.github.io/python-oracledb/ |
| 5 |
""" |
| 6 |
|
| 7 |
import datetime |
| 8 |
import decimal |
| 9 |
import os |
| 10 |
import platform |
| 11 |
from contextlib import contextmanager |
| 12 |
|
| 13 |
from django.conf import settings |
| 14 |
from django.core.exceptions import ImproperlyConfigured |
| 15 |
from django.db import IntegrityError |
| 16 |
from django.db.backends.base.base import BaseDatabaseWrapper |
| 17 |
from django.db.backends.oracle.oracledb_any import is_oracledb |
| 18 |
from django.db.backends.utils import debug_transaction |
| 19 |
from django.utils.asyncio import async_unsafe |
| 20 |
from django.utils.encoding import force_bytes, force_str |
| 21 |
from django.utils.functional import cached_property |
| 22 |
from django.utils.version import get_version_tuple |
| 23 |
|
| 24 |
try: |
| 25 |
from django.db.backends.oracle.oracledb_any import oracledb as Database |
| 26 |
except ImportError as e: |
| 27 |
raise ImproperlyConfigured(f"Error loading oracledb module: {e}") |
| 28 |
|
| 29 |
|
| 30 |
def _setup_environment(environ): |
| 31 |
# Cygwin requires some special voodoo to set the environment variables |
| 32 |
# properly so that Oracle will see them. |
| 33 |
if platform.system().upper().startswith("CYGWIN"): |
| 34 |
try: |
| 35 |
import ctypes |
| 36 |
except ImportError as e: |
| 37 |
raise ImproperlyConfigured( |
| 38 |
"Error loading ctypes: %s; " |
| 39 |
"the Oracle backend requires ctypes to " |
| 40 |
"operate correctly under Cygwin." % e |
| 41 |
) |
| 42 |
kernel32 = ctypes.CDLL("kernel32") |
| 43 |
for name, value in environ: |
| 44 |
kernel32.SetEnvironmentVariableA(name, value) |
| 45 |
else: |
| 46 |
os.environ.update(environ) |
| 47 |
|
| 48 |
|
| 49 |
_setup_environment( |
| 50 |
[ |
| 51 |
# Oracle takes client-side character set encoding from the environment. |
| 52 |
("NLS_LANG", ".AL32UTF8"), |
| 53 |
# This prevents Unicode from getting mangled by getting encoded into the |
| 54 |
# potentially non-Unicode database character set. |
| 55 |
("ORA_NCHAR_LITERAL_REPLACE", "TRUE"), |
| 56 |
] |
| 57 |
) |
| 58 |
|
| 59 |
|
| 60 |
# Some of these import oracledb, so import them after checking if it's |
| 61 |
# installed. |
| 62 |
from .client import DatabaseClient # NOQA |
| 63 |
from .creation import DatabaseCreation # NOQA |
| 64 |
from .features import DatabaseFeatures # NOQA |
| 65 |
from .introspection import DatabaseIntrospection # NOQA |
| 66 |
from .operations import DatabaseOperations # NOQA |
| 67 |
from .schema import DatabaseSchemaEditor # NOQA |
| 68 |
from .utils import Oracle_datetime, dsn # NOQA |
| 69 |
from .validation import DatabaseValidation # NOQA |
| 70 |
|
| 71 |
|
| 72 |
@contextmanager |
| 73 |
def wrap_oracle_errors(): |
| 74 |
try: |
| 75 |
yield |
| 76 |
except Database.DatabaseError as e: |
| 77 |
# oracledb raises a oracledb.DatabaseError exception with the |
| 78 |
# following attributes and values: |
| 79 |
# code = 2091 |
| 80 |
# message = 'ORA-02091: transaction rolled back |
| 81 |
# 'ORA-02291: integrity constraint (TEST_DJANGOTEST.SYS |
| 82 |
# _C00102056) violated - parent key not found' |
| 83 |
# or: |
| 84 |
# 'ORA-00001: unique constraint (DJANGOTEST.DEFERRABLE_ |
| 85 |
# PINK_CONSTRAINT) violated |
| 86 |
# Convert that case to Django's IntegrityError exception. |
| 87 |
x = e.args[0] |
| 88 |
if ( |
| 89 |
hasattr(x, "code") |
| 90 |
and hasattr(x, "message") |
| 91 |
and x.code == 2091 |
| 92 |
and ("ORA-02291" in x.message or "ORA-00001" in x.message) |
| 93 |
): |
| 94 |
raise IntegrityError(*tuple(e.args)) |
| 95 |
raise |
| 96 |
|
| 97 |
|
| 98 |
class _UninitializedOperatorsDescriptor: |
| 99 |
def __get__(self, instance, cls=None): |
| 100 |
# If connection.operators is looked up before a connection has been |
| 101 |
# created, transparently initialize connection.operators to avert an |
| 102 |
# AttributeError. |
| 103 |
if instance is None: |
| 104 |
raise AttributeError("operators not available as class attribute") |
| 105 |
# Creating a cursor will initialize the operators. |
| 106 |
instance.cursor().close() |
| 107 |
return instance.__dict__["operators"] |
| 108 |
|
| 109 |
|
| 110 |
class DatabaseWrapper(BaseDatabaseWrapper): |
| 111 |
vendor = "oracle" |
| 112 |
display_name = "Oracle" |
| 113 |
# This dictionary maps Field objects to their associated Oracle column |
| 114 |
# types, as strings. Column-type strings can contain format strings; they'll |
| 115 |
# be interpolated against the values of Field.__dict__ before being output. |
| 116 |
# If a column type is set to None, it won't be included in the output. |
| 117 |
# |
| 118 |
# Any format strings starting with "qn_" are quoted before being used in the |
| 119 |
# output (the "qn_" prefix is stripped before the lookup is performed. |
| 120 |
data_types = { |
| 121 |
"AutoField": "NUMBER(11) GENERATED BY DEFAULT ON NULL AS IDENTITY", |
| 122 |
"BigAutoField": "NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY", |
| 123 |
"BinaryField": "BLOB", |
| 124 |
"BooleanField": "NUMBER(1)", |
| 125 |
"CharField": "NVARCHAR2(%(max_length)s)", |
| 126 |
"DateField": "DATE", |
| 127 |
"DateTimeField": "TIMESTAMP", |
| 128 |
"DecimalField": "NUMBER(%(max_digits)s, %(decimal_places)s)", |
| 129 |
"DurationField": "INTERVAL DAY(9) TO SECOND(6)", |
| 130 |
"FileField": "NVARCHAR2(%(max_length)s)", |
| 131 |
"FilePathField": "NVARCHAR2(%(max_length)s)", |
| 132 |
"FloatField": "DOUBLE PRECISION", |
| 133 |
"IntegerField": "NUMBER(11)", |
| 134 |
"JSONField": "NCLOB", |
| 135 |
"BigIntegerField": "NUMBER(19)", |
| 136 |
"IPAddressField": "VARCHAR2(15)", |
| 137 |
"GenericIPAddressField": "VARCHAR2(39)", |
| 138 |
"OneToOneField": "NUMBER(11)", |
| 139 |
"PositiveBigIntegerField": "NUMBER(19)", |
| 140 |
"PositiveIntegerField": "NUMBER(11)", |
| 141 |
"PositiveSmallIntegerField": "NUMBER(11)", |
| 142 |
"SlugField": "NVARCHAR2(%(max_length)s)", |
| 143 |
"SmallAutoField": "NUMBER(5) GENERATED BY DEFAULT ON NULL AS IDENTITY", |
| 144 |
"SmallIntegerField": "NUMBER(11)", |
| 145 |
"TextField": "NCLOB", |
| 146 |
"TimeField": "TIMESTAMP", |
| 147 |
"URLField": "VARCHAR2(%(max_length)s)", |
| 148 |
"UUIDField": "VARCHAR2(32)", |
| 149 |
} |
| 150 |
data_type_check_constraints = { |
| 151 |
"BooleanField": "%(qn_column)s IN (0,1)", |
| 152 |
"JSONField": "%(qn_column)s IS JSON", |
| 153 |
"PositiveBigIntegerField": "%(qn_column)s >= 0", |
| 154 |
"PositiveIntegerField": "%(qn_column)s >= 0", |
| 155 |
"PositiveSmallIntegerField": "%(qn_column)s >= 0", |
| 156 |
} |
| 157 |
|
| 158 |
# Oracle doesn't support a database index on these columns. |
| 159 |
_limited_data_types = ("clob", "nclob", "blob") |
| 160 |
|
| 161 |
operators = _UninitializedOperatorsDescriptor() |
| 162 |
|
| 163 |
_standard_operators = { |
| 164 |
"exact": "= %s", |
| 165 |
"iexact": "= UPPER(%s)", |
| 166 |
"contains": ( |
| 167 |
"LIKE TRANSLATE(%s USING NCHAR_CS) ESCAPE TRANSLATE('\\' USING NCHAR_CS)" |
| 168 |
), |
| 169 |
"icontains": ( |
| 170 |
"LIKE UPPER(TRANSLATE(%s USING NCHAR_CS)) " |
| 171 |
"ESCAPE TRANSLATE('\\' USING NCHAR_CS)" |
| 172 |
), |
| 173 |
"gt": "> %s", |
| 174 |
"gte": ">= %s", |
| 175 |
"lt": "< %s", |
| 176 |
"lte": "<= %s", |
| 177 |
"startswith": ( |
| 178 |
"LIKE TRANSLATE(%s USING NCHAR_CS) ESCAPE TRANSLATE('\\' USING NCHAR_CS)" |
| 179 |
), |
| 180 |
"endswith": ( |
| 181 |
"LIKE TRANSLATE(%s USING NCHAR_CS) ESCAPE TRANSLATE('\\' USING NCHAR_CS)" |
| 182 |
), |
| 183 |
"istartswith": ( |
| 184 |
"LIKE UPPER(TRANSLATE(%s USING NCHAR_CS)) " |
| 185 |
"ESCAPE TRANSLATE('\\' USING NCHAR_CS)" |
| 186 |
), |
| 187 |
"iendswith": ( |
| 188 |
"LIKE UPPER(TRANSLATE(%s USING NCHAR_CS)) " |
| 189 |
"ESCAPE TRANSLATE('\\' USING NCHAR_CS)" |
| 190 |
), |
| 191 |
} |
| 192 |
|
| 193 |
_likec_operators = { |
| 194 |
**_standard_operators, |
| 195 |
"contains": "LIKEC %s ESCAPE '\\'", |
| 196 |
"icontains": "LIKEC UPPER(%s) ESCAPE '\\'", |
| 197 |
"startswith": "LIKEC %s ESCAPE '\\'", |
| 198 |
"endswith": "LIKEC %s ESCAPE '\\'", |
| 199 |
"istartswith": "LIKEC UPPER(%s) ESCAPE '\\'", |
| 200 |
"iendswith": "LIKEC UPPER(%s) ESCAPE '\\'", |
| 201 |
} |
| 202 |
|
| 203 |
# The patterns below are used to generate SQL pattern lookup clauses when |
| 204 |
# the right-hand side of the lookup isn't a raw string (it might be an expression |
| 205 |
# or the result of a bilateral transformation). |
| 206 |
# In those cases, special characters for LIKE operators (e.g. \, %, _) |
| 207 |
# should be escaped on the database side. |
| 208 |
# |
| 209 |
# Note: we use str.format() here for readability as '%' is used as a wildcard for |
| 210 |
# the LIKE operator. |
| 211 |
pattern_esc = r"REPLACE(REPLACE(REPLACE({}, '\', '\\'), '%%', '\%%'), '_', '\_')" |
| 212 |
_pattern_ops = { |
| 213 |
"contains": "'%%' || {} || '%%'", |
| 214 |
"icontains": "'%%' || UPPER({}) || '%%'", |
| 215 |
"startswith": "{} || '%%'", |
| 216 |
"istartswith": "UPPER({}) || '%%'", |
| 217 |
"endswith": "'%%' || {}", |
| 218 |
"iendswith": "'%%' || UPPER({})", |
| 219 |
} |
| 220 |
|
| 221 |
_standard_pattern_ops = { |
| 222 |
k: "LIKE TRANSLATE( " + v + " USING NCHAR_CS)" |
| 223 |
" ESCAPE TRANSLATE('\\' USING NCHAR_CS)" |
| 224 |
for k, v in _pattern_ops.items() |
| 225 |
} |
| 226 |
_likec_pattern_ops = { |
| 227 |
k: "LIKEC " + v + " ESCAPE '\\'" for k, v in _pattern_ops.items() |
| 228 |
} |
| 229 |
|
| 230 |
Database = Database |
| 231 |
SchemaEditorClass = DatabaseSchemaEditor |
| 232 |
# Classes instantiated in __init__(). |
| 233 |
client_class = DatabaseClient |
| 234 |
creation_class = DatabaseCreation |
| 235 |
features_class = DatabaseFeatures |
| 236 |
introspection_class = DatabaseIntrospection |
| 237 |
ops_class = DatabaseOperations |
| 238 |
validation_class = DatabaseValidation |
| 239 |
_connection_pools = {} |
| 240 |
|
| 241 |
def __init__(self, *args, **kwargs): |
| 242 |
super().__init__(*args, **kwargs) |
| 243 |
use_returning_into = self.settings_dict["OPTIONS"].get( |
| 244 |
"use_returning_into", True |
| 245 |
) |
| 246 |
self.features.can_return_columns_from_insert = use_returning_into |
| 247 |
|
| 248 |
@property |
| 249 |
def is_pool(self): |
| 250 |
return self.settings_dict["OPTIONS"].get("pool", False) |
| 251 |
|
| 252 |
@property |
| 253 |
def pool(self): |
| 254 |
if not self.is_pool: |
| 255 |
return None |
| 256 |
|
| 257 |
if self.settings_dict.get("CONN_MAX_AGE", 0) != 0: |
| 258 |
raise ImproperlyConfigured( |
| 259 |
"Pooling doesn't support persistent connections." |
| 260 |
) |
| 261 |
|
| 262 |
pool_key = (self.alias, self.settings_dict["USER"]) |
| 263 |
if pool_key not in self._connection_pools: |
| 264 |
connect_kwargs = self.get_connection_params() |
| 265 |
pool_options = connect_kwargs.pop("pool") |
| 266 |
if pool_options is not True: |
| 267 |
connect_kwargs.update(pool_options) |
| 268 |
|
| 269 |
pool = Database.create_pool( |
| 270 |
user=self.settings_dict["USER"], |
| 271 |
password=self.settings_dict["PASSWORD"], |
| 272 |
dsn=dsn(self.settings_dict), |
| 273 |
**connect_kwargs, |
| 274 |
) |
| 275 |
self._connection_pools.setdefault(pool_key, pool) |
| 276 |
|
| 277 |
return self._connection_pools[pool_key] |
| 278 |
|
| 279 |
def close_pool(self): |
| 280 |
if self.pool: |
| 281 |
self.pool.close(force=True) |
| 282 |
pool_key = (self.alias, self.settings_dict["USER"]) |
| 283 |
del self._connection_pools[pool_key] |
| 284 |
|
| 285 |
def get_database_version(self): |
| 286 |
return self.oracle_version |
| 287 |
|
| 288 |
def get_connection_params(self): |
| 289 |
# Pooling feature is only supported for oracledb. |
| 290 |
if self.is_pool and not is_oracledb: |
| 291 |
raise ImproperlyConfigured( |
| 292 |
"Pooling isn't supported by cx_Oracle. Use python-oracledb instead." |
| 293 |
) |
| 294 |
conn_params = self.settings_dict["OPTIONS"].copy() |
| 295 |
if "use_returning_into" in conn_params: |
| 296 |
del conn_params["use_returning_into"] |
| 297 |
return conn_params |
| 298 |
|
| 299 |
@async_unsafe |
| 300 |
def get_new_connection(self, conn_params): |
| 301 |
if self.pool: |
| 302 |
return self.pool.acquire() |
| 303 |
return Database.connect( |
| 304 |
user=self.settings_dict["USER"], |
| 305 |
password=self.settings_dict["PASSWORD"], |
| 306 |
dsn=dsn(self.settings_dict), |
| 307 |
**conn_params, |
| 308 |
) |
| 309 |
|
| 310 |
def init_connection_state(self): |
| 311 |
super().init_connection_state() |
| 312 |
cursor = self.create_cursor() |
| 313 |
# Set the territory first. The territory overrides NLS_DATE_FORMAT |
| 314 |
# and NLS_TIMESTAMP_FORMAT to the territory default. When all of |
| 315 |
# these are set in single statement it isn't clear what is supposed |
| 316 |
# to happen. |
| 317 |
cursor.execute("ALTER SESSION SET NLS_TERRITORY = 'AMERICA'") |
| 318 |
# Set Oracle date to ANSI date format. This only needs to execute |
| 319 |
# once when we create a new connection. We also set the Territory |
| 320 |
# to 'AMERICA' which forces Sunday to evaluate to a '1' in |
| 321 |
# TO_CHAR(). |
| 322 |
cursor.execute( |
| 323 |
"ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'" |
| 324 |
" NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'" |
| 325 |
+ (" TIME_ZONE = 'UTC'" if settings.USE_TZ else "") |
| 326 |
) |
| 327 |
cursor.close() |
| 328 |
if "operators" not in self.__dict__: |
| 329 |
# Ticket #14149: Check whether our LIKE implementation will |
| 330 |
# work for this connection or we need to fall back on LIKEC. |
| 331 |
# This check is performed only once per DatabaseWrapper |
| 332 |
# instance per thread, since subsequent connections will use |
| 333 |
# the same settings. |
| 334 |
cursor = self.create_cursor() |
| 335 |
try: |
| 336 |
cursor.execute( |
| 337 |
"SELECT 1 FROM DUAL WHERE DUMMY %s" |
| 338 |
% self._standard_operators["contains"], |
| 339 |
["X"], |
| 340 |
) |
| 341 |
except Database.DatabaseError: |
| 342 |
self.operators = self._likec_operators |
| 343 |
self.pattern_ops = self._likec_pattern_ops |
| 344 |
else: |
| 345 |
self.operators = self._standard_operators |
| 346 |
self.pattern_ops = self._standard_pattern_ops |
| 347 |
cursor.close() |
| 348 |
self.connection.stmtcachesize = 20 |
| 349 |
# Ensure all changes are preserved even when AUTOCOMMIT is False. |
| 350 |
if not self.get_autocommit(): |
| 351 |
self.commit() |
| 352 |
|
| 353 |
@async_unsafe |
| 354 |
def create_cursor(self, name=None): |
| 355 |
return FormatStylePlaceholderCursor(self.connection, self) |
| 356 |
|
| 357 |
def _commit(self): |
| 358 |
if self.connection is not None: |
| 359 |
with debug_transaction(self, "COMMIT"), wrap_oracle_errors(): |
| 360 |
return self.connection.commit() |
| 361 |
|
| 362 |
# Oracle doesn't support releasing savepoints. But we fake them when query |
| 363 |
# logging is enabled to keep query counts consistent with other backends. |
| 364 |
def _savepoint_commit(self, sid): |
| 365 |
if self.queries_logged: |
| 366 |
self.queries_log.append( |
| 367 |
{ |
| 368 |
"sql": "-- RELEASE SAVEPOINT %s (faked)" % self.ops.quote_name(sid), |
| 369 |
"time": "0.000", |
| 370 |
} |
| 371 |
) |
| 372 |
|
| 373 |
def _set_autocommit(self, autocommit): |
| 374 |
with self.wrap_database_errors: |
| 375 |
self.connection.autocommit = autocommit |
| 376 |
|
| 377 |
def check_constraints(self, table_names=None): |
| 378 |
""" |
| 379 |
Check constraints by setting them to immediate. Return them to deferred |
| 380 |
afterward. |
| 381 |
""" |
| 382 |
with self.cursor() as cursor: |
| 383 |
cursor.execute("SET CONSTRAINTS ALL IMMEDIATE") |
| 384 |
cursor.execute("SET CONSTRAINTS ALL DEFERRED") |
| 385 |
|
| 386 |
def is_usable(self): |
| 387 |
try: |
| 388 |
self.connection.ping() |
| 389 |
except Database.Error: |
| 390 |
return False |
| 391 |
else: |
| 392 |
return True |
| 393 |
|
| 394 |
def close_if_health_check_failed(self): |
| 395 |
if self.pool: |
| 396 |
# The pool only returns healthy connections. |
| 397 |
return |
| 398 |
return super().close_if_health_check_failed() |
| 399 |
|
| 400 |
@cached_property |
| 401 |
def oracle_version(self): |
| 402 |
with self.temporary_connection(): |
| 403 |
return tuple(int(x) for x in self.connection.version.split(".")) |
| 404 |
|
| 405 |
@cached_property |
| 406 |
def oracledb_version(self): |
| 407 |
return get_version_tuple(Database.__version__) |
| 408 |
|
| 409 |
|
| 410 |
class OracleParam: |
| 411 |
""" |
| 412 |
Wrapper object for formatting parameters for Oracle. If the string |
| 413 |
representation of the value is large enough (greater than 4000 characters) |
| 414 |
the input size needs to be set as CLOB. Alternatively, if the parameter |
| 415 |
has an `input_size` attribute, then the value of the `input_size` attribute |
| 416 |
will be used instead. Otherwise, no input size will be set for the |
| 417 |
parameter when executing the query. |
| 418 |
""" |
| 419 |
|
| 420 |
def __init__(self, param, cursor, strings_only=False): |
| 421 |
# With raw SQL queries, datetimes can reach this function |
| 422 |
# without being converted by DateTimeField.get_db_prep_value. |
| 423 |
if settings.USE_TZ and ( |
| 424 |
isinstance(param, datetime.datetime) |
| 425 |
and not isinstance(param, Oracle_datetime) |
| 426 |
): |
| 427 |
param = Oracle_datetime.from_datetime(param) |
| 428 |
|
| 429 |
string_size = 0 |
| 430 |
has_boolean_data_type = ( |
| 431 |
cursor.database.features.supports_boolean_expr_in_select_clause |
| 432 |
) |
| 433 |
if not has_boolean_data_type: |
| 434 |
# Oracle < 23c doesn't recognize True and False correctly. |
| 435 |
if param is True: |
| 436 |
param = 1 |
| 437 |
elif param is False: |
| 438 |
param = 0 |
| 439 |
if hasattr(param, "bind_parameter"): |
| 440 |
self.force_bytes = param.bind_parameter(cursor) |
| 441 |
elif isinstance(param, (bytes, datetime.timedelta)): |
| 442 |
self.force_bytes = param |
| 443 |
else: |
| 444 |
# To transmit to the database, we need Unicode if supported |
| 445 |
# To get size right, we must consider bytes. |
| 446 |
self.force_bytes = force_str(param, cursor.charset, strings_only) |
| 447 |
if isinstance(self.force_bytes, str): |
| 448 |
# We could optimize by only converting up to 4000 bytes here |
| 449 |
string_size = len(force_bytes(param, cursor.charset, strings_only)) |
| 450 |
if hasattr(param, "input_size"): |
| 451 |
# If parameter has `input_size` attribute, use that. |
| 452 |
self.input_size = param.input_size |
| 453 |
elif string_size > 4000: |
| 454 |
# Mark any string param greater than 4000 characters as a CLOB. |
| 455 |
self.input_size = Database.DB_TYPE_CLOB |
| 456 |
elif isinstance(param, datetime.datetime): |
| 457 |
self.input_size = Database.DB_TYPE_TIMESTAMP |
| 458 |
elif has_boolean_data_type and isinstance(param, bool): |
| 459 |
self.input_size = Database.DB_TYPE_BOOLEAN |
| 460 |
else: |
| 461 |
self.input_size = None |
| 462 |
|
| 463 |
|
| 464 |
class VariableWrapper: |
| 465 |
""" |
| 466 |
An adapter class for cursor variables that prevents the wrapped object |
| 467 |
from being converted into a string when used to instantiate an OracleParam. |
| 468 |
This can be used generally for any other object that should be passed into |
| 469 |
Cursor.execute as-is. |
| 470 |
""" |
| 471 |
|
| 472 |
def __init__(self, var): |
| 473 |
self.var = var |
| 474 |
|
| 475 |
def bind_parameter(self, cursor): |
| 476 |
return self.var |
| 477 |
|
| 478 |
def __getattr__(self, key): |
| 479 |
return getattr(self.var, key) |
| 480 |
|
| 481 |
def __setattr__(self, key, value): |
| 482 |
if key == "var": |
| 483 |
self.__dict__[key] = value |
| 484 |
else: |
| 485 |
setattr(self.var, key, value) |
| 486 |
|
| 487 |
|
| 488 |
class FormatStylePlaceholderCursor: |
| 489 |
""" |
| 490 |
Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var" |
| 491 |
style. This fixes it -- but note that if you want to use a literal "%s" in |
| 492 |
a query, you'll need to use "%%s". |
| 493 |
""" |
| 494 |
|
| 495 |
charset = "utf-8" |
| 496 |
|
| 497 |
def __init__(self, connection, database): |
| 498 |
self.cursor = connection.cursor() |
| 499 |
self.cursor.outputtypehandler = self._output_type_handler |
| 500 |
self.database = database |
| 501 |
|
| 502 |
@staticmethod |
| 503 |
def _output_number_converter(value): |
| 504 |
return decimal.Decimal(value) if "." in value else int(value) |
| 505 |
|
| 506 |
@staticmethod |
| 507 |
def _get_decimal_converter(precision, scale): |
| 508 |
if scale == 0: |
| 509 |
return int |
| 510 |
context = decimal.Context(prec=precision) |
| 511 |
quantize_value = decimal.Decimal(1).scaleb(-scale) |
| 512 |
return lambda v: decimal.Decimal(v).quantize(quantize_value, context=context) |
| 513 |
|
| 514 |
@staticmethod |
| 515 |
def _output_type_handler(cursor, name, defaultType, length, precision, scale): |
| 516 |
""" |
| 517 |
Called for each db column fetched from cursors. Return numbers as the |
| 518 |
appropriate Python type, and NCLOB with JSON as strings. |
| 519 |
""" |
| 520 |
if defaultType == Database.NUMBER: |
| 521 |
if scale == -127: |
| 522 |
if precision == 0: |
| 523 |
# NUMBER column: decimal-precision floating point. |
| 524 |
# This will normally be an integer from a sequence, |
| 525 |
# but it could be a decimal value. |
| 526 |
outconverter = FormatStylePlaceholderCursor._output_number_converter |
| 527 |
else: |
| 528 |
# FLOAT column: binary-precision floating point. |
| 529 |
# This comes from FloatField columns. |
| 530 |
outconverter = float |
| 531 |
elif precision > 0: |
| 532 |
# NUMBER(p,s) column: decimal-precision fixed point. |
| 533 |
# This comes from IntegerField and DecimalField columns. |
| 534 |
outconverter = FormatStylePlaceholderCursor._get_decimal_converter( |
| 535 |
precision, scale |
| 536 |
) |
| 537 |
else: |
| 538 |
# No type information. This normally comes from a |
| 539 |
# mathematical expression in the SELECT list. Guess int |
| 540 |
# or Decimal based on whether it has a decimal point. |
| 541 |
outconverter = FormatStylePlaceholderCursor._output_number_converter |
| 542 |
return cursor.var( |
| 543 |
Database.STRING, |
| 544 |
size=255, |
| 545 |
arraysize=cursor.arraysize, |
| 546 |
outconverter=outconverter, |
| 547 |
) |
| 548 |
# oracledb 2.0.0+ returns NLOB columns with IS JSON constraints as |
| 549 |
# dicts. Use a no-op converter to avoid this. |
| 550 |
elif defaultType == Database.DB_TYPE_NCLOB: |
| 551 |
return cursor.var(Database.DB_TYPE_NCLOB, arraysize=cursor.arraysize) |
| 552 |
|
| 553 |
def _format_params(self, params): |
| 554 |
try: |
| 555 |
return {k: OracleParam(v, self, True) for k, v in params.items()} |
| 556 |
except AttributeError: |
| 557 |
return tuple(OracleParam(p, self, True) for p in params) |
| 558 |
|
| 559 |
def _guess_input_sizes(self, params_list): |
| 560 |
# Try dict handling; if that fails, treat as sequence |
| 561 |
if hasattr(params_list[0], "keys"): |
| 562 |
sizes = {} |
| 563 |
for params in params_list: |
| 564 |
for k, value in params.items(): |
| 565 |
if value.input_size: |
| 566 |
sizes[k] = value.input_size |
| 567 |
if sizes: |
| 568 |
self.setinputsizes(**sizes) |
| 569 |
else: |
| 570 |
# It's not a list of dicts; it's a list of sequences |
| 571 |
sizes = [None] * len(params_list[0]) |
| 572 |
for params in params_list: |
| 573 |
for i, value in enumerate(params): |
| 574 |
if value.input_size: |
| 575 |
sizes[i] = value.input_size |
| 576 |
if sizes: |
| 577 |
self.setinputsizes(*sizes) |
| 578 |
|
| 579 |
def _param_generator(self, params): |
| 580 |
# Try dict handling; if that fails, treat as sequence |
| 581 |
if hasattr(params, "items"): |
| 582 |
return {k: v.force_bytes for k, v in params.items()} |
| 583 |
else: |
| 584 |
return [p.force_bytes for p in params] |
| 585 |
|
| 586 |
def _fix_for_params(self, query, params, unify_by_values=False): |
| 587 |
# oracledb wants no trailing ';' for SQL statements. For PL/SQL, it |
| 588 |
# it does want a trailing ';' but not a trailing '/'. However, these |
| 589 |
# characters must be included in the original query in case the query |
| 590 |
# is being passed to SQL*Plus. |
| 591 |
if query.endswith(";") or query.endswith("/"): |
| 592 |
query = query[:-1] |
| 593 |
if params is None: |
| 594 |
params = [] |
| 595 |
elif hasattr(params, "keys"): |
| 596 |
# Handle params as dict |
| 597 |
args = {k: ":%s" % k for k in params} |
| 598 |
query %= args |
| 599 |
elif unify_by_values and params: |
| 600 |
# Handle params as a dict with unified query parameters by their |
| 601 |
# values. It can be used only in single query execute() because |
| 602 |
# executemany() shares the formatted query with each of the params |
| 603 |
# list. e.g. for input params = [0.75, 2, 0.75, 'sth', 0.75] |
| 604 |
# params_dict = { |
| 605 |
# (float, 0.75): ':arg0', |
| 606 |
# (int, 2): ':arg1', |
| 607 |
# (str, 'sth'): ':arg2', |
| 608 |
# } |
| 609 |
# args = [':arg0', ':arg1', ':arg0', ':arg2', ':arg0'] |
| 610 |
# params = {':arg0': 0.75, ':arg1': 2, ':arg2': 'sth'} |
| 611 |
# The type of parameters in param_types keys is necessary to avoid |
| 612 |
# unifying 0/1 with False/True. |
| 613 |
param_types = [(type(param), param) for param in params] |
| 614 |
params_dict = { |
| 615 |
param_type: ":arg%d" % i |
| 616 |
for i, param_type in enumerate(dict.fromkeys(param_types)) |
| 617 |
} |
| 618 |
args = [params_dict[param_type] for param_type in param_types] |
| 619 |
params = { |
| 620 |
placeholder: param for (_, param), placeholder in params_dict.items() |
| 621 |
} |
| 622 |
query %= tuple(args) |
| 623 |
else: |
| 624 |
# Handle params as sequence |
| 625 |
args = [(":arg%d" % i) for i in range(len(params))] |
| 626 |
query %= tuple(args) |
| 627 |
return query, self._format_params(params) |
| 628 |
|
| 629 |
def execute(self, query, params=None): |
| 630 |
query, params = self._fix_for_params(query, params, unify_by_values=True) |
| 631 |
self._guess_input_sizes([params]) |
| 632 |
with wrap_oracle_errors(): |
| 633 |
return self.cursor.execute(query, self._param_generator(params)) |
| 634 |
|
| 635 |
def executemany(self, query, params=None): |
| 636 |
if not params: |
| 637 |
# No params given, nothing to do |
| 638 |
return None |
| 639 |
# uniform treatment for sequences and iterables |
| 640 |
params_iter = iter(params) |
| 641 |
query, firstparams = self._fix_for_params(query, next(params_iter)) |
| 642 |
# we build a list of formatted params; as we're going to traverse it |
| 643 |
# more than once, we can't make it lazy by using a generator |
| 644 |
formatted = [firstparams] + [self._format_params(p) for p in params_iter] |
| 645 |
self._guess_input_sizes(formatted) |
| 646 |
with wrap_oracle_errors(): |
| 647 |
return self.cursor.executemany( |
| 648 |
query, [self._param_generator(p) for p in formatted] |
| 649 |
) |
| 650 |
|
| 651 |
def close(self): |
| 652 |
try: |
| 653 |
self.cursor.close() |
| 654 |
except Database.InterfaceError: |
| 655 |
# already closed |
| 656 |
pass |
| 657 |
|
| 658 |
def var(self, *args): |
| 659 |
return VariableWrapper(self.cursor.var(*args)) |
| 660 |
|
| 661 |
def arrayvar(self, *args): |
| 662 |
return VariableWrapper(self.cursor.arrayvar(*args)) |
| 663 |
|
| 664 |
def __getattr__(self, attr): |
| 665 |
return getattr(self.cursor, attr) |
| 666 |
|
| 667 |
def __iter__(self): |
| 668 |
return iter(self.cursor) |