L i/?ddlmZddlmZddlmZddlmZmZm Z ddl m Z ddl m Z ddlmZddlmZerdd lmZdd lmZdd lmZdd lmZdd lmZhdZhdZGdde dZy)) annotations)ChainMap)deepcopy) TYPE_CHECKINGAnycast)BaseConnection)extract_from_dict)StreamlitAPIException) cache_data) timedelta) DataFrame) Connection)EngineSession> urlhostportquerydriverdialectdatabasepasswordusername>rrrceZdZdZd d dZdddddd d dZddZeddZedd Z edd Z y) SQLConnectiona#A connection to a SQL database using a SQLAlchemy Engine. Initialize this connection object using ``st.connection("sql")`` or ``st.connection("", type="sql")``. Connection parameters for a SQLConnection can be specified using ``secrets.toml`` and/or ``**kwargs``. Possible connection parameters include: - ``url`` or keyword arguments for |sqlalchemy.engine.URL.create()|_, except ``drivername``. Use ``dialect`` and ``driver`` instead of ``drivername``. - Keyword arguments for |sqlalchemy.create_engine()|_, including custom ``connect()`` arguments used by your specific ``dialect`` or ``driver``. - ``autocommit``. If this is ``False`` (default), the connection operates in manual commit (transactional) mode. If this is ``True``, the connection operates in autocommit (non-transactional) mode. If ``url`` exists as a connection parameter, Streamlit will pass it to ``sqlalchemy.engine.make_url()``. Otherwise, Streamlit requires (at a minimum) ``dialect``, ``username``, and ``host``. Streamlit will use ``dialect`` and ``driver`` (if defined) to derive ``drivername``, then pass the relevant connection parameters to ``sqlalchemy.engine.URL.create()``. In addition to the default keyword arguments for ``sqlalchemy.create_engine()``, your dialect may accept additional keyword arguments. For example, if you use ``dialect="snowflake"`` with `Snowflake SQLAlchemy `_, you can pass a value for ``private_key`` to use key-pair authentication. If you use ``dialect="bigquery"`` with `Google BigQuery `_, you can pass a value for ``location``. 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 database interactions can be performed by using the ``.session`` property to receive a regular SQLAlchemy Session. .. Important:: `SQLAlchemy `_ must be installed in your environment to use this connection. You must also install your driver, such as ``pyodbc`` or ``psycopg2``. .. |sqlalchemy.engine.URL.create()| replace:: ``sqlalchemy.engine.URL.create()`` .. _sqlalchemy.engine.URL.create(): https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.engine.URL.create .. |sqlalchemy.engine.make_url()| replace:: ``sqlalchemy.engine.make_url()`` .. _sqlalchemy.engine.make_url(): https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.engine.make_url .. |sqlalchemy.create_engine()| replace:: ``sqlalchemy.create_engine()`` .. _sqlalchemy.create_engine(): https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.create_engine Examples -------- **Example 1: Configuration with URL** You can configure your SQL connection using Streamlit's `Secrets management `_. The following example specifies a SQL connection URL. ``.streamlit/secrets.toml``: >>> [connections.sql] >>> url = "xxx+xxx://xxx:xxx@xxx:xxx/xxx" Your app code: >>> import streamlit as st >>> >>> conn = st.connection("sql") >>> df = conn.query("SELECT * FROM pet_owners") >>> st.dataframe(df) **Example 2: Configuration with dialect, host, and username** If you do not specify ``url``, you must at least specify ``dialect``, ``host``, and ``username`` instead. The following example also includes ``password``. ``.streamlit/secrets.toml``: >>> [connections.sql] >>> dialect = "xxx" >>> host = "xxx" >>> username = "xxx" >>> password = "xxx" Your app code: >>> import streamlit as st >>> >>> conn = st.connection("sql") >>> df = conn.query("SELECT * FROM pet_owners") >>> st.dataframe(df) **Example 3: Configuration with keyword arguments** You can configure your SQL connection with keyword arguments (with or without ``secrets.toml``). For example, if you use Microsoft Entra ID with a Microsoft Azure SQL server, you can quickly set up a local connection for development using `interactive authentication `_. This example requires the `Microsoft ODBC Driver for SQL Server `_ for *Windows* in addition to the ``sqlalchemy`` and ``pyodbc`` packages for Python. >>> import streamlit as st >>> >>> conn = st.connection( ... "sql", ... dialect="mssql", ... driver="pyodbc", ... host="xxx.database.windows.net", ... database="xxx", ... username="xxx", ... query={ ... "driver": "ODBC Driver 18 for SQL Server", ... "authentication": "ActiveDirectoryInteractive", ... "encrypt": "yes", ... }, ... ) >>> >>> df = conn.query("SELECT * FROM pet_owners") >>> st.dataframe(df) c ddl}ddlm}m}t |}t t |}t||jj}t|s tdd|vr ||d}ntD]} | |vstd| |dd|vrd|dnd z} |j| |d |jd |d d |vrt|d nd|jd|jdi}t||jjdi} |j |fi| } |rt#d| j%dSt#d| S)Nr)URLmake_urlzvMissing SQL DB connection configuration. Did you forget to set this in `secrets.toml` or as kwargs to `st.connection`?rz!Missing SQL DB connection param: rr+rrrrrr) drivernamerrrrrrcreate_engine_kwargsr AUTOCOMMIT)isolation_level) sqlalchemysqlalchemy.enginerr rr _ALL_CONNECTION_PARAMSr_secretsto_dictlenr _REQUIRED_CONNECTION_PARAMScreategetint create_enginerexecution_options) self autocommitkwargsr'rr conn_param_kwargs conn_paramsrpr#r$engs j/mnt/ssd/data/python-lab/Trading/venv/lib/python3.12/site-packages/streamlit/connections/sql_connection.py_connectzSQLConnection._connects3&!-.DfM0$--2G2G2IJ ;'`  K ;u-.C0 YK'/2STUSV0WXX Y%Y//7;/F!K)*+BJ**%$Z0$4 (17;1FSV,-D$4!oogr2C ( DMM%%&`_, is supported. Default is None. **kwargs: dict Additional keyword arguments are passed to |pandas.read_sql|_. .. |pandas.read_sql| replace:: ``pandas.read_sql`` .. _pandas.read_sql: https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html Returns ------- pandas.DataFrame The result of running the query, formatted as a pandas DataFrame. Example ------- >>> import streamlit as st >>> >>> conn = st.connection("sql") >>> df = conn.query( ... "SELECT * FROM pet_owners WHERE owner = :owner", ... ttl=3600, ... params={"owner": "barbara"}, ... ) >>> st.dataframe(df) r)text) DatabaseError InternalErrorOperationalError)retryretry_if_exception_typestop_after_attempt wait_fixedc$jS)N)reset)_r3s r:z%SQLConnection.query..1s DJJLr<T)afterstopreraiserGwaitc ddl}jj}td|j||f|||d|S)Nrrr?r@rA)pandas _instanceconnectrread_sql) sqlr?r@rAr5pdinstancer3rCs r:_queryz#SQLConnection.query.._query0s\ ~~--/H I('!     r<.rM)r=r>rV)NNN) r[strr?str | list[str] | Noner@ int | NonerA Any | Noner5rreturnr)r'rCsqlalchemy.excrDrErFtenacityrGrHrIrJr`replace __qualname___connection_namer )r3r[r=r>r?r@rAr5rDrErFrGrHrIrJr^ttl_strrCs` @r:rzSQLConnection.querys.P $QQ   (#A&) /?@A 15$(!%   - "           4  '#s  "(!4!4 5Qt7L7L6MQwiX %          r<c6|jjS)aCall ``.connect()`` on the underlying SQLAlchemy Engine, returning a new connection object. Calling this method is equivalent to calling ``self._instance.connect()``. NOTE: This method should not be confused with the internal ``_connect`` method used to implement a Streamlit Connection. Returns ------- sqlalchemy.engine.Connection A new SQLAlchemy connection object. )rXrYr3s r:rYzSQLConnection.connectds~~%%''r<c|jS)zThe underlying SQLAlchemy Engine. This is equivalent to accessing ``self._instance``. Returns ------- sqlalchemy.engine.base.Engine The underlying SQLAlchemy Engine. )rXrls r:enginezSQLConnection.enginets~~r<cBtd|jjS)a The name of the driver used by the underlying SQLAlchemy Engine. This is equivalent to accessing ``self._instance.driver``. Returns ------- str The name of the driver. For example, ``"pyodbc"`` or ``"psycopg2"``. r`)rrXrrls r:rzSQLConnection.driversE4>>0011r<c2ddlm}||jS)aReturn 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. Returns ------- sqlalchemy.orm.Session A SQLAlchemy Session. Example ------- >>> import streamlit as st >>> conn = st.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() rr)sqlalchemy.ormrrX)r3rs r:sessionzSQLConnection.sessions6 +t~~&&r<)F)r4boolr5rrdr)r[r`r=z bool | strr>zfloat | int | timedelta | Noner?rar@rbrArcr5rrdr)rdSQLAlchemyConnection)rdr)rdr`)rdr) __name__ __module__rh__doc__r;rrYpropertyrnrrrr<r:rr6szx*#`$?.2,0 $!C C ! C , C * C C C C  C J(    2 2''r<rrN) __future__r collectionsrcopyrtypingrrrstreamlit.connectionsr streamlit.connections.utilr streamlit.errorsr streamlit.runtime.cachingr datetimer rWrr(rrtsqlalchemy.engine.baserrqrr)r-rryr<r:rsW(# ++0820" D-& >u'N8,u'r<