gYUddlmZddlmZmZmZddlmZddlm Z ddl m Z ddl m Z ddlmZej eZded <erdd lmZdd lmZdd lmZdd lmZddlmZGdde dZy)) annotations) TYPE_CHECKINGFinalcast)logger)BaseConnection)running_in_sis)StreamlitAPIException) cache_datar_LOGGER) timedelta) DataFrame)SnowflakeCursorSession)SnowflakeConnectionceZdZdZd dZdddd d dZ d ddZddZed d Z dd Z y)ramA connection to Snowflake using the Snowflake Connector for Python. Initialize this connection object using ``st.connection("snowflake")`` or ``st.connection("", type="snowflake")``. Connection parameters for a SnowflakeConnection can be specified using ``secrets.toml`` and/or ``**kwargs``. Connection parameters are passed to |snowflake.connector.connect()|. When an app is running in Streamlit in Snowflake, ``st.connection("snowflake")`` connects automatically using the app owner's role without further configuration. ``**kwargs`` will be ignored in this case. Use ``secrets.toml`` and ``**kwargs`` to configure your connection for local development. SnowflakeConnection includes several convenience methods. For example, you can directly execute a SQL query with ``.query()`` or access the underlying Snowflake Connector object with ``.raw_connection``. .. |snowflake.connector.connect()| replace:: ``snowflake.connector.connect()`` .. _snowflake.connector.connect(): https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-api#label-snowflake-connector-methods-connect .. Tip:: `snowflake-snowpark-python `_ must be installed in your environment to use this connection. You can install Snowflake extras along with Streamlit: >>> pip install streamlit[snowflake] .. Important:: Account identifiers must be of the form ``-`` where ```` is the name of your Snowflake organization and ```` is the unique name of your account within your organization. This is dash-separated, not dot-separated like when used in SQL queries. For more information, see `Account identifiers `_. Examples -------- **Example 1: Configuration with Streamlit secrets** You can configure your Snowflake connection using Streamlit's `Secrets management `_. For example, if you have MFA enabled on your account, you can connect using `key-pair authentication `_. ``.streamlit/secrets.toml``: >>> [connections.snowflake] >>> account = "xxx-xxx" >>> user = "xxx" >>> private_key_file = "/xxx/xxx/xxx.p8" >>> role = "xxx" >>> warehouse = "xxx" >>> database = "xxx" >>> schema = "xxx" Your app code: >>> import streamlit as st >>> conn = st.connection("snowflake") >>> df = conn.query("SELECT * FROM my_table") **Example 2: Configuration with keyword arguments and external authentication** You can configure your Snowflake connection with keyword arguments. The keyword arguments are merged with (and take precedence over) the values in ``secrets.toml``. However, if you name your connection ``"snowflake"`` and don't have a ``[connections.snowflake]`` dictionary in your ``secrets.toml`` file, Streamlit will ignore any keyword arguments and use the default Snowflake connection as described in Example 5 and Example 6. To configure your connection using only keyword arguments, declare a name for the connection other than ``"snowflake"``. For example, if your Snowflake account supports SSO, you can set up a quick local connection for development using `browser-based SSO `_. Because there is nothing configured in ``secrets.toml``, the name is an empty string and the type is set to ``"snowflake"``. This prevents Streamlit from ignoring the keyword arguments and using a default Snowflake connection. >>> import streamlit as st >>> conn = st.connection( ... "", ... type="snowflake", ... account="xxx-xxx", ... user="xxx", ... authenticator="externalbrowser", ... ) >>> df = conn.query("SELECT * FROM my_table") **Example 3: Named connection with Snowflake's connection configuration file** Snowflake's Python Connector supports a `connection configuration file `_, which is well integrated with Streamlit's ``SnowflakeConnection``. If you already have one or more connections configured, all you need to do is pass the name of the connection to use. ``~/.snowflake/connections.toml``: >>> [my_connection] >>> account = "xxx-xxx" >>> user = "xxx" >>> password = "xxx" >>> warehouse = "xxx" >>> database = "xxx" >>> schema = "xxx" Your app code: >>> import streamlit as st >>> conn = st.connection("my_connection", type="snowflake") >>> df = conn.query("SELECT * FROM my_table") **Example 4: Named connection with Streamlit secrets and Snowflake's connection configuration file** If you have a Snowflake configuration file with a connection named ``my_connection`` as in Example 3, you can pass the connection name through ``secrets.toml``. ``.streamlit/secrets.toml``: >>> [connections.snowflake] >>> connection_name = "my_connection" Your app code: >>> import streamlit as st >>> conn = st.connection("snowflake") >>> df = conn.query("SELECT * FROM my_table") **Example 5: Default connection with an environment variable** If you don't have a ``[connections.snowflake]`` dictionary in your ``secrets.toml`` file and use ``st.connection("snowflake")``, Streamlit will use the default connection for the `Snowflake Python Connector `_. If you have a Snowflake configuration file with a connection named ``my_connection`` as in Example 3, you can set an environment variable to declare it as the default Snowflake connection. >>> SNOWFLAKE_DEFAULT_CONNECTION_NAME = "my_connection" Your app code: >>> import streamlit as st >>> conn = st.connection("snowflake") >>> df = conn.query("SELECT * FROM my_table") **Example 6: Default connection in Snowflake's connection configuration file** If you have a Snowflake configuration file that defines your ``default`` connection, Streamlit will automatically use it if no other connection is declared. ``~/.snowflake/connections.toml``: >>> [default] >>> account = "xxx-xxx" >>> user = "xxx" >>> password = "xxx" >>> warehouse = "xxx" >>> database = "xxx" >>> schema = "xxx" Your app code: >>> import streamlit as st >>> conn = st.connection("snowflake") >>> df = conn.query("SELECT * FROM my_table") c ddl}ddlm}tr;ddlm}|}t |dr |j S|jjSd|j_ |jj}t|r7tjdi||}|jjd i|S|j dk(r/tjd|jjS|jjd i|S#|$r(}tst|s t#d |d}~wwxYw) Nr)Errorget_active_session connectionqmarkzVConnect to Snowflake using the Streamlit secret defined under [connections.snowflake]. snowflakezConnect to Snowflake using the default configuration as defined in https://docs.snowflake.cn/en/developer-guide/python-connector/python-connector-connect#setting-a-default-connectiona?Missing Snowflake connection configuration. Did you forget to set this in `secrets.toml`, a Snowflake configuration file, or as kwargs to `st.connection`? See the [SnowflakeConnection configuration documentation](https://docs.streamlit.io/st.connections.snowflakeconnection-configuration) for more details and examples.)snowflake.connectorrr snowflake.snowpark.contextrhasattrr_conn connector paramstyle_secretsto_dictlenr infoconnect_connection_namer ) selfkwargsrSnowflakeErrorrsession st_secrets conn_kwargses _/var/www/openai/venv/lib/python3.12/site-packages/streamlit/connections/snowflake_connection.py_connectzSnowflakeConnection._connects>"?   )*Gw -)))==&& &*1 & ..0J: /76v6 2y**22A[AA$$ 3 M!**2244.9&&..88 8 z?3v;+5G s%"AD>=D<DE#EENzRunning `snowflake.query(...)`.)ttl show_spinnerparamsc 8 ddlm}m}m}m} d |fd|dd| fd| d dfd } t |j d d } | jd jd | | _t|| | } | |S)aRun a read-only SQL query. This method implements query result caching and simple error handling/retries. The caching behavior is identical to that of using ``@st.cache_data``. .. note:: Queries that are run without a specified ``ttl`` are cached indefinitely. Parameters ---------- sql : str The read-only SQL query to execute. ttl : float, int, timedelta or None The maximum number of seconds to keep results in the cache. If this is ``None`` (default), cached results do not expire with time. show_spinner : boolean or string Whether to enable the spinner. When a cached query is executed, no spinner is displayed because the result is immediately available. When a new query is executed, the default is to show a spinner with the message "Running ``snowflake.query(...)``." If this is ``False``, no spinner displays while executing the query. If this is a string, the string will be used as the message for the spinner. params : list, tuple, dict or None List of parameters to pass to the Snowflake Connector for Python ``Cursor.execute()`` method. This connector supports binding data to a SQL statement using qmark bindings. For more information and examples, see the `Snowflake Connector for Python documentation `_. This defaults to ``None``. Returns ------- pandas.DataFrame The result of running the query, formatted as a pandas DataFrame. Example ------- >>> import streamlit as st >>> >>> conn = st.connection("snowflake") >>> df = conn.query("SELECT * FROM my_table") >>> st.dataframe(df) r)retryretry_if_exceptionstop_after_attempt wait_fixed08001c$jS)N)reset)_r(s r/z+SnowflakeConnection.query..Vs DJJLTc>t|dxr|jk(S)Nsqlstate)rrA)r.'SQLSTATE_CONNECTION_WAS_NOT_ESTABLISHEDs r/r=z+SnowflakeConnection.query..]s%'!Z0JJJ"IIJr>)afterstopreraiser5waitcjj}|j|fdi|jS)Nr3) _instancecursorexecutefetch_pandas_all)sqlcurr)r3r(s r/_queryz)SnowflakeConnection.query.._queryUs>..'')C CKK 5F 5f 5'') )r>.r<)r2r1)rMstrreturnr) tenacityr5r6r7r8rQreplace __qualname__r'r ) r(rMr1r2r3r)r5r6r7r8rOttl_strrBs ` `` @r/queryzSnowflakeConnection.queryst WV3:/ (#A&%JA  *  *  '#s  "(!4!4 5Qt7L7L6MQwiX %   c{r>c Tddlm}|d|j|||||d|\}} } } || | fS)aS Write a ``pandas.DataFrame`` to a table in a Snowflake database. This convenience method is a thin wrapper around ``snowflake.connector.pandas_tools.write_pandas()`` using the underlying connection. The ``conn`` parameter is passed automatically. For more information and additional keyword arguments, see the `Snowflake Connector for Python documentation `_. Parameters ---------- df: pandas.DataFrame The ``pandas.DataFrame`` object containing the data to be copied into the table. table_name: str Name of the table where the data should be copied to. database: str Name of the database containing the table. By default, the function writes to the database that is currently in use in the session. .. Note:: If you specify this parameter, you must also specify the schema parameter. schema: str Name of the schema containing the table. By default, the function writes to the table in the schema that is currently in use in the session. chunk_size: int Number of elements to insert at a time. By default, the function inserts all elements in one chunk. **kwargs: Any Additional keyword arguments for ``snowflake.connector.pandas_tools.write_pandas()``. Returns ------- tuple[bool, int, int] A tuple containing three values: 1. A boolean value that is ``True`` if the write was successful. 2. An integer giving the number of chunks of data that were copied. 3. An integer giving the number of rows that were inserted. Example ------- The following example uses the database and schema currently in use in the session and copies the data into a table named "my_table." >>> import streamlit as st >>> import pandas as pd >>> >>> df = pd.DataFrame( ... {"Name": ["Mary", "John", "Robert"], "Pet": ["dog", "cat", "bird"]} ... ) >>> conn = st.connection("snowflake") >>> conn.write_pandas(df, "my_table") r) write_pandas)conndf table_namedatabaseschema chunk_sizer) snowflake.connector.pandas_toolsrYrI) r(r[r\r]r^r_r)rYsuccessnchunksnrowsr<s r/rYz SnowflakeConnection.write_pandasvsPH B%1& !! & & "%%((r>c6|jjS)aDCreate a new cursor object from this connection. Snowflake Connector cursors implement the Python Database API v2.0 specification (PEP-249). For more information, see the `Snowflake Connector for Python documentation `_. Returns ------- snowflake.connector.cursor.SnowflakeCursor A cursor object for the connection. Example ------- The following example uses a cursor to insert multiple rows into a table. The ``qmark`` parameter style is specified as an optional keyword argument. Alternatively, the parameter style can be declared in your connection configuration file. For more information, see the `Snowflake Connector for Python documentation `_. >>> import streamlit as st >>> >>> conn = st.connection("snowflake", "paramstyle"="qmark") >>> rows_to_insert = [("Mary", "dog"), ("John", "cat"), ("Robert", "bird")] >>> conn.cursor().executemany( ... "INSERT INTO mytable (name, pet) VALUES (?, ?)", rows_to_insert ... ) )rIrJr(s r/rJzSnowflakeConnection.cursors>~~$$&&r>c|jS)aAccess the underlying connection object from the Snowflake Connector for Python. For information on how to use the Snowflake Connector for Python, see the `Snowflake Connector for Python documentation `_. Returns ------- snowflake.connector.connection.SnowflakeConnection The connection object. Example ------- The following example uses a cursor to submit an asynchronous query, saves the query ID, then periodically checks the query status through the connection before retrieving the results. >>> import streamlit as st >>> import time >>> >>> conn = st.connection("snowflake") >>> cur = conn.cursor() >>> cur.execute_async("SELECT * FROM my_table") >>> query_id = cur.sfqid >>> while True: ... status = conn.raw_connection.get_query_status(query_id) ... if conn.raw_connection.is_still_running(status): ... time.sleep(1) ... else: ... break >>> cur.get_results_from_sfqid(query_id) >>> df = cur.fetchall() )rIres r/raw_connectionz"SnowflakeConnection.raw_connectionsJ~~r>cddlm}ddlm}t r|St ||j jd|jijS)aVCreate a new Snowpark session from this connection. For information on how to use Snowpark sessions, see the `Snowpark developer guide `_ and `Snowpark API Reference `_. Returns ------- snowflake.snowpark.Session A new Snowpark session for this connection. Example ------- The following example creates a new Snowpark session and uses it to run a query. >>> import streamlit as st >>> >>> conn = st.connection("snowflake") >>> session = conn.session() >>> df = session.sql("SELECT * FROM my_table").collect() rrrr) rrsnowflake.snowpark.sessionrr rbuilderconfigsrIcreate)r(rrs r/r+zSnowflakeConnection.sessionsM4 B6  %' ' W__,,lDNN-KLSSU  r>)rRInternalSnowflakeConnection)rMrQr1zfloat | int | timedelta | Noner2z bool | strrRr)NNN) r[rr\rQr] str | Noner^rnr_z int | NonerRztuple[bool, int, int])rRr)rRr) __name__ __module__rU__doc__r0rWrYrJpropertyrgr+rr>r/rr-sn`6x/3#D ^ ^, ^ ! ^ ^H $!!% P) P)P) P)  P)  P) P)d'B$$L" r>rrmN) __future__rtypingrrr streamlitrstreamlit.connectionsrstreamlit.connections.utilr streamlit.errorsr streamlit.runtime.cachingr get_loggerror __annotations__datetimer pandasrsnowflake.connector.cursorrrirrrrmrr>r/rs^*#--0520"""8,," :2 E .)FGE r>