o
    ȳgY                     @  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: column_namesr   )r    r$   f/var/www/html/chatdoc2/venv/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
   $r0   c                   @  s0  e Zd ZdZ										dddeddZe	dfdgd d!Ze					dhdid(d)Ze	*	+	,	-	.djdkd4d5Ze	dld6d7Z
dmd9d:Zed;d<d=d>dmd?d@Ze	dldAdBZdfdndDdEZdodHdIZdodJdKZ	LdpdddMdqdVdWZ	L	drdddMdsdZd[Zdfdnd\d]Z	L	drdddMdtd_d`ZdudbdcZdS )v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)r5   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>viewsbindonlyr5   N)_engine_schema
ValueErrorr   
_inspectorsetlistget_table_namesget_view_names_all_tables_include_tables_ignore_tablesget_usable_table_names_usable_tablesr-   r*   	TypeError_sample_rows_in_table_info_indexes_in_table_inforF   dictrJ   _max_string_length_view_supportr   	_metadatareflect)rK   r4   r5   r7   r9   r;   r<   r=   r?   rA   rB   rC   missing_tablesusable_tablesr$   rI   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   )clsri   rk   rl   _engine_argsr$   r$   r%   from_uri   s   zSQLDatabase.from_uri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_contextrq   DATABRICKS_HOSTrr   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=)ri   rk   r$   )
databricksru   ImportError!dbruntime.databricks_repl_contextrv   browserHostNameAttributeErrorr   apiToken	clusterIdrS   ro   )rm   rp   r5   rq   rr   rs   rt   rk   rl   ru   contextrv   default_hostdefault_api_token	http_pathurir$   r$   r%   from_databricks   sP   4

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)ri   zRcnos-connector package not found, please install with `pip install cnos-connector`)cnosdb_connectorr   ro   r{   )rm   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.)rQ   dialectr    rK   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.)rZ   sortedrY   r[   r   r$   r$   r%   r\     s   
z"SQLDatabase.get_usable_table_namesz0.0.1r\   z1.0)alternativeremovalc                 C     |   S r   )r\   r   r$   r$   r%   rW   "     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 rD   c                 S     g | ]}|j qS r$   r    rG   tblr$   r$   r%   
<listcomp>=      z.SQLDatabase.get_table_info.<locals>.<listcomp>rM   c                   s4   g | ]}|j t v rjd kr|j ds|qS )sqlitesqlite_)r    rU   r   
startswithr   all_table_namesrK   r$   r%   r   G  s    z

/*
z*/z

)r\   rU   
differencerS   rd   sorted_tablesre   rc   rQ   rV   rR   rF   r    appendcolumnsitemstyper   _columnsremover   r   compilerstripr`   r_   _get_table_indexes_get_sample_rowssortjoin)rK   r   rf   metadata_table_names
to_reflectmeta_tablestablesrH   kvcreate_tabler   has_extra_info	final_strr$   r   r%   r   ,  sV   



zSQLDatabase.get_table_inforH   r   c                 C  s(   | j |j}dtt|}d| S )Nr   zTable Indexes:
)rT   get_indexesr    r   mapr&   )rK   rH   indexesindexes_formattedr$   r$   r%   r   l  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   )rG   colr$   r$   r%   r   v  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#   )rG   ir$   r$   r%   r   ~  s    zBSQLDatabase._get_sample_rows.<locals>.<lambda>.<locals>.<listcomp>r$   )lsr$   r$   r%   <lambda>~  s    z.SQLDatabase._get_sample_rows.<locals>.<lambda>r   c                 S  s   g | ]}d  |qS )r   )r   rG   rowr$   r$   r%   r     s    r   z rows from z table:
)r   limitr_   r   r   rQ   connectexecuterV   r   r   r    )rK   rH   commandcolumns_str
connectionsample_rows_resultsample_rowssample_rows_strr$   r$   r%   r   q  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   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                 S  s   g | ]}|  qS r$   )_asdict)rG   xr$   r$   r%   r     s    z(SQLDatabase._execute.<locals>.<listcomp>onecursorz8Fetch parameter must be either 'one', 'all', or 'cursor')rQ   beginrR   r   exec_driver_sqlr-   r   r   r   r^   r   r   returns_rowsfetchallfetchoner   rS   )	rK   r   r   r   r   r   r   result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)   )r0   rb   )rG   columnvaluer   r$   r%   
<dictcomp>  s    z.SQLDatabase.run.<locals>.<listcomp>.<dictcomp>)r   )rG   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   )rK   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   rS   )rK   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   )rK   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   )rV   r\   r  r   )rK   r   r   r$   r$   r%   rv   9  s   zSQLDatabase.get_context)
NNNNr2   FNFr3   F)r4   r   r5   r6   r7   r8   r9   r:   r;   r:   r<   r*   r=   r>   r?   r@   rA   r>   rB   r*   rC   r>   rE   )ri   rj   rk   r@   rl   r   r   r1   )NNNNN)rp   r   r5   r   rq   r6   rr   r6   rs   r6   rt   r6   rk   r@   rl   r   r   r1   )r   r   r   r   r   )r   r   r   r   r   r   r   r   r   r   r   r1   )r   r   )r   r   )r   r:   r   r   )rH   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__rh   classmethodro   r   r   propertyr   r\   r   rW   r   r   r   r   r   r   r  r  rv   r$   r$   r$   r%   r1   /   sv    P
`(

@
!\%r1   )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&   r0   r1   r$   r$   r$   r%   <module>   s    ( 
