pandasai.exceptions.ExecuteSQLQueryNotUsed: For Direct SQL set to true, execute_sql_query function must be used. Generating Error Prompt!!!

[ichangjun]

System Info

pandasai:2.2.10 pathon:3.10.14

🐛 Describe the bug

code: smartDf = Agent( [mysql_connector1, mysql_connector2, mysql_connector3, mysql_connector4,mysql_connector5,mysql_connector6, mysql_connector7, mysql_connector8, mysql_connector9],

smartDf = Agent([mysql_connector1,mysql_connector2,mysql_connector3, mysql_connector8,mysql_connector5],

config={
    "llm": llm,
    "enable_cache": False,
    "verbose": True,
    "enforce_privacy": True,
    "open_charts": False,
    "max_retries": 1,
    "direct_sql": True,
    "dataframe_serializer": 4,
    "memory_size": 15,
    "lazy_load_connector": True,
    "custom_whitelisted_dependencies": ["pymysql", "sqlalchemy", "pandasai"]
},
vectorstore=vector_store, description=_description)

error message: 2024-08-09 00:45:57,654 - kdserver_logger - INFO - question: 根据性别，统计男女占比 Traceback (most recent call last): File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\code_cleaning.py", line 95, in execute code_to_run = self.get_code_to_run(input, code_context) File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\code_cleaning.py", line 152, in get_code_to_run code_to_run = self._clean_code(code, context) File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\code_cleaning.py", line 521, in _clean_code raise ExecuteSQLQueryNotUsed( pandasai.exceptions.ExecuteSQLQueryNotUsed: For Direct SQL set to true, execute_sql_query function must be used. Generating Error Prompt!!!

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\generate_chat_pipeline.py", line 335, in run ).run(input) File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\pipeline.py", line 137, in run raise e File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\pipeline.py", line 101, in run step_output = logic.execute( File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\code_cleaning.py", line 101, in execute return self.on_retry(code_to_run, e) File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\generate_chat_pipeline.py", line 162, in on_code_retry return self.code_exec_error_pipeline.run(correction_input) File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\error_correction_pipeline\error_correction_pipeline.py", line 48, in run return self.pipeline.run(input) File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\pipeline.py", line 137, in run raise e File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\pipeline.py", line 101, in run step_output = logic.execute( File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\code_cleaning.py", line 95, in execute code_to_run = self.get_code_to_run(input, code_context) File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\code_cleaning.py", line 152, in get_code_to_run code_to_run = self._clean_code(code, context) File "C:\ProgramData\anaconda3\envs\langchain\lib\site-packages\pandasai\pipelines\chat\code_cleaning.py", line 521, in _clean_code raise ExecuteSQLQueryNotUsed( pandasai.exceptions.ExecuteSQLQueryNotUsed: For Direct SQL set to true, execute_sql_query function must be used. Generating Error Prompt!!! 2024-08-09 00:45:58,928 - kdserver_logger - INFO - ====================================================llm cost: 1.2734711999655701 2024-08-09 00:45:58,928 - kdserver_logger - INFO - chat result type: <class 'str'> 2024-08-09 00:45:58,928 - kdserver_logger - INFO - chat result: Unfortunately, I was not able to answer your question, because of the following error:

For Direct SQL set to true, execute_sql_query function must be used. Generating Error Prompt!!!

[dosubot[bot]]

Hey @ichangjun, I'm here to help you with any issues or questions you have. I'm a bot that can assist with bug fixes, answer questions, and help you become a contributor. Let me know how I can assist you while we wait for a human maintainer.

To resolve the error pandasai.exceptions.ExecuteSQLQueryNotUsed: For Direct SQL set to true, execute_sql_query function must be used. Generating Error Prompt!!!, you need to ensure that you are using the execute_sql_query function as required by the library when the direct_sql configuration is set to true.

Here is an example of how you can use the execute_sql_query function correctly:

from pandasai import PandasAI
from pandasai.exceptions import ExecuteSQLQueryNotUsed

# Assuming you have a PandasAI instance and a context with direct_sql set to true
pandas_ai = PandasAI()
context = {
    "config": {
        "direct_sql": True
    },
    # other context properties
}

# Your SQL query
sql_query = "SELECT * FROM your_table"

try:
    # Use the execute_sql_query function
    result = pandas_ai.execute_sql_query(sql_query, context)
    print(result)
except ExecuteSQLQueryNotUsed as e:
    print(f"Error: {e}")

Make sure that the execute_sql_query function is used whenever you are executing SQL queries directly, as this is a requirement when the direct_sql configuration is enabled.

Additionally, ensure that your SQL query is safe by using the _is_sql_query_safe method provided by the SQLConnector class. This method checks for potentially malicious SQL keywords such as INSERT, UPDATE, DELETE, DROP, EXEC, ALTER, and CREATE. Here is an example of how you can use it:

from pandasai.connectors.sql import SQLConnector, MaliciousQueryError

# Assuming you have an instance of SQLConnector
sql_connector = SQLConnector(config)

# Your SQL query
sql_query = "SELECT * FROM your_table"

try:
    if sql_connector._is_sql_query_safe(sql_query):
        result = sql_connector.execute_direct_sql_query(sql_query)
        print(result)
    else:
        print("The SQL query is not safe to execute.")
except MaliciousQueryError as e:
    print(f"Error: {e}")

By following these steps, you can ensure that your SQL queries are executed safely and correctly when using the pandasai library with direct SQL queries ^[1].

To continue talking to Dosu, mention @dosu.

[ichangjun]

@oedokumaci 读取Mysql数据库，使用的是pandasai中的方法，具体代码如下： from pandasai.connectors import MySQLConnector mysql_connector1 = MySQLConnector( config={ "host": common_db_config["host"], "port": common_db_config["port"], "database": common_db_config["database"], "username": common_db_config["username"], "password": common_db_config["password"], "table": "DATA_ONE_TABLE_DIFFICULT" }, field_descriptions=_field_descriptions ) 类似于这样的数据库连接，有9个，= smartDf = SmartDatalake( [mysql_connector1, mysql_connector2, mysql_connector3, mysql_connector4,mysql_connector6, mysql_connector7, mysql_connector8, mysql_connector9],

smartDf = Agent([mysql_connector1,mysql_connector2,mysql_connector3, mysql_connector8,mysql_connector5],

config={
    "save_logs": True,
    "verbose": False,
    "enforce_privacy": False,
    "open_charts": False,
    "memory_size": 50,
    "enable_cache": False,
    "save_charts": False,
    "direct_sql": True,
    "data_viz_library": "plotly",
    "lazy_load_connector": True,
    "max_retries": 1,
    "use_error_correction_framework": True,
    "use_error_correction": True,
    "dataframe_serializer": 4,
    "custom_whitelisted_dependencies": ["sqlalchemy", "plotly",
                                        "pymysql", "mysql"],
    "llm": llm
})

然后就报错了，报错信息如下：

2024-08-12 10:05:45,827 - kdserver_logger - INFO - chat result: Unfortunately, I was not able to answer your question, because of the following error:

For Direct SQL set to true, execute_sql_query function must be used. Generating Error Prompt!!!

[oedokumaci]

@oedokumaci 读取Mysql数据库，使用的是pandasai中的方法，具体代码如下： from pandasai.connectors import MySQLConnector mysql_connector1 = MySQLConnector( config={ "host": common_db_config["host"], "port": common_db_config["port"], "database": common_db_config["database"], "username": common_db_config["username"], "password": common_db_config["password"], "table": "DATA_ONE_TABLE_DIFFICULT" }, field_descriptions=_field_descriptions ) 类似于这样的数据库连接，有9个，= smartDf = SmartDatalake( [mysql_connector1, mysql_connector2, mysql_connector3, mysql_connector4,mysql_connector6, mysql_connector7, mysql_connector8, mysql_connector9], # smartDf = Agent([mysql_connector1,mysql_connector2,mysql_connector3, mysql_connector8,mysql_connector5], config={ "save_logs": True, "verbose": False, "enforce_privacy": False, "open_charts": False, "memory_size": 50, "enable_cache": False, "save_charts": False, "direct_sql": True, "data_viz_library": "plotly", "lazy_load_connector": True, "max_retries": 1, "use_error_correction_framework": True, "use_error_correction": True, "dataframe_serializer": 4, "custom_whitelisted_dependencies": ["sqlalchemy", "plotly", "pymysql", "mysql"], "llm": llm }) 然后就报错了，报错信息如下：

2024-08-12 10:05:45,827 - kdserver_logger - INFO - chat result: Unfortunately, I was not able to answer your question, because of the following error:

For Direct SQL set to true, execute_sql_query function must be used. Generating Error Prompt!!!

I'm not the @dosu bot.