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/mysql/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
7from ... import exc
8from ... import util
9from ...sql.base import _exclusive_against
10from ...sql.base import _generative
11from ...sql.base import ColumnCollection
12from ...sql.dml import Insert as StandardInsert
13from ...sql.elements import ClauseElement
14from ...sql.expression import alias
15from ...util.langhelpers import public_factory
18__all__ = ("Insert", "insert")
21class Insert(StandardInsert):
22 """MySQL-specific implementation of INSERT.
24 Adds methods for MySQL-specific syntaxes such as ON DUPLICATE KEY UPDATE.
26 The :class:`~.mysql.Insert` object is created using the
27 :func:`sqlalchemy.dialects.mysql.insert` function.
29 .. versionadded:: 1.2
31 """
33 stringify_dialect = "mysql"
34 inherit_cache = False
36 @property
37 def inserted(self):
38 """Provide the "inserted" namespace for an ON DUPLICATE KEY UPDATE
39 statement
41 MySQL's ON DUPLICATE KEY UPDATE clause allows reference to the row
42 that would be inserted, via a special function called ``VALUES()``.
43 This attribute provides all columns in this row to be referenceable
44 such that they will render within a ``VALUES()`` function inside the
45 ON DUPLICATE KEY UPDATE clause. The attribute is named ``.inserted``
46 so as not to conflict with the existing
47 :meth:`_expression.Insert.values` method.
49 .. tip:: The :attr:`_mysql.Insert.inserted` attribute is an instance
50 of :class:`_expression.ColumnCollection`, which provides an
51 interface the same as that of the :attr:`_schema.Table.c`
52 collection described at :ref:`metadata_tables_and_columns`.
53 With this collection, ordinary names are accessible like attributes
54 (e.g. ``stmt.inserted.some_column``), but special names and
55 dictionary method names should be accessed using indexed access,
56 such as ``stmt.inserted["column name"]`` or
57 ``stmt.inserted["values"]``. See the docstring for
58 :class:`_expression.ColumnCollection` for further examples.
60 .. seealso::
62 :ref:`mysql_insert_on_duplicate_key_update` - example of how
63 to use :attr:`_expression.Insert.inserted`
65 """
66 return self.inserted_alias.columns
68 @util.memoized_property
69 def inserted_alias(self):
70 return alias(self.table, name="inserted")
72 @_generative
73 @_exclusive_against(
74 "_post_values_clause",
75 msgs={
76 "_post_values_clause": "This Insert construct already "
77 "has an ON DUPLICATE KEY clause present"
78 },
79 )
80 def on_duplicate_key_update(self, *args, **kw):
81 r"""
82 Specifies the ON DUPLICATE KEY UPDATE clause.
84 :param \**kw: Column keys linked to UPDATE values. The
85 values may be any SQL expression or supported literal Python
86 values.
88 .. warning:: This dictionary does **not** take into account
89 Python-specified default UPDATE values or generation functions,
90 e.g. those specified using :paramref:`_schema.Column.onupdate`.
91 These values will not be exercised for an ON DUPLICATE KEY UPDATE
92 style of UPDATE, unless values are manually specified here.
94 :param \*args: As an alternative to passing key/value parameters,
95 a dictionary or list of 2-tuples can be passed as a single positional
96 argument.
98 Passing a single dictionary is equivalent to the keyword argument
99 form::
101 insert().on_duplicate_key_update({"name": "some name"})
103 Passing a list of 2-tuples indicates that the parameter assignments
104 in the UPDATE clause should be ordered as sent, in a manner similar
105 to that described for the :class:`_expression.Update`
106 construct overall
107 in :ref:`tutorial_parameter_ordered_updates`::
109 insert().on_duplicate_key_update(
110 [("name", "some name"), ("value", "some value")])
112 .. versionchanged:: 1.3 parameters can be specified as a dictionary
113 or list of 2-tuples; the latter form provides for parameter
114 ordering.
117 .. versionadded:: 1.2
119 .. seealso::
121 :ref:`mysql_insert_on_duplicate_key_update`
123 """
124 if args and kw:
125 raise exc.ArgumentError(
126 "Can't pass kwargs and positional arguments simultaneously"
127 )
129 if args:
130 if len(args) > 1:
131 raise exc.ArgumentError(
132 "Only a single dictionary or list of tuples "
133 "is accepted positionally."
134 )
135 values = args[0]
136 else:
137 values = kw
139 inserted_alias = getattr(self, "inserted_alias", None)
140 self._post_values_clause = OnDuplicateClause(inserted_alias, values)
143insert = public_factory(
144 Insert, ".dialects.mysql.insert", ".dialects.mysql.Insert"
145)
148class OnDuplicateClause(ClauseElement):
149 __visit_name__ = "on_duplicate_key_update"
151 _parameter_ordering = None
153 stringify_dialect = "mysql"
155 def __init__(self, inserted_alias, update):
156 self.inserted_alias = inserted_alias
158 # auto-detect that parameters should be ordered. This is copied from
159 # Update._proces_colparams(), however we don't look for a special flag
160 # in this case since we are not disambiguating from other use cases as
161 # we are in Update.values().
162 if isinstance(update, list) and (
163 update and isinstance(update[0], tuple)
164 ):
165 self._parameter_ordering = [key for key, value in update]
166 update = dict(update)
168 if isinstance(update, dict):
169 if not update:
170 raise ValueError(
171 "update parameter dictionary must not be empty"
172 )
173 elif isinstance(update, ColumnCollection):
174 update = dict(update)
175 else:
176 raise ValueError(
177 "update parameter must be a non-empty dictionary "
178 "or a ColumnCollection such as the `.c.` collection "
179 "of a Table object"
180 )
181 self.update = update