Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1from ... import exc 

2from ... import util 

3from ...sql.base import _generative 

4from ...sql.dml import Insert as StandardInsert 

5from ...sql.elements import ClauseElement 

6from ...sql.expression import alias 

7from ...util.langhelpers import public_factory 

8 

9 

10__all__ = ("Insert", "insert") 

11 

12 

13class Insert(StandardInsert): 

14 """MySQL-specific implementation of INSERT. 

15 

16 Adds methods for MySQL-specific syntaxes such as ON DUPLICATE KEY UPDATE. 

17 

18 The :class:`~.mysql.Insert` object is created using the 

19 :func:`sqlalchemy.dialects.mysql.insert` function. 

20 

21 .. versionadded:: 1.2 

22 

23 """ 

24 

25 @property 

26 def inserted(self): 

27 """Provide the "inserted" namespace for an ON DUPLICATE KEY UPDATE statement 

28 

29 MySQL's ON DUPLICATE KEY UPDATE clause allows reference to the row 

30 that would be inserted, via a special function called ``VALUES()``. 

31 This attribute provides all columns in this row to be referenceable 

32 such that they will render within a ``VALUES()`` function inside the 

33 ON DUPLICATE KEY UPDATE clause. The attribute is named ``.inserted`` 

34 so as not to conflict with the existing 

35 :meth:`_expression.Insert.values` method. 

36 

37 .. seealso:: 

38 

39 :ref:`mysql_insert_on_duplicate_key_update` - example of how 

40 to use :attr:`_expression.Insert.inserted` 

41 

42 """ 

43 return self.inserted_alias.columns 

44 

45 @util.memoized_property 

46 def inserted_alias(self): 

47 return alias(self.table, name="inserted") 

48 

49 @_generative 

50 def on_duplicate_key_update(self, *args, **kw): 

51 r""" 

52 Specifies the ON DUPLICATE KEY UPDATE clause. 

53 

54 :param \**kw: Column keys linked to UPDATE values. The 

55 values may be any SQL expression or supported literal Python 

56 values. 

57 

58 .. warning:: This dictionary does **not** take into account 

59 Python-specified default UPDATE values or generation functions, 

60 e.g. those specified using :paramref:`_schema.Column.onupdate`. 

61 These values will not be exercised for an ON DUPLICATE KEY UPDATE 

62 style of UPDATE, unless values are manually specified here. 

63 

64 :param \*args: As an alternative to passing key/value parameters, 

65 a dictionary or list of 2-tuples can be passed as a single positional 

66 argument. 

67 

68 Passing a single dictionary is equivalent to the keyword argument 

69 form:: 

70 

71 insert().on_duplicate_key_update({"name": "some name"}) 

72 

73 Passing a list of 2-tuples indicates that the parameter assignments 

74 in the UPDATE clause should be ordered as sent, in a manner similar 

75 to that described for the :class:`_expression.Update` 

76 construct overall 

77 in :ref:`updates_order_parameters`:: 

78 

79 insert().on_duplicate_key_update( 

80 [("name", "some name"), ("value", "some value")]) 

81 

82 .. versionchanged:: 1.3 parameters can be specified as a dictionary 

83 or list of 2-tuples; the latter form provides for parameter 

84 ordering. 

85 

86 

87 .. versionadded:: 1.2 

88 

89 .. seealso:: 

90 

91 :ref:`mysql_insert_on_duplicate_key_update` 

92 

93 """ 

94 if args and kw: 

95 raise exc.ArgumentError( 

96 "Can't pass kwargs and positional arguments simultaneously" 

97 ) 

98 

99 if args: 

100 if len(args) > 1: 

101 raise exc.ArgumentError( 

102 "Only a single dictionary or list of tuples " 

103 "is accepted positionally." 

104 ) 

105 values = args[0] 

106 else: 

107 values = kw 

108 

109 inserted_alias = getattr(self, "inserted_alias", None) 

110 self._post_values_clause = OnDuplicateClause(inserted_alias, values) 

111 return self 

112 

113 

114insert = public_factory( 

115 Insert, ".dialects.mysql.insert", ".dialects.mysql.Insert" 

116) 

117 

118 

119class OnDuplicateClause(ClauseElement): 

120 __visit_name__ = "on_duplicate_key_update" 

121 

122 _parameter_ordering = None 

123 

124 def __init__(self, inserted_alias, update): 

125 self.inserted_alias = inserted_alias 

126 

127 # auto-detect that parameters should be ordered. This is copied from 

128 # Update._proces_colparams(), however we don't look for a special flag 

129 # in this case since we are not disambiguating from other use cases as 

130 # we are in Update.values(). 

131 if isinstance(update, list) and ( 

132 update and isinstance(update[0], tuple) 

133 ): 

134 self._parameter_ordering = [key for key, value in update] 

135 update = dict(update) 

136 

137 if not update or not isinstance(update, dict): 

138 raise ValueError("update parameter must be a non-empty dictionary") 

139 self.update = update