:mod:`snowmobile.core` ====================== .. py:module:: snowmobile.core .. autoapi-nested-parse:: ``snowmobile`` lives in ``snowmobile.core`` to keep from cluttering intellisense/autocomplete while interacting with the API. Subpackages ----------- .. toctree:: :titlesonly: :maxdepth: 3 cfg/index.rst Submodules ---------- .. toctree:: :titlesonly: :maxdepth: 1 configuration/index.rst connection/index.rst markup/index.rst qa/index.rst script/index.rst sql/index.rst statement/index.rst table/index.rst tag/index.rst Package Contents ---------------- Classes ~~~~~~~ .. autoapisummary:: snowmobile.core.Generic snowmobile.core.ExceptionHandler snowmobile.core.Configuration snowmobile.core.Snowmobile snowmobile.core.connect snowmobile.core.Section snowmobile.core.Scope snowmobile.core.Name snowmobile.core.Statement snowmobile.core.Column snowmobile.core.Diff snowmobile.core.Empty snowmobile.core.SnowFrame snowmobile.core.SQL snowmobile.core.Markup snowmobile.core.Script snowmobile.core.Table .. class:: Generic Bases: :class:`object` Generic dunder implementation for ``snowmobile`` objects. Base class for all ``snowmobile`` objects that do **not** inherit from pydantic's BaseModel or configuration class, :class:`Config`. .. class:: ExceptionHandler(within: snowmobile.core.errors.Optional[Any] = None, ctx_id: snowmobile.core.errors.Optional[int] = None, in_context: bool = False, children: snowmobile.core.errors.Dict[(int, Any)] = None, is_active_parent: bool = False, to_mirror: snowmobile.core.errors.Optional[List[Any]] = None) Bases: :class:`snowmobile.core.Generic` All :xref:`snowmobile` classes contain a :class:`ExceptionHandler`. :param within: Class for which the ExceptionHandler is intended. :type within: Optional[Any] :param ctx_id: Context ID; set/unset by methods when entering/exiting certain contexts. :type ctx_id: Optional[int] :param in_context: Class is currently within a specific :attr:`ctx_id` :type in_context: bool :param children: Attributes of the :attr:`within` class for which the :class:`ExceptionHandler` should mirror the methods called on the parent class. # TODO: Refactor this out; it's essentially janky multi inheritance :type children: Dict[int, Any] :param is_active_parent: The :attr:`within` class is currently enforcing the context rules on its :attr:`children` :type is_active_parent: bool :param to_mirror: Methods called in the `attr:`within` class that should be applied to its :attr:`children` (i.e. set/reset context ID, etc) :type to_mirror: Optional[List[Any]] .. method:: current(self) :property: All exceptions in the current context. .. method:: collect(self, e: Any[snowmobile_errors]) Stores an exception. .. method:: first(self) -> snowmobile.core.errors.Error :property: First exception encountered. .. method:: last(self) -> snowmobile.core.errors.Error :property: Last exception encountered. .. method:: seen(self, from_ctx: snowmobile.core.errors.Optional[int] = None, of_type: snowmobile.core.errors.Optional[(Any[snowmobile_errors], List[snowmobile_errors])] = None, to_raise: snowmobile.core.errors.Optional[bool] = None, with_ids: snowmobile.core.errors.Optional[(int, List[int], Set[int])] = None, all_time: bool = False) -> bool Boolean indicator of if an exception has been seen. .. method:: get(self, from_ctx: snowmobile.core.errors.Optional[int] = None, of_type: snowmobile.core.errors.Optional[(Any[snowmobile_errors], List[snowmobile_errors])] = None, to_raise: snowmobile.core.errors.Optional[bool] = None, with_ids: snowmobile.core.errors.Optional[(int, List[int], Set[int])] = None, all_time: bool = False, last: bool = False, first: bool = False, _raise: bool = False) Boolean indicator of if an exception has been seen. .. method:: ctx_id(self) :property: Current context id. .. method:: set(self, ctx_id: snowmobile.core.errors.Optional[int] = None, in_context: bool = False, outcome: snowmobile.core.errors.Optional[int] = None) **Set** attributes on self. .. method:: set_from(self, other: snowmobile.core.exception_handler.ExceptionHandler) -> snowmobile.core.exception_handler.ExceptionHandler Updates attributes of self with those from 'other'. .. method:: reset(self, ctx_id: bool = False, in_context: bool = False, outcome: bool = False) -> snowmobile.core.exception_handler.ExceptionHandler **Resets** attributes on self. .. method:: by_tmstmp(self) :property: All exceptions by timestamp, ordered by most to least recent. .. class:: Configuration(creds: Optional[str] = None, config_file_nm: Optional[str] = None, from_config: Optional[(Path, str)] = None, export_dir: Optional[(Path, str)] = None, silence: bool = False) Bases: :class:`snowmobile.core.base.Generic` A parsed `snowmobile.toml` file. *All keyword arguments optional.* :param config_file_nm: Name of configuration file to use; defaults to `snowmobile.toml`. :type config_file_nm: Optional[str] :param creds: Alias for the set of credentials to authenticate with; default behavior will fall back to the `connection.default-creds` specified in `snowmobile.toml`, `or the first set of credentials stored if this configuration option is left blank`. :type creds: Optional[str] :param from_config: A full path to a specific configuration file to use; bypasses any checks for a cached file location and can be useful for container-based processes with restricted access to the local file system. :type from_config: Optional[str, Path] :param export_dir: Path to save a template `snowmobile.toml` file to; if pr, the file will be exported within the __init__ method and nothing else will be instantiated. :type export_dir: Optional[Path] .. attribute:: file_nm Configuration file name; defaults to 'snowmobile.toml'. :type: str .. attribute:: cache Persistent cache; caches :attr:`location`. :type: snowmobile.core.cache.Cache .. attribute:: location Full path to configuration file. :type: pathlib.Path .. attribute:: connection :annotation: :Optional[cfg.Connection] **[connection]** from snowmobile.toml. :type: snowmobile.core.cfg.Connection .. attribute:: loading :annotation: :Optional[cfg.Loading] **[loading]** from snowmobile.toml. :type: snowmobile.core.cfg.Loading .. attribute:: script :annotation: :Optional[cfg.Script] **[script]** from snowmobile.toml. :type: snowmobile.core.cfg.Script .. attribute:: sql :annotation: :Optional[cfg.SQL] **[sql]** from snowmobile-ext.toml. :type: snowmobile.core.cfg.SQL .. attribute:: ext_sources :annotation: :Optional[cfg.Location] **[external-sources]** from snowmobile.toml. :type: snowmobile.core.cfg.Location .. method:: markdown(self) -> snowmobile.core.cfg.Markup :property: Accessor for cfg.script.markdown. .. method:: attrs(self) -> snowmobile.core.cfg.Attributes :property: Accessor for cfg.script.markdown.attributes. .. method:: wildcards(self) -> snowmobile.core.cfg.Wildcard :property: Accessor for cfg.script.patterns.wildcards. .. method:: batch_set_attrs(obj: Any, attrs: dict, to_none: bool = False) :staticmethod: Batch sets attributes on an object from a dictionary. :param obj: Object to set attributes on. :type obj: Any :param attrs: Dictionary containing attributes. :type attrs: dict :param to_none: Set all of the object's attributes batching a key in `wrap` to `None`; defaults ot `False`. :type to_none: bool Returns (Any): Object post-setting attributes. .. method:: attrs_from_obj(obj: Any, within: Optional[List[str]] = None) -> Dict[(str, MethodType)] :staticmethod: Utility to return attributes/properties from an object as a dictionary. .. method:: methods_from_obj(obj: Any, within: Optional[List[str]] = None) -> Dict[(str, MethodType)] :staticmethod: Returns callable components of an object as a dictionary. .. method:: scopes(self) :property: All combinations of scope type and scope attribute. .. method:: scopes_from_kwargs(self, only_populated: bool = False, **kwargs) -> Dict Turns *script.filter()* arguments into a valid set of kwargs for :class:`Scope`. Returns dictionary of all combinations of 'arg' ("kw", "obj", "desc", "anchor" and "nm"), including empty sets for any 'arg' not included in the keyword arguments provided. .. method:: scopes_from_tag(self, t: Any) Generates list of keyword arguments to instantiate all scopes for a wrap. .. method:: json(self, by_alias: bool = False, **kwargs) Serialization method for core object model. .. class:: Snowmobile(creds: Optional[str] = None, delay: bool = False, ensure_alive: bool = True, config_file_nm: Optional[str] = None, from_config: Optional[(str, Path)] = None, silence: bool = False, **connect_kwargs) Bases: :class:`snowmobile.core.sql.SQL` Primary method of statement execution and accessor to parsed snowmobile.toml. :param creds: Alias for the set of credentials to authenticate with; default behavior will fall back to the ``connection.default-creds`` specified in `snowmobile.toml`, `or the first set of credentials stored if this configuration option is left blank`. :type creds: Optional[str] :param delay: Optionally delay establishing a connection when the object is instantiated, enabling access to the configuration object model through the :attr:`Connection.cfg` attribute; defaults to `False`. :type delay: bool :param ensure_alive: Establish a new connection if a method requiring a connection against the database is called while :attr:`alive` is `False`; defaults to `True`. :type ensure_alive: bool :param config_file_nm: Name of configuration file to use; defaults to `snowmobile.toml`. :type config_file_nm: Optional[str] :param from_config: A full path to a specific configuration file to use; bypasses any checks for a cached file location and can be useful for container-based processes with restricted access to the local file system. :type from_config: Optional[str, Path] :param \*\*connect_kwargs: Additional arguments to provide to :xref:`snowflake.connector.connect()`; arguments provided here will over-ride connection arguments specified in `snowmobile.toml`, including: * Connection parameters in `connection.default-arguments` * Credentials parameters associated with a given alias * Connection parameters associated with a given alias Initializes a :class:`snowmobile.SQL` object. .. attribute:: cfg :annotation: :Configuration *snowmobile.toml* :type: snowmobile.core.configuration.Configuration .. attribute:: con :annotation: :Optional[SnowflakeConnection] Can be `None` until set by :meth:`Snowmobile.connect()` :type: SnowflakeConnection .. attribute:: e :annotation: :ExceptionHandler Exception / context management :type: snowmobile.core.exception_handler.ExceptionHandler .. attribute:: ensure_alive :annotation: :bool Reconnect to :ref:`Snowflake` if connection is lost :type: bool .. method:: connect(self, **kwargs) -> snowmobile.core.connection.Snowmobile Establishes connection to Snowflake. Re-implements `snowflake.connector.connect()` with connection arguments sourced from snowmobile's object model, specifically: * Credentials from `snowmobile.toml`. * Default connection arguments from `snowmobile.toml`. * Optional keyword arguments either passed to :meth:`snowmobile.connect()` or directly to this method. kwargs: Optional keyword arguments to pass to snowflake.connector.connect(); arguments passed here will over-ride ``connection.default-arguments`` specified in ``snowmobile.toml``. .. method:: disconnect(self) -> snowmobile.core.connection.Snowmobile Disconnect from connection with which Connection() was instantiated. .. method:: alive(self) -> bool :property: Check if the connection is alive. .. method:: cursor(self) -> snowflake.connector.connection.SnowflakeCursor :property: :class:`SnowflakeCursor` accessor. .. method:: dictcursor(self) -> snowflake.connector.DictCursor :property: :class:`DictCursor` accessor. .. method:: ex(self, sql: str, on_error: Optional[str] = None, **kwargs) -> snowflake.connector.connection.SnowflakeCursor Executes a command via :class:`SnowflakeCursor`. :param sql: ``sql`` command as a string. :type sql: str :param on_error: String value to impose a specific behavior if an error occurs during the execution of ``sql``. :type on_error: str :param \*\*kwargs: Optional keyword arguments for :meth:`SnowflakeCursor.execute()`. Returns (SnowflakeCursor): :class:`SnowflakeCursor` object that executed the command. .. method:: exd(self, sql: str, on_error: Optional[str] = None, **kwargs) -> snowflake.connector.DictCursor Executes a command via :class:`DictCursor`. :param sql: ``sql`` command as a string. :type sql: str :param on_error: String value to impose a specific behavior if an error occurs during the execution of ``sql``. :type on_error: str :param \*\*kwargs: Optional keyword arguments for :meth:`SnowflakeCursor.execute()`. Returns (DictCursor): :class:`DictCursor` object that executed the command. .. method:: query(self, sql: str, as_df: bool = False, as_cur: bool = False, as_dcur: bool = False, as_scalar: bool = False, lower: bool = True, on_error: Optional[str] = None) -> Union[(pd.DataFrame, SnowflakeCursor)] Execute a query and return results. Default behavior of `results=True` will return results as a :class:`pandas.DataFrame`, otherwise will execute the sql provided with a :class:`SnowflakeCursor` and return the cursor object. :param sql: Raw SQL to execute. :type sql: str :param as_df: Return results in DataFrame. :type as_df: bool :param as_cur: Return results in Cursor. :type as_cur: bool :param as_dcur: Return results in a DictCursor. :type as_dcur: bool :param as_scalar: Return results as a single scalar value. :type as_scalar: bool :param lower: Boolean value indicating whether or not to return results with columns lower-cased. :type lower: bool :param on_error: String value to impose a specific behavior if an error occurs during the execution of ``sql``. :type on_error: str Returns (Union[pd.DataFrame, SnowflakeCursor]): Results from ``sql`` as a :class:`DataFrame` by default or the :class:`SnowflakeCursor` object if `results=False`. .. class:: connect(creds: Optional[str] = None, delay: bool = False, ensure_alive: bool = True, config_file_nm: Optional[str] = None, from_config: Optional[(str, Path)] = None, silence: bool = False, **connect_kwargs) Bases: :class:`snowmobile.core.sql.SQL` Primary method of statement execution and accessor to parsed snowmobile.toml. :param creds: Alias for the set of credentials to authenticate with; default behavior will fall back to the ``connection.default-creds`` specified in `snowmobile.toml`, `or the first set of credentials stored if this configuration option is left blank`. :type creds: Optional[str] :param delay: Optionally delay establishing a connection when the object is instantiated, enabling access to the configuration object model through the :attr:`Connection.cfg` attribute; defaults to `False`. :type delay: bool :param ensure_alive: Establish a new connection if a method requiring a connection against the database is called while :attr:`alive` is `False`; defaults to `True`. :type ensure_alive: bool :param config_file_nm: Name of configuration file to use; defaults to `snowmobile.toml`. :type config_file_nm: Optional[str] :param from_config: A full path to a specific configuration file to use; bypasses any checks for a cached file location and can be useful for container-based processes with restricted access to the local file system. :type from_config: Optional[str, Path] :param \*\*connect_kwargs: Additional arguments to provide to :xref:`snowflake.connector.connect()`; arguments provided here will over-ride connection arguments specified in `snowmobile.toml`, including: * Connection parameters in `connection.default-arguments` * Credentials parameters associated with a given alias * Connection parameters associated with a given alias Initializes a :class:`snowmobile.SQL` object. .. attribute:: cfg :annotation: :Configuration *snowmobile.toml* :type: snowmobile.core.configuration.Configuration .. attribute:: con :annotation: :Optional[SnowflakeConnection] Can be `None` until set by :meth:`Snowmobile.connect()` :type: SnowflakeConnection .. attribute:: e :annotation: :ExceptionHandler Exception / context management :type: snowmobile.core.exception_handler.ExceptionHandler .. attribute:: ensure_alive :annotation: :bool Reconnect to :ref:`Snowflake` if connection is lost :type: bool .. method:: connect(self, **kwargs) -> snowmobile.core.connection.Snowmobile Establishes connection to Snowflake. Re-implements `snowflake.connector.connect()` with connection arguments sourced from snowmobile's object model, specifically: * Credentials from `snowmobile.toml`. * Default connection arguments from `snowmobile.toml`. * Optional keyword arguments either passed to :meth:`snowmobile.connect()` or directly to this method. kwargs: Optional keyword arguments to pass to snowflake.connector.connect(); arguments passed here will over-ride ``connection.default-arguments`` specified in ``snowmobile.toml``. .. method:: disconnect(self) -> snowmobile.core.connection.Snowmobile Disconnect from connection with which Connection() was instantiated. .. method:: alive(self) -> bool :property: Check if the connection is alive. .. method:: cursor(self) -> snowflake.connector.connection.SnowflakeCursor :property: :class:`SnowflakeCursor` accessor. .. method:: dictcursor(self) -> snowflake.connector.DictCursor :property: :class:`DictCursor` accessor. .. method:: ex(self, sql: str, on_error: Optional[str] = None, **kwargs) -> snowflake.connector.connection.SnowflakeCursor Executes a command via :class:`SnowflakeCursor`. :param sql: ``sql`` command as a string. :type sql: str :param on_error: String value to impose a specific behavior if an error occurs during the execution of ``sql``. :type on_error: str :param \*\*kwargs: Optional keyword arguments for :meth:`SnowflakeCursor.execute()`. Returns (SnowflakeCursor): :class:`SnowflakeCursor` object that executed the command. .. method:: exd(self, sql: str, on_error: Optional[str] = None, **kwargs) -> snowflake.connector.DictCursor Executes a command via :class:`DictCursor`. :param sql: ``sql`` command as a string. :type sql: str :param on_error: String value to impose a specific behavior if an error occurs during the execution of ``sql``. :type on_error: str :param \*\*kwargs: Optional keyword arguments for :meth:`SnowflakeCursor.execute()`. Returns (DictCursor): :class:`DictCursor` object that executed the command. .. method:: query(self, sql: str, as_df: bool = False, as_cur: bool = False, as_dcur: bool = False, as_scalar: bool = False, lower: bool = True, on_error: Optional[str] = None) -> Union[(pd.DataFrame, SnowflakeCursor)] Execute a query and return results. Default behavior of `results=True` will return results as a :class:`pandas.DataFrame`, otherwise will execute the sql provided with a :class:`SnowflakeCursor` and return the cursor object. :param sql: Raw SQL to execute. :type sql: str :param as_df: Return results in DataFrame. :type as_df: bool :param as_cur: Return results in Cursor. :type as_cur: bool :param as_dcur: Return results in a DictCursor. :type as_dcur: bool :param as_scalar: Return results as a single scalar value. :type as_scalar: bool :param lower: Boolean value indicating whether or not to return results with columns lower-cased. :type lower: bool :param on_error: String value to impose a specific behavior if an error occurs during the execution of ``sql``. :type on_error: str Returns (Union[pd.DataFrame, SnowflakeCursor]): Results from ``sql`` as a :class:`DataFrame` by default or the :class:`SnowflakeCursor` object if `results=False`. .. class:: Section(cfg: snowmobile.core.Configuration, is_marker: bool = None, h_contents: Optional[str] = None, index: Optional[int] = None, parsed: Optional[Dict] = None, raw: Optional[str] = None, sql: Optional[str] = None, results: Optional[pd.DataFrame] = None, incl_sql_tag: bool = False, is_multiline: bool = False, result_wrap: Optional[str] = None) Bases: :class:`snowmobile.core.Generic` Represents any (1-6 level) header section within `Script Name (doc).md`. Class is created with a call to the :meth:`~snowmobile.core.statement.Statement.as_section()` method or by the :class:`snowmobile.core.markup.Markup` class in the case of a :class:`~snowmobile.core.cfg.script.Marker`. In order to include execution metadata if available without sacrificing base-case parsing, the below implementation heavily relies properties over attributes to reconcile what's populated in the :attr:`~snowmobile.Script.st` vs :attr:`~snowmobile.Script.executed` attributes of :class:`~snowmobile.core.Script`. :param is_marker: Information provided is associated with a marker as opposed to a statement; defaults to *False*. :type is_marker: bool :param h_contents: String representation of header contents. :type h_contents: str :param index: Statement index position or *None* if marker. :type index: int :param parsed: Parsed arguments from the statement or marker within the script. :type parsed: dict :param raw: Raw wrap as :attr:`parsed` was parsed from. :type raw: str :param sql: Statement's raw sql or *None* if marker. :type sql: str :param results: Results returned by statement's sql as a :class:`~pandas.DataFrame`; will be *None* if statement hasn't been executed or if a marker. :type results: pd.DataFrame .. attribute:: hx String representation of header level (e.g. '#' for h1), based on the script/statement header-level specifications in `snowmobile.toml`. :type: str Instantiation of a ``script.Section`` object. .. method:: reorder_attrs(self, parsed: dict, cfg: snowmobile.core.Configuration) -> Dict Re-orders parsed attributes based on configuration. .. method:: parse_contents(self, cfg: snowmobile.core.Configuration) -> List[Item] Unpacks sorted dictionary of parsed attributes into formatted Items. .. method:: header(self) -> str :property: Constructs the header for a section. Uses specifications in `snowmobile.toml` to determine: (1) The level of the header depending on whether it's a statement section or a script section. (2) Whether or not to include the statement index as part of the header. :returns: Formatted header line as a string. .. method:: sql_md(self) -> str :property: Returns renderable sql or an empty string if script-level section. .. method:: body(self) -> str :property: All section content except for header. .. method:: md(self) -> str :property: Constructs a full section as a string from various components. :returns: Full string of valid markdown for the section. .. class:: Scope(arg: str, base: str) Bases: :class:`snowmobile.core.Generic` Handles the scope/context for :class:`Statement` objects and derived classes. Should never be interacted with from the user-facing API. .. attribute:: base The left-most word within a statement wrap. For **generic** st this will be the `keyword` and for **QA** statements this will be the literal word ``qa``. :type: str .. attribute:: component The component within a given wrap that is being evaluated; this will be exactly **one** of `kw`, `obj`, `anchor`, `desc`, or `nm`. :type: str .. attribute:: incl_arg The keyword argument that would be used to exclude a given component; * e.g. if :attr:`component` is `kw`, :attr:`incl_arg` would be ``incl_kw``. :type: str .. attribute:: excl_arg The keyword argument that would be used to exclude a given component; this would be the same as the above example except the value would be ``excl_kw`` as opposed to ``incl_kw``. :type: str .. attribute:: fallback_to The default values to fall back to for :attr:`incl_arg` and :attr:`excl_arg` if they are not passed as a keyword argument by the user in :class:`Script`; defaults to including the :attr:`base` and excluding an empty list. :type: dict provided_args (dict): The set of keyword arguments provided at the time of the last call to :meth:`eval()`. check_against_args (dict): The set of keyword arguments checked against at the time of the last call to :meth:`eval()`; will use provided arguments if they exist and the arguments from :attr:`fallback_to` otherwise. is_included (bool): Name is included based on the results of the last call to :meth:`eval()`. is_excluded (bool): Name is excluded based on the results of the last call to :meth:`eval()`. Instantiates a :class:`Scope` object. .. method:: parse_kwargs(self, **kwargs) -> None Parses all filter arguments looking for those that match its base. Looks for include/exclude arguments within kwargs, populating :attr:`provided_args` with those that were provided and populates :attr:`check_against_args` with the same values if they were provided and fills in defaults from :attr:`fallback_to` otherwise. :param \*\*kwargs: Keyword arguments passed to :class:`Script.filter()` (e.g. `incl_kw`, `excl_kw`, ..) .. method:: matches_patterns(self, arg: str) -> bool Returns indication of if :attr:`base` matches a given set of patterns. :param arg: Will either be the value of :attr:`incl_arg` or :attr:`exclude_arg`. :type arg: str Returns (bool): Indication of whether any matches were found. .. method:: included(self) :property: Name is included based on results of last :meth:`eval()`. .. method:: eval(self, **kwargs) -> bool Evaluates filter arguments and updates context accordingly. Updates the values of :attr:`is_included`, :attr:`is_excluded`, and :attr:`included`. :param \*\*kwargs: Keyword arguments passed to :class:`Script.filter()` (e.g. `incl_kw`, `excl_kw`, ..) Returns (bool): Indicator of whether or not the statement should be included/excluded based on the context/keyword arguments pr. .. class:: Name(configuration: snowmobile.core.Configuration, nm_pr: Optional[str] = None, sql: Optional[str] = None, index: Optional[int] = None) Bases: :class:`snowmobile.core.Generic` Handles the decomposition/parsing of statement name. Should never be instantiated directly by the user-facing API but its attributes are likely to be accessed often as part of :class:`Statement` and derived classes. .. attribute:: cfg :class:`snowmobile.Configuration` object; represents fully parsed **snowmobile.toml** file. :type: snowmobile.Configuration .. attribute:: patt :class:`snowmobile.Schema.Pattern` object; represents ``script.patterns`` section of **snowmobile.toml**. :type: snowmobile.Schema.Pattern .. attribute:: _nm_pr Provided wrap name for a given :class:`Statement`; can be empty. :type: str .. attribute:: index Statement index position within :class:`Script`; can be empty. :type: int .. attribute:: is_included Indicator of whether or not the combination of all scopes for this statement wrap is included within a given context. :type: bool .. attribute:: incl_idx_in_desc Indicator of whether or not to include the statement index in the `description` component of the wrap; defaults to `True` so that all generated statement tags are guaranteed to be unique for a given script. * Mainly included for testing purposes where setting to `False` enables comparing generated to provided statement tags without having to change the index position of the hard-coded/pr statement wrap when adding/removing tests. :type: bool .. attribute:: first_line_remainder The remainder of the first line once excluding the :attr:`first_keyword` and stripping repeating whitespace. :type: str .. attribute:: scopes Combination of all scopes for a given wrap; this is essentially the all possible combinations of including/excluding any of the `kw`, `nm`, `obj`, `desc`, and `anchor` for a given instance of :class:`Name`. :type: set[Scope] .. method:: scope(self, **kwargs) -> bool Evaluates all component's of a wrap's scope against a set of filter args. **kwargs: Keyword arguments passed to :class:`Script.filter()` (e.g. `incl_kw`, `excl_kw`, ..) Returns (bool): Value indicating whether or not the statement should be included based on the outcome of the evaluation of all of its components. .. method:: nm(self, ge: bool = False, pr: bool = False, og: bool = True) -> str The final statement's **name** that is used by the API. This will be the full statement name if a tag exists and a parsed/generated name otherwise. .. method:: kw(self, ge: bool = False, pr: bool = False) The final statement's **keyword** that is used by the API. This will be the provided keyword if a statement wrap exists and a parsed/ge keyword otherwise. .. method:: obj(self, ge: bool = False, pr: bool = False) The final statement's **object** that is used by the API. This will be the object within a wrap if a statement wrap exists and follows the correct structure and a parsed/ge object otherwise. .. method:: desc(self, ge: bool = False, pr: bool = False) The final statement's **description** that is used by the API. This will be the description within a wrap if a statement wrap exists and follows the correct structure and a parsed/ge description otherwise. .. method:: anchor(self, ge: bool = False, pr: bool = False) The final statement's **anchor** that is used by the API. This will be the anchor within a wrap if a statement wrap exists and follows the correct structure and a parsed/ge wrap name otherwise. .. method:: set(self, key, value) -> snowmobile.core.name.Name Custom attribute setting. .. class:: Statement(sn: snowmobile.core.connection.Snowmobile, statement: Union[(sqlparse.sql.Statement, str)], index: Optional[int] = None, attrs_raw: Optional[str] = None, e: Optional[ExceptionHandler] = None, **kwargs) Bases: :class:`snowmobile.core.tag.Attrs`, :class:`snowmobile.core.Name`, :class:`snowmobile.core.Generic` Base class for all :class:`Statement` objects. Home for attributes and methods that are associated with **all** statement objects, generic or QA. .. attribute:: sn :class:`snowmobile.connect` object. :type: snowmobile.connect .. attribute:: statement A :class:`sqlparse.sql.Statement` object. :type: Union[sqlparse.sql.Statement, str] .. attribute:: index The context-specific index position of a statement within a script; can be `None`. :type: int .. attribute:: patterns :class:`config.Pattern` object for more succinct access to values specified in **snowmobile.toml**. :type: config.Pattern .. attribute:: results The results of the statement if executed as a :class:`pandas.DataFrame`. :type: pd.DataFrame .. attribute:: outcome Numeric indicator of outcome; defaults to `0` and is modified based on the outcome of statement execution and/or QA validation for derived classes. :type: int .. attribute:: outcome_txt Plain text of outcome ('skipped', 'failed', 'completed', 'passed'). :type: str .. attribute:: outcome_html HTML text for the outcome as an admonition/information banner based on the following mapping of :attr:`outcome_txt` to admonition argument: * `failed` ------> `warning` * `completed` --> `info` * `passed` -----> `success` :type: str .. attribute:: start_time Unix timestamp of the query start time if executed; 0 otherwise. :type: int .. attribute:: end_time Unix timestamp of the query end time if executed; 0 otherwise. :type: int .. attribute:: execution_time Execution time of the query in seconds if executed; 0 otherwise. :type: int .. attribute:: execution_time_txt Plain text description of execution time if executed; returned in seconds if execution time is less than 60 seconds, minutes otherwise. :type: str .. attribute:: first_keyword The first keyword within the statement as a :class:`sqlparse.sql.Token`. :type: sqlparse.sql.Token .. attribute:: sql The sql associated with the statement as a raw string. :type: str Initialize self. See help(type(self)) for accurate signature. .. method:: sql(self, set_as: Optional[str] = None, tag: bool = False) -> Union[(str, Statement)] Raw sql from statement, including result limit if enabled. .. method:: parse(self) -> Tuple[(Dict, str)] Parses tag contents into a valid dictionary. Uses the values specified in **snowmobile.toml** to parse a raw string of statement attributes into a valid dictionary. .. note:: * If :attr:`is_multiline` is `True` and `name` is not included within the arguments, an assertion error will be thrown. * If :attr:`is_multiline` is `False`, the raw string within the wrap will be treated as the name. * The :attr:`wrap` attribute is set once parsing is completed and name has been validated. Returns (dict): Parsed wrap arguments as a dictionary. .. method:: start(self) Sets :attr:`start_time` attribute. .. method:: end(self) Updates execution time attributes. In namespace, sets: * :attr:`end_time` * :attr:`execution_time` * :attr:`execution_time_txt` .. method:: trim(self) -> str Statement as a string including only the sql and a single-line wrap name. .. note:: The wrap name used here will be the user-pr wrap from the original script or a generated :attr:`Name.nm` if a wrap was not provided for a given statement. .. method:: is_derived(self) :property: Indicates whether or not it's a generic or derived (QA) statement. .. method:: lines(self) -> List[str] :property: Returns each line within the statement as a list. .. method:: as_section(self, incl_sql_tag: Optional[bool] = None, result_wrap: Optional[str] = None) -> snowmobile.core.Section Returns current statement as a :class:`Section` object. .. method:: set_state(self, index: Optional[int] = None, ctx_id: Optional[int] = None, in_context: Optional[bool] = None, filters: dict = None) -> snowmobile.core.statement.Statement Sets current state/context on a statement object. :param ctx_id: Unix timestamp the :meth:`script.filter()` context manager was invoked. :type ctx_id: int :param filters: Kwargs passed to :meth:`script.filter()`. :type filters: dict :param index: Integer to set as the statement's index position. :type index: int .. method:: reset(self, index: bool = False, ctx_id: bool = False, in_context: bool = False, scope: bool = False) -> snowmobile.core.statement.Statement Resets attributes on the statement object to reflect as if read from source. In its current form, includes: * Resetting the statement/wrap's index to their original values. * Resetting the :attr:`is_included` attribute of the statement's :attr:`wrap` to `True`. * Populating :attr:`error_last` with errors from current context. * Caching current context's timestamp and resetting back to `None`. .. method:: process(self) Used by derived classes for post-processing the returned results. .. method:: run(self, as_df: bool = True, lower: bool = True, render: bool = False, on_error: Optional[str] = None, on_exception: Optional[str] = None, on_failure: Optional[str] = None, ctx_id: Optional[int] = None) -> snowmobile.core.statement.Statement Run method for all statement objects. :param as_df: Store results of query as :class:`pandas.DataFrame` or :class:`SnowflakeCursor`. :type as_df: bool :param lower: Lower case column names in :attr:`results` DataFrame if `as_df=True`. :type lower: bool :param render: Render the sql executed as markdown. :type render: bool :param on_error: Behavior if an execution/database error is encountered * `None`: default behavior, exception will be raised * `c`: continue with execution :type on_error: str :param on_exception: Behavior if an exception is raised in the **post-processing** of results from a derived class of :class:`Statement` ( :class:`Empty` and :class:`Diff`). * `None`: default behavior, exception will be raised * `c`: continue with execution :type on_exception: str :param on_failure: Behavior if no error is encountered in execution or post-processing but the result of the post-processing has turned the statement's :attr:`outcome` attribute to False, indicating the results returned by the statement have failed validation. * `None`: default behavior, exception will be raised * `c`: continue with execution :type on_failure: str Returns (Statement): Statement object post-executing query. .. method:: outcome_txt(self, _id: Optional[int] = None) -> str Outcome as a string. .. method:: outcome_html(self) -> str :property: Outcome as an html admonition banner. .. class:: Column(original: str, current: Optional[str] = None, prior: Optional[str] = None, src: Optional[str] = None) Bases: :class:`snowmobile.core.Generic` A single column within a :class:`~snowmobile.core.snowframe.SnowFrame`. .. attribute:: original Original column name. :type: str .. attribute:: current Current version of the column name. :type: str .. attribute:: prior Prior version of the column name (version `n-1`). :type: str .. attribute:: src Column source (original ``df`` or added by ``snowmobile``). :type: str .. attribute:: original Original column name. :type: str .. attribute:: src Source of column; 'df' if from source DataFrame, 'snowmobile' otherwise. :type: str .. attribute:: current Current column name. :type: str .. attribute:: prior Prior (version of) column name. :type: str .. method:: update(self) Migrate from prior to current context within this context. .. method:: lower(self) -> str Lower case column. .. method:: upper(self) -> str Upper case column. .. method:: dedupe(current: str, char: Optional[str] = None) -> str :staticmethod: Dedupes consecutive characters within a string. .. note:: * Must iterate through matches and perform replacements in the order of the **largest to the smallest by number of characters**; this is to avoid altering the matches found before replacing them. :param current: String containing characters to dedupe. :type current: str :param char: Character to dedupe. :type char: str .. method:: reformat(self, fill_char: Optional[str] = None, dedupe_special: bool = True) -> str Reformat column for a load to the database. :param fill_char: Character to replace special characters and whitespace with; defaults to `_`. :type fill_char: str :param dedupe_special: Dedupe consecutive special characters; defaults to `True`. :type dedupe_special: bool .. class:: Diff(sn: snowmobile.core.connection.Snowmobile = None, **kwargs) Bases: :class:`snowmobile.core.qa.QA` QA class for comparison of values within a table based on partitioning on a field. .. attribute:: partition_on Column name to partition data on before comparing the partitioned datasets; defaults to 'src_description`. :type: str .. attribute:: end_index_at Column name that marks the last column to use as an index column when joining the partitioned datasets back together. :type: str .. attribute:: compare_patterns Regex patterns to match columns on that should be *included* in comparison (numeric columns you're running QA on). :type: list .. attribute:: ignore_patterns Regex patterns to match columns on that should be *ignored* both for the comparison and the index. :type: list .. attribute:: generic_metric_col_nm Column name to use for the melted field names; defaults to 'Metric'. :type: str .. attribute:: compare_cols Columns that are used in comparison once statement is executed and parsing is applied. :type: list .. attribute:: drop_cols Columns that are dropped once statement is executed and parsing is applied. :type: list .. attribute:: idx_cols Columns that are used for the index to join the data back together once statement is executed and parsing is applied. :type: list .. attribute:: ub_raw Maximum absolute raw difference (upper bound) that two fields that are being compared can differ from each other without causing a failure. :type: float .. attribute:: ub_perc Maximum absolute percentage difference (upper bound) that two comparison fields can differ from each other without causing a failure. :type: float Instantiates a ``qa-diff`` statement. :param delta_column_suffix: Suffix to add to columns that comparison is being run on; defaults to 'Delta'. :type delta_column_suffix: str :param partition_on: Column to partition the data on in order to compare. :type partition_on: str :param end_index_at: Column name that marks the last column to use as an index when joining the partitioned datasets back together. :type end_index_at: str :param compare_patterns: Regex patterns matching columns to be *included* in comparison. :type compare_patterns: list :param ignore_patterns: Regex patterns to match columns on that should be *ignored* both for the comparison and the index. :type ignore_patterns: list :param generic_metric_col_nm: Column name to use for the melted field names; defaults to 'Metric'. :type generic_metric_col_nm: str :param raw_upper_bound: Maximum absolute raw difference that two fields that are being compared can differ from each other without causing a failure. :type raw_upper_bound: float :param percentage_upper_bound: Maximum absolute percentage difference that two comparison fields can differ from each other without causing a failure. :type percentage_upper_bound: float .. method:: split_cols(self) -> snowmobile.core.qa.Diff Post-processes results returned from a ``qa-diff`` statement. Executes private methods to split columns into: * Index columns * Drop columns * Comparison columns Then runs checks needed to ensure minimum requirements are met in order for a valid partition/comparison to be made. .. method:: partitioned_by(self) -> Set[Any] :property: Distinct values within the ``partition_on`` column that data is partitioned by. .. method:: partitions_are_equal(partitions: Dict[(str, pd.DataFrame)], abs_tol: float, rel_tol: float) -> bool :staticmethod: Evaluates if a dictionary of DataFrames are identical. :param partitions: A dictionary of DataFrames returned by :meth:`snowmobile.DataFrame`. :type partitions: Dict[str, pd.DataFrame] :param abs_tol: Absolute tolerance for difference in any value amongst the DataFrames being compared. :type abs_tol: float :param rel_tol: Relative tolerance for difference in any value amongst the DataFrames being compared. :type rel_tol: float Returns (bool): Indication of equality amongst all the DataFrames contained in ``partitions``. .. method:: process(self) -> snowmobile.core.qa.Diff Post-processing for :class:`Diff`-specific results. .. class:: Empty(sn: snowmobile.core.connection.Snowmobile, **kwargs) Bases: :class:`snowmobile.core.qa.QA` QA class for verification that a statement's results are empty. The most widely applicable use of :class:`Empty` is for simple verification that a table's dimensions are as expected. Initialize self. See help(type(self)) for accurate signature. .. method:: process(self) -> snowmobile.core.qa.QA Over-ride method; checks if results are empty and updates outcome .. class:: SnowFrame(df: pandas.DataFrame) Bases: :class:`snowmobile.core.Generic` Extends a :class:`~pandas.DataFrame` with a ``.snf`` entry point. .. method:: shared_cols(self, df2: pandas.DataFrame) -> List[Tuple[pd.Series, pd.Series]] Returns list of tuples containing column pairs that are common between two DataFrames. .. method:: series_max_diff_abs(col1: pandas.Series, col2: pandas.Series, tolerance: float) -> bool :staticmethod: Determines if the max **absolute** difference between two :class:`pandas.Series` is within a tolerance level. .. method:: series_max_diff_rel(col1: pandas.Series, col2: pandas.Series, tolerance: float) -> bool :staticmethod: Determines if the maximum **relative** difference between two :class:`pandas.Series` is within a tolerance level. .. method:: df_max_diff_abs(self, df2: pandas.DataFrame, tolerance: float) -> bool Determines if the maximum **absolute** difference between any value in the shared columns of 2 DataFrames is within a tolerance level. .. method:: df_max_diff_rel(self, df2: pandas.DataFrame, tolerance: float) -> bool Determines if the maximum **relative** difference between any value in the shared columns of 2 DataFrames is within a tolerance level. .. method:: df_diff(self, df2: pandas.DataFrame, abs_tol: Optional[float] = None, rel_tol: Optional[float] = None) -> bool Determines if the column-wise difference between two DataFrames is within a relative **or** absolute tolerance level. .. note:: * ``df1`` and ``df2`` are assumed to have a shared, pre-defined index. * Exactly **one** of ``abs_tol`` and ``rel_tol`` is expected to be a a valid float; the other is expected to be **None**. * If valid float values are provided for both ``abs_tol`` and ``rel_tol``, the outcome of the maximum **absolute** difference with respect to ``abs_tol`` will be returned regardless of the value of ``rel_tol``. :param df2: 2nd DataFrame for comparison. :type df2: pd.DataFrame :param abs_tol: Absolute tolerance; default is None. :type abs_tol: float :param rel_tol: Relative tolerance; default is None. :type rel_tol: float Returns (bool): Boolean indicating whether or not difference is within tolerance. .. method:: partitions(self, on: str) -> Dict[(str, pd.DataFrame)] Returns a dictionary of DataFrames given a DataFrame and a partition column. .. note:: * The number of distinct values within ``partition_on`` column will be 1:1 with the number of partitions that are returned. * The ``partition_on`` column is dropped from the partitions that are returned. * The depth of a vertical concatenation of all partitions should equal the depth of the original DataFrame. :param on: The column name to use for partitioning the data. :type on: str Returns (Dict[str, pd.DataFrame]): Dictionary of {(str) partition_value: (pd.DataFrame) associated subset of df} .. method:: ddl(self, table: str) -> str Returns a string containing 'create table' DDL given a table name .. method:: lower(self, col: Optional[str] = None) -> pandas.DataFrame Lower cases all column names **or** all values within `col` if pr. .. method:: upper(self, col: Optional[str] = None) -> pandas.DataFrame Upper cases all column names **or** all values within `col` if pr. .. method:: reformat(self) Re-formats DataFrame's columns via :class:`Column.reformat()`. .. method:: append_dupe_suffix(self) Adds a trailing index number '_i' to duplicate column names. .. method:: to_list(self, col: Optional[str] = None, n: Optional[int] = None) -> List Succinctly retrieves a column as a list. :param col: Name of column. :type col: str :param n: Number of records to return; defaults to full depth of column. :type n: int .. method:: add_tmstmp(self, col_nm: Optional[str] = None) -> pandas.DataFrame Adds a column containing the current timestamp to a DataFrame. :param col_nm: Name for column; defaults to `LOADED_TMSTMP`. :type col_nm: str .. method:: original(self) -> pandas.DataFrame :property: Returns the DataFrame in its original form (drops columns added by :class:`SnowFrame` and reverts to original column names). .. method:: has_dupes(self) -> bool :property: DataFrame has duplicate column names. .. method:: cols_matching(self, patterns: List[str], ignore_patterns: List[str] = None) -> List[str] Returns a list of columns given a list of patterns to find. :param patterns: List of regex patterns to match columns on. :type patterns: List[str] :param ignore_patterns: Optional list of regex patterns to exclude. :type ignore_patterns: List[str] Returns (List[str]): List of columns found/excluded. .. method:: cols_ending(self, nm: str, ignore_patterns: Optional[List] = None) -> List[str] Returns all columns up to ``nm`` in a DataFrame. :param nm: Name of column to end index at. :type nm: str :param ignore_patterns: Optional list of regex patterns to exclude in the list that's returned; primarily used to for getting `end-index-at` list while excluding `src_description`. :type ignore_patterns: List[str] Returns (List[str]): List of column names matching criterion. .. class:: SQL(_query_func: Callable, _cfg: snowmobile.core.configuration.Configuration, nm: Optional[str] = None, schema: Optional[str] = None, obj: Optional[str] = None, auto_run: Optional[bool] = True) Bases: :class:`snowmobile.core.Generic` SQL class for generation & execution of common sql commands. Intended to be interacted with as a parent of :class:`~snowmobile.Snowmobile`. .. note:: * All arguments except for :attr:`sn` are optional. * The benefit of setting the other attributes on an instance of :class:`SQL` is to (optionally) avoid passing the same information to multiple methods when generating a variety of statements around the same object. .. attribute:: nm Object name to use in generated sql (e.g. 'some_table_name') :type: str .. attribute:: obj Object type to use in generated sql (e.g. 'table') :type: str .. attribute:: schema Schema to use when dot-prefixing sql; defaults to the schema with which the :attr:`sn` is connected to. :type: str .. attribute:: auto_run Indicates whether to automatically execute the sql generated by a given method; defaults to *True* :type: bool Initializes a :class:`snowmobile.SQL` object. .. method:: info_schema(self, loc: str, where: Optional[List[str]] = None, fields: Optional[List[str]] = None, order_by: Optional[List] = None, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Generic case of selecting from information schema location. .. method:: table_info(self, nm: Optional[str] = None, fields: List[str] = None, restrictions: Dict[(str, str)] = None, order_by: List[Optional[str, int]] = None, all_schemas: bool = False, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Query ``information_schema.tables`` for a given table or view. :param nm: Table name, including schema if creating a stage outside of the current schema. :type nm: str :param fields: List of fields to include in returned results (e.g. ['table_name', 'table_type', 'last_altered']) :type fields: List[str] :param restrictions: List of conditionals typed as literal components of a `where` clause (e.g. ["table_type = 'base table'", 'last_altered::date = current_date()'] ). :type restrictions: List[str] :param order_by: List of fields or their ordinal positions to order the results by. :type order_by: List[str] :param all_schemas: Include tables/views from all schemas; defaults to `False`. :type all_schemas: bool :param run: Determines whether to run the generated sql or not; defaults to `None` which will reference the current value of the :attr:`auto_run` attribute which defaults to `True`. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: column_info(self, nm: Optional[str] = None, fields: Optional[List] = None, restrictions: Optional[Dict] = None, order_by: Optional[List] = None, all_schemas: bool = False, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Query ``information_schema.columns`` for a given table or view. :param nm: Table name, including schema if creating a stage outside of the current schema. :type nm: str :param fields: List of fields to include in returned results (e.g. ['ordinal_position', 'column_name', 'data_type']) :type fields: List[str] :param restrictions: List of conditionals typed as literal components of a `where` clause (e.g.["regexp_count(lower(column_name), 'tmstmp') = 0"]). :type restrictions: List[str] :param order_by: List of fields or their ordinal positions to order the results by. :type order_by: List[str] :param all_schemas: Include tables/views from all schemas; defaults to `False`. :type all_schemas: bool :param run: Determines whether to run the generated sql or not; defaults to `None` which will reference the current value of the :attr:`auto_run` attribute which defaults to `True`. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: columns(self, nm: Optional[str] = None, from_info_schema: bool = False, lower: bool = False, run: Optional[bool] = None) -> Union[(str, List)] Returns an ordered list of columns for a table or view. .. note:: * Default behavior is to retrieve the columns for a table or view by selecting a single sample record and returning the column index from the DataFrame that's returned which is much faster than selecting the **column_names** from ``information_schema.columns`` pulling column names from the information schema * This can be changed by passing `from_info_schema=True`. :param nm: Name of table or view, including schema if the table or view is outside of the current schema. :type nm: str :param from_info_schema: Indicates whether to retrieve columns via the ``information_schema.columns`` or by selecting a sample record from the table or view; defaults to `False`. :type from_info_schema: bool :param lower: Lower case each column in the list that's returned. :type lower: bool :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (Union[str, List]): Either: 1. An ordered list of columns for the table or view, **or** 2. The query against the table or view as a :class:`str` of sql. .. method:: select(self, nm: Optional[str] = None, fields: Optional[List[str]] = None, apply: Optional[List[Tuple[str, str]]] = None, n: Optional[int] = None, run: Optional[bool] = None, **kwargs) -> Union[(str, pd.DataFrame)] Generic `select` statement. :param nm: Table to select from, including schema if the table is outside of the current schema :type nm: str :param fields: Select these fields (optional). :type fields: Optional[List[str]] :param apply: Select aggregations of these fields. **apply** [ (*this_func, to_this_field, [as_alias]*), (*.., .., [..]*), ] * ``apply`` should be provided as a list of tuples, each containing a minimum of 2 items (respectively) representing the aggregate function to apply and the field to which it should be applied * By default, the aggregated result inherits the name of the field being aggregated, including any qualifier (optionally) provided with the field name or an explicit alias included as a 3rd item within the tuple *The following snippet exhaustively illustrates the functionality described above* .. code-block:: python sn.select( nm='sandbox.sample_table', apply=[ ('count', 'col1'), ('count', 'distinct col1'), ('count', 'distinct col1', 'col1_dst'), ], run=False, ) >>> select count(col1) as col1 ,count(distinct col1) as distinct_col1 ,count(distinct col1) as col1_dst from sandbox.sample_table :type apply: Optional[List[Tuple[str, str]]] :param n: Number of records to return, implemented as a 'limit' clause in the query; defaults to 1. :type n: int :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: exists(self, nm: Optional[str] = None) -> bool Checks the existence of a table or view. :param nm: Name of table or view, including schema if the table or view is outside of the current schema. :type nm: str Returns (bool): Boolean indication of whether or not the table or view exists. .. method:: is_distinct(self, nm: Optional[str] = None, field: Optional[str] = None) -> bool Checks if table `nm` is distinct on column `on_col` :param nm: Table name. :type nm: str :param field: Column name. :type field: str .. method:: count(self, nm: Optional[str] = None, of: Optional[str] = None, dst_of: Optional[str] = None, as_perc: Optional[bool] = None, run: Optional[bool] = None) -> Union[(int, float)] Number of records within a table or view. :param nm: Table name, including schema if querying outside current schema. :type nm: str :param of: Column name (indistinct). :type of: str :param dst_of: Column name (distinct). :type dst_of: str :param as_perc: Option to return distinct count of the `dst_of` column as a percentage of the namespace depth of the table or view. :type as_perc: bool :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: show(self, obj: str, in_loc: Optional[str] = None, names: bool = False, run: Optional[bool] = None, **kwargs) -> Union[(pd.DataFrame, List[str], str)] Show schema objects of typ 'obj', optionally 'in_loc'. :param obj: Schema object type ('tables', 'file formats', etc). :type obj: str :param in_loc: Snowflake location ('in schema sandbox', 'in database prod', etc). :type in_loc: str :param names: Return a list of schema object names only ('name' field). :type names: bool :param run: Execute the generated sql or return it as a string. :type run: bool Returns (Union[pd.DataFrame, str]): Either: 1. The results of the query as a :class:`pandas.DataFrame` 2. The 'names' column of the results returned as a list 3. The generated query as a :class:`str` of sql .. method:: ddl(self, nm: Optional[str] = None, obj: Optional[str] = None, run: Optional[bool] = None) -> str Query the DDL for an schema object. :param nm: Name of the object to get DDL for, including schema if object is outside of the current schema. :type nm: str :param obj: Type of object to get DDL for (e.g. 'table', 'view', 'file-format'). :type obj: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (str): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: comment(self, nm: Optional[str] = None, obj: Optional[str] = None, set_as: Optional[str] = None, from_json: bool = False, as_json: bool = False, run: Optional[bool] = None, **kwargs) -> Union[(str, Dict)] Get or set comment on a schema object. :param nm: Name of the schema object, including schema prefix if object is outside implicit scope of the current connection. :type nm: str :param obj: Type of schema object (e.g. 'table', 'schema', etc). :type obj: str :param set_as: Content to set as comment on schema object. :type set_as: str :param from_json: Parse schema object comment as a string of json and return it as a dictionary. :type from_json: bool :param as_json: Dump contents of 'set_as' to a string of json prior to setting comment. :type as_json: bool :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool :param \*\*kwargs: Keyword argument to pass to `json.loads(comment)` if *from_json=True*. Returns (Union[str, pd.DataFrame]): Either: 1. The schema object comment as a :class:`str` 2. The generated query as a :class:`str` of sql. 3. The schema object comment as a dictionary if *from_json=True* .. method:: last_altered(self, nm: Optional[str] = None, run: Optional[bool] = None) -> Union[(str, pd.Timestamp)] Last altered timestamp for a table or view. :param nm: Table name, including schema if creating a stage outside of the current schema. :type nm: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: truncate(self, nm: Optional[str] = None, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Truncate a table. :param nm: Name of table, including schema if the table is outside of the current schema. :type nm: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: drop(self, nm: Optional[str] = None, obj: Optional[str] = None, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Drop a ``Snowflake`` object. :param nm: Schema object's name. :type nm: str :param obj: Type of schema object (e.g. 'table', 'view', or 'schema') :type obj: str :param run: Execute generated statement; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: clone(self, nm: Optional[str] = None, to: Optional[str] = None, obj: Optional[str] = None, run: Optional[bool] = None, replace: bool = False) -> Union[(str, pd.DataFrame)] Clone a ``Snowflake`` object. .. warning:: * Make sure to read `Snowflake's documentation `_ for restrictions and considerations when cloning objects. .. note:: * In this specific method, the value provided to ``nm`` and ``to`` can be a single object name, a single schema, or both in the form of `obj_schema.obj_name` depending on the desired outcome. * Additionally, **at least one of the** ``nm`` **or** ``to`` **arguments must be pr**. * The defaults for the target object are constructed such that users can **either**: 1. Clone objects to *other* schemas that inherit the source object's *name* without specifying so in the ``to`` argument, **or** 2. Clone objects within the *current* schema that inherit the source object's *schema* without specifying so in the ``to`` argument. * If providing a schema without a name to either argument, prefix the value provided with `__` to signify it's a schema and not a lower-level object to be cloned. * e.g. providing `nm='sample_table'` and `to='__sandbox'` will clone `sample_table` from the current schema to `sandbox.sample_table`. * An assertion error will be raised raised if neither argument is specified as *this would result in a command to clone an object and store it in an object that has the same name & schema as the object being cloned*. :param nm: Name of the object to clone, including schema if cloning an object outside of the current schema. :type nm: str :param to: Target name for cloned object, including schema if cloning an object outside of the current schema. :type to: str :param obj: Type of object to clone (e.g. 'table', 'view', 'file-format'); defaults to `table`. :type obj: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool :param replace: Indicates whether to replace an existing stage if pre-existing; default is `False`. :type replace: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: create_stage(self, nm_stage: str, nm_format: str, replace: bool = False, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Create a staging table. :param nm_stage: Name of stage to create, including schema if creating a stage outside of the current schema. :type nm_stage: str :param nm_format: Name of file format to specify for the stage, including schema if using a format from outside of the current schema. :type nm_format: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool :param replace: Indicates whether to replace an existing stage if pre-existing; default is `False`. :type replace: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: put_file_from_stage(self, path: Union[(Path, str)], nm_stage: str, options: Optional[Dict] = None, ignore_defaults: bool = False, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Generates a 'put' command into a staging table from a local file. :param path: Path to local data file as a :class:`pathlib.Path` or string. :type path: Union[Path, str] :param nm_stage: Name of the staging table to load into. :type nm_stage: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool :param options: Optional arguments to add to `put` statement in addition to the values specified in the ``loading.put`` section of **snowmobile.toml**. :type options: dict :param ignore_defaults: Option to ignore the values specified in **snowmobile.toml**; defaults to `False`. :type ignore_defaults: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: copy_into_table_from_stage(self, nm: str, nm_stage: str, options: Optional[Dict] = None, ignore_defaults: bool = False, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Generates a command to copy data into a table from a staging table. :param nm: Name of the object to drop, including schema if creating a stage outside of the current schema. :type nm: str :param nm_stage: Name of the staging table to load from. :type nm_stage: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool :param options: Optional arguments to add to `put` statement in addition to the values specified in the ``loading.put`` section of **snowmobile.toml**. :type options: dict :param ignore_defaults: Option to ignore the values specified in **snowmobile.toml**; defaults to `False`. :type ignore_defaults: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: current(self, obj: str, run: Optional[bool] = None) -> Union[(str, Union[str, int])] Generic implementation of 'select current' for session-based objects. :param obj: Type of object to retrieve information for (schema, session, ..). :type obj: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: current_session(self, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Select the current session. .. method:: current_schema(self, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Select the current schema. .. method:: current_database(self, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Select the current database. .. method:: current_warehouse(self, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Select the current warehouse. .. method:: current_role(self, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Select the current role. .. method:: use(self, obj: str, nm: str, run: Optional[bool] = None) Generic implementation of 'use' command for schema objects. :param nm: Name of object to use (schema name, warehouse name, role name, ..). :type nm: str :param obj: Type of object to use (schema, warehouse, role, ..). :type obj: str :param run: Execute generated sql; defaults to `True`, otherwise returns sql as a string. :type run: bool Returns (Union[str, pd.DataFrame]): Either: 1. The results of the query as a :class:`pandas.DataFrame`, or 2. The generated query as a :class:`str` of sql. .. method:: use_schema(self, nm: Optional[str] = None, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Use schema command. .. method:: use_database(self, nm: Optional[str] = None, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Use database command. .. method:: use_warehouse(self, nm: Optional[str] = None, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Use warehouse command. .. method:: use_role(self, nm: Optional[str] = None, run: Optional[bool] = None) -> Union[(str, pd.DataFrame)] Use role command. .. method:: order(by: List[Union[int, str]]) -> str :staticmethod: Generates 'order by' clause from a list of fields or field ordinal positions. .. method:: where(restrictions: Dict) -> str :staticmethod: Generates a 'where' clause based on a dictionary of restrictions. :param restrictions: A dictionary of conditionals where each key/value pair respectively represents the left/right side of a condition within a 'where' clause. :type restrictions: dict Returns (str): Formatted where clause. .. method:: fields(fields: Optional[List[str]] = None) -> str :staticmethod: Utility to generate fields within a 'select' statement. .. class:: Markup(sn: snowmobile.core.connection.Snowmobile, path: pathlib.Path, contents: Dict[(int, Union[Statement, Marker])], nm: Optional[str] = None, prefix: Optional[str] = None, suffix: Optional[str] = None, root_dir: Optional[Union[str, Path]] = None, sub_dir: Optional[str] = None, incl_sql: bool = True, incl_markers: bool = True, incl_sql_tag: bool = False, incl_exp_ctx: bool = True, result_wrap: Optional[str] = None) Bases: :class:`snowmobile.core.Generic` Contains all sections within the context of a :class:`~snowmobile.Script`. :param sn: A :class:`~snowmobile.Snowmobile` instance. :type sn: Snowmobile :param path: A full path to the sql file that script was instantiated from. :type path: Path :param contents: A dictionary of the script's contents (st and markers) by index position. :type contents: Dict[int, Union[Statement, Marker]] :param nm: Alternate file name to use; defaults to ``path.name``. :type nm: Optional[str] :param prefix: Prefix to prepend to original file name when exporting. :type prefix: Optional[str] :param suffix: Suffix to append to original file name when exporting. :type suffix: Optional[str] :param root_dir: Alternate target directory for exports; defaults to ``./.snowmobile`` where ``.`` is the directory containing the sql file that the ``script`` was created from. :type root_dir: Optional[Union[str, Path]] :param sub_dir: Alternate sub-directory name; defaults to ``path.name`` where ``path`` is a full :class:`~pathlib.Path` to the sql file that the ``script`` was created from. :type sub_dir: Optional[str] :param incl_sql: Include statements in export. :type incl_sql: bool :param incl_markers: Include markers in export. :type incl_markers: bool :param incl_sql_tag: Include the raw wrap in the sql that is rendered in the `md` export. :type incl_sql_tag: bool :param incl_exp_ctx: Include (configurable) disclaimer at the top of exported `sql` file. :type incl_exp_ctx: bool .. attribute:: exported List of file paths that current instance has exported to. :type: List[Path] .. attribute:: created List of directory paths that current instance has created (should mostly apply for initial scaffolding build on first run only). :type: List[Path] .. method:: export_dir(self) -> pathlib.Path :property: Documentation sub-directory; `.snowmobile` by default. .. method:: sections(self) -> Dict[(int, Section)] :property: Dictionary of all :class:`sections` by index position. .. method:: markdown(self) -> str :property: Full markdown file as a string. .. method:: sql(self) :property: SQL for save. .. method:: save(self, md: bool = True, sql: bool = True) -> None Save files to disk. :param md: Export a generated markdown file. :type md: bool :param sql: Export a generated sql file. :type sql: bool .. class:: Script(sn: Optional[Snowmobile] = None, path: Optional[(Path, str)] = None, sql: Optional[str] = None, as_generic: bool = False, delay: bool = True, **kwargs) Bases: :class:`snowmobile.core.Generic` Parser and operator of local sql files. :param sn: An instance of :class:`~snowmobile.core.connection.Snowmobile`. :type sn: snowmobile.core.connection.Snowmobile :param path: A full path to a sql file or readable text file containing valid sql code. :type path: Optional[Path, str] :param path: A raw string of valid sql code as opposed to reading from a ``path``. :type path: Optional[str] :param as_generic: Instantiate all statements as generic st; skips all checks for a mapping of a statement anchor to a derived statement class to instantiate in the place of a generic :class:`~snowmobile.core.statement.Statement`. :type as_generic: bool :param delay: Delay connection of the :class:`Snowmobile`; only applicable if the ``sn`` argument is omitted and :class:`Script` is instantiating a :class:`Snowmobile` in its absence. :type delay: bool :param \*\*kwargs: Any keyword arguments to pass to :class:`Snowmobile`; only applicable if the ``sn`` argument is omitted and :class:`Script` is instantiating a :class:`Snowmobile` in its absence .. attribute:: sn An instance of :class:`~snowmobile.core.connection.Snowmobile` :type: snowmobile.core.connection.Snowmobile .. attribute:: patterns Configured patterns from :ref:`snowmobile.toml`. :type: snowmobile.core.cfg.script.Pattern .. attribute:: as_generic Instantiate all statements as generic st; skips all checks for a mapping of a statement anchor to a derived statement class to instantiate in the place of a generic :class:`~snowmobile.core.statement.Statement`. :type: bool .. attribute:: filters Dictionary of filters that have been passed to the current instance of :class:`snowmobile.core.Script`. :type: Dict[Any[str, int], Dict[str, Set]] .. attribute:: markers Dictionary of all markers found in the script. :type: Dict[int, cfg.Marker] .. attribute:: path Path to sql file (e.g. *full/path/to/script.sql*). :type: Path .. attribute:: name Name of sql file (e.g. *script.sql*). :type: str .. attribute:: source Raw sql text of script; will be the text contained in the raw sql file when initially read from source and reflect any modifications to the script's contents made post-instantiation. :type: str .. method:: read(self, path: pathlib.Path = None) -> snowmobile.core.script.Script Runs quick path validation and reads in a sql file as a string. A valid `path` must be provided if the `script.path` attribute hasn't been set; ``ValueErrors`` will be thrown if neither is valid. :param path: Full path to a sql object. :type path: pathlib.Path .. method:: from_str(self, sql: str, name: str, directory: pathlib.Path = Path.cwd()) -> snowmobile.core.script.Script Instantiates a raw string of sql as a script. .. method:: source(self, original: bool = False) -> str The script's sql as a raw string. .. method:: parse_one(self, s: Union[(sqlparse.sql.Statement, str)], index: Optional[int] = None, nm: Optional[str] = None) -> None Adds a statement object to the script. Default behavior will only add ``sqlparse.sql.Statement`` objects returned from ``script.source_stream``. ``clean_parse()`` utility function is utilized so that generated sql within Python can be inserted back into the script as raw strings. :param s: A sqlparse.sql.Statement object or a raw string of SQL for an individual statement. :type s: Union[sqlparse.sql.Statement, str] :param index: Index position of the statement within the script; defaults to ``n + 1`` if index is not provided where ``n`` is the number of statements within the script at the time ``parse_one()`` is called. :type index: int :param nm: Optionally provided the name of the statement being added; the script instance will treat this value as if it were provided within an in-script wrap. :type nm: Optional[str] .. method:: parse_stream(self, stream: str) -> None Parses a stream of sql and adds onto existing Script contents. .. method:: filter(self, incl_kw: Optional[(List[str], str)] = None, incl_obj: Optional[(List[str], str)] = None, incl_desc: Optional[(List[str], str)] = None, incl_anchor: Optional[(List[str], str)] = None, incl_nm: Optional[(List[str], str)] = None, excl_kw: Optional[(List[str], str)] = None, excl_obj: Optional[(List[str], str)] = None, excl_desc: Optional[(List[str], str)] = None, excl_anchor: Optional[(List[str], str)] = None, excl_nm: Optional[(List[str], str)] = None, as_id: Optional[Union[str, int]] = None, from_id: Optional[Union[str, int]] = None, last: bool = False) -> ContextManager[Script] Subset the script based on attributes of its st. ``script.filter()`` returns a modified instance of script that can be operated on within the context defined. .. note:: Keyword arguments beginning with ``incl`` or ``excl`` expect a string or a list of strings containing regex patterns with which to check for a match against the associated attribute of its st' :class:`~snowmobile.core.name.Name`. :param incl_kw: Include only :attr:`~snowmobile.core.name.Name.kw` :param incl_obj: Include only :attr:`~snowmobile.core.name.Name.obj` :param incl_desc: Include only :attr:`~snowmobile.core.name.Name.desc` :param incl_anchor: Include only :attr:`~snowmobile.core.name.Name.anchor` :param incl_nm: Include only :attr:`~snowmobile.core.name.Name.nm` :param excl_kw: Exclude :attr:`~snowmobile.core.name.Name.kw` :param excl_obj: Exclude :attr:`~snowmobile.core.name.Name.obj` :param excl_desc: Exclude :attr:`~snowmobile.core.name.Name.desc` :param excl_anchor: Exclude :attr:`~snowmobile.core.name.Name.anchor` :param excl_nm: Exclude :attr:`~snowmobile.core.name.Name.nm` :param as_id: ID to assign the filters passed to method; used to populated the :attr:`filters` attribute :param from_id: ID previously used on the same instance of :class:`Script` from which to populate filtered arguments :param last: Re-use the last set of filters passed to context manager. Returns (Script): The instance of script based on the context imposed by arguments pr. .. method:: depth(self) -> int :property: Count of statements in the script. .. method:: lines(self) -> int :property: Number of lines in the script .. method:: excluded(self) :property: All statements by index position excluded from the current context. .. method:: executed(self) -> Dict[(int, Statement)] :property: Executed statements by index position included in the current context. .. method:: reset(self, index: bool = False, ctx_id: bool = False, in_context: bool = False, scope: bool = False, _filter: bool = False) -> snowmobile.core.script.Script Resets indices and scope on all statements to their state as read from source. Invoked before exiting :meth:`filter()` context manger to reverse the revised indices set by :meth:`index_to()` and inclusion/ exclusion scope set by :meth:`Statement.Name.scope()`. .. method:: duplicates(self) -> Dict[(str, int)] :property: Dictionary of indistinct statement names/tags within script. .. method:: s(self, _id: Optional[(str, int)] = None) -> Any[(Statement, Empty, Diff)] Fetch a single statement by _id. .. method:: st(self) -> Dict[(Union[int, str], Statement)] :property: Accessor for all statements. .. method:: dtl(self, full: bool = False, excluded: bool = False, title: bool = True, r: bool = False) -> Union[(str, None)] Prints summary of statements within the current scope to console. .. method:: first_s(self) :property: First statement by index position. .. method:: last_s(self) :property: Last statement by index position .. method:: first(self) -> Union[(Statement, Empty, Diff)] :property: First statement executed. .. method:: last(self) -> Union[(Statement, Empty, Diff)] :property: Last statement executed. .. method:: doc(self, nm: Optional[str] = None, prefix: Optional[str] = None, suffix: Optional[str] = None, incl_markers: Optional[bool] = True, incl_sql: Optional[bool] = True, incl_exp_ctx: Optional[bool] = True, result_wrap: Optional[str] = None) -> snowmobile.core.Markup Returns a :class:`~snowmobile.core.markup.Markup` from the script. :param nm: Alternate file name to use. :type nm: Optional[str] :param prefix: Prefix for file name. :type prefix: Optional[str] :param suffix: Suffix for file name. :type suffix: Optional[str] :param incl_markers: Include markers in exported files. :type incl_markers: Optional[bool] :param incl_sql: Include sql in exported files. :type incl_sql: Optional[bool] :param incl_exp_ctx: Include disclaimer of programmatic save in exported sql file. :type incl_exp_ctx: Optional[bool] :returns: A :class:`~snowmobile.core.markup.Markup` instance based on the contents included in the script's context. .. method:: ids(self, _id: Optional[Union[Tuple, List]] = None) -> List[int] Utility function to get a list of statement IDs given an `_id`. Invoked within script.run() if the `_id` parameter is either a: (1) tuple of integers (lower and upper bound of statement indices to run) (2) list of integers or strings (statement names or indices to run) (3) default=None; returns all statement indices within scope if so :param _id: _id field provided to script.run() if it's neither an integer or a string. :type _id: Union[Tuple, List] Returns (List[int]): A list of statement indices to run. .. method:: run(self, _id: Optional[(str, int, Tuple[int, int], List)] = None, as_df: bool = True, on_error: Optional[str] = None, on_exception: Optional[str] = None, on_failure: Optional[str] = None, lower: bool = True, render: bool = False, **kwargs) -> None Performs statement-by-statement execution of the script's contents. Executes script's contents that are included within its current context and any (optional) value passed to the ``_id`` argument. .. note:: Keyword arguments ``on_exception`` and ``on_failure`` are only applicable to derived classes of :class:`~snowmobile.core.statement.Statement` (e.g., those within :mod:`snowmobile.core.qa` by default). :param _id: Identifier for statement(s) to execute, can be either: - *None* (default); execute all statements - A single statement's :attr:`~snowmobile.core.Name.nm` - A single statement's index position - A tuple of lower/upper index bounds of statements to execute - A list of statement names or index positions to execute :type _id: Optional[str, int, Tuple[int, int], List] :param as_df: Store statement's results as a :class:`~pandas.DataFrame`; defaults to *True* :type as_df: bool :param on_error: Action to take on **execution** error; providing `c` will continue execution as opposed to raising exception. :type on_error: Optional[str] :param on_exception: Action to take on **post-processing** error from a derived :class:`~snowmobile.core.statement.Statement`; providing `c` will continue execution as opposed to raising exception. :type on_exception: Optional[str] :param on_failure: Action to take on **failure** of post-processing assertion from a derived :class:`~snowmobile.core.statement.Statement`; providing `c` will continue execution as opposed to raising exception. :type on_failure: Optional[str] :param lower: Lower-case columns in results returned if ``as_df=True``. :type lower: bool :param render: Render sql executed as markdown; only applicable in Jupyter/iPython environments. :type render: bool :param \*\*kwargs: .. method:: items(self, by_index: bool = True, ignore_scope: bool = False, statements: bool = True, markers: bool = False, validate: bool = True) -> ItemsView[(Union[int, str], Union[Statement, Marker])] Dunder items. .. method:: keys(self, **kwargs) -> KeysView[Union[int, str]] Access keys of items only. .. method:: values(self, **kwargs) -> ValuesView[Union[int, str]] Access values of items only. .. method:: dict(self, **kwargs) -> Dict Unpacking items view into an actual dictionary. .. class:: Table(df: pandas.DataFrame, table: str, sn: Optional[Snowmobile] = None, if_exists: Optional[str] = None, as_is: bool = False, path_ddl: Optional[Union[str, Path]] = None, path_output: Optional[(str, Path)] = None, file_format: Optional[str] = None, incl_tmstmp: Optional[bool] = None, tmstmp_col_nm: Optional[str] = None, reformat_cols: Optional[bool] = None, validate_format: Optional[bool] = None, validate_table: Optional[bool] = None, upper_case_cols: Optional[bool] = None, lower_case_table: Optional[bool] = None, keep_local: Optional[bool] = None, on_error: Optional[str] = None, check_dupes: Optional[bool] = None, load_copy: Optional[bool] = None, **kwargs) Bases: :class:`snowmobile.core.Generic` Constructed with a :class:`DataFrame` and a table name to load into. The ``df`` and ``table``'s compatibility can be inspected prior to calling the :meth:`Table.load()` method or by providing `as_is=True`` when instantiating the object; the latter will kick off the loading process invoked by :meth:`.load()` based on the parameters provided to :class:`snowmobile.Table()`. :param df: The :class:`~pandas.DataFrame` to load. :type df: DataFrame :param table: The table name to load ``df`` into. :type table: str :param sn: An instance of :class:`~snowmobile.Snowmobile`; can be used to load a table on a specific connection or from a specific ``snowmobile.toml`` file. :type sn: Optional[Snowmobile] :param if_exists: Action to take if ``table`` already exists - options are `fail`, `replace`, `append`, and `truncate`; defaults to ``append``. :type if_exists: Optional[str] :param as_is: Load ``df`` into ``table`` based on the parameters provided to :class:`Table` without further pre-inspection by the user; defaults to `False`. :type as_is: bool :param path_ddl: Alternate path to file format DDL to use for load. :type path_ddl: Optional[Path] :param keep_local: Keep local file that is written out as part of the bulk loading process; defaults to `False`. :type keep_local: Optional[bool] :param path_output: Path to write output local file to; defaults to a generated file name exported in the current working directory. :type path_output: Optional[str Path] :param file_format: The name of the file_format to use when loading ``df``; defaults to ``snowmobile_default_psv``. :type file_format: Optional[str] :param incl_tmstmp: Include timestamp of load as part of ``table``; defaults to `True`. :type incl_tmstmp: Optional[bool] :param tmstmp_col_nm: Name to use for load timestamp if ``incl_tmstmp=True``; defaults to `loaded_tmstmp`. :type tmstmp_col_nm: Optional[str] :param upper_case_cols: Upper case columns of ``df`` when loading into ``table``; defaults to `True`. :type upper_case_cols: Optional[bool] :param reformat_cols: Reformat applicable columns of ``df`` to be DB-compliant; defaults to `True`. Reformatting primarily entails: - Replacing spaces and special characters with underscores - De-duping consecutive special characters - De-duping repeated column names; adds an ``_i`` suffix to duplicate fields where ``i`` is the nth duplicate name for a field :type reformat_cols: Optional[bool] :param validate_format: Validate the :xref:`file format` being used prior to kicking off the load; defaults to `True`. Validation entails: - Checking if the file format being used already exists based on formats accessible to the current connection - Executing DDL for the file format being used if not, pulled from the ``DDL`` `ext-location` and the statement name ``create file format~{format name}`` .. tip:: Providing `validate_format=False` will speed up loading time when batch-loading into an existing table by skipping this step :type validate_format: Optional[bool] :param validate_table: Perform validations of ``df`` against ``table`` prior to kicking off the loading process; defaults to `True`. Validation entails: - Checking the existence of ``table``; no further validation is performed if it does **not** exist - Compares the columns of ``df`` to the columns of ``table`` and stores results for use during loading process .. note:: Table validation results are used in conjunction with the ``if_exists`` parameter to determine the desired behavior based on the (potential) existence of ``table`` and its compatibility with ``df``. .. tip:: Providing `validate_table=False` will speed up loading time time when batch-loading into an existing table :type validate_table: Optional[bool] :param lower_case_table: Lower case ``table`` name; defaults to `False`. :type lower_case_table: Optional[bool] :param on_error: Action to take if an exception is encountered as part of the validating or loading process - providing ``on_error='c'`` will *continue* past an exception as opposed to raising it; defaults to `None` meaning any exception encountered will be raised :type on_error: Optional[str] :param check_dupes: Check for duplicate field names in ``df``; defaults to `True`. :type check_dupes: Optional[bool] :param load_copy: Alter and load a deep copy of ``df`` as opposed to the ``df`` in-memory as passed to the parameter; defaults to `True`. :type load_copy: Optional[bool] .. attribute:: db_responses Responses from database during loading process. :type: Dict[str, str] .. attribute:: loaded Table was loaded successfully. :type: bool .. method:: load(self, if_exists: Optional[str] = None, from_script: pathlib.Path = None, verbose: bool = True, **kwargs) -> snowmobile.core.table.Table Loads ``df`` into ``table``. :param if_exists: Determines behavior to take if the table being loaded into already exists; defaults to **append**; options are **replace**, **append**, **truncate**, and **fail** :type if_exists: Optional[str] :param from_script: Path to sql file containing custom DDL for ``table``; DDL is assumed to have a valid statement name as is parsed by :class:`~snowmobile.core.script.Script` and following the naming convention of ``create table~TABLE`` where ``TABLE`` is equal to the value provided to the ``table`` keyword argument :type from_script: Optional[Union[Path, str]] :param verbose: Verbose console output; defaults to **True** :type verbose: bool Returns (Table): The :class:`Table` after attempting load of ``df`` into ``table``; a successful load can be verified by inspecting :attr:`loaded` .. method:: exists(self) -> bool :property: Indicates if the target table exists. .. method:: col_diff(self, mismatched: bool = False) -> Dict[(int, Tuple[str, str])] Returns diff detail of local DataFrame to in-warehouse table. .. method:: cols_match(self) -> bool :property: Indicates if columns match between DataFrame and table. .. method:: load_statements(self, from_script: pathlib.Path) -> List[str] Generates exhaustive list of the statements to execute for a given instance of loading a DataFrame. .. method:: to_local(self, quote_all: bool = True) -> None Export to local file via configuration in ``snowmobile.toml``. .. method:: tm_load(self) -> int :property: Seconds elapsed during loading. .. method:: tm_validate_load(self) -> int :property: Seconds elapsed during validation. .. method:: tm_total(self) -> int :property: Total seconds elapsed for load. .. method:: validate(self, if_exists: str) -> None Validates load based on current state through a variety of operations. :param if_exists: Desired behavior if table already exists; intended to be passed in from :meth:`table.load()` by default. :type if_exists: str