database ======== .. py:module:: database Classes ------- .. autoapisummary:: database.Base database.NumpyType database.DataBaseEntry Functions --------- .. autoapisummary:: database._dataframe database._to_numpy database.process_responsibility_column database.process_mask_column database.db_to_pandas database.update_database database.add_to_database database.initialise_rex_db Module Contents --------------- .. py:function:: _dataframe(db, table) .. py:function:: _to_numpy(buffer, shape, dtype) .. py:function:: process_responsibility_column(df, dtype=np.float32) .. py:function:: process_mask_column(df, column) .. py:function:: db_to_pandas(db, dtype=np.float32, table='rex', process=True) for interactive use .. py:function:: update_database(db, explanation, time_taken, multi=False, clauses=None, analysis_results=None) .. py:function:: add_to_database(db, args, target, confidence, responsibility, sufficiency_mask, sufficiency_confidence, contrastive_mask, contrastive_confidence, inverse_classification, inverse_confidence, complete_mask, complete_classification, complete_confidence, area, entropy, insertion_curve, deletion_curve, time_taken, passing, failing, depth_reached, avg_box_size, multi=False, multi_no=None) .. py:class:: Base Bases: :py:obj:`sqlalchemy.orm.DeclarativeBase` Base class used for declarative class definitions. The :class:`_orm.DeclarativeBase` allows for the creation of new declarative bases in such a way that is compatible with type checkers:: from sqlalchemy.orm import DeclarativeBase class Base(DeclarativeBase): pass The above ``Base`` class is now usable as the base for new declarative mappings. The superclass makes use of the ``__init_subclass__()`` method to set up new classes and metaclasses aren't used. When first used, the :class:`_orm.DeclarativeBase` class instantiates a new :class:`_orm.registry` to be used with the base, assuming one was not provided explicitly. The :class:`_orm.DeclarativeBase` class supports class-level attributes which act as parameters for the construction of this registry; such as to indicate a specific :class:`_schema.MetaData` collection as well as a specific value for :paramref:`_orm.registry.type_annotation_map`:: from typing_extensions import Annotated from sqlalchemy import BigInteger from sqlalchemy import MetaData from sqlalchemy import String from sqlalchemy.orm import DeclarativeBase bigint = Annotated[int, "bigint"] my_metadata = MetaData() class Base(DeclarativeBase): metadata = my_metadata type_annotation_map = { str: String().with_variant(String(255), "mysql", "mariadb"), bigint: BigInteger(), } Class-level attributes which may be specified include: :param metadata: optional :class:`_schema.MetaData` collection. If a :class:`_orm.registry` is constructed automatically, this :class:`_schema.MetaData` collection will be used to construct it. Otherwise, the local :class:`_schema.MetaData` collection will supercede that used by an existing :class:`_orm.registry` passed using the :paramref:`_orm.DeclarativeBase.registry` parameter. :param type_annotation_map: optional type annotation map that will be passed to the :class:`_orm.registry` as :paramref:`_orm.registry.type_annotation_map`. :param registry: supply a pre-existing :class:`_orm.registry` directly. .. versionadded:: 2.0 Added :class:`.DeclarativeBase`, so that declarative base classes may be constructed in such a way that is also recognized by :pep:`484` type checkers. As a result, :class:`.DeclarativeBase` and other subclassing-oriented APIs should be seen as superseding previous "class returned by a function" APIs, namely :func:`_orm.declarative_base` and :meth:`_orm.registry.generate_base`, where the base class returned cannot be recognized by type checkers without using plugins. **__init__ behavior** In a plain Python class, the base-most ``__init__()`` method in the class hierarchy is ``object.__init__()``, which accepts no arguments. However, when the :class:`_orm.DeclarativeBase` subclass is first declared, the class is given an ``__init__()`` method that links to the :paramref:`_orm.registry.constructor` constructor function, if no ``__init__()`` method is already present; this is the usual declarative constructor that will assign keyword arguments as attributes on the instance, assuming those attributes are established at the class level (i.e. are mapped, or are linked to a descriptor). This constructor is **never accessed by a mapped class without being called explicitly via super()**, as mapped classes are themselves given an ``__init__()`` method directly which calls :paramref:`_orm.registry.constructor`, so in the default case works independently of what the base-most ``__init__()`` method does. .. versionchanged:: 2.0.1 :class:`_orm.DeclarativeBase` has a default constructor that links to :paramref:`_orm.registry.constructor` by default, so that calls to ``super().__init__()`` can access this constructor. Previously, due to an implementation mistake, this default constructor was missing, and calling ``super().__init__()`` would invoke ``object.__init__()``. The :class:`_orm.DeclarativeBase` subclass may also declare an explicit ``__init__()`` method which will replace the use of the :paramref:`_orm.registry.constructor` function at this level:: class Base(DeclarativeBase): def __init__(self, id=None): self.id = id Mapped classes still will not invoke this constructor implicitly; it remains only accessible by calling ``super().__init__()``:: class MyClass(Base): def __init__(self, id=None, name=None): self.name = name super().__init__(id=id) Note that this is a different behavior from what functions like the legacy :func:`_orm.declarative_base` would do; the base created by those functions would always install :paramref:`_orm.registry.constructor` for ``__init__()``. .. py:class:: NumpyType(*args, **kwargs) Bases: :py:obj:`sqlalchemy.types.TypeDecorator` Allows the creation of types which add additional functionality to an existing type. This method is preferred to direct subclassing of SQLAlchemy's built-in types as it ensures that all required functionality of the underlying type is kept in place. Typical usage:: import sqlalchemy.types as types class MyType(types.TypeDecorator): """Prefixes Unicode values with "PREFIX:" on the way in and strips it off on the way out. """ impl = types.Unicode cache_ok = True def process_bind_param(self, value, dialect): return "PREFIX:" + value def process_result_value(self, value, dialect): return value[7:] def copy(self, **kw): return MyType(self.impl.length) The class-level ``impl`` attribute is required, and can reference any :class:`.TypeEngine` class. Alternatively, the :meth:`load_dialect_impl` method can be used to provide different type classes based on the dialect given; in this case, the ``impl`` variable can reference ``TypeEngine`` as a placeholder. The :attr:`.TypeDecorator.cache_ok` class-level flag indicates if this custom :class:`.TypeDecorator` is safe to be used as part of a cache key. This flag defaults to ``None`` which will initially generate a warning when the SQL compiler attempts to generate a cache key for a statement that uses this type. If the :class:`.TypeDecorator` is not guaranteed to produce the same bind/result behavior and SQL generation every time, this flag should be set to ``False``; otherwise if the class produces the same behavior each time, it may be set to ``True``. See :attr:`.TypeDecorator.cache_ok` for further notes on how this works. Types that receive a Python type that isn't similar to the ultimate type used may want to define the :meth:`TypeDecorator.coerce_compared_value` method. This is used to give the expression system a hint when coercing Python objects into bind parameters within expressions. Consider this expression:: mytable.c.somecol + datetime.date(2009, 5, 15) Above, if "somecol" is an ``Integer`` variant, it makes sense that we're doing date arithmetic, where above is usually interpreted by databases as adding a number of days to the given date. The expression system does the right thing by not attempting to coerce the "date()" value into an integer-oriented bind parameter. However, in the case of ``TypeDecorator``, we are usually changing an incoming Python type to something new - ``TypeDecorator`` by default will "coerce" the non-typed side to be the same type as itself. Such as below, we define an "epoch" type that stores a date value as an integer:: class MyEpochType(types.TypeDecorator): impl = types.Integer cache_ok = True epoch = datetime.date(1970, 1, 1) def process_bind_param(self, value, dialect): return (value - self.epoch).days def process_result_value(self, value, dialect): return self.epoch + timedelta(days=value) Our expression of ``somecol + date`` with the above type will coerce the "date" on the right side to also be treated as ``MyEpochType``. This behavior can be overridden via the :meth:`~TypeDecorator.coerce_compared_value` method, which returns a type that should be used for the value of the expression. Below we set it such that an integer value will be treated as an ``Integer``, and any other value is assumed to be a date and will be treated as a ``MyEpochType``:: def coerce_compared_value(self, op, value): if isinstance(value, int): return Integer() else: return self .. warning:: Note that the **behavior of coerce_compared_value is not inherited by default from that of the base type**. If the :class:`.TypeDecorator` is augmenting a type that requires special logic for certain types of operators, this method **must** be overridden. A key example is when decorating the :class:`_postgresql.JSON` and :class:`_postgresql.JSONB` types; the default rules of :meth:`.TypeEngine.coerce_compared_value` should be used in order to deal with operators like index operations:: from sqlalchemy import JSON from sqlalchemy import TypeDecorator class MyJsonType(TypeDecorator): impl = JSON cache_ok = True def coerce_compared_value(self, op, value): return self.impl.coerce_compared_value(op, value) Without the above step, index operations such as ``mycol['foo']`` will cause the index value ``'foo'`` to be JSON encoded. Similarly, when working with the :class:`.ARRAY` datatype, the type coercion for index operations (e.g. ``mycol[5]``) is also handled by :meth:`.TypeDecorator.coerce_compared_value`, where again a simple override is sufficient unless special rules are needed for particular operators:: from sqlalchemy import ARRAY from sqlalchemy import TypeDecorator class MyArrayType(TypeDecorator): impl = ARRAY cache_ok = True def coerce_compared_value(self, op, value): return self.impl.coerce_compared_value(op, value) .. py:attribute:: impl .. py:attribute:: cache_ok :value: True Indicate if statements using this :class:`.ExternalType` are "safe to cache". The default value ``None`` will emit a warning and then not allow caching of a statement which includes this type. Set to ``False`` to disable statements using this type from being cached at all without a warning. When set to ``True``, the object's class and selected elements from its state will be used as part of the cache key. For example, using a :class:`.TypeDecorator`:: class MyType(TypeDecorator): impl = String cache_ok = True def __init__(self, choices): self.choices = tuple(choices) self.internal_only = True The cache key for the above type would be equivalent to:: >>> MyType(["a", "b", "c"])._static_cache_key (, ('choices', ('a', 'b', 'c'))) The caching scheme will extract attributes from the type that correspond to the names of parameters in the ``__init__()`` method. Above, the "choices" attribute becomes part of the cache key but "internal_only" does not, because there is no parameter named "internal_only". The requirements for cacheable elements is that they are hashable and also that they indicate the same SQL rendered for expressions using this type every time for a given cache value. To accommodate for datatypes that refer to unhashable structures such as dictionaries, sets and lists, these objects can be made "cacheable" by assigning hashable structures to the attributes whose names correspond with the names of the arguments. For example, a datatype which accepts a dictionary of lookup values may publish this as a sorted series of tuples. Given a previously un-cacheable type as:: class LookupType(UserDefinedType): """a custom type that accepts a dictionary as a parameter. this is the non-cacheable version, as "self.lookup" is not hashable. """ def __init__(self, lookup): self.lookup = lookup def get_col_spec(self, **kw): return "VARCHAR(255)" def bind_processor(self, dialect): ... # works with "self.lookup" ... Where "lookup" is a dictionary. The type will not be able to generate a cache key:: >>> type_ = LookupType({"a": 10, "b": 20}) >>> type_._static_cache_key :1: SAWarning: UserDefinedType LookupType({'a': 10, 'b': 20}) will not produce a cache key because the ``cache_ok`` flag is not set to True. Set this flag to True if this type object's state is safe to use in a cache key, or False to disable this warning. symbol('no_cache') If we **did** set up such a cache key, it wouldn't be usable. We would get a tuple structure that contains a dictionary inside of it, which cannot itself be used as a key in a "cache dictionary" such as SQLAlchemy's statement cache, since Python dictionaries aren't hashable:: >>> # set cache_ok = True >>> type_.cache_ok = True >>> # this is the cache key it would generate >>> key = type_._static_cache_key >>> key (, ('lookup', {'a': 10, 'b': 20})) >>> # however this key is not hashable, will fail when used with >>> # SQLAlchemy statement cache >>> some_cache = {key: "some sql value"} Traceback (most recent call last): File "", line 1, in TypeError: unhashable type: 'dict' The type may be made cacheable by assigning a sorted tuple of tuples to the ".lookup" attribute:: class LookupType(UserDefinedType): """a custom type that accepts a dictionary as a parameter. The dictionary is stored both as itself in a private variable, and published in a public variable as a sorted tuple of tuples, which is hashable and will also return the same value for any two equivalent dictionaries. Note it assumes the keys and values of the dictionary are themselves hashable. """ cache_ok = True def __init__(self, lookup): self._lookup = lookup # assume keys/values of "lookup" are hashable; otherwise # they would also need to be converted in some way here self.lookup = tuple((key, lookup[key]) for key in sorted(lookup)) def get_col_spec(self, **kw): return "VARCHAR(255)" def bind_processor(self, dialect): ... # works with "self._lookup" ... Where above, the cache key for ``LookupType({"a": 10, "b": 20})`` will be:: >>> LookupType({"a": 10, "b": 20})._static_cache_key (, ('lookup', (('a', 10), ('b', 20)))) .. versionadded:: 1.4.14 - added the ``cache_ok`` flag to allow some configurability of caching for :class:`.TypeDecorator` classes. .. versionadded:: 1.4.28 - added the :class:`.ExternalType` mixin which generalizes the ``cache_ok`` flag to both the :class:`.TypeDecorator` and :class:`.UserDefinedType` classes. .. seealso:: :ref:`sql_caching` .. py:method:: process_bind_param(value, dialect) Receive a bound parameter value to be converted. Custom subclasses of :class:`_types.TypeDecorator` should override this method to provide custom behaviors for incoming data values. This method is called at **statement execution time** and is passed the literal Python data value which is to be associated with a bound parameter in the statement. The operation could be anything desired to perform custom behavior, such as transforming or serializing data. This could also be used as a hook for validating logic. :param value: Data to operate upon, of any type expected by this method in the subclass. Can be ``None``. :param dialect: the :class:`.Dialect` in use. .. seealso:: :ref:`types_typedecorator` :meth:`_types.TypeDecorator.process_result_value` .. py:method:: process_result_value(value, dialect) Receive a result-row column value to be converted. Custom subclasses of :class:`_types.TypeDecorator` should override this method to provide custom behaviors for data values being received in result rows coming from the database. This method is called at **result fetching time** and is passed the literal Python data value that's extracted from a database result row. The operation could be anything desired to perform custom behavior, such as transforming or deserializing data. :param value: Data to operate upon, of any type expected by this method in the subclass. Can be ``None``. :param dialect: the :class:`.Dialect` in use. .. seealso:: :ref:`types_typedecorator` :meth:`_types.TypeDecorator.process_bind_param` .. py:class:: DataBaseEntry(id, path, target, confidence, responsibility, responsibility_shape, sufficiency_mask, mask_shape, sufficiency_confidence, time_taken, contrastive_mask=None, contrastive_confidence=None, inverse_classification=None, inverse_confidence=None, complete_mask=None, complete_classification=None, complete_confidence=None, passing=None, failing=None, area=None, entropy=None, insertion_curve=None, deletion_curve=None, total_work=None, multi=False, multi_no=None, depth_reached=None, avg_box_size=None, tree_depth=None, search_limit=None, iters=None, min_size=None, distribution=None, distribution_args=None, initial_radius=None, radius_eta=None, method=None, spotlights=0, spotlight_size=0, spotlight_eta=0.0, obj_function=None) Bases: :py:obj:`Base` Base class used for declarative class definitions. The :class:`_orm.DeclarativeBase` allows for the creation of new declarative bases in such a way that is compatible with type checkers:: from sqlalchemy.orm import DeclarativeBase class Base(DeclarativeBase): pass The above ``Base`` class is now usable as the base for new declarative mappings. The superclass makes use of the ``__init_subclass__()`` method to set up new classes and metaclasses aren't used. When first used, the :class:`_orm.DeclarativeBase` class instantiates a new :class:`_orm.registry` to be used with the base, assuming one was not provided explicitly. The :class:`_orm.DeclarativeBase` class supports class-level attributes which act as parameters for the construction of this registry; such as to indicate a specific :class:`_schema.MetaData` collection as well as a specific value for :paramref:`_orm.registry.type_annotation_map`:: from typing_extensions import Annotated from sqlalchemy import BigInteger from sqlalchemy import MetaData from sqlalchemy import String from sqlalchemy.orm import DeclarativeBase bigint = Annotated[int, "bigint"] my_metadata = MetaData() class Base(DeclarativeBase): metadata = my_metadata type_annotation_map = { str: String().with_variant(String(255), "mysql", "mariadb"), bigint: BigInteger(), } Class-level attributes which may be specified include: :param metadata: optional :class:`_schema.MetaData` collection. If a :class:`_orm.registry` is constructed automatically, this :class:`_schema.MetaData` collection will be used to construct it. Otherwise, the local :class:`_schema.MetaData` collection will supercede that used by an existing :class:`_orm.registry` passed using the :paramref:`_orm.DeclarativeBase.registry` parameter. :param type_annotation_map: optional type annotation map that will be passed to the :class:`_orm.registry` as :paramref:`_orm.registry.type_annotation_map`. :param registry: supply a pre-existing :class:`_orm.registry` directly. .. versionadded:: 2.0 Added :class:`.DeclarativeBase`, so that declarative base classes may be constructed in such a way that is also recognized by :pep:`484` type checkers. As a result, :class:`.DeclarativeBase` and other subclassing-oriented APIs should be seen as superseding previous "class returned by a function" APIs, namely :func:`_orm.declarative_base` and :meth:`_orm.registry.generate_base`, where the base class returned cannot be recognized by type checkers without using plugins. **__init__ behavior** In a plain Python class, the base-most ``__init__()`` method in the class hierarchy is ``object.__init__()``, which accepts no arguments. However, when the :class:`_orm.DeclarativeBase` subclass is first declared, the class is given an ``__init__()`` method that links to the :paramref:`_orm.registry.constructor` constructor function, if no ``__init__()`` method is already present; this is the usual declarative constructor that will assign keyword arguments as attributes on the instance, assuming those attributes are established at the class level (i.e. are mapped, or are linked to a descriptor). This constructor is **never accessed by a mapped class without being called explicitly via super()**, as mapped classes are themselves given an ``__init__()`` method directly which calls :paramref:`_orm.registry.constructor`, so in the default case works independently of what the base-most ``__init__()`` method does. .. versionchanged:: 2.0.1 :class:`_orm.DeclarativeBase` has a default constructor that links to :paramref:`_orm.registry.constructor` by default, so that calls to ``super().__init__()`` can access this constructor. Previously, due to an implementation mistake, this default constructor was missing, and calling ``super().__init__()`` would invoke ``object.__init__()``. The :class:`_orm.DeclarativeBase` subclass may also declare an explicit ``__init__()`` method which will replace the use of the :paramref:`_orm.registry.constructor` function at this level:: class Base(DeclarativeBase): def __init__(self, id=None): self.id = id Mapped classes still will not invoke this constructor implicitly; it remains only accessible by calling ``super().__init__()``:: class MyClass(Base): def __init__(self, id=None, name=None): self.name = name super().__init__(id=id) Note that this is a different behavior from what functions like the legacy :func:`_orm.declarative_base` would do; the base created by those functions would always install :paramref:`_orm.registry.constructor` for ``__init__()``. .. py:attribute:: __tablename__ :value: 'rex' .. py:attribute:: id .. py:attribute:: path .. py:attribute:: target .. py:attribute:: confidence .. py:attribute:: time .. py:attribute:: responsibility .. py:attribute:: responsibility_shape .. py:attribute:: total_work .. py:attribute:: passing .. py:attribute:: failing .. py:attribute:: sufficiency_mask .. py:attribute:: mask_shape .. py:attribute:: sufficiency_confidence .. py:attribute:: contrastive_mask .. py:attribute:: contrastive_confidence .. py:attribute:: inverse_classification .. py:attribute:: inverse_confidence .. py:attribute:: complete_mask .. py:attribute:: complete_classification .. py:attribute:: complete_confidence .. py:attribute:: area .. py:attribute:: entropy .. py:attribute:: insertion_curve .. py:attribute:: deletion_curve .. py:attribute:: multi .. py:attribute:: multi_no .. py:attribute:: depth_reached .. py:attribute:: avg_box_size .. py:attribute:: tree_depth .. py:attribute:: search_limit_per_iter .. py:attribute:: iters .. py:attribute:: min_size .. py:attribute:: distribution .. py:attribute:: distribution_args .. py:attribute:: spatial_radius .. py:attribute:: spatial_eta .. py:attribute:: method .. py:attribute:: spotlights .. py:attribute:: spotlight_size .. py:attribute:: spotlight_eta .. py:attribute:: obj_function .. py:attribute:: search_limit :value: None .. py:function:: initialise_rex_db(name, echo=False)