Show API reference for
  1. Contents
  2. st.connections.SQLConnection
  3. st.connections.SQLConnection.connect
  4. SQLConnection.query
  5. SQLConnection.reset
  6. st.connections.SQLConnection.driver
  7. st.connections.SQLConnection.engine
  8. SQLConnection.session
star

Tip

This page only contains the st.connections.SQLConnection class. For a deeper dive into creating and managing data connections within Streamlit apps, read Connecting to data.

st.connections.SQLConnection

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

Python
import streamlit as st

conn = st.experimental_connection("sql")
df = conn.query("select * from pet_owners")
st.dataframe(df)

st.connections.SQLConnection.connect

priority_high

Warning

This method does not exist in version 1.23.0 of Streamlit.

SQLConnection.query

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

Python
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)

SQLConnection.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]

SQLConnection.reset()

Example

Python
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...

st.connections.SQLConnection.driver

priority_high

Warning

This method does not exist in version 1.23.0 of Streamlit.

st.connections.SQLConnection.engine

priority_high

Warning

This method does not exist in version 1.23.0 of Streamlit.

SQLConnection.session

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

Python
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()
arrow_backPrevious: SnowflakeConnectionarrow_forwardNext: BaseConnection
forum

Still have questions?

Our forums are full of helpful information and Streamlit experts.