Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/json.py: 33%

1from ... import types as sqltypes 

2 

3# technically, all the dialect-specific datatypes that don't have any special 

4# behaviors would be private with names like _MSJson. However, we haven't been 

5# doing this for mysql.JSON or sqlite.JSON which both have JSON / JSONIndexType 

6# / JSONPathType in their json.py files, so keep consistent with that 

7# sub-convention for now. A future change can update them all to be 

8# package-private at once. 

9 

10 

11class JSON(sqltypes.JSON): 

12 """MSSQL JSON type. 

13 

14 MSSQL supports JSON-formatted data as of SQL Server 2016. 

15 

16 The :class:`_mssql.JSON` datatype at the DDL level will represent the 

17 datatype as ``NVARCHAR(max)``, but provides for JSON-level comparison 

18 functions as well as Python coercion behavior. 

19 

20 :class:`_mssql.JSON` is used automatically whenever the base 

21 :class:`_types.JSON` datatype is used against a SQL Server backend. 

22 

23 .. seealso:: 

24 

25 :class:`_types.JSON` - main documentation for the generic 

26 cross-platform JSON datatype. 

27 

28 The :class:`_mssql.JSON` type supports persistence of JSON values 

29 as well as the core index operations provided by :class:`_types.JSON` 

30 datatype, by adapting the operations to render the ``JSON_VALUE`` 

31 or ``JSON_QUERY`` functions at the database level. 

32 

33 The SQL Server :class:`_mssql.JSON` type necessarily makes use of the 

34 ``JSON_QUERY`` and ``JSON_VALUE`` functions when querying for elements 

35 of a JSON object. These two functions have a major restriction in that 

36 they are **mutually exclusive** based on the type of object to be returned. 

37 The ``JSON_QUERY`` function **only** returns a JSON dictionary or list, 

38 but not an individual string, numeric, or boolean element; the 

39 ``JSON_VALUE`` function **only** returns an individual string, numeric, 

40 or boolean element. **both functions either return NULL or raise 

41 an error if they are not used against the correct expected value**. 

42 

43 To handle this awkward requirement, indexed access rules are as follows: 

44 

45 1. When extracting a sub element from a JSON that is itself a JSON 

46 dictionary or list, the :meth:`_types.JSON.Comparator.as_json` accessor 

47 should be used:: 

48 

49 stmt = select( 

50 data_table.c.data["some key"].as_json() 

51 ).where( 

52 data_table.c.data["some key"].as_json() == {"sub": "structure"} 

53 ) 

54 

55 2. When extracting a sub element from a JSON that is a plain boolean, 

56 string, integer, or float, use the appropriate method among 

57 :meth:`_types.JSON.Comparator.as_boolean`, 

58 :meth:`_types.JSON.Comparator.as_string`, 

59 :meth:`_types.JSON.Comparator.as_integer`, 

60 :meth:`_types.JSON.Comparator.as_float`:: 

61 

62 stmt = select( 

63 data_table.c.data["some key"].as_string() 

64 ).where( 

65 data_table.c.data["some key"].as_string() == "some string" 

66 ) 

67 

68 .. versionadded:: 1.4 

69 

70 

71 """ 

72 

73 # note there was a result processor here that was looking for "number", 

74 # but none of the tests seem to exercise it. 

75 

76 

77# Note: these objects currently match exactly those of MySQL, however since 

78# these are not generalizable to all JSON implementations, remain separately 

79# implemented for each dialect. 

80class _FormatTypeMixin(object): 

81 def _format_value(self, value): 

82 raise NotImplementedError() 

83 

84 def bind_processor(self, dialect): 

85 super_proc = self.string_bind_processor(dialect) 

86 

87 def process(value): 

88 value = self._format_value(value) 

89 if super_proc: 

90 value = super_proc(value) 

91 return value 

92 

93 return process 

94 

95 def literal_processor(self, dialect): 

96 super_proc = self.string_literal_processor(dialect) 

97 

98 def process(value): 

99 value = self._format_value(value) 

100 if super_proc: 

101 value = super_proc(value) 

102 return value 

103 

104 return process 

105 

106 

107class JSONIndexType(_FormatTypeMixin, sqltypes.JSON.JSONIndexType): 

108 def _format_value(self, value): 

109 if isinstance(value, int): 

110 value = "$[%s]" % value 

111 else: 

112 value = '$."%s"' % value 

113 return value 

114 

115 

116class JSONPathType(_FormatTypeMixin, sqltypes.JSON.JSONPathType): 

117 def _format_value(self, value): 

118 return "$%s" % ( 

119 "".join( 

120 [ 

121 "[%s]" % elem if isinstance(elem, int) else '."%s"' % elem 

122 for elem in value 

123 ] 

124 ) 

125 )