PostgreSQL – Documentation SQLAlchemy 1.3 – Bien choisir son serveur d impression

Prise en charge de la base de données PostgreSQL.

Prise en charge DBAPI

Les options dialect / DBAPI suivantes sont disponibles. Veuillez vous reporter aux sections individuelles de DBAPI pour obtenir des informations sur la connexion.

INSERT / UPDATE… RETOURNER

Le dialecte supporte les PG 8.2 INSERT..RECLINANT, MISE À JOUR..RECLINANT et
SUPPRIMER .. RETOURNER syntaxes. INSERT..RECLINANT est utilisé par défaut
pour les instructions INSERT à une seule ligne afin d'extraire les données nouvellement générées
identificateurs de clé primaire. Pour spécifier un explicite RETOUR clause,
Utilisez le _UpdateBase.returning () méthode par déclaration:

# INSERT..RETURNING
résultat = table.insérer().rentrant(table.c.col1, table.c.col2).
    valeurs(Nom='foo')
impression résultat.fetchall()

# UPDATE..RETURNING
résultat = table.mise à jour().rentrant(table.c.col1, table.c.col2).
    où(table.c.Nom=='foo').valeurs(Nom='bar')
impression résultat.fetchall()

# DELETE..RETURNING
résultat = table.effacer().rentrant(table.c.col1, table.c.col2).
    où(table.c.Nom=='foo')
impression résultat.fetchall()

DE SEULEMENT…

Le dialecte prend en charge le mot clé ONLY de PostgreSQL pour ne cibler que certains
table dans une hiérarchie d'héritage. Ceci peut être utilisé pour produire le
SÉLECTIONNER ... DE SEULEMENT, MISE À JOUR SEULEMENT ..., et EFFACER DE SEULEMENT ...

syntaxes. Il utilise le mécanisme des astuces de SQLAlchemy:

# SELECTIONNER ... A PARTIR DE ...
résultat = table.sélectionner().avec_hint(table, 'SEULEMENT', 'postgresql')
impression résultat.fetchall()

# MISE À JOUR UNIQUEMENT ...
table.mise à jour(valeurs=dict(foo='bar')).avec_hint('SEULEMENT',
                                               nom du dialecte='postgresql')

# SUPPRIMER DE SEULEMENT ...
table.effacer().avec_hint('SEULEMENT', nom du dialecte='postgresql')

Options de réflexion spéciales

le Inspecteur utilisé pour le backend PostgreSQL est une instance
de PGInspector, qui offre des méthodes supplémentaires:

de sqlalchemy importation create_engine, inspecter

moteur = create_engine("postgresql + psycopg2: // localhost / test")
insp = inspecter(moteur)  # sera un PGInspector

impression(insp.get_enums())

classe sqlalchemy.dialects.postgresql.base.PGInspector(Connecticut)

Bases: sqlalchemy.engine.reflection.Inspector

get_enums(schéma = Aucun)

Retourne une liste d'objets ENUM.

Chaque membre est un dictionnaire contenant ces champs:

• name – nom de l'énum

• schéma – le nom du schéma pour l'énumération.

• visible – booléen, que cette énumération soit visible ou non
  dans le chemin de recherche par défaut.

• étiquettes – une liste d'étiquettes de chaîne qui s'appliquent à l'énumération.

Paramètres

schéma – nom du schéma. Si aucun, le schéma par défaut
(généralement «public») est utilisé. Peut également être réglé sur '*' pour
indiquez des énumérations de charge pour tous les schémas.

get_foreign_table_names(schéma = Aucun)

Renvoie une liste de noms FOREIGN TABLE.

Le comportement est similaire à celui de Inspector.get_table_names (),
sauf que la liste est limitée aux tables qui signalent une
relâchement valeur de F.

get_table_oid(nom de la table, schéma = Aucun)

Renvoie l'OID du nom de la table donnée.

get_view_names(schéma = Aucun, include = ('plain', 'matérialisé'))

Renvoyer tous les noms de vue dans schéma.

Paramètres

• schéma – Facultatif, récupérez les noms d'un schéma autre que celui par défaut.
  Pour les devis spéciaux, utilisez quoted_name.

• comprendre –

  spécifier les types de vues à renvoyer. Passé
  sous forme de valeur de chaîne (pour un type unique) ou de tuple (pour un nombre quelconque)
  de types). Par défaut à ('plaine', 'matérialisé').

Types de tableau

Le dialecte PostgreSQL supporte les tableaux, à la fois en tant que types de colonne multidimensionnels
ainsi que des littéraux de tableau:

Types JSON

Le dialecte PostgreSQL prend en charge les types de données JSON et JSONB, y compris
Le support natif de psycopg2 et celui de tous les logiciels spéciaux de PostgreSQL
les opérateurs:

Type HSTORE

Le type HSTORE PostgreSQL ainsi que les littéraux hstore sont pris en charge:

Types ENUM

PostgreSQL a une structure TYPE pouvant être créée indépendamment qui est utilisée
pour implémenter un type énuméré. Cette approche introduit des
la complexité du côté SQLAlchemy en termes de quand ce type devrait être
Créé et abandonné. Le type object est aussi un reflet indépendant
entité. Les sections suivantes doivent être consultées:

de sqlalchemy importation TypeDécorateur
de sqlalchemy.dialects.postgresql importation Tableau

classe ArrayOfEnum(TypeDécorateur):
    impl = Tableau

    def bind_expression(soi, bindvalue):
        revenir sa.jeter(bindvalue, soi)

    def result_processor(soi, dialecte, coltype):
        super_rp = super(ArrayOfEnum, soi).result_processor(
            dialecte, coltype)

        def handle_raw_string(valeur):
            interne = ré.rencontre(r"^ (. *) $", valeur).groupe(1)
            revenir interne.Divisé(",") si interne autre []

        def processus(valeur):
            si valeur est Aucun:
                revenir Aucun
            revenir super_rp(handle_raw_string(valeur))
        revenir processus

Table(
    'mes données', métadonnées,
    Colonne('id', Entier, clé primaire=Vrai),
    Colonne('Les données', ArrayOfEnum(ENUM('une', 'b'c', nom ='myenum')))

)

classe CastingArray(Tableau):
    def bind_expression(soi, bindvalue):
        revenir sa.jeter(bindvalue, soi)

Table(
    'mes données', métadonnées,
    Colonne('id', Entier, clé primaire=Vrai),
    Colonne('Les données', CastingArray(JSONB))
)

Types de données PostgreSQL

Comme avec tous les dialectes SQLAlchemy, tous les types UPPERCASE connus pour être
valables avec PostgreSQL sont importables à partir du dialecte de niveau supérieur, que ce soit
ils proviennent de sqlalchemy.types ou du dialecte local:

de sqlalchemy.dialects.postgresql importation 
    Tableau, BIGINT, BIT, BOOLÉAN, BYTEA, CARBONISER, CIDR, DATE, 
    DOUBLE PRECISION, ENUM, FLOTTE, HSTORE, INET, ENTIER, 
    INTERVALLE, JSON, JSONB, MACADDR, ARGENT, NUMERIC, OID, REAL, SMALLINT, TEXT, 
    TEMPS, TIMESTAMP, UUID, VARCHAR, INT4RANGE, INT8RANGE, NUMRANGE, 
    DATERANGE, TSRANGE, TSTZRANGE, TSVECTOR

Types which are specific to PostgreSQL, or have PostgreSQL-specific
construction arguments, are as follows:

class sqlalchemy.dialects.postgresql.aggregate_order_by(cible, *order_by)

Bases: sqlalchemy.sql.expression.ColumnElement

Represent a PostgreSQL aggregate order by expression.

E.g.:

de sqlalchemy.dialects.postgresql importation aggregate_order_by
expr = func.array_agg(aggregate_order_by(table.c.une, table.c.b.desc()))
stmt = sélectionner([[[[expr])

would represent the expression:

SELECT array_agg(une ORDER BY b DESC) FROM table;

Similarly:

expr = func.string_agg(
    table.c.une,
    aggregate_order_by(literal_column("','"), table.c.une)
)
stmt = sélectionner([[[[expr])

Would represent:

SELECT string_agg(une, ',' ORDER BY une) FROM table;

Changed in version 1.2.13: – the ORDER BY argument may be multiple terms

class sqlalchemy.dialects.postgresql.array(clauses, **kw)

Bases: sqlalchemy.sql.expression.Tuple

A PostgreSQL ARRAY literal.

This is used to produce ARRAY literals in SQL expressions, e.g.:

de sqlalchemy.dialects.postgresql importation array
de sqlalchemy.dialects importation postgresql
de sqlalchemy importation sélectionner, func

stmt = sélectionner([[[[
                array([[[[1,2]) + array([[[[3,4,5])
            ])

impression(stmt.compile(dialect=postgresql.dialect()))

Produces the SQL:

SELECT ARRAY[[[[%(param_1)s, %(param_2)s] ||
    ARRAY[[[[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1

An instance of array will always have the datatype
ARRAY. The “inner” type of the array is inferred from
the values present, unless the type_ keyword argument is passed:

array([[[['foo', 'bar'], type_=CHAR)

Multidimensional arrays are produced by nesting array constructs.
The dimensionality of the final ARRAY type is calculated by
recursively adding the dimensions of the inner ARRAY type:

stmt = sélectionner([[[[
    array([[[[
        array([[[[1, 2]), array([[[[3, 4]), array([[[[colonne('q'), colonne('x')])
    ])
])
impression(stmt.compile(dialect=postgresql.dialect()))

Produces:

SELECT ARRAY[[[[ARRAY[[[[%(param_1)s, %(param_2)s],
ARRAY[[[[%(param_3)s, %(param_4)s], ARRAY[[[[q, x]] AS anon_1

New in version 1.3.6: added support for multidimensional array literals

class sqlalchemy.dialects.postgresql.ARRAY(item_type, as_tuple=False, dimensions=None, zero_indexes=False)

Bases: sqlalchemy.types.ARRAY

PostgreSQL ARRAY type.

le postgresql.ARRAY type is constructed in the same way
as the core types.ARRAY type; a member type is required, and a
number of dimensions is recommended if the type is to be used for more
than one dimension:

de sqlalchemy.dialects importation postgresql

mytable = Table("mytable", metadata,
        Column("data", postgresql.ARRAY(Integer, dimensions=2))
    )

le postgresql.ARRAY type provides all operations defined on the
core types.ARRAY type, including support for “dimensions”,
indexed access, and simple matching such as
types.ARRAY.Comparator.any() et
types.ARRAY.Comparator.all(). postgresql.ARRAY class also
provides PostgreSQL-specific methods for containment operations, including
postgresql.ARRAY.Comparator.contains()
postgresql.ARRAY.Comparator.contained_by(), et
postgresql.ARRAY.Comparator.overlap(), e.g.:

mytable.c.Les données.contient([[[[1, 2])

le postgresql.ARRAY type may not be supported on all
PostgreSQL DBAPIs; it is currently known to work on psycopg2 only.

De plus, le postgresql.ARRAY type does not work directly in
conjunction with the ENUM type. For a workaround, see the
special type at Using ENUM with ARRAY.

class Comparator(expr)

Bases: sqlalchemy.types.Comparator

Define comparison operations for ARRAY.

Note that these operations are in addition to those provided
by the base types.ARRAY.Comparator class, including
types.ARRAY.Comparator.any() et
types.ARRAY.Comparator.all().

contained_by(other)

Boolean expression. Test if elements are a proper subset of the
elements of the argument array expression.

contient(other, **kwargs)

Boolean expression. Test if elements are a superset of the
elements of the argument array expression.

overlap(other)

Boolean expression. Test if array has elements in common with
an argument array expression.

__init__(item_type, as_tuple=False, dimensions=None, zero_indexes=False)

Construct an ARRAY.

E.g.:

Column('myarray', ARRAY(Integer))

Arguments are:

Paramètres

• item_type – The data type of items of this array. Note that
  dimensionality is irrelevant here, so multi-dimensional arrays like
  INTEGER[][], are constructed as ARRAY(Integer), not as
  ARRAY(ARRAY(Integer)) or such.

• as_tuple=False – Specify whether return results
  should be converted to tuples from lists. DBAPIs such
  as psycopg2 return lists by default. When tuples are
  returned, the results are hashable.

• dimensions – if non-None, the ARRAY will assume a fixed
  number of dimensions. This will cause the DDL emitted for this
  ARRAY to include the exact number of bracket clauses [],
  and will also optimize the performance of the type overall.
  Note that PG arrays are always implicitly “non-dimensioned”,
  meaning they can store any number of dimensions no matter how
  they were declared.

• zero_indexes=False –

  when True, index values will be converted
  between Python zero-based and PostgreSQL one-based indexes, e.g.
  a value of one will be added to all index values before passing
  to the database.

sqlalchemy.dialects.postgresql.array_agg(*arg, **kw)

PostgreSQL-specific form of array_agg, ensures
return type is postgresql.ARRAY and not
the plain types.ARRAY, unless an explicit type_

is passed.

sqlalchemy.dialects.postgresql.Any(other, arrexpr, operator=)

A synonym for the ARRAY.Comparator.any() method.

This method is legacy and is here for backwards-compatibility.

sqlalchemy.dialects.postgresql.Tout(other, arrexpr, operator=)

A synonym for the ARRAY.Comparator.all() method.

This method is legacy and is here for backwards-compatibility.

class sqlalchemy.dialects.postgresql.BIT(length=None, varying=False)

Bases: sqlalchemy.types.TypeEngine

class sqlalchemy.dialects.postgresql.BYTEA(length=None)

Bases: sqlalchemy.types.LargeBinary

__init__(length=None)

Construct a LargeBinary type.

Paramètres

length – optional, a length for the column for use in
DDL statements, for those binary types that accept a length,
such as the MySQL BLOB type.

class sqlalchemy.dialects.postgresql.CIDR

Bases: sqlalchemy.types.TypeEngine

class sqlalchemy.dialects.postgresql.DOUBLE_PRECISION(precision=None, asdecimal=False, decimal_return_scale=None)

Bases: sqlalchemy.types.Float

__init__(precision=None, asdecimal=False, decimal_return_scale=None)

Construct a Float.

Paramètres

• précision – the numeric precision for use in DDL CREATE
TABLE.

• asdecimal – the same flag as that of Numeric, but
  defaults to Faux. Note that setting this flag to True

  results in floating point conversion.

• decimal_return_scale –

  Default scale to use when converting
  from floats to Python decimals. Floating point values will typically
  be much longer due to decimal inaccuracy, and most floating point
  database types don’t have a notion of “scale”, so by default the
  float type looks for the first ten decimal places when converting.
  Specifying this value will override that length. Note that the
  MySQL float types, which do include “scale”, will use “scale”
  as the default for decimal_return_scale, if not otherwise specified.

class sqlalchemy.dialects.postgresql.ENUM(*enums, **kw)

Bases: sqlalchemy.types.NativeForEmulated, sqlalchemy.types.Enum

PostgreSQL ENUM type.

This is a subclass of types.Enum which includes
support for PG’s CREATE TYPE et DROP TYPE.

When the builtin type types.Enum is used and the
Enum.native_enum flag is left at its default of
True, the PostgreSQL backend will use a postgresql.ENUM

type as the implementation, so the special create/drop rules
will be used.

The create/drop behavior of ENUM is necessarily intricate, due to the
awkward relationship the ENUM type has in relationship to the
parent table, in that it may be “owned” by just a single table, or
may be shared among many tables.

When using types.Enum ou postgresql.ENUM

in an “inline” fashion, the CREATE TYPE et DROP TYPE is emitted
corresponding to when the Table.create() et Table.drop()

methods are called:

table = Table('sometable', metadata,
    Column('some_enum', ENUM('a', 'b', 'c', name='myenum'))
)

table.create(engine)  # will emit CREATE ENUM and CREATE TABLE
table.drop(engine)  # will emit DROP TABLE and DROP ENUM

To use a common enumerated type between multiple tables, the best
practice is to declare the types.Enum ou
postgresql.ENUM independently, and associate it with the
MetaData object itself:

my_enum = ENUM('a', 'b', 'c', name='myenum', metadata=metadata)

t1 = Table('sometable_one', metadata,
    Column('some_enum', myenum)
)

t2 = Table('sometable_two', metadata,
    Column('some_enum', myenum)
)

When this pattern is used, care must still be taken at the level
of individual table creates. Emitting CREATE TABLE without also
specifying checkfirst=True will still cause issues:

t1.create(engine) # will fail: no such type 'myenum'

If we specify checkfirst=True, the individual table-level create
operation will check for the ENUM and create if not exists:

# will check if enum exists, and emit CREATE TYPE if not
t1.create(engine, checkfirst=True)

When using a metadata-level ENUM type, the type will always be created
and dropped if either the metadata-wide create/drop is called:

metadata.create_all(engine)  # will emit CREATE TYPE
metadata.drop_all(engine)  # will emit DROP TYPE

The type can also be created and dropped directly:

my_enum.create(engine)
my_enum.drop(engine)

Changed in version 1.0.0: The PostgreSQL postgresql.ENUM type
now behaves more strictly with regards to CREATE/DROP. A metadata-level
ENUM type will only be created and dropped at the metadata level,
not the table level, with the exception of
table.create(checkfirst=True).
le table.drop() call will now emit a DROP TYPE for a table-level
enumerated type.

__init__(*enums, **kw)

Construct an ENUM.

Arguments are the same as that of
types.Enum, but also including
the following parameters.

Paramètres

create_type – Defaults to True.
Indicates that CREATE TYPE should be
emitted, after optionally checking for the
presence of the type, when the parent
table is being created; and additionally
cette DROP TYPE is called when the table
is dropped. Quand Faux, no check
will be performed and no CREATE TYPE

ou DROP TYPE is emitted, unless
create()

ou drop()

are called directly.
Setting to Faux is helpful
when invoking a creation scheme to a SQL file
without access to the actual database –
le create() et
drop() methods can
be used to emit SQL to a target bind.

create(bind=None, checkfirst=True)

Émettre CREATE TYPE for this
ENUM.

If the underlying dialect does not support
PostgreSQL CREATE TYPE, no action is taken.

Paramètres

• bind – a connectable Moteur,
  Connection, or similar object to emit
  SQL.

• checkfirst – if True, a query against
  the PG catalog will be first performed to see
  if the type does not exist already before
  creating.

drop(bind=None, checkfirst=True)

Émettre DROP TYPE for this
ENUM.

If the underlying dialect does not support
PostgreSQL DROP TYPE, no action is taken.

Paramètres

• bind – a connectable Moteur,
  Connection, or similar object to emit
  SQL.

• checkfirst – if True, a query against
  the PG catalog will be first performed to see
  if the type actually exists before dropping.

class sqlalchemy.dialects.postgresql.HSTORE(text_type=None)

Bases: sqlalchemy.types.Indexable, sqlalchemy.types.Concatenable, sqlalchemy.types.TypeEngine

Represent the PostgreSQL HSTORE type.

le HSTORE type stores dictionaries containing strings, e.g.:

data_table = Table('data_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', HSTORE)
)

avec engine.connect() comme conn:
    conn.execute(
        data_table.insérer(),
        Les données = "key1": "value1", "key2": "value2"
    )

HSTORE provides for a wide range of operations, including:

• Index operations:

  data_table.c.Les données[[[['some key'] == 'some value'

• Containment operations:

  data_table.c.Les données.has_key('some key')
  
  data_table.c.Les données.has_all([[[['one', 'two', 'three'])

• Concatenation:

  data_table.c.Les données + "k1": "v1"

For a full list of special methods see
HSTORE.comparator_factory.

For usage with the SQLAlchemy ORM, it may be desirable to combine
the usage of HSTORE avec MutableDict dictionary
now part of the sqlalchemy.ext.mutable

extension. This extension will allow “in-place” changes to the
dictionary, e.g. addition of new keys or replacement/removal of existing
keys to/from the current dictionary, to produce events which will be
detected by the unit of work:

de sqlalchemy.ext.mutable importation MutableDict

class MyClass(Base):
    __tablename__ = 'data_table'

    identifiant = Column(Integer, primary_key=True)
    Les données = Column(MutableDict.as_mutable(HSTORE))

my_object = session.query(MyClass).un()

# in-place mutation, requires Mutable extension
# in order for the ORM to detect
my_object.Les données[[[['some_key'] = 'some value'

session.commit()

When the sqlalchemy.ext.mutable extension is not used, the ORM
will not be alerted to any changes to the contents of an existing
dictionary, unless that dictionary value is re-assigned to the
HSTORE-attribute itself, thus generating a change event.

Voir également

hstore – render the PostgreSQL hstore() une fonction.

class Comparator(expr)

Bases: sqlalchemy.types.Comparator, sqlalchemy.types.Comparator

Define comparison operations for HSTORE.

array()

Text array expression. Returns array of alternating keys and
valeurs.

contained_by(other)

Boolean expression. Test if keys are a proper subset of the
keys of the argument jsonb expression.

contient(other, **kwargs)

Boolean expression. Test if keys (or array) are a superset
of/contained the keys of the argument jsonb expression.

defined(clé)

Boolean expression. Test for presence of a non-NULL value for
the key. Note that the key may be a SQLA expression.

effacer(clé)

HStore expression. Returns the contents of this hstore with the
given key deleted. Note that the key may be a SQLA expression.

has_all(other)

Boolean expression. Test for presence of all keys in jsonb

has_any(other)

Boolean expression. Test for presence of any key in jsonb

has_key(other)

Boolean expression. Test for presence of a key. Note that the
key may be a SQLA expression.

keys()

Text array expression. Returns array of keys.

matrix()

Text array expression. Returns array of [key, value] pairs.

slice(array)

HStore expression. Returns a subset of an hstore defined by
array of keys.

vals()

Text array expression. Returns array of values.

__init__(text_type=None)

Construct a new HSTORE.

Paramètres

text_type –

the type that should be used for indexed values.
Defaults to types.Text.

bind_processor(dialect)

Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter value
as the sole positional argument and will return a value to
send to the DB-API.

If processing is not necessary, the method should return None.

Paramètres

dialect – Dialect instance in use.

comparator_factory

alias of HSTORE.Comparator

result_processor(dialect, coltype)

Return a conversion function for processing result row values.

Returns a callable which will receive a result row column
value as the sole positional argument and will return a value
to return to the user.

If processing is not necessary, the method should return None.

Paramètres

class sqlalchemy.dialects.postgresql.hstore(*args, **kwargs)

Bases: sqlalchemy.sql.functions.GenericFunction

Construct an hstore value within a SQL expression using the
PostgreSQL hstore() une fonction.

le hstore function accepts one or two arguments as described
in the PostgreSQL documentation.

E.g.:

de sqlalchemy.dialects.postgresql importation array, hstore

sélectionner([[[[hstore('key1', 'value1')])

sélectionner([[[[
        hstore(
            array([[[['key1', 'key2', 'key3']),
            array([[[['value1', 'value2', 'value3'])
        )
    ])

Voir également

HSTORE – the PostgreSQL HSTORE datatype.

type

alias of HSTORE

class sqlalchemy.dialects.postgresql.INET

Bases: sqlalchemy.types.TypeEngine

class sqlalchemy.dialects.postgresql.INTERVAL(precision=None, fields=None)

Bases: sqlalchemy.types.NativeForEmulated, sqlalchemy.types._AbstractInterval

PostgreSQL INTERVAL type.

The INTERVAL type may not be supported on all DBAPIs.
It is known to work on psycopg2 and not pg8000 or zxjdbc.

__init__(precision=None, fields=None)

Construct an INTERVAL.

Paramètres

• précision – optional integer precision value

• fields –

  string fields specifier. allows storage of fields
  to be limited, such as "YEAR", "MONTH", "DAY TO HOUR",
  etc.

class sqlalchemy.dialects.postgresql.JSON(none_as_null=False, astext_type=None)

Bases: sqlalchemy.types.JSON

Represent the PostgreSQL JSON type.

This type is a specialization of the Core-level types.JSON

type. Be sure to read the documentation for types.JSON for
important tips regarding treatment of NULL values and ORM use.

The operators provided by the PostgreSQL version of JSON

include:

• Index operations (the -> operator):

  data_table.c.Les données[[[['some key']
  
  data_table.c.Les données[[[[5]

• Index operations returning text (the ->> operator):

  data_table.c.Les données[[[['some key'].astext == 'some value'

• Index operations with CAST
  (equivalent to CAST(col ->> ['some['some['some['some key'] AS )):

  data_table.c.Les données[[[['some key'].astext.jeter(Integer) == 5

• Path index operations (the #> operator):

  data_table.c.Les données[([([([('key_1', 'key_2', 5, ..., 'key_n')]

• Path index operations returning text (the #>> operator):

  data_table.c.Les données[([([([('key_1', 'key_2', 5, ..., 'key_n')].astext == 'some value'

Changed in version 1.1: le ColumnElement.cast() operator on
JSON objects now requires that the JSON.Comparator.astext

modifier be called explicitly, if the cast works only from a textual
string.

Index operations return an expression object whose type defaults to
JSON by default, so that further JSON-oriented instructions
may be called upon the result type.

Custom serializers and deserializers are specified at the dialect level,
that is using create_engine(). The reason for this is that when
using psycopg2, the DBAPI only allows serializers at the per-cursor
or per-connection level. E.g.:

engine = create_engine("postgresql://scott:tiger@localhost/test",
                        json_serializer=my_serialize_fn,
                        json_deserializer=my_deserialize_fn
                )

When using the psycopg2 dialect, the json_deserializer is registered
against the database using psycopg2.extras.register_default_json.

class Comparator(expr)

Bases: sqlalchemy.types.Comparator

Define comparison operations for JSON.

property astext

On an indexed expression, use the “astext” (e.g. “->>”)
conversion when rendered in SQL.

E.g.:

sélectionner([[[[data_table.c.Les données[[[['some key'].astext])

__init__(none_as_null=False, astext_type=None)

Construct a JSON type.

Paramètres

• none_as_null –

  if True, persist the value None as a
  SQL NULL value, not the JSON encoding of nul. Note that
  when this flag is False, the null() construct can still
  be used to persist a NULL value:

  de sqlalchemy importation nul
  conn.execute(table.insérer(), Les données=nul())

  Changed in version 0.9.8: – Added none_as_null, et null()

  is now supported in order to persist a NULL value.

• astext_type –

  the type to use for the
  JSON.Comparator.astext

  accessor on indexed attributes. Defaults to types.Text.

comparator_factory

alias of JSON.Comparator

class sqlalchemy.dialects.postgresql.JSONB(none_as_null=False, astext_type=None)

Bases: sqlalchemy.dialects.postgresql.json.JSON

Represent the PostgreSQL JSONB type.

le JSONB type stores arbitrary JSONB format data, e.g.:

data_table = Table('data_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', JSONB)
)

avec engine.connect() comme conn:
    conn.execute(
        data_table.insérer(),
        Les données = "key1": "value1", "key2": "value2"
    )

le JSONB type includes all operations provided by
JSON, including the same behaviors for indexing operations.
It also adds additional operators specific to JSONB, including
JSONB.Comparator.has_key(), JSONB.Comparator.has_all(),
JSONB.Comparator.has_any(), JSONB.Comparator.contains(),
et JSONB.Comparator.contained_by().

Comme le JSON type, the JSONB type does not detect
in-place changes when used with the ORM, unless the
sqlalchemy.ext.mutable extension is used.

Custom serializers and deserializers
are shared with the JSON class, using the json_serializer

et json_deserializer keyword arguments. These must be specified
at the dialect level using create_engine(). When using
psycopg2, the serializers are associated with the jsonb type using
psycopg2.extras.register_default_jsonb on a per-connection basis,
in the same way that psycopg2.extras.register_default_json is used
to register these handlers with the json type.

class Comparator(expr)

Bases: sqlalchemy.dialects.postgresql.json.Comparator

Define comparison operations for JSON.

contained_by(other)

Boolean expression. Test if keys are a proper subset of the
keys of the argument jsonb expression.

contient(other, **kwargs)

Boolean expression. Test if keys (or array) are a superset
of/contained the keys of the argument jsonb expression.

has_all(other)

Boolean expression. Test for presence of all keys in jsonb

has_any(other)

Boolean expression. Test for presence of any key in jsonb

has_key(other)

Boolean expression. Test for presence of a key. Note that the
key may be a SQLA expression.

comparator_factory

alias of JSONB.Comparator

class sqlalchemy.dialects.postgresql.MACADDR

Bases: sqlalchemy.types.TypeEngine

class sqlalchemy.dialects.postgresql.ARGENT

Bases: sqlalchemy.types.TypeEngine

Provide the PostgreSQL MONEY type.

class sqlalchemy.dialects.postgresql.OID

Bases: sqlalchemy.types.TypeEngine

Provide the PostgreSQL OID type.

class sqlalchemy.dialects.postgresql.REAL(precision=None, asdecimal=False, decimal_return_scale=None)

Bases: sqlalchemy.types.Float

The SQL REAL type.

__init__(precision=None, asdecimal=False, decimal_return_scale=None)

Construct a Float.

Paramètres

• précision – the numeric precision for use in DDL CREATE
TABLE.

• asdecimal – the same flag as that of Numeric, but
  defaults to Faux. Note that setting this flag to True

  results in floating point conversion.

• decimal_return_scale –

  Default scale to use when converting
  from floats to Python decimals. Floating point values will typically
  be much longer due to decimal inaccuracy, and most floating point
  database types don’t have a notion of “scale”, so by default the
  float type looks for the first ten decimal places when converting.
  Specifying this value will override that length. Note that the
  MySQL float types, which do include “scale”, will use “scale”
  as the default for decimal_return_scale, if not otherwise specified.

class sqlalchemy.dialects.postgresql.REGCLASS

Bases: sqlalchemy.types.TypeEngine

Provide the PostgreSQL REGCLASS type.

class sqlalchemy.dialects.postgresql.TSVECTOR

Bases: sqlalchemy.types.TypeEngine

le postgresql.TSVECTOR type implements the PostgreSQL
text search type TSVECTOR.

It can be used to do full text queries on natural language
documents.

class sqlalchemy.dialects.postgresql.UUID(as_uuid=False)

Bases: sqlalchemy.types.TypeEngine

PostgreSQL UUID type.

Represents the UUID column type, interpreting
data either as natively returned by the DBAPI
or as Python uuid objects.

The UUID type may not be supported on all DBAPIs.
It is known to work on psycopg2 and not pg8000.

__init__(as_uuid=False)

Construct a UUID type.

Paramètres

as_uuid=False – if True, values will be interpreted
as Python uuid objects, converting to/from string via the
DBAPI.

de psycopg2.extras importation DateTimeRange
de sqlalchemy.dialects.postgresql importation TSRANGE

class RoomBooking(Base):

    __tablename__ = 'room_booking'

    room = Column(Integer(), primary_key=True)
    pendant = Column(TSRANGE())

booking = RoomBooking(
    room=101,
    pendant=DateTimeRange(datetime(2013, 3, 23), None)
)

PostgreSQL Constraint Types

SQLAlchemy supports PostgreSQL EXCLUDE constraints via the
ExcludeConstraint class:

class sqlalchemy.dialects.postgresql.ExcludeConstraint(*elements, **kw)

Bases: sqlalchemy.schema.ColumnCollectionConstraint

A table-level EXCLUDE constraint.

Defines an EXCLUDE constraint as described in the postgres
documentation.

__init__(*elements, **kw)

Créé un ExcludeConstraint object.

E.g.:

const = ExcludeConstraint(
    (Column('period'), '&&'),
    (Column('group'), '='),
    where=(Column('group') != 'some group')
)

The constraint is normally embedded into the Table construct
directly, or added later using append_constraint():

some_table = Table(
    'some_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('period', TSRANGE()),
    Column('group', Chaîne)
)

some_table.append_constraint(
    ExcludeConstraint(
        (some_table.c.period, '&&'),
        (some_table.c.group, '='),
        where=some_table.c.group != 'some group',
        name='some_table_excl_const'
    )
)

Paramètres

• *elements – A sequence of two tuples of the form (column, operator) where
  “column” is a SQL expression element or a raw SQL string, most
  typically a Column object, and “operator” is a string
  containing the operator to use. In order to specify a column name
  when a Column object is not available, while ensuring
  that any necessary quoting rules take effect, an ad-hoc
  Column ou sql.expression.column() object should be
  used.

• name – Optional, the in-database name of this constraint.

• deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
  issuing DDL for this constraint.

• initialement – Optional string. If set, emit INITIALLY when issuing DDL
  for this constraint.

• using – Optional string. If set, emit USING when issuing DDL
  for this constraint. Defaults to ‘gist’.

• where –

  Optional SQL expression construct or literal SQL string.
  If set, emit WHERE

  when issuing DDL for this constraint.

  Attention

  le ExcludeConstraint.where argument to ExcludeConstraint can be passed as a Python string argument, which will be treated as trusted SQL text and rendered as given. DO NOT PASS UNTRUSTED INPUT TO THIS PARAMETER.

Par exemple:

de sqlalchemy.dialects.postgresql importation ExcludeConstraint, TSRANGE

class RoomBooking(Base):

    __tablename__ = 'room_booking'

    room = Column(Integer(), primary_key=True)
    pendant = Column(TSRANGE())

    __table_args__ = (
        ExcludeConstraint(('room', '='), ('during', '&&')),
    )

PostgreSQL DML Constructs

sqlalchemy.dialects.postgresql.dml.insérer(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

Construct a new Insert object.

This constructor is mirrored as a public API function; voir insert() for a full usage and argument description.

class sqlalchemy.dialects.postgresql.dml.Insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

Bases: sqlalchemy.sql.expression.Insert

PostgreSQL-specific implementation of INSERT.

Adds methods for PG-specific syntaxes such as ON CONFLICT.

excluded

Provide the excluded namespace for an ON CONFLICT statement

PG’s ON CONFLICT clause allows reference to the row that would
be inserted, known as excluded. This attribute provides
all columns in this row to be referenceable.

on_conflict_do_nothing(constraint=None, index_elements=None, index_where=None)

Specifies a DO NOTHING action for ON CONFLICT clause.

le constraint et index_elements arguments
are optional, but only one of these can be specified.

Paramètres

• constraint – The name of a unique or exclusion constraint on the table,
  or the constraint object itself if it has a .name attribute.

• index_elements – A sequence consisting of string column names, Column

  objects, or other column expression objects that will be used
  to infer a target index.

• index_where –

  Additional WHERE criterion that can be used to infer a
  conditional target index.

on_conflict_do_update(constraint=None, index_elements=None, index_where=None, set_=None, where=None)

Specifies a DO UPDATE SET action for ON CONFLICT clause.

Either the constraint ou index_elements argument is
required, but only one of these can be specified.

Paramètres

• constraint – The name of a unique or exclusion constraint on the table,
  or the constraint object itself if it has a .name attribute.

• index_elements – A sequence consisting of string column names, Column

  objects, or other column expression objects that will be used
  to infer a target index.

• index_where – Additional WHERE criterion that can be used to infer a
  conditional target index.

• set_ –

  Required argument. A dictionary or other mapping object
  with column names as keys and expressions or literals as values,
  specifying the SET actions to take.
  If the target Column specifies a “.key” attribute distinct
  from the column name, that key should be used.

  Attention

  This dictionary does ne pas take into account
  Python-specified default UPDATE values or generation functions,
  e.g. those specified using Column.onupdate.
  These values will not be exercised for an ON CONFLICT style of
  UPDATE, unless they are manually specified in the
  Insert.on_conflict_do_update.set_ dictionary.

• where –

  Optional argument. If present, can be a literal SQL
  string or an acceptable expression for a WHERE clause
  that restricts the rows affected by DO UPDATE SET. Rows
  not meeting the WHERE condition will not be updated
  (effectively a DO NOTHING for those rows).