Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/dml.py: 47%
72 statements
« prev ^ index » next coverage.py v7.2.7, created at 2023-06-07 06:35 +0000
1# postgresql/on_conflict.py
2# Copyright (C) 2005-2023 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8from . import ext
9from ... import util
10from ...sql import coercions
11from ...sql import roles
12from ...sql import schema
13from ...sql.base import _exclusive_against
14from ...sql.base import _generative
15from ...sql.base import ColumnCollection
16from ...sql.dml import Insert as StandardInsert
17from ...sql.elements import ClauseElement
18from ...sql.expression import alias
19from ...util.langhelpers import public_factory
22__all__ = ("Insert", "insert")
25class Insert(StandardInsert):
26 """PostgreSQL-specific implementation of INSERT.
28 Adds methods for PG-specific syntaxes such as ON CONFLICT.
30 The :class:`_postgresql.Insert` object is created using the
31 :func:`sqlalchemy.dialects.postgresql.insert` function.
33 .. versionadded:: 1.1
35 """
37 stringify_dialect = "postgresql"
38 inherit_cache = False
40 @util.memoized_property
41 def excluded(self):
42 """Provide the ``excluded`` namespace for an ON CONFLICT statement
44 PG's ON CONFLICT clause allows reference to the row that would
45 be inserted, known as ``excluded``. This attribute provides
46 all columns in this row to be referenceable.
48 .. tip:: The :attr:`_postgresql.Insert.excluded` attribute is an
49 instance of :class:`_expression.ColumnCollection`, which provides
50 an interface the same as that of the :attr:`_schema.Table.c`
51 collection described at :ref:`metadata_tables_and_columns`.
52 With this collection, ordinary names are accessible like attributes
53 (e.g. ``stmt.excluded.some_column``), but special names and
54 dictionary method names should be accessed using indexed access,
55 such as ``stmt.excluded["column name"]`` or
56 ``stmt.excluded["values"]``. See the docstring for
57 :class:`_expression.ColumnCollection` for further examples.
59 .. seealso::
61 :ref:`postgresql_insert_on_conflict` - example of how
62 to use :attr:`_expression.Insert.excluded`
64 """
65 return alias(self.table, name="excluded").columns
67 _on_conflict_exclusive = _exclusive_against(
68 "_post_values_clause",
69 msgs={
70 "_post_values_clause": "This Insert construct already has "
71 "an ON CONFLICT clause established"
72 },
73 )
75 @_generative
76 @_on_conflict_exclusive
77 def on_conflict_do_update(
78 self,
79 constraint=None,
80 index_elements=None,
81 index_where=None,
82 set_=None,
83 where=None,
84 ):
85 r"""
86 Specifies a DO UPDATE SET action for ON CONFLICT clause.
88 Either the ``constraint`` or ``index_elements`` argument is
89 required, but only one of these can be specified.
91 :param constraint:
92 The name of a unique or exclusion constraint on the table,
93 or the constraint object itself if it has a .name attribute.
95 :param index_elements:
96 A sequence consisting of string column names, :class:`_schema.Column`
97 objects, or other column expression objects that will be used
98 to infer a target index.
100 :param index_where:
101 Additional WHERE criterion that can be used to infer a
102 conditional target index.
104 :param set\_:
105 A dictionary or other mapping object
106 where the keys are either names of columns in the target table,
107 or :class:`_schema.Column` objects or other ORM-mapped columns
108 matching that of the target table, and expressions or literals
109 as values, specifying the ``SET`` actions to take.
111 .. versionadded:: 1.4 The
112 :paramref:`_postgresql.Insert.on_conflict_do_update.set_`
113 parameter supports :class:`_schema.Column` objects from the target
114 :class:`_schema.Table` as keys.
116 .. warning:: This dictionary does **not** take into account
117 Python-specified default UPDATE values or generation functions,
118 e.g. those specified using :paramref:`_schema.Column.onupdate`.
119 These values will not be exercised for an ON CONFLICT style of
120 UPDATE, unless they are manually specified in the
121 :paramref:`.Insert.on_conflict_do_update.set_` dictionary.
123 :param where:
124 Optional argument. If present, can be a literal SQL
125 string or an acceptable expression for a ``WHERE`` clause
126 that restricts the rows affected by ``DO UPDATE SET``. Rows
127 not meeting the ``WHERE`` condition will not be updated
128 (effectively a ``DO NOTHING`` for those rows).
130 .. versionadded:: 1.1
133 .. seealso::
135 :ref:`postgresql_insert_on_conflict`
137 """
138 self._post_values_clause = OnConflictDoUpdate(
139 constraint, index_elements, index_where, set_, where
140 )
142 @_generative
143 @_on_conflict_exclusive
144 def on_conflict_do_nothing(
145 self, constraint=None, index_elements=None, index_where=None
146 ):
147 """
148 Specifies a DO NOTHING action for ON CONFLICT clause.
150 The ``constraint`` and ``index_elements`` arguments
151 are optional, but only one of these can be specified.
153 :param constraint:
154 The name of a unique or exclusion constraint on the table,
155 or the constraint object itself if it has a .name attribute.
157 :param index_elements:
158 A sequence consisting of string column names, :class:`_schema.Column`
159 objects, or other column expression objects that will be used
160 to infer a target index.
162 :param index_where:
163 Additional WHERE criterion that can be used to infer a
164 conditional target index.
166 .. versionadded:: 1.1
168 .. seealso::
170 :ref:`postgresql_insert_on_conflict`
172 """
173 self._post_values_clause = OnConflictDoNothing(
174 constraint, index_elements, index_where
175 )
178insert = public_factory(
179 Insert, ".dialects.postgresql.insert", ".dialects.postgresql.Insert"
180)
183class OnConflictClause(ClauseElement):
184 stringify_dialect = "postgresql"
186 def __init__(self, constraint=None, index_elements=None, index_where=None):
188 if constraint is not None:
189 if not isinstance(constraint, util.string_types) and isinstance(
190 constraint,
191 (schema.Constraint, ext.ExcludeConstraint),
192 ):
193 constraint = getattr(constraint, "name") or constraint
195 if constraint is not None:
196 if index_elements is not None:
197 raise ValueError(
198 "'constraint' and 'index_elements' are mutually exclusive"
199 )
201 if isinstance(constraint, util.string_types):
202 self.constraint_target = constraint
203 self.inferred_target_elements = None
204 self.inferred_target_whereclause = None
205 elif isinstance(constraint, schema.Index):
206 index_elements = constraint.expressions
207 index_where = constraint.dialect_options["postgresql"].get(
208 "where"
209 )
210 elif isinstance(constraint, ext.ExcludeConstraint):
211 index_elements = constraint.columns
212 index_where = constraint.where
213 else:
214 index_elements = constraint.columns
215 index_where = constraint.dialect_options["postgresql"].get(
216 "where"
217 )
219 if index_elements is not None:
220 self.constraint_target = None
221 self.inferred_target_elements = index_elements
222 self.inferred_target_whereclause = index_where
223 elif constraint is None:
224 self.constraint_target = (
225 self.inferred_target_elements
226 ) = self.inferred_target_whereclause = None
229class OnConflictDoNothing(OnConflictClause):
230 __visit_name__ = "on_conflict_do_nothing"
233class OnConflictDoUpdate(OnConflictClause):
234 __visit_name__ = "on_conflict_do_update"
236 def __init__(
237 self,
238 constraint=None,
239 index_elements=None,
240 index_where=None,
241 set_=None,
242 where=None,
243 ):
244 super(OnConflictDoUpdate, self).__init__(
245 constraint=constraint,
246 index_elements=index_elements,
247 index_where=index_where,
248 )
250 if (
251 self.inferred_target_elements is None
252 and self.constraint_target is None
253 ):
254 raise ValueError(
255 "Either constraint or index_elements, "
256 "but not both, must be specified unless DO NOTHING"
257 )
259 if isinstance(set_, dict):
260 if not set_:
261 raise ValueError("set parameter dictionary must not be empty")
262 elif isinstance(set_, ColumnCollection):
263 set_ = dict(set_)
264 else:
265 raise ValueError(
266 "set parameter must be a non-empty dictionary "
267 "or a ColumnCollection such as the `.c.` collection "
268 "of a Table object"
269 )
270 self.update_values_to_set = [
271 (coercions.expect(roles.DMLColumnRole, key), value)
272 for key, value in set_.items()
273 ]
274 self.update_whereclause = where