o
    |qid                     @  s  d Z ddlmZ ddl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%e%  d%ddZ&ddd&ddZ'd'd d!Z(G d"d# d#Z)dS )(%SQLAlchemy wrapper around a database.    )annotationsN)AnyDictIterableListLiteralOptionalSequenceUnion)
deprecated)get_from_env)MetaDataTablecreate_engineinspectselecttext)URLEngineResult)ProgrammingErrorSQLAlchemyError)CreateTable)
Executable)NullTypereturnNonec                  C  sx   z1ddl } t| dr)t| jdr,| jj}t|dr|jdu r/d
dd	}||_W dS W dS W dS W dS  ty;   Y dS w )z?Patch DuckDBPyType to be hashable for SQLAlchemy compatibility.r   NtypingDuckDBPyType__hash__r   intc                 S  s   t t| S N)hashstrself r'   q/home/app/PaddleOCR-VL/.venv_paddleocr/lib/python3.10/site-packages/langchain_community/utilities/sql_database.pyr    &   s   z%_patch_duckdb_types.<locals>.__hash__)r   r!   )duckdbhasattrr   r   r    ImportError)r)   Zduckdb_typer    r'   r'   r(   _patch_duckdb_types   s   
r,   index+sqlalchemy.engine.interfaces.ReflectedIndexr$   c                 C  s&   d| d  d| d  dt | d  S )NzName: namez
, Unique: uniquez, Columns: Zcolumn_namesr$   )r-   r'   r'   r(   _format_index3   s   
r2   z...)suffixcontentr   lengthr!   r3   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)r4   r5   r3   r'   r'   r(   truncate_word:   s
   $r;   schemac                 C  s    t d| std|  d| S )zHSanitize a schema name to only contain letters, digits, and underscores.z^[a-zA-Z0-9_]+$zSchema name 'z_' contains invalid characters. Schema names must contain only letters, digits, and underscores.)rematch
ValueErrorr<   r'   r'   r(   sanitize_schemaI   s
   
rA   c                   @  s@  e Zd ZdZ										dhdiddZe	djdkd d!Zeed"d#d$d%					dldmd,d-Ze	.	/	0	1	2dndod8d9Z	e
dpd:d;Zdqd=d>Zed?d@d$dAdqdBdCZe
dpdDdEZ	drdsdHdIZdtdLdMZdtdNdOZ	PdudddQdvdZd[Z	P	dwdddQdxd^d_Zdjdyd`daZ	P	dwdddQdzdcddZd{dfdgZdS )|SQLDatabaser   N   F,  enginer   r<   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_tablesr@   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 r"   )_custom_table_info).0tableintersectionr&   r'   r(   	<genexpr>   s    z'SQLDatabase.__init__.<locals>.<genexpr>Zviewsbindonlyr<   N)_engine_schemar?   r   
_inspectorsetlistget_table_namesZget_view_names_all_tables_include_tables_ignore_tablesget_usable_table_namesZ_usable_tablesr8   r!   	TypeError_sample_rows_in_table_info_indexes_in_table_inforU   dictrY   _max_string_length_view_supportr   	_metadatareflect)r&   rE   r<   rG   rI   rK   rL   rM   rO   rQ   rR   rS   missing_tablesZusable_tablesr'   rX   r(   __init__V   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   )clsrr   rt   ru   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_contextr{   ZDATABRICKS_HOSTr|   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=)rr   rt   r'   )Z
databricksr   r+   Z!dbruntime.databricks_repl_contextr   ZbrowserHostNameAttributeErrorr   ZapiTokenZ	clusterIdr?   rw   )rv   rz   r<   r{   r|   r}   r~   rt   ru   r   contextr   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)rr   zRcnos-connector package not found, please install with `pip install cnos-connector`)Zcnosdb_connectorr   rw   r+   )rv   r   r   r   r   r   r   r   r'   r'   r(   from_cnosdb  s   zSQLDatabase.from_cnosdbc                 C  s
   | j jjS )z/Return string representation of dialect to use.)r^   dialectr/   r%   r'   r'   r(   r   C  s   
zSQLDatabase.dialectIterable[str]c                 C  s    | j rt| j S t| j| j S zGet names of tables available.)re   sortedrd   rf   r%   r'   r'   r(   rg   H  s   
z"SQLDatabase.get_usable_table_namesz0.0.1rg   )alternativery   c                 C     |   S r   )rg   r%   r'   r'   r(   rc   N     zSQLDatabase.get_table_namesc                 C  r   )z-Information about all tables in the database.)get_table_infor%   r'   r'   r(   
table_infoS  r   zSQLDatabase.table_infotable_namesget_col_commentsc              	     s2     |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 ]}jrb|jjv rb|j|j  qMz|j D ]\}	}
t|
jtu ry|j|
 qhW n" ty   t|j D ]\}	}
t|
jtu r|j|
 qY nw tt|j}|  }|rzi }|jD ]}|jr|j||j< q|r|d| d	7 }W n ty   td
w 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 rT   c                 S     g | ]}|j qS r'   r/   rV   tblr'   r'   r(   
<listcomp>k      z.SQLDatabase.get_table_info.<locals>.<listcomp>r[   c                   s4   g | ]}|j t v rjd kr|j ds|qS )ZsqliteZsqlite_)r/   ra   r   
startswithr   Zall_table_namesr&   r'   r(   r   u  s    z

/*
Column Comments: z
*/z:Column comments are available on PostgreSQL, MySQL, Oraclez

/*
z*/z

)"rg   ra   
differencer?   rn   Zsorted_tablesro   rm   r^   rb   r_   rU   r/   appendcolumnsitemstyper   Z_columnsremover   rk   r$   r   compilerstripcomment	Exceptionrj   ri   _get_table_indexes_get_sample_rowssortjoin)r&   r   r   rp   Zmetadata_table_namesZ
to_reflectZmeta_tablesZtablesrW   kvZcreate_tabler   Zcolumn_comments_dictcolumnZhas_extra_infoZ	final_strr'   r   r(   r   X  s   




zSQLDatabase.get_table_inforW   r   c                 C  s(   | j |j}dtt|}d| S )Nr   zTable Indexes:
)r`   Zget_indexesr/   r   mapr2   )r&   rW   ZindexesZindexes_formattedr'   r'   r(   r     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   )rV   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   r1   )rV   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   rV   rowr'   r'   r(   r     s    r   z rows from z table:
)r   limitri   r   r   r^   connectexecuterb   r   r   r/   )r&   rW   commandZcolumns_str
connectionZsample_rows_resultZsample_rowsZsample_rows_strr'   r'   r(   r     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 np| jdkr3|jd| jf|d n`| jdkr9nZ| jdkrI|jd	| jf|d nJ| jd
krZ|jd| j |d n9| jdkrk|jd| j |d n(| jdkrqn"| jdkr|jd| jf|d n| jdkr|jdt| j |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 ?r)   zSET search_path TO oraclez#ALTER SESSION SET CURRENT_SCHEMA = Zsqlany
postgresqlzSET search_path TO %sZhanazSET SCHEMA z#Query expression has unknown type: r   c                 S  s   g | ]}|  qS r'   )_asdict)rV   xr'   r'   r(   r   '  s    z(SQLDatabase._execute.<locals>.<listcomp>onecursorz8Fetch parameter must be either 'one', 'all', or 'cursor')r^   beginr_   r   Zexec_driver_sqlrA   r8   r$   r   r   rh   r   r   Zreturns_rowsZfetchallZfetchoner   r?   )	r&   r   r   r   r   r   r   resultZfirst_resultr'   r'   r(   _execute  s   














I@
MMz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 ))r5   )r;   rl   )rV   r   valuer%   r'   r(   
<dictcomp>J  s    z.SQLDatabase.run.<locals>.<listcomp>.<dictcomp>)r   )rV   rr%   r'   r(   r   I  s    
z#SQLDatabase.run.<locals>.<listcomp>c                 S  s   g | ]}t | qS r'   )tuplevaluesr   r'   r'   r(   r   R  s    r   )r   r$   )r&   r   r   r   r   r   r   resr'   r%   r(   run4  s   
zSQLDatabase.runc              
   C  s>   z|  |W S  ty } z	 d| W  Y d}~S d}~ww )r   Error: N)r   r?   )r&   r   er'   r'   r(   get_table_info_no_throwY  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   )r&   r   r   r   r   r   r   r'   r'   r(   run_no_throwi  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   )rb   rg   r   r   )r&   r   r   r'   r'   r(   r     s   zSQLDatabase.get_context)
NNNNrC   FNFrD   F)rE   r   r<   rF   rG   rH   rI   rJ   rK   rJ   rL   r!   rM   rN   rO   rP   rQ   rN   rR   r!   rS   rN   r"   )rr   rs   rt   rP   ru   r   r   rB   )NNNNN)rz   r$   r<   r$   r{   rF   r|   rF   r}   rF   r~   rF   rt   rP   ru   r   r   rB   )r   r   r   r   r   )r   r$   r   r$   r   r$   r   r$   r   r$   r   rB   )r   r$   )r   r   )NF)r   rJ   r   rN   r   r$   )rW   r   r   r$   )r   )
r   r   r   r   r   r   r   r   r   r   )r   F)r   r   r   r   r   rN   r   r   r   r   r   r   )r   rJ   r   r$   )r   r$   r   r   r   rN   r   r   r   r   r   r   )r   r   )__name__
__module____qualname____doc__rq   classmethodrw   r   r   r   propertyr   rg   rc   r   r   r   r   r   r   r   r   r   r'   r'   r'   r(   rB   S   s    P
`(

[
!a%rB   )r   r   )r-   r.   r   r$   )r4   r   r5   r!   r3   r$   r   r$   )r<   r$   r   r$   )*r   
__future__r   r=   r   r   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,   r2   r;   rA   rB   r'   r'   r'   r(   <module>   s&    ( 



