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 

2""" 

3############################################################################## 

4## SDCQueryHelpers 

5## Helper functions for reading and transforming queries 

6## 

7############################################################################## 

8""" 

9 

10from jinja2 import Template 

11 

12from sdc_etl_libs.sdc_file_helpers.SDCFileHelpers import SDCFileHelpers 

13 

14 

15class SDCQueryHelpers: 

16 """ Helper functions for reading and transforming queries""" 

17 

18 @staticmethod 

19 def get_query_from_schema(schema_, file_type_='sql', kwargs=None): 

20 """ Returns a string representing the query associated with a schema after any query_params 

21 in the schema have been substituted into the query from values in kwargs dictionary for key 

22 'query_params'. 

23 

24 param: schema_: schema containing query_name key, and optionally query_params key 

25 type: schema_: dict 

26 param: file_type_: type of file the query is in, defaults to 'sql' 

27 type: file_type_: str 

28 param: kwargs: contains an optional query_params key with value a dictionary of key value pairs to be substituted in the query. 

29 type: kwargs: dict 

30 return: a query defined by the schema with any query_params substituted 

31 rtype: str 

32 """ 

33 

34 if kwargs is None: 

35 kwargs = {} 

36 

37 if 'query_name' in schema_["info"]["access"]: 

38 if 'query_params' in schema_["info"]["opts"]: 

39 return SDCQueryHelpers.get_query_by_name( 

40 schema_["info"]["access"]['query_name'], file_type_=file_type_, 

41 query_params_=schema_["info"]["opts"]['query_params'], kwargs=kwargs) 

42 return SDCQueryHelpers.get_query_by_name(schema_["info"]["access"]['query_name'], file_type_=file_type_, kwargs=kwargs) 

43 return None 

44 

45 @staticmethod 

46 def get_query_by_name(query_name_, file_type_='sql', query_params_=None, kwargs=None): 

47 """ Returns a string representing the query associated with query_name after any query_params (if passed) 

48 have been substituted into the query from values in kwargs dictionary for key 'query_params'. 

49 

50 param: query_name_: name of the query to get 

51 type: query_name_: str 

52 param: file_type_: type of file the query is in, defaults to 'sql' 

53 type: file_type_: str 

54 param: query_params_: list of valid parameters for the query 

55 type: query_params_: list 

56 param: kwargs: contains an optional query_params key with value a dictionary of key value pairs to be substituted in the query. 

57 type: kwargs: dict 

58 raises: Exception: if query_params is not a list 

59 raises: Exception: keys in kwargs['query_params'] are not in query_params list 

60 raises: Exception: if query_params list is non-empty, but kwargs['query_params'] is empty 

61 return: a query defined by the schema with any query_params substituted 

62 rtype: str 

63 """ 

64 

65 if query_params_ is None: 

66 query_params_ = [] 

67 

68 if kwargs is None: 

69 kwargs = {} 

70 

71 raw_query = SDCQueryHelpers.get_raw_query_by_name(query_name_, file_type_=file_type_) 

72 

73 if not isinstance(query_params_, list): 

74 raise Exception('Bad query_params in schema: must be a list') 

75 

76 if 'query_params' in kwargs: 

77 if not isinstance(kwargs['query_params'], dict): 

78 raise Exception('Bad query_params: must be a dictionary') 

79 for param in kwargs['query_params']: 

80 if param not in query_params_: 

81 raise Exception('Passed parameter "' + param + '" not expected by schema') 

82 else: 

83 if query_params_: 

84 raise Exception('Required parameters are missing') 

85 

86 for param in query_params_: 

87 if param not in kwargs['query_params']: 

88 raise Exception('Failed to pass parameter "' + param + '" expected by schema') 

89 

90 query = None 

91 if query_params_ and kwargs['query_params']: 

92 template = Template(raw_query) 

93 query = template.render(kwargs['query_params']) 

94 else: 

95 query = raw_query 

96 

97 return query 

98 

99 @staticmethod 

100 def get_raw_query_by_name(query_name_, file_type_='sql'): 

101 """ Returns a raw query given the query_name 

102 

103 param: query_name_: name of the query to get 

104 type: query_name_: str 

105 param: file_type_: type of file the query is in, defaults to 'sql' 

106 type: file_type_: str 

107 raises: Exception: if query_name cannot be found. 

108 return: a query defined by the schema 

109 rtype: str 

110 """ 

111 

112 query = None 

113 try: 

114 file_path = SDCFileHelpers.get_file_path(file_type_, query_name_ + "." + file_type_) 

115 with open(file_path, 'r') as file: 

116 query = file.read() 

117 except Exception as e: 

118 raise Exception("Error reading sql file ") from e 

119 

120 return query