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/hstore.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 .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 For usage with the SQLAlchemy ORM, it may be desirable to combine 

100 the usage of :class:`.HSTORE` with :class:`.MutableDict` dictionary 

101 now part of the :mod:`sqlalchemy.ext.mutable` 

102 extension. This extension will allow "in-place" changes to the 

103 dictionary, e.g. addition of new keys or replacement/removal of existing 

104 keys to/from the current dictionary, to produce events which will be 

105 detected by the unit of work:: 

106 

107 from sqlalchemy.ext.mutable import MutableDict 

108 

109 class MyClass(Base): 

110 __tablename__ = 'data_table' 

111 

112 id = Column(Integer, primary_key=True) 

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

114 

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

116 

117 # in-place mutation, requires Mutable extension 

118 # in order for the ORM to detect 

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

120 

121 session.commit() 

122 

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

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

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

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

127 

128 .. seealso:: 

129 

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

131 

132 

133 """ 

134 

135 __visit_name__ = "HSTORE" 

136 hashable = False 

137 text_type = sqltypes.Text() 

138 

139 def __init__(self, text_type=None): 

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

141 

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

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

144 

145 .. versionadded:: 1.1.0 

146 

147 """ 

148 if text_type is not None: 

149 self.text_type = text_type 

150 

151 class Comparator( 

152 sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator 

153 ): 

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

155 

156 def has_key(self, other): 

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

158 key may be a SQLA expression. 

159 """ 

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

161 

162 def has_all(self, other): 

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

164 """ 

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

166 

167 def has_any(self, other): 

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

169 """ 

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

171 

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

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

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

175 """ 

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

177 

178 def contained_by(self, other): 

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

180 keys of the argument jsonb expression. 

181 """ 

182 return self.operate( 

183 CONTAINED_BY, other, result_type=sqltypes.Boolean 

184 ) 

185 

186 def _setup_getitem(self, index): 

187 return GETITEM, index, self.type.text_type 

188 

189 def defined(self, key): 

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

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

192 """ 

193 return _HStoreDefinedFunction(self.expr, key) 

194 

195 def delete(self, key): 

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

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

198 """ 

199 if isinstance(key, dict): 

200 key = _serialize_hstore(key) 

201 return _HStoreDeleteFunction(self.expr, key) 

202 

203 def slice(self, array): 

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

205 array of keys. 

206 """ 

207 return _HStoreSliceFunction(self.expr, array) 

208 

209 def keys(self): 

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

211 return _HStoreKeysFunction(self.expr) 

212 

213 def vals(self): 

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

215 return _HStoreValsFunction(self.expr) 

216 

217 def array(self): 

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

219 values. 

220 """ 

221 return _HStoreArrayFunction(self.expr) 

222 

223 def matrix(self): 

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

225 return _HStoreMatrixFunction(self.expr) 

226 

227 comparator_factory = Comparator 

228 

229 def bind_processor(self, dialect): 

230 if util.py2k: 

231 encoding = dialect.encoding 

232 

233 def process(value): 

234 if isinstance(value, dict): 

235 return _serialize_hstore(value).encode(encoding) 

236 else: 

237 return value 

238 

239 else: 

240 

241 def process(value): 

242 if isinstance(value, dict): 

243 return _serialize_hstore(value) 

244 else: 

245 return value 

246 

247 return process 

248 

249 def result_processor(self, dialect, coltype): 

250 if util.py2k: 

251 encoding = dialect.encoding 

252 

253 def process(value): 

254 if value is not None: 

255 return _parse_hstore(value.decode(encoding)) 

256 else: 

257 return value 

258 

259 else: 

260 

261 def process(value): 

262 if value is not None: 

263 return _parse_hstore(value) 

264 else: 

265 return value 

266 

267 return process 

268 

269 

270class hstore(sqlfunc.GenericFunction): 

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

272 PostgreSQL ``hstore()`` function. 

273 

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

275 in the PostgreSQL documentation. 

276 

277 E.g.:: 

278 

279 from sqlalchemy.dialects.postgresql import array, hstore 

280 

281 select([hstore('key1', 'value1')]) 

282 

283 select([ 

284 hstore( 

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

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

287 ) 

288 ]) 

289 

290 .. seealso:: 

291 

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

293 

294 """ 

295 

296 type = HSTORE 

297 name = "hstore" 

298 

299 

300class _HStoreDefinedFunction(sqlfunc.GenericFunction): 

301 type = sqltypes.Boolean 

302 name = "defined" 

303 

304 

305class _HStoreDeleteFunction(sqlfunc.GenericFunction): 

306 type = HSTORE 

307 name = "delete" 

308 

309 

310class _HStoreSliceFunction(sqlfunc.GenericFunction): 

311 type = HSTORE 

312 name = "slice" 

313 

314 

315class _HStoreKeysFunction(sqlfunc.GenericFunction): 

316 type = ARRAY(sqltypes.Text) 

317 name = "akeys" 

318 

319 

320class _HStoreValsFunction(sqlfunc.GenericFunction): 

321 type = ARRAY(sqltypes.Text) 

322 name = "avals" 

323 

324 

325class _HStoreArrayFunction(sqlfunc.GenericFunction): 

326 type = ARRAY(sqltypes.Text) 

327 name = "hstore_to_array" 

328 

329 

330class _HStoreMatrixFunction(sqlfunc.GenericFunction): 

331 type = ARRAY(sqltypes.Text) 

332 name = "hstore_to_matrix" 

333 

334 

335# 

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

337# which provides its own native extensions. 

338# 

339 

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

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

342# behavior. 

343HSTORE_PAIR_RE = re.compile( 

344 r""" 

345( 

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

347) 

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

349( 

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

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

352) 

353""", 

354 re.VERBOSE, 

355) 

356 

357HSTORE_DELIMITER_RE = re.compile( 

358 r""" 

359[ ]* , [ ]* 

360""", 

361 re.VERBOSE, 

362) 

363 

364 

365def _parse_error(hstore_str, pos): 

366 """format an unmarshalling error.""" 

367 

368 ctx = 20 

369 hslen = len(hstore_str) 

370 

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

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

373 

374 if len(parsed_tail) > ctx: 

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

376 if len(residual) > ctx: 

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

378 

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

380 parsed_tail, 

381 pos, 

382 residual, 

383 ) 

384 

385 

386def _parse_hstore(hstore_str): 

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

388 

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

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

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

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

393 be the case. 

394 

395 

396 

397 """ 

398 result = {} 

399 pos = 0 

400 pair_match = HSTORE_PAIR_RE.match(hstore_str) 

401 

402 while pair_match is not None: 

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

404 if pair_match.group("value_null"): 

405 value = None 

406 else: 

407 value = ( 

408 pair_match.group("value") 

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

410 .replace("\\\\", "\\") 

411 ) 

412 result[key] = value 

413 

414 pos += pair_match.end() 

415 

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

417 if delim_match is not None: 

418 pos += delim_match.end() 

419 

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

421 

422 if pos != len(hstore_str): 

423 raise ValueError(_parse_error(hstore_str, pos)) 

424 

425 return result 

426 

427 

428def _serialize_hstore(val): 

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

430 both be strings (except None for values). 

431 

432 """ 

433 

434 def esc(s, position): 

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

436 return "NULL" 

437 elif isinstance(s, util.string_types): 

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

439 else: 

440 raise ValueError( 

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

442 ) 

443 

444 return ", ".join( 

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

446 )