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

1# postgresql/on_conflict.py 

2# Copyright (C) 2005-2020 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: http://www.opensource.org/licenses/mit-license.php 

7 

8from . import ext 

9from ... import util 

10from ...sql import schema 

11from ...sql.base import _generative 

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

13from ...sql.elements import ClauseElement 

14from ...sql.expression import alias 

15from ...util.langhelpers import public_factory 

16 

17 

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

19 

20 

21class Insert(StandardInsert): 

22 """PostgreSQL-specific implementation of INSERT. 

23 

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

25 

26 The :class:`_postgresql.Insert` object is created using the 

27 :func:`sqlalchemy.dialects.postgresql.insert` function. 

28 

29 .. versionadded:: 1.1 

30 

31 """ 

32 

33 @util.memoized_property 

34 def excluded(self): 

35 """Provide the ``excluded`` namespace for an ON CONFLICT statement 

36 

37 PG's ON CONFLICT clause allows reference to the row that would 

38 be inserted, known as ``excluded``. This attribute provides 

39 all columns in this row to be referenceable. 

40 

41 .. seealso:: 

42 

43 :ref:`postgresql_insert_on_conflict` - example of how 

44 to use :attr:`_expression.Insert.excluded` 

45 

46 """ 

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

48 

49 @_generative 

50 def on_conflict_do_update( 

51 self, 

52 constraint=None, 

53 index_elements=None, 

54 index_where=None, 

55 set_=None, 

56 where=None, 

57 ): 

58 r""" 

59 Specifies a DO UPDATE SET action for ON CONFLICT clause. 

60 

61 Either the ``constraint`` or ``index_elements`` argument is 

62 required, but only one of these can be specified. 

63 

64 :param constraint: 

65 The name of a unique or exclusion constraint on the table, 

66 or the constraint object itself if it has a .name attribute. 

67 

68 :param index_elements: 

69 A sequence consisting of string column names, :class:`_schema.Column` 

70 objects, or other column expression objects that will be used 

71 to infer a target index. 

72 

73 :param index_where: 

74 Additional WHERE criterion that can be used to infer a 

75 conditional target index. 

76 

77 :param set\_: 

78 Required argument. A dictionary or other mapping object 

79 with column names as keys and expressions or literals as values, 

80 specifying the ``SET`` actions to take. 

81 If the target :class:`_schema.Column` specifies a ". 

82 key" attribute distinct 

83 from the column name, that key should be used. 

84 

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

86 Python-specified default UPDATE values or generation functions, 

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

88 These values will not be exercised for an ON CONFLICT style of 

89 UPDATE, unless they are manually specified in the 

90 :paramref:`.Insert.on_conflict_do_update.set_` dictionary. 

91 

92 :param where: 

93 Optional argument. If present, can be a literal SQL 

94 string or an acceptable expression for a ``WHERE`` clause 

95 that restricts the rows affected by ``DO UPDATE SET``. Rows 

96 not meeting the ``WHERE`` condition will not be updated 

97 (effectively a ``DO NOTHING`` for those rows). 

98 

99 .. versionadded:: 1.1 

100 

101 

102 .. seealso:: 

103 

104 :ref:`postgresql_insert_on_conflict` 

105 

106 """ 

107 self._post_values_clause = OnConflictDoUpdate( 

108 constraint, index_elements, index_where, set_, where 

109 ) 

110 return self 

111 

112 @_generative 

113 def on_conflict_do_nothing( 

114 self, constraint=None, index_elements=None, index_where=None 

115 ): 

116 """ 

117 Specifies a DO NOTHING action for ON CONFLICT clause. 

118 

119 The ``constraint`` and ``index_elements`` arguments 

120 are optional, but only one of these can be specified. 

121 

122 :param constraint: 

123 The name of a unique or exclusion constraint on the table, 

124 or the constraint object itself if it has a .name attribute. 

125 

126 :param index_elements: 

127 A sequence consisting of string column names, :class:`_schema.Column` 

128 objects, or other column expression objects that will be used 

129 to infer a target index. 

130 

131 :param index_where: 

132 Additional WHERE criterion that can be used to infer a 

133 conditional target index. 

134 

135 .. versionadded:: 1.1 

136 

137 .. seealso:: 

138 

139 :ref:`postgresql_insert_on_conflict` 

140 

141 """ 

142 self._post_values_clause = OnConflictDoNothing( 

143 constraint, index_elements, index_where 

144 ) 

145 return self 

146 

147 

148insert = public_factory( 

149 Insert, ".dialects.postgresql.insert", ".dialects.postgresql.Insert" 

150) 

151 

152 

153class OnConflictClause(ClauseElement): 

154 def __init__(self, constraint=None, index_elements=None, index_where=None): 

155 

156 if constraint is not None: 

157 if not isinstance(constraint, util.string_types) and isinstance( 

158 constraint, 

159 (schema.Index, schema.Constraint, ext.ExcludeConstraint), 

160 ): 

161 constraint = getattr(constraint, "name") or constraint 

162 

163 if constraint is not None: 

164 if index_elements is not None: 

165 raise ValueError( 

166 "'constraint' and 'index_elements' are mutually exclusive" 

167 ) 

168 

169 if isinstance(constraint, util.string_types): 

170 self.constraint_target = constraint 

171 self.inferred_target_elements = None 

172 self.inferred_target_whereclause = None 

173 elif isinstance(constraint, schema.Index): 

174 index_elements = constraint.expressions 

175 index_where = constraint.dialect_options["postgresql"].get( 

176 "where" 

177 ) 

178 elif isinstance(constraint, ext.ExcludeConstraint): 

179 index_elements = constraint.columns 

180 index_where = constraint.where 

181 else: 

182 index_elements = constraint.columns 

183 index_where = constraint.dialect_options["postgresql"].get( 

184 "where" 

185 ) 

186 

187 if index_elements is not None: 

188 self.constraint_target = None 

189 self.inferred_target_elements = index_elements 

190 self.inferred_target_whereclause = index_where 

191 elif constraint is None: 

192 self.constraint_target = ( 

193 self.inferred_target_elements 

194 ) = self.inferred_target_whereclause = None 

195 

196 

197class OnConflictDoNothing(OnConflictClause): 

198 __visit_name__ = "on_conflict_do_nothing" 

199 

200 

201class OnConflictDoUpdate(OnConflictClause): 

202 __visit_name__ = "on_conflict_do_update" 

203 

204 def __init__( 

205 self, 

206 constraint=None, 

207 index_elements=None, 

208 index_where=None, 

209 set_=None, 

210 where=None, 

211 ): 

212 super(OnConflictDoUpdate, self).__init__( 

213 constraint=constraint, 

214 index_elements=index_elements, 

215 index_where=index_where, 

216 ) 

217 

218 if ( 

219 self.inferred_target_elements is None 

220 and self.constraint_target is None 

221 ): 

222 raise ValueError( 

223 "Either constraint or index_elements, " 

224 "but not both, must be specified unless DO NOTHING" 

225 ) 

226 

227 if not isinstance(set_, dict) or not set_: 

228 raise ValueError("set parameter must be a non-empty dictionary") 

229 self.update_values_to_set = [ 

230 (key, value) for key, value in set_.items() 

231 ] 

232 self.update_whereclause = where