Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/hstore.py : 47%

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
8import re
10from .array import ARRAY
11from ... import types as sqltypes
12from ... import util
13from ...sql import functions as sqlfunc
14from ...sql import operators
17__all__ = ("HSTORE", "hstore")
19idx_precedence = operators._PRECEDENCE[operators.json_getitem_op]
21GETITEM = operators.custom_op(
22 "->",
23 precedence=idx_precedence,
24 natural_self_precedent=True,
25 eager_grouping=True,
26)
28HAS_KEY = operators.custom_op(
29 "?",
30 precedence=idx_precedence,
31 natural_self_precedent=True,
32 eager_grouping=True,
33)
35HAS_ALL = operators.custom_op(
36 "?&",
37 precedence=idx_precedence,
38 natural_self_precedent=True,
39 eager_grouping=True,
40)
42HAS_ANY = operators.custom_op(
43 "?|",
44 precedence=idx_precedence,
45 natural_self_precedent=True,
46 eager_grouping=True,
47)
49CONTAINS = operators.custom_op(
50 "@>",
51 precedence=idx_precedence,
52 natural_self_precedent=True,
53 eager_grouping=True,
54)
56CONTAINED_BY = operators.custom_op(
57 "<@",
58 precedence=idx_precedence,
59 natural_self_precedent=True,
60 eager_grouping=True,
61)
64class HSTORE(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine):
65 """Represent the PostgreSQL HSTORE type.
67 The :class:`.HSTORE` type stores dictionaries containing strings, e.g.::
69 data_table = Table('data_table', metadata,
70 Column('id', Integer, primary_key=True),
71 Column('data', HSTORE)
72 )
74 with engine.connect() as conn:
75 conn.execute(
76 data_table.insert(),
77 data = {"key1": "value1", "key2": "value2"}
78 )
80 :class:`.HSTORE` provides for a wide range of operations, including:
82 * Index operations::
84 data_table.c.data['some key'] == 'some value'
86 * Containment operations::
88 data_table.c.data.has_key('some key')
90 data_table.c.data.has_all(['one', 'two', 'three'])
92 * Concatenation::
94 data_table.c.data + {"k1": "v1"}
96 For a full list of special methods see
97 :class:`.HSTORE.comparator_factory`.
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::
107 from sqlalchemy.ext.mutable import MutableDict
109 class MyClass(Base):
110 __tablename__ = 'data_table'
112 id = Column(Integer, primary_key=True)
113 data = Column(MutableDict.as_mutable(HSTORE))
115 my_object = session.query(MyClass).one()
117 # in-place mutation, requires Mutable extension
118 # in order for the ORM to detect
119 my_object.data['some_key'] = 'some value'
121 session.commit()
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.
128 .. seealso::
130 :class:`.hstore` - render the PostgreSQL ``hstore()`` function.
133 """
135 __visit_name__ = "HSTORE"
136 hashable = False
137 text_type = sqltypes.Text()
139 def __init__(self, text_type=None):
140 """Construct a new :class:`.HSTORE`.
142 :param text_type: the type that should be used for indexed values.
143 Defaults to :class:`_types.Text`.
145 .. versionadded:: 1.1.0
147 """
148 if text_type is not None:
149 self.text_type = text_type
151 class Comparator(
152 sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator
153 ):
154 """Define comparison operations for :class:`.HSTORE`."""
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)
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)
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)
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)
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 )
186 def _setup_getitem(self, index):
187 return GETITEM, index, self.type.text_type
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)
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)
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)
209 def keys(self):
210 """Text array expression. Returns array of keys."""
211 return _HStoreKeysFunction(self.expr)
213 def vals(self):
214 """Text array expression. Returns array of values."""
215 return _HStoreValsFunction(self.expr)
217 def array(self):
218 """Text array expression. Returns array of alternating keys and
219 values.
220 """
221 return _HStoreArrayFunction(self.expr)
223 def matrix(self):
224 """Text array expression. Returns array of [key, value] pairs."""
225 return _HStoreMatrixFunction(self.expr)
227 comparator_factory = Comparator
229 def bind_processor(self, dialect):
230 if util.py2k:
231 encoding = dialect.encoding
233 def process(value):
234 if isinstance(value, dict):
235 return _serialize_hstore(value).encode(encoding)
236 else:
237 return value
239 else:
241 def process(value):
242 if isinstance(value, dict):
243 return _serialize_hstore(value)
244 else:
245 return value
247 return process
249 def result_processor(self, dialect, coltype):
250 if util.py2k:
251 encoding = dialect.encoding
253 def process(value):
254 if value is not None:
255 return _parse_hstore(value.decode(encoding))
256 else:
257 return value
259 else:
261 def process(value):
262 if value is not None:
263 return _parse_hstore(value)
264 else:
265 return value
267 return process
270class hstore(sqlfunc.GenericFunction):
271 """Construct an hstore value within a SQL expression using the
272 PostgreSQL ``hstore()`` function.
274 The :class:`.hstore` function accepts one or two arguments as described
275 in the PostgreSQL documentation.
277 E.g.::
279 from sqlalchemy.dialects.postgresql import array, hstore
281 select([hstore('key1', 'value1')])
283 select([
284 hstore(
285 array(['key1', 'key2', 'key3']),
286 array(['value1', 'value2', 'value3'])
287 )
288 ])
290 .. seealso::
292 :class:`.HSTORE` - the PostgreSQL ``HSTORE`` datatype.
294 """
296 type = HSTORE
297 name = "hstore"
300class _HStoreDefinedFunction(sqlfunc.GenericFunction):
301 type = sqltypes.Boolean
302 name = "defined"
305class _HStoreDeleteFunction(sqlfunc.GenericFunction):
306 type = HSTORE
307 name = "delete"
310class _HStoreSliceFunction(sqlfunc.GenericFunction):
311 type = HSTORE
312 name = "slice"
315class _HStoreKeysFunction(sqlfunc.GenericFunction):
316 type = ARRAY(sqltypes.Text)
317 name = "akeys"
320class _HStoreValsFunction(sqlfunc.GenericFunction):
321 type = ARRAY(sqltypes.Text)
322 name = "avals"
325class _HStoreArrayFunction(sqlfunc.GenericFunction):
326 type = ARRAY(sqltypes.Text)
327 name = "hstore_to_array"
330class _HStoreMatrixFunction(sqlfunc.GenericFunction):
331 type = ARRAY(sqltypes.Text)
332 name = "hstore_to_matrix"
335#
336# parsing. note that none of this is used with the psycopg2 backend,
337# which provides its own native extensions.
338#
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)
357HSTORE_DELIMITER_RE = re.compile(
358 r"""
359[ ]* , [ ]*
360""",
361 re.VERBOSE,
362)
365def _parse_error(hstore_str, pos):
366 """format an unmarshalling error."""
368 ctx = 20
369 hslen = len(hstore_str)
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)]
374 if len(parsed_tail) > ctx:
375 parsed_tail = "[...]" + parsed_tail[1:]
376 if len(residual) > ctx:
377 residual = residual[:-1] + "[...]"
379 return "After %r, could not parse residual at position %d: %r" % (
380 parsed_tail,
381 pos,
382 residual,
383 )
386def _parse_hstore(hstore_str):
387 """Parse an hstore from its literal string representation.
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.
397 """
398 result = {}
399 pos = 0
400 pair_match = HSTORE_PAIR_RE.match(hstore_str)
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
414 pos += pair_match.end()
416 delim_match = HSTORE_DELIMITER_RE.match(hstore_str[pos:])
417 if delim_match is not None:
418 pos += delim_match.end()
420 pair_match = HSTORE_PAIR_RE.match(hstore_str[pos:])
422 if pos != len(hstore_str):
423 raise ValueError(_parse_error(hstore_str, pos))
425 return result
428def _serialize_hstore(val):
429 """Serialize a dictionary into an hstore literal. Keys and values must
430 both be strings (except None for values).
432 """
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 )
444 return ", ".join(
445 "%s=>%s" % (esc(k, "key"), esc(v, "value")) for k, v in val.items()
446 )