-------------------- Execute a query using an authenticated user. If not specified, uses the default user. Args: sql_text (str): SQL query text to execute. Allowed two placeholders: {public_key} which will be replaced with the user.public_key, and {resource}
(self, sql_text:str, sql_type:SXTSqlType = SXTSqlType.DQL,
resources:list = None, user:SXTUser = None,
biscuits:list = None, output_format:SXTOutputFormat = SXTOutputFormat.JSON)
| 120 | |
| 121 | |
| 122 | def execute_query(self, sql_text:str, sql_type:SXTSqlType = SXTSqlType.DQL, |
| 123 | resources:list = None, user:SXTUser = None, |
| 124 | biscuits:list = None, output_format:SXTOutputFormat = SXTOutputFormat.JSON) -> tuple: |
| 125 | """-------------------- |
| 126 | Execute a query using an authenticated user. If not specified, uses the default user. |
| 127 | |
| 128 | Args: |
| 129 | sql_text (str): SQL query text to execute. Allowed two placeholders: {public_key} which will be replaced with the user.public_key, and {resource} which is replaced with the first element in resource list (resource[0]). |
| 130 | resources (list): (optional) List of Resources ("schema.table_name") in the sql_text. Supplying will optimize performance. If only 1 value, can optionally supply a str. |
| 131 | sql_type (SXTSqlType): (optional) Type of query, DML, DDL, DQL. Supplying will optimize performance. |
| 132 | user (SXTUser): (optional) Authenticated user to use to execute the query. Defaults to default user. |
| 133 | biscuits (list): (optional) List of biscuit tokens for permissioned tables. If only querying public tables, this is not needed. |
| 134 | output_format (SXTOutputFormat): (optional) Output format enum, either JSON or CSV. Defaults to SXTOutputFormat.JSON. |
| 135 | |
| 136 | Returns: |
| 137 | bool: True if success, False if in Error. |
| 138 | list: Rows, either in JSON or CSV format. |
| 139 | |
| 140 | Examples: |
| 141 | >>> from spacenadtime import SpaceAndTime |
| 142 | >>> sxt = SpaceAndTime() |
| 143 | >>> sxt.authenticate() |
| 144 | >>> execute_query('Select 1 as A from SXTDEMO.Singularity') |
| 145 | 1 |
| 146 | |
| 147 | """ |
| 148 | if not user: user = self.user |
| 149 | if not resources: resources = [] |
| 150 | if not biscuits: biscuits = [] |
| 151 | rtn = [] |
| 152 | |
| 153 | try: |
| 154 | resources = resources if type(resources)==list else [str(resources)] |
| 155 | sql_text = self.__replaceall(mainstr=sql_text, replacemap={'resource':resources[0] if resources else [] ,'public_key':user.public_key }) |
| 156 | self.logger.info(f'Executing query: \n{sql_text}') |
| 157 | |
| 158 | if self.network_calls_enabled: |
| 159 | if sql_type == SXTSqlType.DDL : |
| 160 | success, rtn = user.base_api.sql_ddl(sql_text=sql_text, biscuits=biscuits, app_name=self.application_name) |
| 161 | |
| 162 | elif sql_type == SXTSqlType.DML and resources: |
| 163 | success, rtn = user.base_api.sql_dml(sql_text=sql_text, biscuits=biscuits, app_name=self.application_name, resources=resources) |
| 164 | |
| 165 | elif sql_type == SXTSqlType.DQL and resources: |
| 166 | success, rtn = user.base_api.sql_dql(sql_text=sql_text, biscuits=biscuits, app_name=self.application_name, resources=resources) |
| 167 | |
| 168 | else: |
| 169 | success, rtn = user.base_api.sql_exec(sql_text=sql_text, biscuits=biscuits, app_name=self.application_name) |
| 170 | else: |
| 171 | success, rtn = (True, [{'col1':'data', 'col2':'data'},{'col1':'data', 'col2':'data'},{'col1':'data', 'col2':'data'}] ) |
| 172 | |
| 173 | if not success: raise SxTQueryError(f'Query Failed: {str(rtn)}', logger=self.logger) |
| 174 | |
| 175 | except SxTQueryError as ex: |
| 176 | self.logger.error(f'Error in query execution: {ex}') |
| 177 | return False, {'error':f'Error in query execution: {ex}'} |
| 178 | |
| 179 | if output_format == SXTOutputFormat.JSON: return True, rtn |