Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/hstore.py: 50%
145 statements
« prev ^ index » next coverage.py v7.2.7, created at 2023-06-07 06:35 +0000
1# postgresql/hstore.py
2# Copyright (C) 2005-2023 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
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 .. container:: topic
101 **Detecting Changes in HSTORE columns when using the ORM**
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::
111 from sqlalchemy.ext.mutable import MutableDict
113 class MyClass(Base):
114 __tablename__ = 'data_table'
116 id = Column(Integer, primary_key=True)
117 data = Column(MutableDict.as_mutable(HSTORE))
119 my_object = session.query(MyClass).one()
121 # in-place mutation, requires Mutable extension
122 # in order for the ORM to detect
123 my_object.data['some_key'] = 'some value'
125 session.commit()
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.
132 .. seealso::
134 :class:`.hstore` - render the PostgreSQL ``hstore()`` function.
137 """
139 __visit_name__ = "HSTORE"
140 hashable = False
141 text_type = sqltypes.Text()
143 def __init__(self, text_type=None):
144 """Construct a new :class:`.HSTORE`.
146 :param text_type: the type that should be used for indexed values.
147 Defaults to :class:`_types.Text`.
149 .. versionadded:: 1.1.0
151 """
152 if text_type is not None:
153 self.text_type = text_type
155 class Comparator(
156 sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator
157 ):
158 """Define comparison operations for :class:`.HSTORE`."""
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)
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)
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)
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.
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)
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 )
191 def _setup_getitem(self, index):
192 return GETITEM, index, self.type.text_type
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)
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)
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)
214 def keys(self):
215 """Text array expression. Returns array of keys."""
216 return _HStoreKeysFunction(self.expr)
218 def vals(self):
219 """Text array expression. Returns array of values."""
220 return _HStoreValsFunction(self.expr)
222 def array(self):
223 """Text array expression. Returns array of alternating keys and
224 values.
225 """
226 return _HStoreArrayFunction(self.expr)
228 def matrix(self):
229 """Text array expression. Returns array of [key, value] pairs."""
230 return _HStoreMatrixFunction(self.expr)
232 comparator_factory = Comparator
234 def bind_processor(self, dialect):
235 if util.py2k:
236 encoding = dialect.encoding
238 def process(value):
239 if isinstance(value, dict):
240 return _serialize_hstore(value).encode(encoding)
241 else:
242 return value
244 else:
246 def process(value):
247 if isinstance(value, dict):
248 return _serialize_hstore(value)
249 else:
250 return value
252 return process
254 def result_processor(self, dialect, coltype):
255 if util.py2k:
256 encoding = dialect.encoding
258 def process(value):
259 if value is not None:
260 return _parse_hstore(value.decode(encoding))
261 else:
262 return value
264 else:
266 def process(value):
267 if value is not None:
268 return _parse_hstore(value)
269 else:
270 return value
272 return process
275class hstore(sqlfunc.GenericFunction):
276 """Construct an hstore value within a SQL expression using the
277 PostgreSQL ``hstore()`` function.
279 The :class:`.hstore` function accepts one or two arguments as described
280 in the PostgreSQL documentation.
282 E.g.::
284 from sqlalchemy.dialects.postgresql import array, hstore
286 select(hstore('key1', 'value1'))
288 select(
289 hstore(
290 array(['key1', 'key2', 'key3']),
291 array(['value1', 'value2', 'value3'])
292 )
293 )
295 .. seealso::
297 :class:`.HSTORE` - the PostgreSQL ``HSTORE`` datatype.
299 """
301 type = HSTORE
302 name = "hstore"
303 inherit_cache = True
306class _HStoreDefinedFunction(sqlfunc.GenericFunction):
307 type = sqltypes.Boolean
308 name = "defined"
309 inherit_cache = True
312class _HStoreDeleteFunction(sqlfunc.GenericFunction):
313 type = HSTORE
314 name = "delete"
315 inherit_cache = True
318class _HStoreSliceFunction(sqlfunc.GenericFunction):
319 type = HSTORE
320 name = "slice"
321 inherit_cache = True
324class _HStoreKeysFunction(sqlfunc.GenericFunction):
325 type = ARRAY(sqltypes.Text)
326 name = "akeys"
327 inherit_cache = True
330class _HStoreValsFunction(sqlfunc.GenericFunction):
331 type = ARRAY(sqltypes.Text)
332 name = "avals"
333 inherit_cache = True
336class _HStoreArrayFunction(sqlfunc.GenericFunction):
337 type = ARRAY(sqltypes.Text)
338 name = "hstore_to_array"
339 inherit_cache = True
342class _HStoreMatrixFunction(sqlfunc.GenericFunction):
343 type = ARRAY(sqltypes.Text)
344 name = "hstore_to_matrix"
345 inherit_cache = True
348#
349# parsing. note that none of this is used with the psycopg2 backend,
350# which provides its own native extensions.
351#
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)
370HSTORE_DELIMITER_RE = re.compile(
371 r"""
372[ ]* , [ ]*
373""",
374 re.VERBOSE,
375)
378def _parse_error(hstore_str, pos):
379 """format an unmarshalling error."""
381 ctx = 20
382 hslen = len(hstore_str)
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)]
387 if len(parsed_tail) > ctx:
388 parsed_tail = "[...]" + parsed_tail[1:]
389 if len(residual) > ctx:
390 residual = residual[:-1] + "[...]"
392 return "After %r, could not parse residual at position %d: %r" % (
393 parsed_tail,
394 pos,
395 residual,
396 )
399def _parse_hstore(hstore_str):
400 """Parse an hstore from its literal string representation.
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.
410 """
411 result = {}
412 pos = 0
413 pair_match = HSTORE_PAIR_RE.match(hstore_str)
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
427 pos += pair_match.end()
429 delim_match = HSTORE_DELIMITER_RE.match(hstore_str[pos:])
430 if delim_match is not None:
431 pos += delim_match.end()
433 pair_match = HSTORE_PAIR_RE.match(hstore_str[pos:])
435 if pos != len(hstore_str):
436 raise ValueError(_parse_error(hstore_str, pos))
438 return result
441def _serialize_hstore(val):
442 """Serialize a dictionary into an hstore literal. Keys and values must
443 both be strings (except None for values).
445 """
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 )
457 return ", ".join(
458 "%s=>%s" % (esc(k, "key"), esc(v, "value")) for k, v in val.items()
459 )