Pirate Wiki

Toggle Theme

Connections ​

You can use your own Python code to connect to a data store or an API. However, Streamlit provides a convenient st.connection method to easily connect your apps to databases or APIs with just a few lines of code.

The advantages of using st.connection are:

  • It has good integration with Streamlit secrets.
  • It lets your cache the resources & data, and even configure ttl and max_entries.

Secrets ​

Consider the following example on how you configure secrets for st.connection:

toml
# .streamlit/secrets.toml

[connections.pets_db]
url = "sqlite:///pets.db"
python
# streamlit_app.py
import streamlit as st

# Create the SQL connection to pets_db as specified in your secrets file.
conn = st.connection('pets_db', type='sql')

Multiple environments ​

If you have different connections for different environments, you can define different connection sections in your secrets file for each environment and then decide which to use at runtime. st.connection supports this with the env:<MY_NAME_VARIABLE> syntax.

toml
# ~/.streamlit/secrets.toml

[connections.local]
url = "mysql://me:****@localhost:3306/local_db"

[connections.staging]
url = "mysql://jdoe:******@staging.acmecorp.com:3306/staging_db"
python
# streamlit_app.py
import streamlit as st

conn = st.connection("env:DB_CONN", "sql")
df = conn.query("select * from mytable")
# ...

You can now specify whether to connect to local or staging at runtime by setting the DB_CONN environment variable as:

shell
# connect to local
DB_CONN=local streamlit run streamlit_app.py

# connect to staging
DB_CONN=staging streamlit run streamlit_app.py

Caching ​

By default, Streamlit caches the resource (st.connection object) and data (result-set from conn.query) indefinitely. In other words, ttl is set to None by default. But you can configure them.

NOTE

The connection objects (st.connection) are cached using st.cache_resource, while the results from the methods query() and read() are cached using st.cache_data.

python
# streamlit_app.py
import streamlit as st

conn = st.connection('pets_db', type='sql')

# Query and display the data you inserted
pet_owners = conn.query('select * from pet_owners')
st.dataframe(pet_owners)

In the above example, Streamlit caches both the results from st.connection() and conn.query() indefinitely as long as the app server runs.

python
# streamlit_app.py
import streamlit as st

conn = st.connection('pets_db', type='sql')

# The table gets refreshed every hour, so cache ttl is set to 3600 seconds
pet_owners = conn.query('select * from pet_owners', ttl=3600)
st.dataframe(pet_owners)

In the above example, Streamlit invalidates the conn.query() cache after 1 hour.

SQLAlchemy ​

All SQLConnections in Streamlit use SQLAlchemy. So, you'll need to install it using:

shell
pip install SQLAlchemy

NOTE

For most SQL dialects, you probably need to install the driver as well. SQLite driver ships with python3, so it's not needed.

Advanced configuration ​

The SQLConnection configuration uses SQLAlchemy's create_engine() function. Several popular SQLAlchemy dialects, such as Snowflake and Google BigQuery, can be configured using additional arguments to create_engine(). These can be passed directly as **kwargs to the st.connection call or specified in an additional secrets section called create_engine_kwargs.

IMPORTANT

If you have both kwargs and secrets.toml values, they will be merged (typically, kwargs take precedence).

For example, Snowflake connection takes an additional connect_args argument. It can configured as follows:

toml
# .streamlit/secrets.toml

[connections.snowflake]
url = "snowflake://<user_login_name>@<account_identifier>/"

[connections.snowflake.create_engine_kwargs.connect_args]
authenticator = "externalbrowser"
warehouse = "xxx"
role = "xxx"
python
# streamlit_app.py
import streamlit as st

# url and connect_args from secrets.toml above are picked up and used here
conn = st.connection("snowflake", "sql")
# ...

You can also pass these directly using **kwargs as follows:

python
# streamlit_app.py
import streamlit as st

# secrets.toml is not needed
conn = st.connection(
    "snowflake",
    "sql",
    url = "snowflake://<user_login_name>@<account_identifier>/",
    connect_args = dict(
        authenticator = "externalbrowser",
        warehouse = "xxx",
        role = "xxx",
    )
)
# ...

Custom Connections ​

Creating custom connection classes isn't covered in this book, but if you're interested in building your own connection, refer to this section of official docs.