st.connections.SnowflakeConnection

A connection to Snowflake using the Snowflake Connector for Python.

Initialize this connection object using st.connection("snowflake") or st.connection("<name>", 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.

Tip

snowflake-snowpark-python must be installed in your environment to use this connection. You can install Snowflake extras along with Streamlit:

Terminal

pip install streamlit[snowflake]

Important

Account identifiers must be of the form <orgname>-<account_name> where <orgname> is the name of your Snowflake organization and <account_name> 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.

Class description[source]
st.connections.SnowflakeConnection(connection_name, **kwargs)
Methods
cursor()	Return a PEP 249-compliant cursor object.
query(sql, , ttl=None, show_spinner="Running `snowflake.query(...)`.", params=None, *kwargs)	Run a read-only SQL query.
reset()	Reset this connection so that it gets reinitialized the next time it's used.
session()	Create a new Snowpark Session from this connection.
write_pandas(df, table_name, database=None, schema=None, chunk_size=None, **kwargs)	Call snowflake.connector.pandas_tools.write_pandas with this connection.
Attributes
raw_connection	Access the underlying Snowflake Python connector object.

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:
Python
[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:
Python
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 (with or without secrets.toml). For example, if your Snowflake account supports SSO, you can set up a quick local connection for development using browser-based SSO.
Python
import streamlit as st
conn = st.connection(
    "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:
Python
[my_connection]
account = "xxx-xxx"
user = "xxx"
password = "xxx"
warehouse = "xxx"
database = "xxx"
schema = "xxx"
Your app code:
Python
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:
Python
[connections.snowflake]
connection_name = "my_connection"
Your app code:
Python
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 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.
Python
SNOWFLAKE_DEFAULT_CONNECTION_NAME = "my_connection"
Your app code:
Python
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:
Python
[default]
account = "xxx-xxx"
user = "xxx"
password = "xxx"
warehouse = "xxx"
database = "xxx"
schema = "xxx"
Your app code:
Python
import streamlit as st
conn = st.connection("snowflake")
df = conn.query("SELECT * FROM my_table")

SnowflakeConnection.cursor

Return a PEP 249-compliant cursor object.

For more information, see the Snowflake Python Connector documentation.

Function signature[source]
SnowflakeConnection.cursor()

SnowflakeConnection.query

Run a read-only SQL query.

This method implements both query result caching (with caching behavior identical to that of using @st.cache_data) as well as simple error handling/retries.

Note

Queries that are run without a specified ttl are cached indefinitely.

Function signature[source]
SnowflakeConnection.query(sql, , ttl=None, show_spinner="Running `snowflake.query(...)`.", params=None, *kwargs)
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, or None if cached results should not expire. The default is None.
show_spinner (boolean or string)	Enable the spinner. The default is to show a spinner when there is a "cache miss" and the cached resource is being created. If a string, the value of the show_spinner param will be used for the spinner text.
params (list, tuple, dict or None)	List of parameters to pass to the execute method. This connector supports binding data to a SQL statement using qmark bindings. For more information and examples, see the Snowflake Python Connector documentation. Default is None.
Returns
(pandas.DataFrame)	The result of running the query, formatted as a pandas DataFrame.

Example

Python

import streamlit as st

conn = st.connection("snowflake")
df = conn.query("select * from pet_owners")
st.dataframe(df)

SnowflakeConnection.raw_connection

Access the underlying Snowflake Python connector object.

Information on how to use the Snowflake Python Connector can be found in the Snowflake Python Connector documentation.

Function signature[source]
SnowflakeConnection.raw_connection

SnowflakeConnection.reset

Reset this connection so that it gets reinitialized the next time it's used.

This method can be useful when a connection has become stale, an auth token has expired, or in similar scenarios where a broken connection might be fixed by reinitializing it. Note that some connection methods may already use reset() in their error handling code.

Function signature[source]
SnowflakeConnection.reset()

Example

Python

import streamlit as st

conn = st.connection("my_conn")

# Reset the connection before using it if it isn't healthy
# Note: is_healthy() isn't a real method and is just shown for example here.
if not conn.is_healthy():
    conn.reset()

# Do stuff with conn...

SnowflakeConnection.session

Create a new Snowpark Session from this connection.

Information on how to use Snowpark sessions can be found in the Snowpark documentation.

Function signature[source]
SnowflakeConnection.session()

SnowflakeConnection.write_pandas

Call snowflake.connector.pandas_tools.write_pandas with this connection.

This convenience method is simply a thin wrapper around the write_pandas function of the same name from snowflake.connector.pandas_tools. For more information, see the Snowflake Python Connector documentation.

Function signature[source]
SnowflakeConnection.write_pandas(df, table_name, database=None, schema=None, chunk_size=None, **kwargs)
Returns
(tuple[bool, int, int])	A tuple containing three values: A bool that is True if the write was successful. An int giving the number of chunks of data that were copied. An int giving the number of rows that were inserted.

arrow_backPrevious: st.connection arrow_forwardNext: SQLConnection

forum

Still have questions?

Our forums are full of helpful information and Streamlit experts.

Tip