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/array.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 

8import re 

9 

10from ... import types as sqltypes 

11from ... import util 

12from ...sql import expression 

13from ...sql import operators 

14 

15 

16def Any(other, arrexpr, operator=operators.eq): 

17 """A synonym for the :meth:`.ARRAY.Comparator.any` method. 

18 

19 This method is legacy and is here for backwards-compatibility. 

20 

21 .. seealso:: 

22 

23 :func:`_expression.any_` 

24 

25 """ 

26 

27 return arrexpr.any(other, operator) 

28 

29 

30def All(other, arrexpr, operator=operators.eq): 

31 """A synonym for the :meth:`.ARRAY.Comparator.all` method. 

32 

33 This method is legacy and is here for backwards-compatibility. 

34 

35 .. seealso:: 

36 

37 :func:`_expression.all_` 

38 

39 """ 

40 

41 return arrexpr.all(other, operator) 

42 

43 

44class array(expression.Tuple): 

45 

46 """A PostgreSQL ARRAY literal. 

47 

48 This is used to produce ARRAY literals in SQL expressions, e.g.:: 

49 

50 from sqlalchemy.dialects.postgresql import array 

51 from sqlalchemy.dialects import postgresql 

52 from sqlalchemy import select, func 

53 

54 stmt = select([ 

55 array([1,2]) + array([3,4,5]) 

56 ]) 

57 

58 print(stmt.compile(dialect=postgresql.dialect())) 

59 

60 Produces the SQL:: 

61 

62 SELECT ARRAY[%(param_1)s, %(param_2)s] || 

63 ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1 

64 

65 An instance of :class:`.array` will always have the datatype 

66 :class:`_types.ARRAY`. The "inner" type of the array is inferred from 

67 the values present, unless the ``type_`` keyword argument is passed:: 

68 

69 array(['foo', 'bar'], type_=CHAR) 

70 

71 Multidimensional arrays are produced by nesting :class:`.array` constructs. 

72 The dimensionality of the final :class:`_types.ARRAY` 

73 type is calculated by 

74 recursively adding the dimensions of the inner :class:`_types.ARRAY` 

75 type:: 

76 

77 stmt = select([ 

78 array([ 

79 array([1, 2]), array([3, 4]), array([column('q'), column('x')]) 

80 ]) 

81 ]) 

82 print(stmt.compile(dialect=postgresql.dialect())) 

83 

84 Produces:: 

85 

86 SELECT ARRAY[ARRAY[%(param_1)s, %(param_2)s], 

87 ARRAY[%(param_3)s, %(param_4)s], ARRAY[q, x]] AS anon_1 

88 

89 .. versionadded:: 1.3.6 added support for multidimensional array literals 

90 

91 .. seealso:: 

92 

93 :class:`_postgresql.ARRAY` 

94 

95 """ 

96 

97 __visit_name__ = "array" 

98 

99 def __init__(self, clauses, **kw): 

100 super(array, self).__init__(*clauses, **kw) 

101 if isinstance(self.type, ARRAY): 

102 self.type = ARRAY( 

103 self.type.item_type, 

104 dimensions=self.type.dimensions + 1 

105 if self.type.dimensions is not None 

106 else 2, 

107 ) 

108 else: 

109 self.type = ARRAY(self.type) 

110 

111 def _bind_param(self, operator, obj, _assume_scalar=False, type_=None): 

112 if _assume_scalar or operator is operators.getitem: 

113 return expression.BindParameter( 

114 None, 

115 obj, 

116 _compared_to_operator=operator, 

117 type_=type_, 

118 _compared_to_type=self.type, 

119 unique=True, 

120 ) 

121 

122 else: 

123 return array( 

124 [ 

125 self._bind_param( 

126 operator, o, _assume_scalar=True, type_=type_ 

127 ) 

128 for o in obj 

129 ] 

130 ) 

131 

132 def self_group(self, against=None): 

133 if against in (operators.any_op, operators.all_op, operators.getitem): 

134 return expression.Grouping(self) 

135 else: 

136 return self 

137 

138 

139CONTAINS = operators.custom_op("@>", precedence=5) 

140 

141CONTAINED_BY = operators.custom_op("<@", precedence=5) 

142 

143OVERLAP = operators.custom_op("&&", precedence=5) 

144 

145 

146class ARRAY(sqltypes.ARRAY): 

147 

148 """PostgreSQL ARRAY type. 

149 

150 .. versionchanged:: 1.1 The :class:`_postgresql.ARRAY` type is now 

151 a subclass of the core :class:`_types.ARRAY` type. 

152 

153 The :class:`_postgresql.ARRAY` type is constructed in the same way 

154 as the core :class:`_types.ARRAY` type; a member type is required, and a 

155 number of dimensions is recommended if the type is to be used for more 

156 than one dimension:: 

157 

158 from sqlalchemy.dialects import postgresql 

159 

160 mytable = Table("mytable", metadata, 

161 Column("data", postgresql.ARRAY(Integer, dimensions=2)) 

162 ) 

163 

164 The :class:`_postgresql.ARRAY` type provides all operations defined on the 

165 core :class:`_types.ARRAY` type, including support for "dimensions", 

166 indexed access, and simple matching such as 

167 :meth:`.types.ARRAY.Comparator.any` and 

168 :meth:`.types.ARRAY.Comparator.all`. :class:`_postgresql.ARRAY` 

169 class also 

170 provides PostgreSQL-specific methods for containment operations, including 

171 :meth:`.postgresql.ARRAY.Comparator.contains` 

172 :meth:`.postgresql.ARRAY.Comparator.contained_by`, and 

173 :meth:`.postgresql.ARRAY.Comparator.overlap`, e.g.:: 

174 

175 mytable.c.data.contains([1, 2]) 

176 

177 The :class:`_postgresql.ARRAY` type may not be supported on all 

178 PostgreSQL DBAPIs; it is currently known to work on psycopg2 only. 

179 

180 Additionally, the :class:`_postgresql.ARRAY` 

181 type does not work directly in 

182 conjunction with the :class:`.ENUM` type. For a workaround, see the 

183 special type at :ref:`postgresql_array_of_enum`. 

184 

185 .. seealso:: 

186 

187 :class:`_types.ARRAY` - base array type 

188 

189 :class:`_postgresql.array` - produces a literal array value. 

190 

191 """ 

192 

193 class Comparator(sqltypes.ARRAY.Comparator): 

194 

195 """Define comparison operations for :class:`_types.ARRAY`. 

196 

197 Note that these operations are in addition to those provided 

198 by the base :class:`.types.ARRAY.Comparator` class, including 

199 :meth:`.types.ARRAY.Comparator.any` and 

200 :meth:`.types.ARRAY.Comparator.all`. 

201 

202 """ 

203 

204 def contains(self, other, **kwargs): 

205 """Boolean expression. Test if elements are a superset of the 

206 elements of the argument array expression. 

207 """ 

208 return self.operate(CONTAINS, other, result_type=sqltypes.Boolean) 

209 

210 def contained_by(self, other): 

211 """Boolean expression. Test if elements are a proper subset of the 

212 elements of the argument array expression. 

213 """ 

214 return self.operate( 

215 CONTAINED_BY, other, result_type=sqltypes.Boolean 

216 ) 

217 

218 def overlap(self, other): 

219 """Boolean expression. Test if array has elements in common with 

220 an argument array expression. 

221 """ 

222 return self.operate(OVERLAP, other, result_type=sqltypes.Boolean) 

223 

224 comparator_factory = Comparator 

225 

226 def __init__( 

227 self, item_type, as_tuple=False, dimensions=None, zero_indexes=False 

228 ): 

229 """Construct an ARRAY. 

230 

231 E.g.:: 

232 

233 Column('myarray', ARRAY(Integer)) 

234 

235 Arguments are: 

236 

237 :param item_type: The data type of items of this array. Note that 

238 dimensionality is irrelevant here, so multi-dimensional arrays like 

239 ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as 

240 ``ARRAY(ARRAY(Integer))`` or such. 

241 

242 :param as_tuple=False: Specify whether return results 

243 should be converted to tuples from lists. DBAPIs such 

244 as psycopg2 return lists by default. When tuples are 

245 returned, the results are hashable. 

246 

247 :param dimensions: if non-None, the ARRAY will assume a fixed 

248 number of dimensions. This will cause the DDL emitted for this 

249 ARRAY to include the exact number of bracket clauses ``[]``, 

250 and will also optimize the performance of the type overall. 

251 Note that PG arrays are always implicitly "non-dimensioned", 

252 meaning they can store any number of dimensions no matter how 

253 they were declared. 

254 

255 :param zero_indexes=False: when True, index values will be converted 

256 between Python zero-based and PostgreSQL one-based indexes, e.g. 

257 a value of one will be added to all index values before passing 

258 to the database. 

259 

260 .. versionadded:: 0.9.5 

261 

262 

263 """ 

264 if isinstance(item_type, ARRAY): 

265 raise ValueError( 

266 "Do not nest ARRAY types; ARRAY(basetype) " 

267 "handles multi-dimensional arrays of basetype" 

268 ) 

269 if isinstance(item_type, type): 

270 item_type = item_type() 

271 self.item_type = item_type 

272 self.as_tuple = as_tuple 

273 self.dimensions = dimensions 

274 self.zero_indexes = zero_indexes 

275 

276 @property 

277 def hashable(self): 

278 return self.as_tuple 

279 

280 @property 

281 def python_type(self): 

282 return list 

283 

284 def compare_values(self, x, y): 

285 return x == y 

286 

287 def _proc_array(self, arr, itemproc, dim, collection): 

288 if dim is None: 

289 arr = list(arr) 

290 if ( 

291 dim == 1 

292 or dim is None 

293 and ( 

294 # this has to be (list, tuple), or at least 

295 # not hasattr('__iter__'), since Py3K strings 

296 # etc. have __iter__ 

297 not arr 

298 or not isinstance(arr[0], (list, tuple)) 

299 ) 

300 ): 

301 if itemproc: 

302 return collection(itemproc(x) for x in arr) 

303 else: 

304 return collection(arr) 

305 else: 

306 return collection( 

307 self._proc_array( 

308 x, 

309 itemproc, 

310 dim - 1 if dim is not None else None, 

311 collection, 

312 ) 

313 for x in arr 

314 ) 

315 

316 @util.memoized_property 

317 def _require_cast(self): 

318 return self._against_native_enum or isinstance( 

319 self.item_type, sqltypes.JSON 

320 ) 

321 

322 @util.memoized_property 

323 def _against_native_enum(self): 

324 return ( 

325 isinstance(self.item_type, sqltypes.Enum) 

326 and self.item_type.native_enum 

327 ) 

328 

329 def bind_expression(self, bindvalue): 

330 if self._require_cast: 

331 return expression.cast(bindvalue, self) 

332 else: 

333 return bindvalue 

334 

335 def bind_processor(self, dialect): 

336 item_proc = self.item_type.dialect_impl(dialect).bind_processor( 

337 dialect 

338 ) 

339 

340 def process(value): 

341 if value is None: 

342 return value 

343 else: 

344 return self._proc_array( 

345 value, item_proc, self.dimensions, list 

346 ) 

347 

348 return process 

349 

350 def result_processor(self, dialect, coltype): 

351 item_proc = self.item_type.dialect_impl(dialect).result_processor( 

352 dialect, coltype 

353 ) 

354 

355 def process(value): 

356 if value is None: 

357 return value 

358 else: 

359 return self._proc_array( 

360 value, 

361 item_proc, 

362 self.dimensions, 

363 tuple if self.as_tuple else list, 

364 ) 

365 

366 if self._against_native_enum: 

367 super_rp = process 

368 

369 def handle_raw_string(value): 

370 inner = re.match(r"^{(.*)}$", value).group(1) 

371 return inner.split(",") if inner else [] 

372 

373 def process(value): 

374 if value is None: 

375 return value 

376 # isinstance(value, util.string_types) is required to handle 

377 # the # case where a TypeDecorator for and Array of Enum is 

378 # used like was required in sa < 1.3.17 

379 return super_rp( 

380 handle_raw_string(value) 

381 if isinstance(value, util.string_types) 

382 else value 

383 ) 

384 

385 return process