connecting-streamlit-to-snowflake
Connecting Streamlit apps to Snowflake. Use when setting up database connections, managing secrets, or querying Snowflake from a Streamlit app.
- License 许可
- Apache-2.0
- 所属仓库
- legout/pi-config
- 最近更新
- 2026-04-28
- Stars
- ★ 3
- Forks
- 0
01 前言 Front Matter
- name
- connecting-streamlit-to-snowflake
- license
- Apache-2.0
- description
- Connecting Streamlit apps to Snowflake. Use when setting up database connections, managing secrets, or querying Snowflake from a Streamlit app.
03 SKILL.md 正文
Streamlit Snowflake connection
Connect your Streamlit app to Snowflake the right way.
Use st.connection
Always use st.connection("snowflake") instead of raw connectors.
import streamlit as st
conn = st.connection("snowflake")
# Query data
df = conn.query("SELECT * FROM my_table LIMIT 100")
st.dataframe(df)
Why st.connection:
- Automatic connection pooling
- Built-in caching
- Handles reconnection
- Works with st.secrets
Caller's rights connection (Streamlit 1.53+)
For apps running in Snowflake, use caller's rights to run queries with the viewer's permissions instead of the app owner's:
conn = st.connection("snowflake", type="snowflake-callers-rights")
This is useful when:
- Different users should see different data based on their Snowflake roles
- You want row-level security to apply based on the viewer
- You don't want the app to have elevated permissions
Cached queries
Use the built-in ttl parameter to cache query results:
from datetime import timedelta
conn = st.connection("snowflake")
# Cache for 10 minutes
df = conn.query("SELECT * FROM metrics", ttl=timedelta(minutes=10))
# Cache for 1 hour
df = conn.query("SELECT * FROM reference_data", ttl=3600)
Configure with st.secrets
Store credentials in .streamlit/secrets.toml (never commit this file).
CRITICAL: Derive the account and host values from the user's Snowflake CLI connection config. Run snow connection list and use the exact values. A wrong account will redirect to the wrong login page.
# .streamlit/secrets.toml
[connections.snowflake]
account = "ORGNAME-ACCTNAME" # from `snow connection list`
host = "myaccount.snowflakecomputing.com" # from `snow connection list` (include if present)
user = "your_user"
authenticator = "externalbrowser"
warehouse = "your_warehouse"
database = "your_database"
schema = "your_schema"
Add to .gitignore:
.streamlit/secrets.toml
Parameterized queries
Use parameters to prevent SQL injection:
conn = st.connection("snowflake")
# Safe: parameterized
df = conn.query(
"SELECT * FROM users WHERE region = :region",
params={"region": selected_region}
)
# UNSAFE: string formatting - don't do this
# df = conn.query(f"SELECT * FROM users WHERE region = '{selected_region}'")
Write data
Use the session for write operations:
conn = st.connection("snowflake")
session = conn.session()
# Write a dataframe
session.write_pandas(df, "MY_TABLE", auto_create_table=True)
# Execute statements
session.sql("INSERT INTO logs VALUES (:ts, :msg)", params={...}).collect()
Multiple connections
Define multiple connections in secrets:
# .streamlit/secrets.toml
[connections.snowflake]
account = "prod_account"
# ... prod credentials
[connections.snowflake_staging]
account = "staging_account"
# ... staging credentials
prod_conn = st.connection("snowflake")
staging_conn = st.connection("snowflake_staging")
Chat with Cortex
Build a chat interface using Snowflake Cortex LLMs:
import streamlit as st
from snowflake.cortex import complete
st.set_page_config(page_title="AI Assistant", page_icon=":sparkles:")
if "messages" not in st.session_state:
st.session_state.messages = []
for msg in st.session_state.messages:
with st.chat_message(msg["role"]):
st.write(msg["content"])
if prompt := st.chat_input("Ask anything"):
st.session_state.messages.append({"role": "user", "content": prompt})
with st.chat_message("user"):
st.write(prompt)
with st.chat_message("assistant"):
response = st.write_stream(
complete(
"claude-3-5-sonnet",
prompt,
session=st.connection("snowflake").session(),
stream=True,
)
)
st.session_state.messages.append({"role": "assistant", "content": response})
See building-streamlit-chat-ui for more chat patterns (avatars, suggestions, history management).
Python 3.12+ dependency caveat
streamlit[snowflake] gates snowflake-connector-python on python_version < "3.12". On Python 3.12+, the connector is silently skipped and you get No module named 'snowflake' at runtime. Always add snowflake-connector-python>=3.3.0 as an explicit dependency in pyproject.toml:
dependencies = [
"snowflake-connector-python>=3.3.0",
"streamlit[snowflake]>=1.54.0",
]
References
04 同仓库更多 Skills
visual-explainer
Generate beautiful, self-contained HTML pages that visually explain systems, code changes, plans, and data. Use when the user asks for a diagram, architecture overview, diff review, plan review, project recap, comparison table, or any visual explanation of technical concepts. Also use proactively when you are about to render a complex ASCII table (4+ rows or 3+ columns) — present it as a styled HTML page instead.
table-extractor
>
smart-ocr
>
html-slides
>
using-streamlit-session-state
Using st.session_state to manage state across Streamlit reruns. Use when persisting data, handling widget state, implementing callbacks, or debugging state issues. Covers initialization patterns, widget-state association, and common gotchas.
using-streamlit-markdown
Covers all Markdown features in Streamlit including GitHub-flavored syntax plus Streamlit extensions like colored text, badges, Material icons, and LaTeX. Use when formatting text, labels, tooltips, or any text-rendering element.