:mod:`snowmobile` ================= .. py:module:: snowmobile .. autoapi-nested-parse:: An analytics-focused wrapper around the snowflake.connector for Python. Subpackages ----------- .. toctree:: :titlesonly: :maxdepth: 3 core/index.rst Package Contents ---------------- Classes ~~~~~~~ .. autoapisummary:: snowmobile.SQL snowmobile.Configuration snowmobile.Snowmobile snowmobile.connect snowmobile.Table snowmobile.Script snowmobile.Statement .. 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:: 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:: 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 .. 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:: 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.