
    NgY                        d Z ddlmZ ddlmZmZmZmZmZm	Z	m
Z
mZ ddlZddlmZ ddlmZ ddlmZmZmZmZmZmZ ddlmZmZ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Z$ddddZ% G d d          Z&dS )%SQLAlchemy wrapper around a database.    )annotations)AnyDictIterableListLiteralOptionalSequenceUnionN)
deprecated)get_from_env)MetaDataTablecreate_engineinspectselecttext)URLEngineResult)ProgrammingErrorSQLAlchemyError)CreateTable)
Executable)NullTypeindex+sqlalchemy.engine.interfaces.ReflectedIndexreturnstrc           	     V    d| d          d| d          dt          | d                    S )NzName: namez
, Unique: uniquez, Columns: column_namesr    )r   s    f/var/www/html/ai-engine/env/lib/python3.11/site-packages/langchain_community/utilities/sql_database.py_format_indexr'      sF    	2v 	2 	2%/ 	2 	2~.//	2 	2    z...)suffixcontentr   lengthintr)   c                   t          | t                    r|dk    r| S t          |           |k    r| S | d|t          |          z
                               dd          d         |z   S )z]
    Truncate a string to a certain number of words, based on the max string
    length.
    r   N    )
isinstancer    lenrsplit)r*   r+   r)   s      r&   truncate_wordr3       so     gs## v{{
7||v)Vc&kk))*11#q99!<vEEr(   c                  h   e Zd ZdZ	 	 	 	 	 	 	 	 	 	 dTdUdZe	 dVdWd            Ze	 	 	 	 	 dXdYd&            Ze	 	 	 	 	 dZd[d1            Ze	d\d2            Z
d]d4Z ed5d6d78          d]d9            Ze	d\d:            ZdVd^d<Zd_d?Zd_d@Z	 d`dddBdadKZ	 	 dbdddBdcdNZdVd^dOZ	 	 dbdddBdddQZdedSZdS )fSQLDatabaser   N   F,  enginer   schemaOptional[str]metadataOptional[MetaData]ignore_tablesOptional[List[str]]include_tablessample_rows_in_table_infor,   indexes_in_table_infoboolcustom_table_infoOptional[dict]view_supportmax_string_lengthlazy_table_reflectionc                    | _         | _        |r|rt          d          t           j                    _        t           j                            |          |	r j                            |          ng z              _        |rt          |          nt                       _	         j	        r$ j	         j        z
  }|rt          d| d          |rt          |          nt                       _
         j
        r$ j
         j        z
  }|rt          d| d                                           }|rt          |          n j         _        t          |t                    st          d          | _        | _        | _         j        r{t           j        t&                    st          d          t           j                                       j                  t'           fd j        D                        _        |
 _        |	 _        |pt/                       _        |s< j                            |	 j         t5           j                   j        	           d
S d
S )z Create engine from database URI.z4Cannot specify both include_tables and ignore_tables)r9   zinclude_tables  not found in databasezignore_tables z,sample_rows_in_table_info must be an integerz]table_info must be a dictionary with table names as keys and the desired table info as valuesc              3  >   K   | ]}|v |j         |         fV  d S N)_custom_table_info).0tableintersectionselfs     r&   	<genexpr>z'SQLDatabase.__init__.<locals>.<genexpr>o   sG       + +L(( /67((((+ +r(   viewsbindonlyr9   N)_engine_schema
ValueErrorr   
_inspectorsetget_table_namesget_view_names_all_tables_include_tables_ignore_tablesget_usable_table_names_usable_tablesr0   r,   	TypeError_sample_rows_in_table_info_indexes_in_table_inforL   dictrO   _max_string_length_view_supportr   	_metadatareflectlist)rP   r8   r9   r;   r=   r?   r@   rA   rC   rE   rF   rG   missing_tablesusable_tablesrO   s   `             @r&   __init__zSQLDatabase.__init__2   s     	Um 	USTTT!$,// O++6+::@LTt--V-<<<RTV
 

 7EOs>222#%% 	!1D4DDN  LnLLL   5BLc-000suu 	!043CCN  K^KKK   33554AWc-000tGW3S99 	LJKKK*C'&;#"3" 	d5t<< 3  
 t677DDTEUVVL&* + + + + +!4+ + + ' 'D# #4)!/XZZ$ 	N"""\$-..|	 #     	 	r(   database_uriUnion[str, URL]engine_argskwargsr   r   c                2    |pi } | t          |fi |fi |S )z'Construct a SQLAlchemy engine from URI.)r   )clsrn   rp   rq   _engine_argss        r&   from_urizSQLDatabase.from_uri   s6     #(bs=>>>>II&IIIr(   catalogr    host	api_tokenwarehouse_id
cluster_idc           
        	 ddl m}	 n# t          $ r t          d          w xY wd}
	 ddlm}  |            }
|
j        }n# t          t          f$ r d}Y nw xY w|t          dd|          }|
r|
j        nd}|t          dd	|          }|||
r|
j	        }nt          d
          |r|rt          d          |rd| }nd| }d| d| d| d| d| 
} | j        d||d|S )a	  
        Class method to create an SQLDatabase instance from a Databricks connection.
        This method requires the 'databricks-sql-connector' package. If not installed,
        it can be added using `pip install databricks-sql-connector`.

        Args:
            catalog (str): The catalog name in the Databricks database.
            schema (str): The schema name in the catalog.
            host (Optional[str]): The Databricks workspace hostname, excluding
                'https://' part. If not provided, it attempts to fetch from the
                environment variable 'DATABRICKS_HOST'. If still unavailable and if
                running in a Databricks notebook, it defaults to the current workspace
                hostname. Defaults to None.
            api_token (Optional[str]): The Databricks personal access token for
                accessing the Databricks SQL warehouse or the cluster. If not provided,
                it attempts to fetch from 'DATABRICKS_TOKEN'. If still unavailable
                and running in a Databricks notebook, a temporary token for the current
                user is generated. Defaults to None.
            warehouse_id (Optional[str]): The warehouse ID in the Databricks SQL. If
                provided, the method configures the connection to use this warehouse.
                Cannot be used with 'cluster_id'. Defaults to None.
            cluster_id (Optional[str]): The cluster ID in the Databricks Runtime. If
                provided, the method configures the connection to use this cluster.
                Cannot be used with 'warehouse_id'. If running in a Databricks notebook
                and both 'warehouse_id' and 'cluster_id' are None, it uses the ID of the
                cluster the notebook is attached to. Defaults to None.
            engine_args (Optional[dict]): The arguments to be used when connecting
                Databricks. Defaults to None.
            **kwargs (Any): Additional keyword arguments for the `from_uri` method.

        Returns:
            SQLDatabase: An instance of SQLDatabase configured with the provided
                Databricks connection details.

        Raises:
            ValueError: If 'databricks-sql-connector' is not found, or if both
                'warehouse_id' and 'cluster_id' are provided, or if neither
                'warehouse_id' nor 'cluster_id' are provided and it's not executing
                inside a Databricks notebook.
        r   )sqlzfdatabricks-sql-connector package not found, please install with `pip install databricks-sql-connector`N)get_contextrw   DATABRICKS_HOSTrx   DATABRICKS_TOKENz6Need to provide either 'warehouse_id' or 'cluster_id'.z/Can't have both 'warehouse_id' or 'cluster_id'.z/sql/1.0/warehouses/z/sql/protocolv1/o/0/zdatabricks://token:@z?http_path=z	&catalog=z&schema=)rn   rp    )
databricksr|   ImportError!dbruntime.databricks_repl_contextr}   browserHostNameAttributeErrorr   apiToken	clusterIdrX   ru   )rs   rv   r9   rw   rx   ry   rz   rp   rq   r|   contextr}   default_hostdefault_api_token	http_pathuris                   r&   from_databrickszSQLDatabase.from_databricks   s   h	&&&&&&& 	 	 	:  	
 	 EEEEEE!kmmG"2LL^, 	  	  	 LLL	  <(9<HHD07AG,,T$[2DFWXXIJ$6 $.

 L    	PJ 	PNOOO 	<=|==II;z;;IG) G Gd G G"G G-4G G>DG G 	 s|P+PPPPPs   	 #A AA127.0.0.1:8902root cnosdbpublicurluserpasswordtenantdatabasec                    	 ddl m}  ||||||          }|                     |          S # t          $ r t          d          w xY w)a  
        Class method to create an SQLDatabase instance from a CnosDB connection.
        This method requires the 'cnos-connector' package. If not installed, it
        can be added using `pip install cnos-connector`.

        Args:
            url (str): The HTTP connection host name and port number of the CnosDB
                service, excluding "http://" or "https://", with a default value
                of "127.0.0.1:8902".
            user (str): The username used to connect to the CnosDB service, with a
                default value of "root".
            password (str): The password of the user connecting to the CnosDB service,
                with a default value of "".
            tenant (str): The name of the tenant used to connect to the CnosDB service,
                with a default value of "cnosdb".
            database (str): The name of the database in the CnosDB tenant.

        Returns:
            SQLDatabase: An instance of SQLDatabase configured with the provided
            CnosDB connection details.
        r   )make_cnosdb_langchain_uri)rn   zRcnos-connector package not found, please install with `pip install cnos-connector`)cnosdb_connectorr   ru   r   )rs   r   r   r   r   r   r   r   s           r&   from_cnosdbzSQLDatabase.from_cnosdb   sw    <		BBBBBB++CxRRC<<S<111 	 	 	0  	s	   *- Ac                $    | j         j        j        S )z/Return string representation of dialect to use.)rV   dialectr"   rP   s    r&   r   zSQLDatabase.dialect  s     |#((r(   Iterable[str]c                p    | j         rt          | j                   S t          | j        | j        z
            S zGet names of tables available.)r^   sortedr]   r_   r   s    r&   r`   z"SQLDatabase.get_usable_table_names  s7     	0$.///d&)<<===r(   z0.0.1r`   z1.0)alternativeremovalc                *    |                                  S r   )r`   r   s    r&   r[   zSQLDatabase.get_table_names"  s     **,,,r(   c                *    |                                  S )z-Information about all tables in the database.)get_table_infor   s    r&   
table_infozSQLDatabase.table_info'  s     ""$$$r(   table_namesc                                                      |9t          |                                        }|rt          d| d          |d  j        j        D             }t                    t          |          z
  }|r: j                             j         j        t          |           j
                    fd j        j        D             }g }|D ]R} j        r4|j         j        v r&|                     j        |j                            >|j                                        D ]:\  }}	t!          |	j                  t"          u r|j                            |	           ;t)          t+          |                               j                            }
|
                                 } j        p j        }|r|dz  } j        r|d                     |           dz  } j        r|d                     |           dz  }|r|d	z  }|                    |           T|                                 d
                    |          }|S )f  Get information about specified tables.

        Follows best practices as specified in: Rajkumar et al, 2022
        (https://arxiv.org/abs/2204.00498)

        If `sample_rows_in_table_info`, the specified number of sample rows will be
        appended to each table description. This can increase performance as
        demonstrated in the paper.
        Nztable_names rI   c                    g | ]	}|j         
S r   r"   )rM   tbls     r&   
<listcomp>z.SQLDatabase.get_table_info.<locals>.<listcomp>=  s    QQQSQQQr(   rR   c                    g | ]?}|j         t                    v r'j        d k    r|j                             d          =|@S )sqlitesqlite_)r"   rZ   r   
startswith)rM   r   all_table_namesrP   s     r&   r   z.SQLDatabase.get_table_info.<locals>.<listcomp>G  s\     
 
 
x3////\X--#(2E2Ei2P2P-  .--r(   z

/*
z*/z

)r`   rZ   
differencerX   rh   sorted_tablesri   rg   rV   rj   rW   rL   r"   appendcolumnsitemstyper   _columnsremover    r   compilerstriprd   rc   _get_table_indexes_get_sample_rowssortjoin)rP   r   rk   metadata_table_names
to_reflectmeta_tablestablesrN   kvcreate_tabler   has_extra_info	final_strr   s   `             @r&   r   zSQLDatabase.get_table_info,  s    5577" --88IIN X !V!V!V!VWWW)OQQDN4PQQQ))C0D,E,EE
 	N""(\*%%|	 #   
 
 
 
 
~3
 
 
   	& 	&E& 5:9P+P+Pd5ejABBB ++-- - -1<<8++N))!,,, {51199$,GGHHL(//113J+Nt/N   'h&
* FE4#:#:5#A#AEEEE
. DC4#8#8#?#?CCCC
 #d"
MM*%%%%KK''	r(   rN   r   c                    | j                             |j                  }d                    t	          t
          |                    }d| S )Nr   zTable Indexes:
)rY   get_indexesr"   r   mapr'   )rP   rN   indexesindexes_formatteds       r&   r   zSQLDatabase._get_table_indexesl  sD    /--ej99 IIc-&A&ABB5"3555r(   c                   t          |                              | j                  }d                    d |j        D                       }	 | j                                        5 }|                    |          }t          t          d |                    }d d d            n# 1 swxY w Y   d                    d |D                       }n# t          $ r d}Y nw xY w| j         d|j         d| d| S )	N	c                    g | ]	}|j         
S r   r   )rM   cols     r&   r   z0SQLDatabase._get_sample_rows.<locals>.<listcomp>v  s     C C Cc C C Cr(   c                    d | D             S )Nc                <    g | ]}t          |          d d         S )Nd   r%   )rM   is     r&   r   zBSQLDatabase._get_sample_rows.<locals>.<lambda>.<locals>.<listcomp>~  s&    #=#=#=QCFF4C4L#=#=#=r(   r   )lss    r&   <lambda>z.SQLDatabase._get_sample_rows.<locals>.<lambda>~  s    #=#="#=#=#= r(   r   c                8    g | ]}d                      |          S )r   )r   rM   rows     r&   r   z0SQLDatabase._get_sample_rows.<locals>.<listcomp>  s"    (O(O(OC3(O(O(Or(   r   z rows from z table:
)r   limitrc   r   r   rV   connectexecuterj   r   r   r"   )rP   rN   commandcolumns_str
connectionsample_rows_resultsample_rowssample_rows_strs           r&   r   zSQLDatabase._get_sample_rowsq  sk   --%%d&EFF ii C CU] C C CDD	!%%'' :%/%7%7%@%@""==?QRR                #ii(O(O;(O(O(OPPOO   	! 	! 	! OOO	! . ! !5: ! !! !! !	
s6   C &4B&C &B**C -B*."C C C all
parametersexecution_optionsr   Union[str, Executable]fetchLiteral['all', 'one', 'cursor']r   Optional[Dict[str, Any]]r   'Union[Sequence[Dict[str, Any]], Result]c                  |pi }|pi }| j                                         5 }| j        | j        dk    r|                    d| j        f|           n| j        dk    r|                    d| j        f|           n| j        dk    rn| j        dk    r|                    d	| j        f|           n| j        d
k    r |                    d| j         |           n`| j        dk    r |                    d| j         |           n5| j        dk    rn)| j        dk    r|                    d| j        f|           t          |t                    rt          |          }n5t          |t                    rnt          dt          |                     |                    |||          }|j        r|dk    rd |                                D             }nW|dk    r.|                                }|g n|                                g}n#|dk    r|cddd           S t!          d          |cddd           S 	 ddd           n# 1 swxY w Y   g S )z
        Executes SQL command through underlying engine.

        If the statement returns no rows, an empty list is returned.
        N	snowflakez"ALTER SESSION SET search_path = %s)r   bigqueryzSET @@dataset_id=?mssqltrinozUSE ?duckdbzSET search_path TO oraclez#ALTER SESSION SET CURRENT_SCHEMA = sqlany
postgresqlzSET search_path TO %sz#Query expression has unknown type: r   c                6    g | ]}|                                 S r   )_asdict)rM   xs     r&   r   z(SQLDatabase._execute.<locals>.<listcomp>  s     EEEaaiikkEEEr(   onecursorz8Fetch parameter must be either 'one', 'all', or 'cursor')rV   beginrW   r   exec_driver_sqlr0   r    r   r   rb   r   r   returns_rowsfetchallfetchoner   rX   )	rP   r   r   r   r   r   r   resultfirst_results	            r&   _executezSQLDatabase._execute  s     %2
-3\!! G	Z|'<;....<*; /    
 \Z//..,*; /    
 \W,,\W,,..*; /    
 \X--
 ..<dl<<*; /     \X--..LdlLL*; /     \X-- \\11../*; /    '3'' Ww--GZ00 W Ud7mm U UVVV''"3 (  F " E>>EE6??3D3DEEEFFe^^#)??#4#4L#/#7RRl>R>R>T>T=UFFh&&!EG	 G	 G	 G	 G	 G	 G	 G	H %R   OG	 G	 G	 G	 G	 G	 G	 G	vwG	 G	 G	 G	 G	 G	 G	 G	 G	 G	 G	 G	 G	 G	 G	P 	s   G7I&IIIinclude_columns1Union[str, Sequence[Dict[str, Any]], Result[Any]]c                                          ||||          }|dk    r|S  fd|D             }|sd |D             }|sdS t          |          S )zExecute a SQL command and return a string representing the results.

        If the statement returns rows, a string of the results is returned.
        If the statement returns no rows, an empty string is returned.
        r   r   c                P    g | ]"}fd |                                 D             #S )c                D    i | ]\  }}|t          |j                   S ))r+   )r3   rf   )rM   columnvaluerP   s      r&   
<dictcomp>z.SQLDatabase.run.<locals>.<listcomp>.<dictcomp>  s?       !FE eD4KLLL  r(   )r   )rM   rrP   s     r&   r   z#SQLDatabase.run.<locals>.<listcomp>  sT     
 
 

 	   %&WWYY  
 
 
r(   c                P    g | ]#}t          |                                          $S r   )tuplevaluesr   s     r&   r   z#SQLDatabase.run.<locals>.<listcomp>  s(    66635&&666r(   r   )r  r    )rP   r   r   r  r   r   r  ress   `       r&   runzSQLDatabase.run  s     UzEV  
 
 HM
 
 
 

 
 
 
  	766#666C 	2s88Or(   c                h    	 |                      |          S # t          $ r}	 d| cY d}~S d}~ww xY w)r   Error: N)r   rX   )rP   r   es      r&   get_table_info_no_throwz#SQLDatabase.get_table_info_no_throw  sX    	!&&{333 	! 	! 	!* Q==      	!s    
1,11Literal['all', 'one']c               r    	 |                      |||||          S # t          $ r}	 d| cY d}~S d}~ww xY w)a*  Execute a SQL command and return a string representing the results.

        If the statement returns rows, a string of the results is returned.
        If the statement returns no rows, an empty string is returned.

        If the statement throws an error, the error message is returned.
        )r   r   r  r  N)r  r   )rP   r   r   r  r   r   r  s          r&   run_no_throwzSQLDatabase.run_no_throw  sp     
	!88%"3 /      	! 	! 	!* Q==      	!s    
6166Dict[str, Any]c                    t          |                                           }|                                 }|d                    |          dS )z4Return db context that you may want in agent prompt.z, )r   r   )rj   r`   r  r   )rP   r   r   s      r&   r}   zSQLDatabase.get_context9  sF    46688991133
(;9O9OPPPr(   )
NNNNr6   FNFr7   F)r8   r   r9   r:   r;   r<   r=   r>   r?   r>   r@   r,   rA   rB   rC   rD   rE   rB   rF   r,   rG   rB   rK   )rn   ro   rp   rD   rq   r   r   r5   )NNNNN)rv   r    r9   r    rw   r:   rx   r:   ry   r:   rz   r:   rp   rD   rq   r   r   r5   )r   r   r   r   r   )r   r    r   r    r   r    r   r    r   r    r   r5   )r   r    )r   r   )r   r>   r   r    )rN   r   r   r    )r   )
r   r   r   r   r   r   r   r   r   r   )r   F)r   r   r   r   r  rB   r   r   r   r   r   r	  )r   r    r   r  r  rB   r   r   r   r   r   r	  )r   r  )__name__
__module____qualname____doc__rm   classmethodru   r   r   propertyr   r`   r   r[   r   r   r   r   r  r  r  r  r}   r   r(   r&   r5   r5   /   s       //
 !%'+-1.2)*&+,0"!$&+N N N N N`  '+J J J J [J 
 ##'&*$(&*^Q ^Q ^Q ^Q [^Q@  $ & & & & [&P ) ) ) X)> > > > Z%=uMMM- - - NM- % % % X%> > > > >@6 6 6 6

 
 
 
B 27W
 046:W W W W W Wx 27 %	# 046:# # # # # #J! ! ! ! !& (- %	! 046:! ! ! ! ! !8Q Q Q Q Q Qr(   r5   )r   r   r   r    )r*   r   r+   r,   r)   r    r   r    )'r"  
__future__r   typingr   r   r   r   r	   r
   r   r   
sqlalchemylangchain_core._apir   langchain_core.utilsr   r   r   r   r   r   r   sqlalchemy.enginer   r   r   sqlalchemy.excr   r   sqlalchemy.schemar   sqlalchemy.sql.expressionr   sqlalchemy.typesr   r'   r3   r5   r   r(   r&   <module>r/     s   + + " " " " " " P P P P P P P P P P P P P P P P P P P P     * * * * * * - - - - - -                2 1 1 1 1 1 1 1 1 1 < < < < < < < < ) ) ) ) ) ) 0 0 0 0 0 0 % % % % % %    ?D F F F F F FNQ NQ NQ NQ NQ NQ NQ NQ NQ NQr(   