o
    ZhZ                     @  s   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dZ$dddddZ%G d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                 C  s&   d| d  d| d  dt | d  S )NzName: namez
, Unique: uniquez, Columns: Zcolumn_namesr   )r    r#   a/var/www/html/lang_env/lib/python3.10/site-packages/langchain_community/utilities/sql_database.py_format_index   s   
r%   z...)suffixcontentr   lengthintr&   c                C  sJ   t | tr	|dkr| S t| |kr| S | d|t|  ddd | 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&   r#   r#   r$   truncate_word    s
   $r/   c                   @  s>  e Zd ZdZ										dgdhddZe	didjd d!Zeed"d#d$d%					dkdld,d-Ze	.	/	0	1	2dmdnd8d9Z	e
dod:d;Zdpd=d>Zed?d@d$dAdpdBdCZe
dodDdEZdidqdGdHZdrdKdLZdrdMdNZ	OdsdddPdtdYdZZ	O	dudddPdvd]d^Zdidqd_d`Z	O	dudddPdwdbdcZdxdedfZdS )y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                   s  |_ |_|r|rtdtj _ttjj|d|	r&jj|dng  _	|r1t|nt _
j
rHj
j	 }|rHtd| d|rNt|nt _jrejj	 }|retd| d }|rot|nj	_t|ts|td|_|_|_jrtjtstdtjj	 t fdd	jD _|
_|	_|pt _|sȈjj|	j tjjd
 dS dS )z Create engine from database URI.z4Cannot specify both include_tables and ignore_tables)r4   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  s&    | ]}| v r|j | fV  qd S N)_custom_table_info).0tableintersectionselfr#   r$   	<genexpr>o   s    z'SQLDatabase.__init__.<locals>.<genexpr>Zviewsbindonlyr4   N)_engine_schema
ValueErrorr   
_inspectorsetlistget_table_namesZget_view_names_all_tables_include_tables_ignore_tablesget_usable_table_namesZ_usable_tablesr,   r)   	TypeError_sample_rows_in_table_info_indexes_in_table_inforE   dictrI   _max_string_length_view_supportr   	_metadatareflect)rJ   r3   r4   r6   r8   r:   r;   r<   r>   r@   rA   rB   missing_tablesZusable_tablesr#   rH   r$   __init__2   sf   




zSQLDatabase.__init__database_uriUnion[str, URL]engine_argskwargsr   r   c                 K  s$   |pi }| t |fi |fi |S )z'Construct a SQLAlchemy engine from URI.)r   )clsrd   rf   rg   Z_engine_argsr#   r#   r$   from_uri   s   zSQLDatabase.from_uriz0.3.18zFor performing structured retrieval using Databricks SQL, see the latest best practices and recommended APIs at https://docs.unitycatalog.io/ai/integrations/langchain/ insteadz1.0)messageremovalcatalogr   host	api_tokenwarehouse_id
cluster_idc              
   K  s   zddl m}	 W n ty   tdw d}
zddlm} | }
|
j}W n ttfy1   d}Y nw |du r<tdd|}|
rA|
jnd}|du rMtdd	|}|du r_|du r_|
r[|
j	}nt
d
|rg|rgt
d|rod| }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_contextrm   ZDATABRICKS_HOSTrn   Z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=)rd   rf   r#   )Z
databricksrq   ImportErrorZ!dbruntime.databricks_repl_contextrr   ZbrowserHostNameAttributeErrorr   ZapiTokenZ	clusterIdrQ   ri   )rh   rl   r4   rm   rn   ro   rp   rf   rg   rq   contextrr   Zdefault_hostZdefault_api_tokenZ	http_pathurir#   r#   r$   from_databricks   sP   <

zSQLDatabase.from_databricks127.0.0.1:8902root cnosdbpublicurluserpasswordtenantdatabasec                 C  sB   zddl m} ||||||}| j|dW S  ty    td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)rd   zRcnos-connector package not found, please install with `pip install cnos-connector`)Zcnosdb_connectorr   ri   rt   )rh   r~   r   r   r   r   r   rw   r#   r#   r$   from_cnosdb   s   zSQLDatabase.from_cnosdbc                 C  s
   | j jjS )z/Return string representation of dialect to use.)rO   dialectr    rJ   r#   r#   r$   r     s   
zSQLDatabase.dialectIterable[str]c                 C  s    | j rt| j S t| j| j S zGet names of tables available.)rW   sortedrV   rX   r   r#   r#   r$   rY   $  s   
z"SQLDatabase.get_usable_table_namesz0.0.1rY   )alternativerk   c                 C     |   S r   )rY   r   r#   r#   r$   rU   *     zSQLDatabase.get_table_namesc                 C  r   )z-Information about all tables in the database.)get_table_infor   r#   r#   r$   
table_info/  r   zSQLDatabase.table_infotable_namesc                   s     |durt| }|rtd| d| dd jjD }t t| }|r=jjjjt	|j
d  fddjjD }g }|D ]n}jrb|jjv rb|j|j  qM|j D ]\}}	t|	jtu rx|j|	 qgtt|j}
|
  }jpj}|r|d7 }jr|d	| d	7 }jr|d	| d	7 }|r|d
7 }|| qM|  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 rC   c                 S     g | ]}|j qS r#   r    rF   tblr#   r#   r$   
<listcomp>E      z.SQLDatabase.get_table_info.<locals>.<listcomp>rL   c                   s4   g | ]}|j t v rjd kr|j ds|qS )ZsqliteZsqlite_)r    rS   r   
startswithr   Zall_table_namesrJ   r#   r$   r   O  s    z

/*
z*/z

)rY   rS   
differencerQ   r`   Zsorted_tablesra   r_   rO   rT   rP   rE   r    appendcolumnsitemstyper   Z_columnsremover   r   compilerstripr\   r[   _get_table_indexes_get_sample_rowssortjoin)rJ   r   rb   Zmetadata_table_namesZ
to_reflectZmeta_tablesZtablesrG   kvZcreate_tabler   Zhas_extra_infoZ	final_strr#   r   r$   r   4  sV   



zSQLDatabase.get_table_inforG   r   c                 C  s(   | j |j}dtt|}d| S )Nr   zTable Indexes:
)rR   Zget_indexesr    r   mapr%   )rJ   rG   ZindexesZindexes_formattedr#   r#   r$   r   t  s   
zSQLDatabase._get_table_indexesc                 C  s   t || j}ddd |jD }z/| j }||}tt	dd |}W d    n1 s2w   Y  ddd |D }W n t
yM   d}Y nw | j d	|j d
| d| S )N	c                 S  r   r#   r   )rF   colr#   r#   r$   r   ~  r   z0SQLDatabase._get_sample_rows.<locals>.<listcomp>c                 S  s   dd | D S )Nc                 S  s   g | ]
}t |d d qS )Nd   r"   )rF   ir#   r#   r$   r     s    zBSQLDatabase._get_sample_rows.<locals>.<lambda>.<locals>.<listcomp>r#   )Zlsr#   r#   r$   <lambda>  s    z.SQLDatabase._get_sample_rows.<locals>.<lambda>r   c                 S  s   g | ]}d  |qS )r   )r   rF   rowr#   r#   r$   r     s    r{   z rows from z table:
)r   limitr[   r   r   rO   connectexecuterT   r   r   r    )rJ   rG   commandZcolumns_str
connectionZsample_rows_resultZsample_rowsZsample_rows_strr#   r#   r$   r   y  s&   
zSQLDatabase._get_sample_rows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          	      C  s  |pi }|pi }| j  }| jdur| jdkr#|jd| jf|d n]| jdkr3|jd| jf|d nM| jdkr9nG| jdkrI|jd	| jf|d n7| jd
krZ|jd| j |d n&| jdkrk|jd| j |d n| jdkrqn| jdkr|jd| jf|d t|trt|}nt|trn	t	dt
| |j|||d}|jr|dkrdd | D }n%|dkr| }|du rg n| g}n|dkr|W  d   S td|W  d   S W d   g S 1 sw   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   ZbigqueryzSET @@dataset_id=?ZmssqlZtrinozUSE ?ZduckdbzSET search_path TO oraclez#ALTER SESSION SET CURRENT_SCHEMA = Zsqlany
postgresqlzSET search_path TO %sz#Query expression has unknown type: r   c                 S  s   g | ]}|  qS r#   )_asdict)rF   xr#   r#   r$   r     s    z(SQLDatabase._execute.<locals>.<listcomp>onecursorz8Fetch parameter must be either 'one', 'all', or 'cursor')rO   beginrP   r   Zexec_driver_sqlr,   r   r   r   rZ   r   r   Zreturns_rowsZfetchallZfetchoner   rQ   )	rJ   r   r   r   r   r   r   resultZfirst_resultr#   r#   r$   _execute  s   













D;
HHzSQLDatabase._executeinclude_columns1Union[str, Sequence[Dict[str, Any]], Result[Any]]c                  sR    j ||||d}|dkr|S  fdd|D }|s!d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                   s"   g | ]} fd d|  D qS )c                   s    i | ]\}}|t | jd qS ))r(   )r/   r^   )rF   columnvaluer   r#   r$   
<dictcomp>  s    z.SQLDatabase.run.<locals>.<listcomp>.<dictcomp>)r   )rF   rr   r#   r$   r     s    
z#SQLDatabase.run.<locals>.<listcomp>c                 S  s   g | ]}t | qS r#   )tuplevaluesr   r#   r#   r$   r     s    r{   )r   r   )rJ   r   r   r   r   r   r   resr#   r   r$   run  s   
zSQLDatabase.runc              
   C  s>   z|  |W S  ty } z	 d| W  Y d}~S d}~ww )r   Error: N)r   rQ   )rJ   r   er#   r#   r$   get_table_info_no_throw  s   
z#SQLDatabase.get_table_info_no_throwLiteral['all', 'one']c             
   C  sH   z| j |||||dW S  ty# } z	 d| W  Y d}~S d}~w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   )rJ   r   r   r   r   r   r   r#   r#   r$   run_no_throw%  s   zSQLDatabase.run_no_throwDict[str, Any]c                 C  s$   t |  }|  }|d|dS )z4Return db context that you may want in agent prompt.z, )r   r   )rT   rY   r   r   )rJ   r   r   r#   r#   r$   rr   A  s   zSQLDatabase.get_context)
NNNNr1   FNFr2   F)r3   r   r4   r5   r6   r7   r8   r9   r:   r9   r;   r)   r<   r=   r>   r?   r@   r=   rA   r)   rB   r=   rD   )rd   re   rf   r?   rg   r   r   r0   )NNNNN)rl   r   r4   r   rm   r5   rn   r5   ro   r5   rp   r5   rf   r?   rg   r   r   r0   )ry   rz   r{   r|   r}   )r~   r   r   r   r   r   r   r   r   r   r   r0   )r   r   )r   r   )r   r9   r   r   )rG   r   r   r   )r   )
r   r   r   r   r   r   r   r   r   r   )r   F)r   r   r   r   r   r=   r   r   r   r   r   r   )r   r   r   r   r   r=   r   r   r   r   r   r   )r   r   )__name__
__module____qualname____doc__rc   classmethodri   r   rx   r   propertyr   rY   rU   r   r   r   r   r   r   r   r   rr   r#   r#   r#   r$   r0   /   s    P
`(

@
!\%r0   )r   r   r   r   )r'   r   r(   r)   r&   r   r   r   )'r   
__future__r   typingr   r   r   r   r   r	   r
   r   Z
sqlalchemyZlangchain_core._apir   Zlangchain_core.utilsr   r   r   r   r   r   r   Zsqlalchemy.enginer   r   r   Zsqlalchemy.excr   r   Zsqlalchemy.schemar   Zsqlalchemy.sql.expressionr   Zsqlalchemy.typesr   r%   r/   r0   r#   r#   r#   r$   <module>   s    ( 
