Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/hstore.py: 50%

1# dialects/postgresql/hstore.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 

7 

8import re 

9 

10from .array import ARRAY 

11from ... import types as sqltypes 

12from ... import util 

13from ...sql import functions as sqlfunc 

14from ...sql import operators 

15 

16 

17__all__ = ("HSTORE", "hstore") 

18 

19idx_precedence = operators._PRECEDENCE[operators.json_getitem_op] 

20 

21GETITEM = operators.custom_op( 

22 "->", 

23 precedence=idx_precedence, 

24 natural_self_precedent=True, 

25 eager_grouping=True, 

26) 

27 

28HAS_KEY = operators.custom_op( 

29 "?", 

30 precedence=idx_precedence, 

31 natural_self_precedent=True, 

32 eager_grouping=True, 

33) 

34 

35HAS_ALL = operators.custom_op( 

36 "?&", 

37 precedence=idx_precedence, 

38 natural_self_precedent=True, 

39 eager_grouping=True, 

40) 

41 

42HAS_ANY = operators.custom_op( 

43 "?|", 

44 precedence=idx_precedence, 

45 natural_self_precedent=True, 

46 eager_grouping=True, 

47) 

48 

49CONTAINS = operators.custom_op( 

50 "@>", 

51 precedence=idx_precedence, 

52 natural_self_precedent=True, 

53 eager_grouping=True, 

54) 

55 

56CONTAINED_BY = operators.custom_op( 

57 "<@", 

58 precedence=idx_precedence, 

59 natural_self_precedent=True, 

60 eager_grouping=True, 

61) 

62 

63 

64class HSTORE(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): 

65 """Represent the PostgreSQL HSTORE type. 

66 

67 The :class:`.HSTORE` type stores dictionaries containing strings, e.g.:: 

68 

69 data_table = Table('data_table', metadata, 

70 Column('id', Integer, primary_key=True), 

71 Column('data', HSTORE) 

72 ) 

73 

74 with engine.connect() as conn: 

75 conn.execute( 

76 data_table.insert(), 

77 data = {"key1": "value1", "key2": "value2"} 

78 ) 

79 

80 :class:`.HSTORE` provides for a wide range of operations, including: 

81 

82 * Index operations:: 

83 

84 data_table.c.data['some key'] == 'some value' 

85 

86 * Containment operations:: 

87 

88 data_table.c.data.has_key('some key') 

89 

90 data_table.c.data.has_all(['one', 'two', 'three']) 

91 

92 * Concatenation:: 

93 

94 data_table.c.data + {"k1": "v1"} 

95 

96 For a full list of special methods see 

97 :class:`.HSTORE.comparator_factory`. 

98 

99 .. container:: topic 

100 

101 **Detecting Changes in HSTORE columns when using the ORM** 

102 

103 For usage with the SQLAlchemy ORM, it may be desirable to combine the 

104 usage of :class:`.HSTORE` with :class:`.MutableDict` dictionary now 

105 part of the :mod:`sqlalchemy.ext.mutable` extension. This extension 

106 will allow "in-place" changes to the dictionary, e.g. addition of new 

107 keys or replacement/removal of existing keys to/from the current 

108 dictionary, to produce events which will be detected by the unit of 

109 work:: 

110 

111 from sqlalchemy.ext.mutable import MutableDict 

112 

113 class MyClass(Base): 

114 __tablename__ = 'data_table' 

115 

116 id = Column(Integer, primary_key=True) 

117 data = Column(MutableDict.as_mutable(HSTORE)) 

118 

119 my_object = session.query(MyClass).one() 

120 

121 # in-place mutation, requires Mutable extension 

122 # in order for the ORM to detect 

123 my_object.data['some_key'] = 'some value' 

124 

125 session.commit() 

126 

127 When the :mod:`sqlalchemy.ext.mutable` extension is not used, the ORM 

128 will not be alerted to any changes to the contents of an existing 

129 dictionary, unless that dictionary value is re-assigned to the 

130 HSTORE-attribute itself, thus generating a change event. 

131 

132 .. seealso:: 

133 

134 :class:`.hstore` - render the PostgreSQL ``hstore()`` function. 

135 

136 

137 """ 

138 

139 __visit_name__ = "HSTORE" 

140 hashable = False 

141 text_type = sqltypes.Text() 

142 

143 def __init__(self, text_type=None): 

144 """Construct a new :class:`.HSTORE`. 

145 

146 :param text_type: the type that should be used for indexed values. 

147 Defaults to :class:`_types.Text`. 

148 

149 .. versionadded:: 1.1.0 

150 

151 """ 

152 if text_type is not None: 

153 self.text_type = text_type 

154 

155 class Comparator( 

156 sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator 

157 ): 

158 """Define comparison operations for :class:`.HSTORE`.""" 

159 

160 def has_key(self, other): 

161 """Boolean expression. Test for presence of a key. Note that the 

162 key may be a SQLA expression. 

163 """ 

164 return self.operate(HAS_KEY, other, result_type=sqltypes.Boolean) 

165 

166 def has_all(self, other): 

167 """Boolean expression. Test for presence of all keys in jsonb""" 

168 return self.operate(HAS_ALL, other, result_type=sqltypes.Boolean) 

169 

170 def has_any(self, other): 

171 """Boolean expression. Test for presence of any key in jsonb""" 

172 return self.operate(HAS_ANY, other, result_type=sqltypes.Boolean) 

173 

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

175 """Boolean expression. Test if keys (or array) are a superset 

176 of/contained the keys of the argument jsonb expression. 

177 

178 kwargs may be ignored by this operator but are required for API 

179 conformance. 

180 """ 

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

182 

183 def contained_by(self, other): 

184 """Boolean expression. Test if keys are a proper subset of the 

185 keys of the argument jsonb expression. 

186 """ 

187 return self.operate( 

188 CONTAINED_BY, other, result_type=sqltypes.Boolean 

189 ) 

190 

191 def _setup_getitem(self, index): 

192 return GETITEM, index, self.type.text_type 

193 

194 def defined(self, key): 

195 """Boolean expression. Test for presence of a non-NULL value for 

196 the key. Note that the key may be a SQLA expression. 

197 """ 

198 return _HStoreDefinedFunction(self.expr, key) 

199 

200 def delete(self, key): 

201 """HStore expression. Returns the contents of this hstore with the 

202 given key deleted. Note that the key may be a SQLA expression. 

203 """ 

204 if isinstance(key, dict): 

205 key = _serialize_hstore(key) 

206 return _HStoreDeleteFunction(self.expr, key) 

207 

208 def slice(self, array): 

209 """HStore expression. Returns a subset of an hstore defined by 

210 array of keys. 

211 """ 

212 return _HStoreSliceFunction(self.expr, array) 

213 

214 def keys(self): 

215 """Text array expression. Returns array of keys.""" 

216 return _HStoreKeysFunction(self.expr) 

217 

218 def vals(self): 

219 """Text array expression. Returns array of values.""" 

220 return _HStoreValsFunction(self.expr) 

221 

222 def array(self): 

223 """Text array expression. Returns array of alternating keys and 

224 values. 

225 """ 

226 return _HStoreArrayFunction(self.expr) 

227 

228 def matrix(self): 

229 """Text array expression. Returns array of [key, value] pairs.""" 

230 return _HStoreMatrixFunction(self.expr) 

231 

232 comparator_factory = Comparator 

233 

234 def bind_processor(self, dialect): 

235 if util.py2k: 

236 encoding = dialect.encoding 

237 

238 def process(value): 

239 if isinstance(value, dict): 

240 return _serialize_hstore(value).encode(encoding) 

241 else: 

242 return value 

243 

244 else: 

245 

246 def process(value): 

247 if isinstance(value, dict): 

248 return _serialize_hstore(value) 

249 else: 

250 return value 

251 

252 return process 

253 

254 def result_processor(self, dialect, coltype): 

255 if util.py2k: 

256 encoding = dialect.encoding 

257 

258 def process(value): 

259 if value is not None: 

260 return _parse_hstore(value.decode(encoding)) 

261 else: 

262 return value 

263 

264 else: 

265 

266 def process(value): 

267 if value is not None: 

268 return _parse_hstore(value) 

269 else: 

270 return value 

271 

272 return process 

273 

274 

275class hstore(sqlfunc.GenericFunction): 

276 """Construct an hstore value within a SQL expression using the 

277 PostgreSQL ``hstore()`` function. 

278 

279 The :class:`.hstore` function accepts one or two arguments as described 

280 in the PostgreSQL documentation. 

281 

282 E.g.:: 

283 

284 from sqlalchemy.dialects.postgresql import array, hstore 

285 

286 select(hstore('key1', 'value1')) 

287 

288 select( 

289 hstore( 

290 array(['key1', 'key2', 'key3']), 

291 array(['value1', 'value2', 'value3']) 

292 ) 

293 ) 

294 

295 .. seealso:: 

296 

297 :class:`.HSTORE` - the PostgreSQL ``HSTORE`` datatype. 

298 

299 """ 

300 

301 type = HSTORE 

302 name = "hstore" 

303 inherit_cache = True 

304 

305 

306class _HStoreDefinedFunction(sqlfunc.GenericFunction): 

307 type = sqltypes.Boolean 

308 name = "defined" 

309 inherit_cache = True 

310 

311 

312class _HStoreDeleteFunction(sqlfunc.GenericFunction): 

313 type = HSTORE 

314 name = "delete" 

315 inherit_cache = True 

316 

317 

318class _HStoreSliceFunction(sqlfunc.GenericFunction): 

319 type = HSTORE 

320 name = "slice" 

321 inherit_cache = True 

322 

323 

324class _HStoreKeysFunction(sqlfunc.GenericFunction): 

325 type = ARRAY(sqltypes.Text) 

326 name = "akeys" 

327 inherit_cache = True 

328 

329 

330class _HStoreValsFunction(sqlfunc.GenericFunction): 

331 type = ARRAY(sqltypes.Text) 

332 name = "avals" 

333 inherit_cache = True 

334 

335 

336class _HStoreArrayFunction(sqlfunc.GenericFunction): 

337 type = ARRAY(sqltypes.Text) 

338 name = "hstore_to_array" 

339 inherit_cache = True 

340 

341 

342class _HStoreMatrixFunction(sqlfunc.GenericFunction): 

343 type = ARRAY(sqltypes.Text) 

344 name = "hstore_to_matrix" 

345 inherit_cache = True 

346 

347 

348# 

349# parsing. note that none of this is used with the psycopg2 backend, 

350# which provides its own native extensions. 

351# 

352 

353# My best guess at the parsing rules of hstore literals, since no formal 

354# grammar is given. This is mostly reverse engineered from PG's input parser 

355# behavior. 

356HSTORE_PAIR_RE = re.compile( 

357 r""" 

358( 

359 "(?P<key> (\\ . | [^"])* )" # Quoted key 

360) 

361[ ]* => [ ]* # Pair operator, optional adjoining whitespace 

362( 

363 (?P<value_null> NULL ) # NULL value 

364 | "(?P<value> (\\ . | [^"])* )" # Quoted value 

365) 

366""", 

367 re.VERBOSE, 

368) 

369 

370HSTORE_DELIMITER_RE = re.compile( 

371 r""" 

372[ ]* , [ ]* 

373""", 

374 re.VERBOSE, 

375) 

376 

377 

378def _parse_error(hstore_str, pos): 

379 """format an unmarshalling error.""" 

380 

381 ctx = 20 

382 hslen = len(hstore_str) 

383 

384 parsed_tail = hstore_str[max(pos - ctx - 1, 0) : min(pos, hslen)] 

385 residual = hstore_str[min(pos, hslen) : min(pos + ctx + 1, hslen)] 

386 

387 if len(parsed_tail) > ctx: 

388 parsed_tail = "[...]" + parsed_tail[1:] 

389 if len(residual) > ctx: 

390 residual = residual[:-1] + "[...]" 

391 

392 return "After %r, could not parse residual at position %d: %r" % ( 

393 parsed_tail, 

394 pos, 

395 residual, 

396 ) 

397 

398 

399def _parse_hstore(hstore_str): 

400 """Parse an hstore from its literal string representation. 

401 

402 Attempts to approximate PG's hstore input parsing rules as closely as 

403 possible. Although currently this is not strictly necessary, since the 

404 current implementation of hstore's output syntax is stricter than what it 

405 accepts as input, the documentation makes no guarantees that will always 

406 be the case. 

407 

408 

409 

410 """ 

411 result = {} 

412 pos = 0 

413 pair_match = HSTORE_PAIR_RE.match(hstore_str) 

414 

415 while pair_match is not None: 

416 key = pair_match.group("key").replace(r"\"", '"').replace("\\\\", "\\") 

417 if pair_match.group("value_null"): 

418 value = None 

419 else: 

420 value = ( 

421 pair_match.group("value") 

422 .replace(r"\"", '"') 

423 .replace("\\\\", "\\") 

424 ) 

425 result[key] = value 

426 

427 pos += pair_match.end() 

428 

429 delim_match = HSTORE_DELIMITER_RE.match(hstore_str[pos:]) 

430 if delim_match is not None: 

431 pos += delim_match.end() 

432 

433 pair_match = HSTORE_PAIR_RE.match(hstore_str[pos:]) 

434 

435 if pos != len(hstore_str): 

436 raise ValueError(_parse_error(hstore_str, pos)) 

437 

438 return result 

439 

440 

441def _serialize_hstore(val): 

442 """Serialize a dictionary into an hstore literal. Keys and values must 

443 both be strings (except None for values). 

444 

445 """ 

446 

447 def esc(s, position): 

448 if position == "value" and s is None: 

449 return "NULL" 

450 elif isinstance(s, util.string_types): 

451 return '"%s"' % s.replace("\\", "\\\\").replace('"', r"\"") 

452 else: 

453 raise ValueError( 

454 "%r in %s position is not a string." % (s, position) 

455 ) 

456 

457 return ", ".join( 

458 "%s=>%s" % (esc(k, "key"), esc(v, "value")) for k, v in val.items() 

459 )