
    gx>                        d dl mZ d dlmZ d dlmZ d dlmZmZ d dl	m
Z
 d dlmZ d dlmZ d dlmZ erd d	lmZ d d
lmZ d dlmZ d dlmZ d dlmZ h dZh dZ G d de
d         Zy)    )annotations)ChainMap)deepcopy)TYPE_CHECKINGcast)BaseConnection)extract_from_dict)StreamlitAPIException)
cache_data)	timedelta)	DataFrame)
Connection)EngineSession>	   urlhostportquerydriverdialectdatabasepasswordusername>   r   r   r   c                      e Zd ZdZdddZdddddd	 	 	 	 	 	 	 	 	 	 	 ddZddZedd       Zedd	       Z	edd
       Z
y)SQLConnectiona$  A connection to a SQL database using a SQLAlchemy Engine.

    Initialize this connection object using ``st.connection("sql")`` or
    ``st.connection("<name>", 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
    <https://github.com/snowflakedb/snowflake-sqlalchemy#key-pair-authentication-support>`_,
    you can pass a value for ``private_key`` to use key-pair authentication. If
    you use ``dialect="bigquery"`` with `Google BigQuery
    <https://github.com/googleapis/python-bigquery-sqlalchemy#authentication>`_,
    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 <https://pypi.org/project/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 <https://docs.streamlit.io/develop/concepts/connections/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
    <https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16#new-andor-modified-dsn-and-connection-string-keywords>`_.

    This example requires the `Microsoft ODBC Driver for SQL Server
    <https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver16>`_
    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           
        dd l }t        |      }t        t        |      }t	        || j
                  j                               }t        |      st        d      d|v r|j                  j                  |d         }nt        D ]  }||vst        d|        |d   d|v rd|d    ndz   }|j                  j                  j                  ||d	   |j                  d
      |d   d|v rt        |d         nd |j                  d      d|v r|d   nd       }t	        || j
                  j                  di             }	 |j                   |fi |	}
|rt#        d|
j%                  d            S t#        d|
      S )Nr   zvMissing SQL DB connection configuration. Did you forget to set this in `secrets.toml` or as kwargs to `st.connection`?r   z!Missing SQL DB connection param: r   r   + r   r   r   r   r   r   )
drivernamer   r   r   r   r   r   create_engine_kwargsr   
AUTOCOMMIT)isolation_level)
sqlalchemyr   r	   _ALL_CONNECTION_PARAMSr   _secretsto_dictlenr
   enginemake_url_REQUIRED_CONNECTION_PARAMSURLcreategetintcreate_enginer   execution_options)self
autocommitkwargsr$   conn_param_kwargsconn_paramsr   pr    r!   engs              Y/var/www/openai/venv/lib/python3.12/site-packages/streamlit/connections/sql_connection.py_connectzSQLConnection._connect   s   &!-.DfM0$--2G2G2IJ;'` 
 K##,,[-?@C0K'/2STUSV0WXX 1 %Y//7;/F!K)*+BJ ##''..%$Z0$4 (17;1FSV,-D$4.5.Dk'*$ / C  (DMM%%&<bA 
 'j&&sC.BC#"7"7"7"UVV#&&    zRunning `sql.query(...)`.N)show_spinnerttl	index_col	chunksizeparamsc          
     f    ddl m ddlm}m}	m}
 ddlm}m}m	}m
}  | fd |d      d |||	|
f       |d      	      	 	 	 d	 	 	 d fd
       }t        |      j                  dd      }|j                   d j                   d| |_         t        ||      |      } ||f|||d|S )a
  Run a read-only query.

        This method implements query result caching and simple error
        handling/retries. The caching behavior is identical to that of using
        ``@st.cache_data``.

        .. note::
            Queries that are run without a specified ttl are cached indefinitely.

        All keyword arguments passed to this function are passed down to
        |pandas.read_sql|_, except ``ttl``.

        .. |pandas.read_sql| replace:: ``pandas.read_sql``
        .. _pandas.read_sql: https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html

        Parameters
        ----------
        sql : str
            The read-only SQL query to execute.
        show_spinner : boolean or string
            Enable the spinner. The default is to show a spinner when there is a
            "cache miss" and the cached resource is being created. If a string, the value
            of the show_spinner param will be used for the spinner text.
        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 <https://peps.python.org/pep-0249/#paramstyle>`_, 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InternalErrorOperationalError)retryretry_if_exception_typestop_after_attempt
wait_fixedc                $    j                         S )N)reset)_r2   s    r9   <lambda>z%SQLConnection.query.<locals>.<lambda>2  s    DJJLr;      T   )afterstopreraiserF   waitc                |    dd l }j                  j                         } |j                   |       |f|||d|S )Nr   r>   r?   r@   )pandas	_instanceconnectread_sql)	sqlr>   r?   r@   r4   pdinstancer2   rB   s	          r9   _queryz#SQLConnection.query.<locals>._query1  sQ       ~~--/H2;;S	 $#  r;   .rL   )r<   r=   rU   )NNN)rZ   strreturnr   )r$   rB   sqlalchemy.excrC   rD   rE   tenacityrF   rG   rH   rI   r_   replace__qualname___connection_namer   )r2   rZ   r<   r=   r>   r?   r@   r4   rC   rD   rE   rF   rG   rH   rI   r]   ttl_strrB   s   `                @r9   r   zSQLConnection.query   s   P 	$QQ	
 	
 
(#A&)/?@ A

 			 	

	. 

'#s
 	 "(!4!4 5Qt7L7L6MQwiX
%
 
 
	

 
 	
r;   c                6    | j                   j                         S )a  Call ``.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.
        )rW   rX   r2   s    r9   rX   zSQLConnection.connectb  s     ~~%%''r;   c                    | j                   S )zThe underlying SQLAlchemy Engine.

        This is equivalent to accessing ``self._instance``.

        Returns
        -------
        sqlalchemy.engine.base.Engine
            The underlying SQLAlchemy Engine.
        )rW   rh   s    r9   r)   zSQLConnection.enginer  s     ~~r;   c                J    t        t        | j                  j                        S )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   r_   rW   r   rh   s    r9   r   zSQLConnection.driver  s     C..//r;   c                2    ddl m}  || j                        S )a  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
        <https://docs.sqlalchemy.org/en/20/orm/session_basics.html>`_ 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()
        r   r   )sqlalchemy.ormr   rW   )r2   r   s     r9   sessionzSQLConnection.session  s    6 	+t~~&&r;   )F)r3   boolr`   r   )rZ   r_   r<   z
bool | strr=   zfloat | int | timedelta | Noner>   zstr | list[str] | Noner?   z
int | Noner`   r   )r`   SQLAlchemyConnection)r`   r   )r`   r_   )r`   r   )__name__
__module__rd   __doc__r:   r   rX   propertyr)   r   rm    r;   r9   r   r   6   s    {z*'` $?.2,0 $@
@
 !	@

 ,@
 *@
 @
 
@
D(  
 
 
0 
0 ' 'r;   r   r   N)
__future__r   collectionsr   copyr   typingr   r   streamlit.connectionsr   streamlit.connections.utilr	   streamlit.errorsr
   streamlit.runtime.cachingr   datetimer   rV   r   sqlalchemy.enginer   ro   sqlalchemy.engine.baser   rl   r   r%   r+   r   rt   r;   r9   <module>r      sT   ( #    & 0 8 2 0" D-&
  > s'N8, s'r;   