Vous êtes un professionnel et vous avez besoin d'une formation ? Sensibilisation à
l'Intelligence Artificielle Voir le programme détaillé

Module « sqlalchemy »

Classe « JSON »

Informations générales

Héritage

builtins.object
    Generic
builtins.object
    Visitable
        TypeEngine
builtins.object
    TypeEngineMixin
        Indexable
            JSON

Définition

class JSON(Indexable, TypeEngine):

help(JSON)

Represent a SQL JSON type.

.. note::  :class:`_types.JSON`
   is provided as a facade for vendor-specific
   JSON types.  Since it supports JSON SQL operations, it only
   works on backends that have an actual JSON type, currently:

   * PostgreSQL - see :class:`sqlalchemy.dialects.postgresql.JSON` and
     :class:`sqlalchemy.dialects.postgresql.JSONB` for backend-specific
     notes

   * MySQL - see
     :class:`sqlalchemy.dialects.mysql.JSON` for backend-specific notes

   * SQLite as of version 3.9 - see
     :class:`sqlalchemy.dialects.sqlite.JSON` for backend-specific notes

   * Microsoft SQL Server 2016 and later - see
     :class:`sqlalchemy.dialects.mssql.JSON` for backend-specific notes

:class:`_types.JSON` is part of the Core in support of the growing
popularity of native JSON datatypes.

The :class:`_types.JSON` type stores arbitrary JSON format data, e.g.::

    data_table = Table(
        "data_table",
        metadata,
        Column("id", Integer, primary_key=True),
        Column("data", JSON),
    )

    with engine.connect() as conn:
        conn.execute(
            data_table.insert(), {"data": {"key1": "value1", "key2": "value2"}}
        )

**JSON-Specific Expression Operators**

The :class:`_types.JSON`
datatype provides these additional SQL operations:

* Keyed index operations::

    data_table.c.data["some key"]

* Integer index operations::

    data_table.c.data[3]

* Path index operations::

    data_table.c.data[("key_1", "key_2", 5, ..., "key_n")]

* Data casters for specific JSON element types, subsequent to an index
  or path operation being invoked::

    data_table.c.data["some key"].as_integer()

  .. versionadded:: 1.3.11

Additional operations may be available from the dialect-specific versions
of :class:`_types.JSON`, such as
:class:`sqlalchemy.dialects.postgresql.JSON` and
:class:`sqlalchemy.dialects.postgresql.JSONB` which both offer additional
PostgreSQL-specific operations.

**Casting JSON Elements to Other Types**

Index operations, i.e. those invoked by calling upon the expression using
the Python bracket operator as in ``some_column['some key']``, return an
expression object whose type defaults to :class:`_types.JSON` by default,
so that
further JSON-oriented instructions may be called upon the result type.
However, it is likely more common that an index operation is expected
to return a specific scalar element, such as a string or integer.  In
order to provide access to these elements in a backend-agnostic way,
a series of data casters are provided:

* :meth:`.JSON.Comparator.as_string` - return the element as a string

* :meth:`.JSON.Comparator.as_boolean` - return the element as a boolean

* :meth:`.JSON.Comparator.as_float` - return the element as a float

* :meth:`.JSON.Comparator.as_integer` - return the element as an integer

These data casters are implemented by supporting dialects in order to
assure that comparisons to the above types will work as expected, such as::

    # integer comparison
    data_table.c.data["some_integer_key"].as_integer() == 5

    # boolean comparison
    data_table.c.data["some_boolean"].as_boolean() == True

.. versionadded:: 1.3.11 Added type-specific casters for the basic JSON
   data element types.

.. note::

    The data caster functions are new in version 1.3.11, and supersede
    the previous documented approaches of using CAST; for reference,
    this looked like::

       from sqlalchemy import cast, type_coerce
       from sqlalchemy import String, JSON

       cast(data_table.c.data["some_key"], String) == type_coerce(55, JSON)

    The above case now works directly as::

        data_table.c.data["some_key"].as_integer() == 5

    For details on the previous comparison approach within the 1.3.x
    series, see the documentation for SQLAlchemy 1.2 or the included HTML
    files in the doc/ directory of the version's distribution.

**Detecting Changes in JSON columns when using the ORM**

The :class:`_types.JSON` type, when used with the SQLAlchemy ORM, does not
detect in-place mutations to the structure.  In order to detect these, the
:mod:`sqlalchemy.ext.mutable` extension must be used, most typically
using the :class:`.MutableDict` class.  This extension will
allow "in-place" changes to the datastructure to produce events which
will be detected by the unit of work.  See the example at :class:`.HSTORE`
for a simple example involving a dictionary.

Alternatively, assigning a JSON structure to an ORM element that
replaces the old one will always trigger a change event.

**Support for JSON null vs. SQL NULL**

When working with NULL values, the :class:`_types.JSON` type recommends the
use of two specific constants in order to differentiate between a column
that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string of
``"null"``. To insert or select against a value that is SQL NULL, use the
constant :func:`.null`. This symbol may be passed as a parameter value
specifically when using the :class:`_types.JSON` datatype, which contains
special logic that interprets this symbol to mean that the column value
should be SQL NULL as opposed to JSON ``"null"``::

    from sqlalchemy import null

    conn.execute(table.insert(), {"json_value": null()})

To insert or select against a value that is JSON ``"null"``, use the
constant :attr:`_types.JSON.NULL`::

    conn.execute(table.insert(), {"json_value": JSON.NULL})

The :class:`_types.JSON` type supports a flag
:paramref:`_types.JSON.none_as_null` which when set to True will result
in the Python constant ``None`` evaluating to the value of SQL
NULL, and when set to False results in the Python constant
``None`` evaluating to the value of JSON ``"null"``.    The Python
value ``None`` may be used in conjunction with either
:attr:`_types.JSON.NULL` and :func:`.null` in order to indicate NULL
values, but care must be taken as to the value of the
:paramref:`_types.JSON.none_as_null` in these cases.

**Customizing the JSON Serializer**

The JSON serializer and deserializer used by :class:`_types.JSON`
defaults to
Python's ``json.dumps`` and ``json.loads`` functions; in the case of the
psycopg2 dialect, psycopg2 may be using its own custom loader function.

In order to affect the serializer / deserializer, they are currently
configurable at the :func:`_sa.create_engine` level via the
:paramref:`_sa.create_engine.json_serializer` and
:paramref:`_sa.create_engine.json_deserializer` parameters.  For example,
to turn off ``ensure_ascii``::

    engine = create_engine(
        "sqlite://",
        json_serializer=lambda obj: json.dumps(obj, ensure_ascii=False),
    )

.. versionchanged:: 1.3.7

    SQLite dialect's ``json_serializer`` and ``json_deserializer``
    parameters renamed from ``_json_serializer`` and
    ``_json_deserializer``.

.. seealso::

    :class:`sqlalchemy.dialects.postgresql.JSON`

    :class:`sqlalchemy.dialects.postgresql.JSONB`

    :class:`sqlalchemy.dialects.mysql.JSON`

    :class:`sqlalchemy.dialects.sqlite.JSON`

Constructeur(s)

Signature du constructeur	Description
__init__(self, none_as_null: 'bool' = False)	Construct a :class:`_types.JSON` type. [extrait de __init__.__doc__]

Attributs statiques hérités de la classe TypeEngine

should_evaluate_none

Liste des opérateurs

Opérateurs hérités de la classe object

__eq__, __ge__, __gt__, __le__, __lt__, __ne__

Méthodes héritées de la classe TypeEngine

__repr__, __str__, __subclasshook__, adapt, as_generic, bind_expression, coerce_compared_value, column_expression, compare_values, compile, copy, copy_value, dialect_impl, evaluates_none, get_dbapi_type, literal_processor, with_variant

Méthodes héritées de la classe Generic

__class_getitem__, __init_subclass__

Méthodes héritées de la classe Visitable

__class_getitem__, __init_subclass__

Méthodes héritées de la classe object

__delattr__, __dir__, __format__, __getattribute__, __getstate__, __hash__, __reduce__, __reduce_ex__, __setattr__, __sizeof__

Vous êtes un professionnel et vous avez besoin d'une formation ? Calcul scientifique
avec Python Voir le programme détaillé