Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# dialects/sqlite/dml.py
2# Copyright (C) 2005-2024 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 util
9from ...sql import coercions
10from ...sql import roles
11from ...sql.base import _exclusive_against
12from ...sql.base import _generative
13from ...sql.base import ColumnCollection
14from ...sql.dml import Insert as StandardInsert
15from ...sql.elements import ClauseElement
16from ...sql.expression import alias
17from ...util.langhelpers import public_factory
20__all__ = ("Insert", "insert")
23class Insert(StandardInsert):
24 """SQLite-specific implementation of INSERT.
26 Adds methods for SQLite-specific syntaxes such as ON CONFLICT.
28 The :class:`_sqlite.Insert` object is created using the
29 :func:`sqlalchemy.dialects.sqlite.insert` function.
31 .. versionadded:: 1.4
33 .. seealso::
35 :ref:`sqlite_on_conflict_insert`
37 """
39 stringify_dialect = "sqlite"
40 inherit_cache = False
42 @util.memoized_property
43 def excluded(self):
44 """Provide the ``excluded`` namespace for an ON CONFLICT statement
46 SQLite's ON CONFLICT clause allows reference to the row that would
47 be inserted, known as ``excluded``. This attribute provides
48 all columns in this row to be referenceable.
50 .. tip:: The :attr:`_sqlite.Insert.excluded` attribute is an instance
51 of :class:`_expression.ColumnCollection`, which provides an
52 interface the same as that of the :attr:`_schema.Table.c`
53 collection described at :ref:`metadata_tables_and_columns`.
54 With this collection, ordinary names are accessible like attributes
55 (e.g. ``stmt.excluded.some_column``), but special names and
56 dictionary method names should be accessed using indexed access,
57 such as ``stmt.excluded["column name"]`` or
58 ``stmt.excluded["values"]``. See the docstring for
59 :class:`_expression.ColumnCollection` for further examples.
61 """
62 return alias(self.table, name="excluded").columns
64 _on_conflict_exclusive = _exclusive_against(
65 "_post_values_clause",
66 msgs={
67 "_post_values_clause": "This Insert construct already has "
68 "an ON CONFLICT clause established"
69 },
70 )
72 @_generative
73 @_on_conflict_exclusive
74 def on_conflict_do_update(
75 self,
76 index_elements=None,
77 index_where=None,
78 set_=None,
79 where=None,
80 ):
81 r"""
82 Specifies a DO UPDATE SET action for ON CONFLICT clause.
84 :param index_elements:
85 A sequence consisting of string column names, :class:`_schema.Column`
86 objects, or other column expression objects that will be used
87 to infer a target index or unique constraint.
89 :param index_where:
90 Additional WHERE criterion that can be used to infer a
91 conditional target index.
93 :param set\_:
94 A dictionary or other mapping object
95 where the keys are either names of columns in the target table,
96 or :class:`_schema.Column` objects or other ORM-mapped columns
97 matching that of the target table, and expressions or literals
98 as values, specifying the ``SET`` actions to take.
100 .. versionadded:: 1.4 The
101 :paramref:`_sqlite.Insert.on_conflict_do_update.set_`
102 parameter supports :class:`_schema.Column` objects from the target
103 :class:`_schema.Table` as keys.
105 .. warning:: This dictionary does **not** take into account
106 Python-specified default UPDATE values or generation functions,
107 e.g. those specified using :paramref:`_schema.Column.onupdate`.
108 These values will not be exercised for an ON CONFLICT style of
109 UPDATE, unless they are manually specified in the
110 :paramref:`.Insert.on_conflict_do_update.set_` dictionary.
112 :param where:
113 Optional argument. If present, can be a literal SQL
114 string or an acceptable expression for a ``WHERE`` clause
115 that restricts the rows affected by ``DO UPDATE SET``. Rows
116 not meeting the ``WHERE`` condition will not be updated
117 (effectively a ``DO NOTHING`` for those rows).
119 """
121 self._post_values_clause = OnConflictDoUpdate(
122 index_elements, index_where, set_, where
123 )
125 @_generative
126 @_on_conflict_exclusive
127 def on_conflict_do_nothing(self, index_elements=None, index_where=None):
128 """
129 Specifies a DO NOTHING action for ON CONFLICT clause.
131 :param index_elements:
132 A sequence consisting of string column names, :class:`_schema.Column`
133 objects, or other column expression objects that will be used
134 to infer a target index or unique constraint.
136 :param index_where:
137 Additional WHERE criterion that can be used to infer a
138 conditional target index.
140 """
142 self._post_values_clause = OnConflictDoNothing(
143 index_elements, index_where
144 )
147insert = public_factory(
148 Insert, ".dialects.sqlite.insert", ".dialects.sqlite.Insert"
149)
152class OnConflictClause(ClauseElement):
153 stringify_dialect = "sqlite"
155 def __init__(self, index_elements=None, index_where=None):
157 if index_elements is not None:
158 self.constraint_target = None
159 self.inferred_target_elements = index_elements
160 self.inferred_target_whereclause = index_where
161 else:
162 self.constraint_target = (
163 self.inferred_target_elements
164 ) = self.inferred_target_whereclause = None
167class OnConflictDoNothing(OnConflictClause):
168 __visit_name__ = "on_conflict_do_nothing"
171class OnConflictDoUpdate(OnConflictClause):
172 __visit_name__ = "on_conflict_do_update"
174 def __init__(
175 self,
176 index_elements=None,
177 index_where=None,
178 set_=None,
179 where=None,
180 ):
181 super(OnConflictDoUpdate, self).__init__(
182 index_elements=index_elements,
183 index_where=index_where,
184 )
186 if isinstance(set_, dict):
187 if not set_:
188 raise ValueError("set parameter dictionary must not be empty")
189 elif isinstance(set_, ColumnCollection):
190 set_ = dict(set_)
191 else:
192 raise ValueError(
193 "set parameter must be a non-empty dictionary "
194 "or a ColumnCollection such as the `.c.` collection "
195 "of a Table object"
196 )
197 self.update_values_to_set = [
198 (coercions.expect(roles.DMLColumnRole, key), value)
199 for key, value in set_.items()
200 ]
201 self.update_whereclause = where