:py:mod:`thanosql.resources` ============================ .. py:module:: thanosql.resources Package Contents ---------------- Classes ~~~~~~~ .. autoapisummary:: thanosql.resources.FileService thanosql.resources.QueryService thanosql.resources.SchemaService thanosql.resources.TableService thanosql.resources.ViewService .. py:class:: FileService(client: thanosql._client.ThanoSQL) Bases: :py:obj:`thanosql._service.ThanoSQLService` Service layer for file methods. .. attribute:: client The ThanoSQL client used to make requests to the engine. :type: ThanoSQL .. py:method:: get(path: Optional[Union[str, os.PathLike]] = None, option: Optional[str] = None) -> Optional[Content] Details the information of a file or directory in the specified path. :param path: The path to the file/directory relative to the user data root (default value is "/", the user data root directory itself) :type path: str or path_like :param option: - default (None): retrieves file/directory information - download: downloads a file (directory download is not possible) :type option: controls the behavior of the API :returns: Depending on option: - default (None): Returns a Content Pydantic object containing information on the target path - download: Downloads the requested file and returns nothing :rtype: Content | None :raises ThanoSQLValueError: If user attempts to download a directory (when option="download") .. py:method:: create(path: Optional[Union[str, os.PathLike]] = None, file: Optional[Union[str, os.PathLike]] = None) -> Content Uploads a file to the workspace or creates an empty folder. :param path: The destination path of the upload/folder creation relative to the user data root. Should point to a (would-be) directory (default value is "/", the user data root directory itself) :type path: str or path_like :param file: The file to be uploaded. If left empty, creates an empty folder in the given path. :type file: str or PathLike, optional :rtype: A Content Pydantic object containing information on the created file/folder. :raises ThanoSQLValueError: If path does not point to a (would-be) directory .. py:method:: delete(path: Union[str, os.PathLike]) -> None Deletes the specified file or directory permanently from the workspace. :param path: The path to the file or directory to be removed from the workspace. Relative to the user data root, no default value. :type path: str or path_like :rtype: No content .. py:class:: QueryService(client: thanosql._client.ThanoSQL) Bases: :py:obj:`thanosql._service.ThanoSQLService` Service layer for query methods. .. attribute:: client The ThanoSQL client used to make requests to the engine. :type: ThanoSQL .. attribute:: log The query log service layer to access methods involving query logs. :type: QueryLogService .. attribute:: template The query template service layer to access methods involving query templates. :type: QueryTemplateService .. py:method:: execute(query: Optional[str] = None, template_id: Optional[int] = None, template_name: Optional[str] = None, parameters: Optional[dict] = None, schema: Optional[str] = None, table_name: Optional[str] = None, overwrite: Optional[bool] = None, keep_temp_table: Optional[bool] = None, max_results: int = 100) -> QueryLog Executes a query string. There are three ways of requesting a query: - Using a complete query directly in `query`, leaving `template_id`, `template_name`, and `parameters` empty. - Using a template query in `query` and completing it with `parameters`, leaving `template_id` and `template_name` empty. - Recalling a template query from the database using `template_id` or `template_name` (but not both) and completing it with `parameters`, leaving `query` empty. One, and only one, of these ways must be chosen. That is, exactly one of `query`, `template_id`, or `template_name` must be specified. If none or more than one way is selected, an error will occur. :param query: The query string or template to be executed. :type query: str, optional :param template_id: The ID number of the query template to be used. Only relevant when a query template from the database is needed, and `query` and `template_name` are not used simultaneously. :type template_id: int, optional :param template_name: The name of the query template to be used. Only relevant when a query template from the database is needed, and `query` and `template_id` are not used simultaneously. :type template_name: str, optional :param parameters: A dictionary of parameter names and values to fill in the template. Only relevant when a query template, either from `query` or the database, is used. :type parameters: dict, optional :param schema: The schema of the table to save the query results in. If not specified and table_name is also empty, it defaults to "qm". If table_name is specified, it defaults to "public". :type schema: str, optional :param table_name: The name of the table to save the query results in. If not specified while the query produces a result, an automatic table name will be given. :type table_name: str, optional :param overwrite: Whether to overwrite the table if a table with the same `table_name` and `schema` already exists. If not specified, the value is False. :type overwrite: bool, optional :param keep_temp_table: Whether to save temporary tables produced by query execution to the "qm" schema or not. If not specified, the value is True. :type keep_temp_table: bool, optional :param max_results: The maximum number of records to be returned by the response QueryLog. If not specified, it defaults to 100. :type max_results: int, optional :returns: A query log object containing details about the results of the executed query. :rtype: QueryLog :raises ThanoSQLValueError: - If invalid input combination is provided; that is: - query, template_id, and template_name are all empty, or - more than one of query, template_id, and template_name are non-empty - If max_results is not between 0 and 100 (inclusive). - If query and parameters are used but the template has invalid format. - If rendering query template by substituting in parameters fails, either by direct query template or templates from the database. .. py:method:: execute_values(query: str, values: Union[List[tuple], List[dict]], template: Optional[str] = None, page_size: int = 100, schema: Optional[str] = None, table_name: Optional[str] = None, overwrite: Optional[bool] = None, keep_temp_table: Optional[bool] = None, max_results: int = 100) -> QueryLog Executes a query string with a value placeholder. Unlike execute, query templates cannot be used, as this might be confusing when combined with value placeholders. Following psycopg, only a single main placeholder is allowed. If multiple placeholders are required, users should use templates. Unlike psycopg, we will use jinja formatting for consistency with query template purposes. :param query: The query string with a "{{ values }}" placeholder to be executed. :type query: str :param values: List of values to be inserted to the placeholder(s). :type values: list of tuples :param template: How values should be formatted inside {{ values }}. If it is left empty, values will just be listed inside brackets separated by commas. In this case, values should be a list of tuples. For example, we have the following values:: [(1, "gangnam", "seoul"), (2, "haeundae", "busan")] and we leave template empty. The original query looks like:: VALUES {{ values }} The rendered query will look like:: VALUES (1, 'gangnam', 'seoul'), (2, 'haeundae', 'busan') Meanwhile, if we have the following named values:: [ {"id": 1, "name": "gangnam", "city": "seoul"}, {"id": 2, "name": "haeundae", "city": "busan"}, ] and we have the following template:: ({{ id }}, CONCAT(INITCAP({{ name }}), '-gu'), INITCAP({{ city }})) the rendered query will be:: VALUES (1, CONCAT(INITCAP('gangnam', '-gu')), INITCAP('seoul')), (2, CONCAT(INITCAP('haeundae', '-gu')), INITCAP('busan')) :type template: str, optional :param page_size: The maximum number of rows (set of values) to be sent in one statement. If not specified, it defaults to 100. :type page_size: int, default 100 :param schema: The schema of the table to save the query results in. If not specified and table_name is also empty, it defaults to "qm". If table_name is specified, it defaults to "public". :type schema: str, optional :param table_name: The name of the table to save the query results in. If not specified while the query produces a result, an automatic table name will be given. :type table_name: str, optional :param overwrite: Whether to overwrite the table if a table with the same `table_name` and `schema` already exists. If not specified, the value is False. :type overwrite: bool, optional :param keep_temp_table: Whether to save temporary tables produced by query execution to the "qm" schema or not. If not specified, the value is True. :type keep_temp_table: bool, optional :param max_results: The maximum number of records to be returned by the response QueryLog. If not specified, it defaults to 100. :type max_results: int, default 100 :returns: A query log object containing details about the results of the executed query. :rtype: QueryLog :raises ThanoSQLValueError: - If values is empty. - If page_size is less than 1. - If max_results is not between 0 and 100 (inclusive). - If the formats of template and values don't match. .. py:class:: SchemaService(client: thanosql._client.ThanoSQL) Bases: :py:obj:`thanosql._service.ThanoSQLService` Service layer for schema methods. .. attribute:: client The ThanoSQL client used to make requests to the engine. :type: ThanoSQL .. py:method:: list() -> dict Lists schemas stored in the workspace. Does not have any parameters. :returns: A dictionary containing the names of stored schemas in the format of:: { "schemas": [ { "name": "string" } ] } :rtype: dict .. py:method:: create(name: str) -> dict Creates a new schema in the workspace. :param name: The name of the schema to be created. :type name: str :returns: A dictionary containing the name of the created schema and a success message in the format of:: { "schema": "string", "message": "string" } :rtype: dict .. py:class:: TableService(client: thanosql._client.ThanoSQL) Bases: :py:obj:`thanosql._service.ThanoSQLService` Service layer for table methods. .. attribute:: client The ThanoSQL client used to make requests to the engine. :type: ThanoSQL .. attribute:: template The table template service layer to access methods involving table templates. :type: TableTemplateService .. py:method:: list(schema: Optional[str] = None, verbose: Optional[bool] = None, offset: Optional[int] = None, limit: Optional[int] = None) -> List[Table] Lists tables stored in the workspace. :param schema: The schema where the listed tables should reside in. If not set, all tables from all schemas will be included. :type schema: str, optional :param verbose: Whether to include the table columns and constraints in the results. By default, or if set to False, only retrieves the names and schemas of stored tables. :type verbose: bool, optional :param offset: When set to n, skips the first n results and excludes them from the output list. Otherwise, starts the list from the first result stored. Must be greater than 0. :type offset: int, optional :param limit: When set to n, limits the number of results listed to n. Otherwise, lists up to 100 results per call. Must range between 0 to 100. :type limit: int, optional :returns: A list of Table objects. :rtype: List[Table] :raises ThanoSQLValueError: If offset is less than 0 or if limit is not between 0 to 100 (inclusive). .. py:method:: get(name: str, schema: Optional[str] = None) -> Table Shows the details of the specified table. :param name: The name of the table to be retrieved. :type name: str :param schema: The schema where the table to be retrieved is in. If not specified, this method will look for the table in "public". :type schema: str, optional :returns: A Table object. :rtype: Table .. py:method:: update(name: str, schema: Optional[str] = None, table: Optional[BaseTable] = None) -> Table Updates the specified table. :param name: The name of the table to be updated. :type name: str :param schema: The schema where the table to be updated is in. If not specified, this method will look for the table in "public". :type schema: str, optional :param table: BaseTable object containing changed details of the table to be updated. Any attribute of BaseTable can be modified, and if left unset, the current value will be maintained after update. The attributes are as follows: - name: new name to rename the table to - schema: new schema to move the table to - columns: new columns of the updated table. All new columns must be in this object, including columns that already exist in the original table. If this attribute is set but some original columns are not included, they will be removed from the table. - constraints: new constraints of the updated table. All new constraints must be in this object, including constraints that already exist in the original table. If this attribute is set but some original constraints are not included, they will be removed from the table. :type table: BaseTable, optional :returns: Table object of the new table after update. :rtype: Table :raises ThanoSQLValueError: If the table object contains invalid formatting. .. py:method:: create(name: str, table: TableObject, schema: Optional[str] = None, if_not_exists: bool = False) -> Table Creates a new table. :param name: The name of the table to be created. :type name: str :param table: TableObject containing the columns and constraints of the table to be created. In order to create an empty table, pass in an empty object (TableObject()). :type table: TableObject :param schema: The schema to save the created table in. If not specified, the table will be saved to "public". :type schema: str, optional :param if_not_exists: Whether to throw an error if a table of the same name already exists. When set to False (default), an error will be shown. When True, the table will only be created if it does not exist already. Otherwise, do nothing. :type if_not_exists: bool, default False :returns: Table object of the created table. :rtype: Table :raises ThanoSQLValueError: If the table object contains invalid formatting. .. py:method:: upload(name: str, file: Optional[Union[str, os.PathLike]] = None, df: Optional[pandas.DataFrame] = None, schema: Optional[str] = None, table: Optional[TableObject] = None, if_exists: str = 'fail') -> Table Uploads the contents of a CSV or Excel-like file or Pandas DataFrame into the specified table. Either a CSV or Excel-like (.xls, .xlsx, .xlsm, .xlsb, .odf, .ods, .odt) file or DataFrame must be specified. However, both should not be used at the same time. Note: If you are working with a Strict Open XML Spreadsheet (*.xlsx) file, change it to Excel Workbook (*.xlsx), save it, and try using it again. :param name: The name of the table created from the file or DataFrame. :type name: str :param file: CSV or Excel-like file containing tabulated data to be uploaded to the specified table. :type file: str or PathLike, optional :param df: Pandas DataFrame containing data to be uploaded to the specified table. :type df: DataFrame, optional :param schema: The schema to save the created table in. If not specified, the table will be saved to "public". :type schema: str, optional :param table: TableObject containing the columns and constraints of the table to be created. If specified, the created table will follow the object format and no type inference is conducted. Otherwise, type inference will be performed and the table will be created to match the columns from source. :type table: TableObject, optional :param if_exists: What to do if table of the same name already exists. There are only three available values: - fail: fails (throws an error) if the same table exists - append: appends records into an existing table (columns must match in order to not make an error) - replace: deletes existing table and creates a new one with the given name :type if_exists: str, default "fail" :returns: Table object of the uploaded table. :rtype: Table :raises ThanoSQLValueError: - If if_exists is not one of "fail", "append", or "replace". - If neither file nor df is used, or if both are used at the same time. - If file is used but it is neither CSV nor Excel-like. - If the file or df contains badly-formatted contents. - If a table body is specified but does not match the contents of the file or df. - If if_exists is set to "append" but the new contents does not match the format of the existing table. .. py:method:: delete(name: str, schema: Optional[str] = None) -> dict Deletes the specified table. :param name: The name of the table to be deleted. :type name: str :param schema: The schema where the table to be deleted is in. If not specified, this method will look for the table in "public". :type schema: str, optional :returns: A dictionary containing a success message, table name, and schema in the format of:: { "message": "string", "table_name": "string", "schema": "string" } :rtype: dict .. py:class:: ViewService(client: thanosql._client.ThanoSQL) Bases: :py:obj:`thanosql._service.ThanoSQLService` Service layer for view methods. .. attribute:: client The ThanoSQL client used to make requests to the engine. :type: ThanoSQL .. py:method:: list(schema: Optional[str] = None, verbose: Optional[bool] = None, offset: Optional[int] = None, limit: Optional[int] = None) -> List[View] Lists views stored in the workspace. :param schema: When specified, lists views from a specific schema only. Otherwise, lists views from all available schemas. :type schema: str, optional :param verbose: When not set or set to False (default behavior), only lists view names and the schema they belong to. When set to True, includes column descriptions and view definition in the output on top of the basic (name and schema) information. :type verbose: bool, optional :param offset: When set to n, skips the first n views and excludes them from the output list. Otherwise, starts the list from the first view stored. Must be greater than 0. :type offset: int, optional :param limit: When set to n, limits the number of views listed to n. Otherwise, lists up to 100 views per call. Must range between 0 to 100. :type limit: int, optional :returns: A list of View objects. :rtype: List[View] :raises ThanoSQLValueError: If offset is less than 0 or if limit is not between 0 to 100 (inclusive). .. py:method:: get(name: str, schema: Optional[str] = None) -> View Shows the details of the specified view. :param name: The name of the view to be retrieved. :type name: str :param schema: The schema the target view is stored in. When not specified, defaults to "public". :type schema: str, optional :returns: A View object. :rtype: View .. py:method:: delete(name: str, schema: Optional[str] = None) -> dict Deletes the specified view. :param name: The name of the view to be deleted. :type name: str :param schema: The schema the target view is stored in. When not specified, defaults to "public". :type schema: str, optional :returns: A dictionary containing a success message and the name of the deleted view in the format of:: { "message": "string", "view_name": "string" } :rtype: dict