Python cx_Oracle.CLOB Examples

def __init__(self, param, cursor, strings_only=False):
        # With raw SQL queries, datetimes can reach this function
        # without being converted by DateTimeField.get_db_prep_value.
        if settings.USE_TZ and (isinstance(param, datetime.datetime) and
                                not isinstance(param, Oracle_datetime)):
            param = Oracle_datetime.from_datetime(param)

        string_size = 0
        # Oracle doesn't recognize True and False correctly.
        if param is True:
            param = 1
        elif param is False:
            param = 0
        if hasattr(param, 'bind_parameter'):
            self.force_bytes = param.bind_parameter(cursor)
        elif isinstance(param, (Database.Binary, datetime.timedelta)):
            self.force_bytes = param
        else:
            # To transmit to the database, we need Unicode if supported
            # To get size right, we must consider bytes.
            self.force_bytes = force_text(param, cursor.charset, strings_only)
            if isinstance(self.force_bytes, str):
                # We could optimize by only converting up to 4000 bytes here
                string_size = len(force_bytes(param, cursor.charset, strings_only))
        if hasattr(param, 'input_size'):
            # If parameter has `input_size` attribute, use that.
            self.input_size = param.input_size
        elif string_size > 4000:
            # Mark any string param greater than 4000 characters as a CLOB.
            self.input_size = Database.CLOB
        elif isinstance(param, datetime.datetime):
            self.input_size = Database.TIMESTAMP
        else:
            self.input_size = None 

def __init__(self, param, cursor, strings_only=False):
        # With raw SQL queries, datetimes can reach this function
        # without being converted by DateTimeField.get_db_prep_value.
        if settings.USE_TZ and (isinstance(param, datetime.datetime) and
                                not isinstance(param, Oracle_datetime)):
            param = Oracle_datetime.from_datetime(param)

        string_size = 0
        # Oracle doesn't recognize True and False correctly.
        if param is True:
            param = 1
        elif param is False:
            param = 0
        if hasattr(param, 'bind_parameter'):
            self.force_bytes = param.bind_parameter(cursor)
        elif isinstance(param, (Database.Binary, datetime.timedelta)):
            self.force_bytes = param
        else:
            # To transmit to the database, we need Unicode if supported
            # To get size right, we must consider bytes.
            self.force_bytes = force_text(param, cursor.charset, strings_only)
            if isinstance(self.force_bytes, str):
                # We could optimize by only converting up to 4000 bytes here
                string_size = len(force_bytes(param, cursor.charset, strings_only))
        if hasattr(param, 'input_size'):
            # If parameter has `input_size` attribute, use that.
            self.input_size = param.input_size
        elif string_size > 4000:
            # Mark any string param greater than 4000 characters as a CLOB.
            self.input_size = Database.CLOB
        else:
            self.input_size = None 

def __init__(self,
                 auto_convert_lobs=True,
                 threaded=True,
                 coerce_to_unicode=False,
                 coerce_to_decimal=True,
                 arraysize=50,
                 **kwargs):

        self._pop_deprecated_kwargs(kwargs)

        OracleDialect.__init__(self, **kwargs)
        self.threaded = threaded
        self.arraysize = arraysize
        self.auto_convert_lobs = auto_convert_lobs
        self.coerce_to_unicode = coerce_to_unicode
        self.coerce_to_decimal = coerce_to_decimal

        cx_Oracle = self.dbapi

        if cx_Oracle is None:
            self._include_setinputsizes = {}
            self.cx_oracle_ver = (0, 0, 0)
        else:
            self.cx_oracle_ver = self._parse_cx_oracle_ver(cx_Oracle.version)
            if self.cx_oracle_ver < (5, 0) and self.cx_oracle_ver > (0, 0, 0):
                raise exc.InvalidRequestError(
                    "cx_Oracle version 5.0 and above are supported")

            self._has_native_int = hasattr(cx_Oracle, "NATIVE_INT")

            self._include_setinputsizes = {
                cx_Oracle.NCLOB, cx_Oracle.CLOB, cx_Oracle.LOB,
                cx_Oracle.BLOB, cx_Oracle.FIXED_CHAR,
            }

        self._is_cx_oracle_6 = self.cx_oracle_ver >= (6, ) 

def __init__(self, param, cursor, strings_only=False):
        # With raw SQL queries, datetimes can reach this function
        # without being converted by DateTimeField.get_db_prep_value.
        if settings.USE_TZ and isinstance(param, datetime.datetime):
            if timezone.is_naive(param):
                warnings.warn("Oracle received a naive datetime (%s)"
                              " while time zone support is active." % param,
                              RuntimeWarning)
                default_timezone = timezone.get_default_timezone()
                param = timezone.make_aware(param, default_timezone)
            param = param.astimezone(timezone.utc).replace(tzinfo=None)

        # Oracle doesn't recognize True and False correctly in Python 3.
        # The conversion done below works both in 2 and 3.
        if param is True:
            param = "1"
        elif param is False:
            param = "0"
        if hasattr(param, 'bind_parameter'):
            self.force_bytes = param.bind_parameter(cursor)
        else:
            self.force_bytes = convert_unicode(param, cursor.charset,
                                             strings_only)
        if hasattr(param, 'input_size'):
            # If parameter has `input_size` attribute, use that.
            self.input_size = param.input_size
        elif isinstance(param, six.string_types) and len(param) > 4000:
            # Mark any string param greater than 4000 characters as a CLOB.
            self.input_size = Database.CLOB
        else:
            self.input_size = None 

def query(self, db_name=None, sql='', limit_num=0, close_conn=True, **kwargs):
        """返回 ResultSet """
        result_set = ResultSet(full_sql=sql)
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            if db_name:
                cursor.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {db_name}")
            sql = sql.rstrip(';')
            # 支持oralce查询SQL执行计划语句
            if re.match(r"^explain", sql, re.I):
                cursor.execute(sql)
                # 重置SQL文本，获取SQL执行计划
                sql = f"select PLAN_TABLE_OUTPUT from table(dbms_xplan.display)"
            cursor.execute(sql)
            fields = cursor.description
            if any(x[1] == cx_Oracle.CLOB for x in fields):
                rows = [tuple([(c.read() if type(c) == cx_Oracle.LOB else c) for c in r]) for r in cursor]
                if int(limit_num) > 0:
                    rows = rows[0:int(limit_num)]
            else:
                if int(limit_num) > 0:
                    rows = cursor.fetchmany(int(limit_num))
                else:
                    rows = cursor.fetchall()
            result_set.column_list = [i[0] for i in fields] if fields else []
            result_set.rows = [tuple(x) for x in rows]
            result_set.affected_rows = len(result_set.rows)
        except Exception as e:
            logger.warning(f"Oracle 语句执行报错，语句：{sql}，错误信息{traceback.format_exc()}")
            result_set.error = str(e)
        finally:
            if close_conn:
                self.close()
        return result_set 

def __init__(self,
                 auto_convert_lobs=True,
                 threaded=True,
                 coerce_to_unicode=False,
                 coerce_to_decimal=True,
                 arraysize=50,
                 **kwargs):

        self._pop_deprecated_kwargs(kwargs)

        OracleDialect.__init__(self, **kwargs)
        self.threaded = threaded
        self.arraysize = arraysize
        self.auto_convert_lobs = auto_convert_lobs
        self.coerce_to_unicode = coerce_to_unicode
        self.coerce_to_decimal = coerce_to_decimal

        cx_Oracle = self.dbapi

        if cx_Oracle is None:
            self._include_setinputsizes = {}
            self.cx_oracle_ver = (0, 0, 0)
        else:
            self.cx_oracle_ver = self._parse_cx_oracle_ver(cx_Oracle.version)
            if self.cx_oracle_ver < (5, 0) and self.cx_oracle_ver > (0, 0, 0):
                raise exc.InvalidRequestError(
                    "cx_Oracle version 5.0 and above are supported")

            self._has_native_int = hasattr(cx_Oracle, "NATIVE_INT")

            self._include_setinputsizes = {
                cx_Oracle.NCLOB, cx_Oracle.CLOB, cx_Oracle.LOB,
                cx_Oracle.BLOB, cx_Oracle.FIXED_CHAR,
            }

        self._is_cx_oracle_6 = self.cx_oracle_ver >= (6, ) 

def __init__(self, param, cursor, strings_only=False):
        # With raw SQL queries, datetimes can reach this function
        # without being converted by DateTimeField.get_db_prep_value.
        if settings.USE_TZ and (isinstance(param, datetime.datetime) and
                                not isinstance(param, Oracle_datetime)):
            if timezone.is_naive(param):
                warnings.warn("Oracle received a naive datetime (%s)"
                              " while time zone support is active." % param,
                              RuntimeWarning)
                default_timezone = timezone.get_default_timezone()
                param = timezone.make_aware(param, default_timezone)
            param = Oracle_datetime.from_datetime(param.astimezone(timezone.utc))

        if isinstance(param, datetime.timedelta):
            param = duration_string(param)
            if ' ' not in param:
                param = '0 ' + param

        string_size = 0
        # Oracle doesn't recognize True and False correctly in Python 3.
        # The conversion done below works both in 2 and 3.
        if param is True:
            param = 1
        elif param is False:
            param = 0
        if hasattr(param, 'bind_parameter'):
            self.force_bytes = param.bind_parameter(cursor)
        elif isinstance(param, Database.Binary):
            self.force_bytes = param
        else:
            # To transmit to the database, we need Unicode if supported
            # To get size right, we must consider bytes.
            self.force_bytes = convert_unicode(param, cursor.charset,
                                             strings_only)
            if isinstance(self.force_bytes, six.string_types):
                # We could optimize by only converting up to 4000 bytes here
                string_size = len(force_bytes(param, cursor.charset, strings_only))
        if hasattr(param, 'input_size'):
            # If parameter has `input_size` attribute, use that.
            self.input_size = param.input_size
        elif string_size > 4000:
            # Mark any string param greater than 4000 characters as a CLOB.
            self.input_size = Database.CLOB
        else:
            self.input_size = None 

def __init__(self, param, cursor, strings_only=False):
        # With raw SQL queries, datetimes can reach this function
        # without being converted by DateTimeField.get_db_prep_value.
        if settings.USE_TZ and (isinstance(param, datetime.datetime) and
                                not isinstance(param, Oracle_datetime)):
            if timezone.is_aware(param):
                warnings.warn(
                    "The Oracle database adapter received an aware datetime (%s), "
                    "probably from cursor.execute(). Update your code to pass a "
                    "naive datetime in the database connection's time zone (UTC by "
                    "default).", RemovedInDjango20Warning)
                param = param.astimezone(timezone.utc).replace(tzinfo=None)
            param = Oracle_datetime.from_datetime(param)

        string_size = 0
        # Oracle doesn't recognize True and False correctly in Python 3.
        # The conversion done below works both in 2 and 3.
        if param is True:
            param = 1
        elif param is False:
            param = 0
        if hasattr(param, 'bind_parameter'):
            self.force_bytes = param.bind_parameter(cursor)
        elif isinstance(param, (Database.Binary, datetime.timedelta)):
            self.force_bytes = param
        else:
            # To transmit to the database, we need Unicode if supported
            # To get size right, we must consider bytes.
            self.force_bytes = force_text(param, cursor.charset, strings_only)
            if isinstance(self.force_bytes, six.string_types):
                # We could optimize by only converting up to 4000 bytes here
                string_size = len(force_bytes(param, cursor.charset, strings_only))
        if hasattr(param, 'input_size'):
            # If parameter has `input_size` attribute, use that.
            self.input_size = param.input_size
        elif string_size > 4000:
            # Mark any string param greater than 4000 characters as a CLOB.
            self.input_size = Database.CLOB
        elif isinstance(param, decimal.Decimal):
            self.input_size = Database.NUMBER
        else:
            self.input_size = None 

def _generate_connection_outputtype_handler(self):
        """establish the default outputtypehandler established at the
        connection level.

        """

        dialect = self
        cx_Oracle = dialect.dbapi

        number_handler = _OracleNUMBER(asdecimal=True).\
            _cx_oracle_outputtypehandler(dialect)
        float_handler = _OracleNUMBER(asdecimal=False).\
            _cx_oracle_outputtypehandler(dialect)

        def output_type_handler(cursor, name, default_type,
                                size, precision, scale):
            if default_type == cx_Oracle.NUMBER:
                if not dialect.coerce_to_decimal:
                    return None
                elif precision == 0 and scale in (0, -127):
                    # ambiguous type, this occurs when selecting
                    # numbers from deep subqueries
                    return cursor.var(
                        cx_Oracle.STRING,
                        255,
                        outconverter=dialect._detect_decimal,
                        arraysize=cursor.arraysize)
                elif precision and scale > 0:
                    return number_handler(
                        cursor, name, default_type, size, precision, scale
                    )
                else:
                    return float_handler(
                        cursor, name, default_type, size, precision, scale
                    )

            # allow all strings to come back natively as Unicode
            elif dialect.coerce_to_unicode and \
                    default_type in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
                return cursor.var(
                    util.text_type, size, cursor.arraysize
                )
            elif dialect.auto_convert_lobs and default_type in (
                    cx_Oracle.CLOB, cx_Oracle.NCLOB, cx_Oracle.BLOB
            ):
                return cursor.var(
                    default_type, size, cursor.arraysize,
                    outconverter=lambda value: value.read()
                )
        return output_type_handler 

def __init__(self, param, cursor, strings_only=False):
        # With raw SQL queries, datetimes can reach this function
        # without being converted by DateTimeField.get_db_prep_value.
        if settings.USE_TZ and (isinstance(param, datetime.datetime) and
                                not isinstance(param, Oracle_datetime)):
            if timezone.is_aware(param):
                warnings.warn(
                    "The Oracle database adapter received an aware datetime (%s), "
                    "probably from cursor.execute(). Update your code to pass a "
                    "naive datetime in the database connection's time zone (UTC by "
                    "default).", RemovedInDjango20Warning)
                param = param.astimezone(timezone.utc).replace(tzinfo=None)
            param = Oracle_datetime.from_datetime(param)

        if isinstance(param, datetime.timedelta):
            param = duration_string(param)
            if ' ' not in param:
                param = '0 ' + param

        string_size = 0
        # Oracle doesn't recognize True and False correctly in Python 3.
        # The conversion done below works both in 2 and 3.
        if param is True:
            param = 1
        elif param is False:
            param = 0
        if hasattr(param, 'bind_parameter'):
            self.force_bytes = param.bind_parameter(cursor)
        elif isinstance(param, Database.Binary):
            self.force_bytes = param
        else:
            # To transmit to the database, we need Unicode if supported
            # To get size right, we must consider bytes.
            self.force_bytes = convert_unicode(param, cursor.charset,
                                             strings_only)
            if isinstance(self.force_bytes, six.string_types):
                # We could optimize by only converting up to 4000 bytes here
                string_size = len(force_bytes(param, cursor.charset, strings_only))
        if hasattr(param, 'input_size'):
            # If parameter has `input_size` attribute, use that.
            self.input_size = param.input_size
        elif string_size > 4000:
            # Mark any string param greater than 4000 characters as a CLOB.
            self.input_size = Database.CLOB
        else:
            self.input_size = None 

def __init__(self, param, cursor, strings_only=False):
        # With raw SQL queries, datetimes can reach this function
        # without being converted by DateTimeField.get_db_prep_value.
        if settings.USE_TZ and (isinstance(param, datetime.datetime) and
                                not isinstance(param, Oracle_datetime)):
            if timezone.is_aware(param):
                warnings.warn(
                    "The Oracle database adapter received an aware datetime (%s), "
                    "probably from cursor.execute(). Update your code to pass a "
                    "naive datetime in the database connection's time zone (UTC by "
                    "default).", RemovedInDjango20Warning)
                param = param.astimezone(timezone.utc).replace(tzinfo=None)
            param = Oracle_datetime.from_datetime(param)

        string_size = 0
        # Oracle doesn't recognize True and False correctly in Python 3.
        # The conversion done below works both in 2 and 3.
        if param is True:
            param = 1
        elif param is False:
            param = 0
        if hasattr(param, 'bind_parameter'):
            self.force_bytes = param.bind_parameter(cursor)
        elif isinstance(param, (Database.Binary, datetime.timedelta)):
            self.force_bytes = param
        else:
            # To transmit to the database, we need Unicode if supported
            # To get size right, we must consider bytes.
            self.force_bytes = force_text(param, cursor.charset, strings_only)
            if isinstance(self.force_bytes, six.string_types):
                # We could optimize by only converting up to 4000 bytes here
                string_size = len(force_bytes(param, cursor.charset, strings_only))
        if hasattr(param, 'input_size'):
            # If parameter has `input_size` attribute, use that.
            self.input_size = param.input_size
        elif string_size > 4000:
            # Mark any string param greater than 4000 characters as a CLOB.
            self.input_size = Database.CLOB
        elif isinstance(param, decimal.Decimal):
            self.input_size = Database.NUMBER
        else:
            self.input_size = None 

def sqltuningadvisor(self, db_name=None, sql='', close_conn=True, **kwargs):
        """
        add by Jan.song 20200421
        使用DBMS_SQLTUNE包做sql tuning支持
        执行用户需要有advior角色
        返回 ResultSet
        """
        result_set = ResultSet(full_sql=sql)
        task_name = 'sqlaudit' + f'''{threading.currentThread().ident}'''
        task_begin = 0
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            sql = sql.rstrip(';')
            # 创建分析任务
            create_task_sql = f'''DECLARE
                                  my_task_name VARCHAR2(30);
                                  my_sqltext  CLOB;
                                  BEGIN
                                  my_sqltext := '{sql}';
                                  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                                  sql_text    => my_sqltext,
                                  user_name   => '{db_name}', 
                                  scope       => 'COMPREHENSIVE',
                                  time_limit  => 30,
                                  task_name   => '{task_name}',
                                  description => 'tuning');
                                  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '{task_name}');
                                  END;'''
            task_begin = 1
            cursor.execute(create_task_sql)
            # 获取分析报告
            get_task_sql = f'''select DBMS_SQLTUNE.REPORT_TUNING_TASK( '{task_name}') from dual'''
            cursor.execute(get_task_sql)
            fields = cursor.description
            if any(x[1] == cx_Oracle.CLOB for x in fields):
                rows = [tuple([(c.read() if type(c) == cx_Oracle.LOB else c) for c in r]) for r in cursor]
            else:
                rows = cursor.fetchall()
            result_set.column_list = [i[0] for i in fields] if fields else []
            result_set.rows = [tuple(x) for x in rows]
            result_set.affected_rows = len(result_set.rows)
        except Exception as e:
            logger.warning(f"Oracle 语句执行报错，语句：{sql}，错误信息{traceback.format_exc()}")
            result_set.error = str(e)
        finally:
            # 结束分析任务
            if task_begin == 1:
                end_sql = f'''DECLARE 
                             begin 
                             dbms_sqltune.drop_tuning_task('{task_name}');
                             end;'''
                cursor.execute(end_sql)
            if close_conn:
                self.close()
        return result_set 

def _generate_connection_outputtype_handler(self):
        """establish the default outputtypehandler established at the
        connection level.

        """

        dialect = self
        cx_Oracle = dialect.dbapi

        number_handler = _OracleNUMBER(asdecimal=True).\
            _cx_oracle_outputtypehandler(dialect)
        float_handler = _OracleNUMBER(asdecimal=False).\
            _cx_oracle_outputtypehandler(dialect)

        def output_type_handler(cursor, name, default_type,
                                size, precision, scale):
            if default_type == cx_Oracle.NUMBER:
                if not dialect.coerce_to_decimal:
                    return None
                elif precision == 0 and scale in (0, -127):
                    # ambiguous type, this occurs when selecting
                    # numbers from deep subqueries
                    return cursor.var(
                        cx_Oracle.STRING,
                        255,
                        outconverter=dialect._detect_decimal,
                        arraysize=cursor.arraysize)
                elif precision and scale > 0:
                    return number_handler(
                        cursor, name, default_type, size, precision, scale
                    )
                else:
                    return float_handler(
                        cursor, name, default_type, size, precision, scale
                    )

            # allow all strings to come back natively as Unicode
            elif dialect.coerce_to_unicode and \
                    default_type in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
                return cursor.var(
                    util.text_type, size, cursor.arraysize
                )
            elif dialect.auto_convert_lobs and default_type in (
                    cx_Oracle.CLOB, cx_Oracle.NCLOB, cx_Oracle.BLOB
            ):
                return cursor.var(
                    default_type, size, cursor.arraysize,
                    outconverter=lambda value: value.read()
                )
        return output_type_handler 

def __init__(self,
                 auto_convert_lobs=True,
                 threaded=True,
                 coerce_to_unicode=False,
                 coerce_to_decimal=True,
                 arraysize=50,
                 **kwargs):

        self._pop_deprecated_kwargs(kwargs)

        OracleDialect.__init__(self, **kwargs)
        self.threaded = threaded
        self.arraysize = arraysize
        self.auto_convert_lobs = auto_convert_lobs
        self.coerce_to_unicode = coerce_to_unicode
        self.coerce_to_decimal = coerce_to_decimal

        cx_Oracle = self.dbapi

        if cx_Oracle is None:
            self._include_setinputsizes = {}
            self.cx_oracle_ver = (0, 0, 0)
        else:
            self.cx_oracle_ver = self._parse_cx_oracle_ver(cx_Oracle.version)
            if self.cx_oracle_ver < (5, 2) and self.cx_oracle_ver > (0, 0, 0):
                raise exc.InvalidRequestError(
                    "cx_Oracle version 5.2 and above are supported")

            self._has_native_int = hasattr(cx_Oracle, "NATIVE_INT")

            self._include_setinputsizes = {
                cx_Oracle.NCLOB, cx_Oracle.CLOB, cx_Oracle.LOB,
                cx_Oracle.NCHAR, cx_Oracle.FIXED_NCHAR,
                cx_Oracle.BLOB, cx_Oracle.FIXED_CHAR, cx_Oracle.TIMESTAMP,
                _OracleInteger, _OracleBINARY_FLOAT, _OracleBINARY_DOUBLE
            }

            self._paramval = lambda value: value.getvalue()

            # https://github.com/oracle/python-cx_Oracle/issues/176#issuecomment-386821291
            # https://github.com/oracle/python-cx_Oracle/issues/224
            self._values_are_lists = self.cx_oracle_ver >= (6, 3)
            if self._values_are_lists:
                cx_Oracle.__future__.dml_ret_array_val = True

                def _returningval(value):
                    try:
                        return value.values[0][0]
                    except IndexError:
                        return None

                self._returningval = _returningval
            else:
                self._returningval = self._paramval

        self._is_cx_oracle_6 = self.cx_oracle_ver >= (6, ) 

def _generate_connection_outputtype_handler(self):
        """establish the default outputtypehandler established at the
        connection level.

        """

        dialect = self
        cx_Oracle = dialect.dbapi

        number_handler = _OracleNUMBER(asdecimal=True).\
            _cx_oracle_outputtypehandler(dialect)
        float_handler = _OracleNUMBER(asdecimal=False).\
            _cx_oracle_outputtypehandler(dialect)

        def output_type_handler(cursor, name, default_type,
                                size, precision, scale):
            if default_type == cx_Oracle.NUMBER:
                if not dialect.coerce_to_decimal:
                    return None
                elif precision == 0 and scale in (0, -127):
                    # ambiguous type, this occurs when selecting
                    # numbers from deep subqueries
                    return cursor.var(
                        cx_Oracle.STRING,
                        255,
                        outconverter=dialect._detect_decimal,
                        arraysize=cursor.arraysize)
                elif precision and scale > 0:
                    return number_handler(
                        cursor, name, default_type, size, precision, scale
                    )
                else:
                    return float_handler(
                        cursor, name, default_type, size, precision, scale
                    )

            # allow all strings to come back natively as Unicode
            elif dialect.coerce_to_unicode and \
                    default_type in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
                return cursor.var(
                    util.text_type, size, cursor.arraysize
                )
            elif dialect.auto_convert_lobs and default_type in (
                    cx_Oracle.CLOB, cx_Oracle.NCLOB, cx_Oracle.BLOB
            ):
                return cursor.var(
                    default_type, size, cursor.arraysize,
                    outconverter=lambda value: value.read()
                )
        return output_type_handler