Important
This is an experimental feature. Experimental features and their APIs may change or be removed at any time. To learn more, click here.
Tip
This page only contains on the st.connections.SQLConnection API. For a deeper dive into creating and managing data connections within Streamlit apps, read Connecting to data.
A connection to a SQL database using a SQLAlchemy Engine. Initialize using st.experimental_connection("<name>", type="sql").
SQLConnection provides the query() convenience method, which can be used to run simple read-only queries with both caching and simple error handling/retries. More complex DB interactions can be performed by using the .session property to receive a regular SQLAlchemy Session.
SQLConnections should always be created using st.experimental_connection(), not initialized directly. Connection parameters for a SQLConnection can be specified using either st.secrets or **kwargs. Some frequently used parameters include:
- url or arguments for sqlalchemy.engine.URL.create(). Most commonly it includes a dialect, host, database, username and password.
- create_engine_kwargs can be passed via st.secrets, such as for snowflake-sqlalchemy or Google BigQuery. These can also be passed directly as **kwargs to experimental_connection().
- autocommit=True to run with isolation level AUTOCOMMIT. Default is False.
| Class description[source] | |
|---|---|
st.connections.SQLConnection(connection_name, **kwargs) | |
| Methods | |
query(sql, *, ttl=None, index_col=None, chunksize=None, params=None, **kwargs) | Run a read-only query. |
reset() | Reset this connection so that it gets reinitialized the next time it's used. |
| Attributes | |
Return a SQLAlchemy Session. | |
Example
import streamlit as st
conn = st.experimental_connection("sql")
df = conn.query("select * from pet_owners")
st.dataframe(df)
Basic usage:
import streamlit as st
conn = st.experimental_connection("sql")
df = conn.query("select * from pet_owners")
st.dataframe(df)
In case you want to pass a connection URL (or other parameters) directly, it also works:
conn = st.experimental_connection(
"local_db",
type="sql",
url="mysql://user:pass@localhost:3306/mydb"
)
Or specify parameters in secrets:
# .streamlit/secrets.toml
[connections.mydb]
dialect = "mysql"
username = "myuser"
password = "password"
host = "localhost"
database = "mydb"
# streamlit_app.py
conn = st.experimental_connection("mydb", type="sql", autocommit=True)
As described above, some cloud databases use extra **kwargs to specify credentials. These can be passed via secrets using the create_engine_kwargs section:
# .streamlit/secrets.toml
[connections.snowflake]
url = "snowflake://<username>@<account>/"
[connections.snowflake.create_engine_kwargs.connect_args]
authenticator = "externalbrowser"
role = "..."
# ...
Run a read-only 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.
Aside from the ttl kwarg, all kwargs passed to this function are passed down to pd.read_sql and have the behavior described in the pandas documentation.
| Function signature[source] | |
|---|---|
SQLConnection.query(sql, *, ttl=None, index_col=None, chunksize=None, 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. |
index_col (str, list of str, or None) | Column(s) to set as index(MultiIndex). Default is None. |
chunksize (int or None) | If specified, return an iterator where chunksize is the number of rows to include in each chunk. Default is None. |
params (list, tuple, dict or None) | List of parameters to pass to the execute method. The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249 paramstyle, is supported. Default is None. |
**kwargs (dict) | Additional keyword arguments are passed to pd.read_sql. |
| Returns | |
(pd.DataFrame) | The result of running the query, formatted as a pandas DataFrame. |
Example
import streamlit as st
conn = st.experimental_connection("sql")
df = conn.query("select * from pet_owners where owner = :owner", ttl=3600, params={"owner":"barbara"})
st.dataframe(df)
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] | |
|---|---|
SQLConnection.reset() |
Example
import streamlit as st
conn = st.experimental_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...
Return a SQLAlchemy Session.
Users of this connection should use the contextmanager pattern for writes, transactions, and anything more complex than simple read queries.
See the usage example below, which assumes we have a table numbers with a single integer column val. The SQLAlchemy docs also contain much more information on the usage of sessions.
| Function signature[source] | |
|---|---|
SQLConnection.session |
Example
import streamlit as st
conn = st.experimental_connection("sql")
n = st.slider("Pick a number")
if st.button("Add the number!"):
with conn.session as session:
session.execute("INSERT INTO numbers (val) VALUES (:n);", {"n": n})
session.commit()
Still have questions?
Our forums are full of helpful information and Streamlit experts.