ExeSQL get tuple index out of range Error

[marcochang1028]

I am trying to use ExeSQL to perform the sql query, but sometimes it returns the error below.
tuple index out of range Error
From my observation, this error usually happens when the sql statement include LIKE '%XX%'. Here is the simple example which will raise this error

SELECT issue_number, customer_id, complaint_date, issue_description, solution_description FROM customer_issues WHERE solution_description LIKE '%abnormal vibration%'.

I have tried to execute this sql from my python code (psycopg2) and it can return the correct result.

I hope anyone can give me some hint to fix this problem.

[KevinHuSh]

I've submit an issue for that.

[marcochang1028]

I have confirmed that the problem is caused by the Peewee package, I updated the code in ragflow/agent/component
/exesql.py,
and after switching to pymysql and psycopg2 directly, it now works.
I've included the code below for your reference. It might not be perfect, but until a proper fix is in place, it can serve as a temporary workaround.

import re
import pandas as pd
import psycopg2
import pymysql
from abc import ABC
from agent.component.base import ComponentBase, ComponentParamBase

class ExeSQLParam(ComponentParamBase):
"""
Define the ExeSQL component parameters.
"""

def __init__(self):
    super().__init__()
    self.db_type = "mysql"  # 默認為 MySQL
    self.database = ""
    self.username = ""
    self.host = ""
    self.port = 3306
    self.password = ""
    self.loop = 3
    self.top_n = 30

def check(self):
    self.check_valid_value(self.db_type, "Choose DB type", ['mysql', 'postgresql', 'mariadb'])
    self.check_empty(self.database, "Database name")
    self.check_empty(self.username, "Database username")
    self.check_empty(self.host, "IP Address")
    self.check_positive_integer(self.port, "IP Port")
    self.check_empty(self.password, "Database password")
    self.check_positive_integer(self.top_n, "Number of records")

class ExeSQL(ComponentBase, ABC):
component_name = "ExeSQL"

def _run(self, history, **kwargs):
    if not hasattr(self, "_loop"):
        setattr(self, "_loop", 0)
    if self._loop >= self._param.loop:
        self._loop = 0
        raise Exception("Maximum loop time exceeds. Can't query the correct data via sql statement.")
    self._loop += 1

    # 解析 SQL 語句
    ans = self.get_input()
    ans = "".join(ans["content"]) if "content" in ans else ""
    ans = re.sub(r'^.*?SELECT ', 'SELECT ', repr(ans), flags=re.IGNORECASE)
    ans = re.sub(r';.*?SELECT ', '; SELECT ', ans, flags=re.IGNORECASE)
    ans = re.sub(r';[^;]*$', r';', ans)
    if not ans:
        return ExeSQL.be_output("SQL statement not found!")

    # 根據資料庫類型連接資料庫
    try:
        if self._param.db_type in ["mysql", "mariadb"]:
            conn = pymysql.connect(
                host=self._param.host,
                user=self._param.username,
                password=self._param.password,
                db=self._param.database,
                port=self._param.port
            )
        elif self._param.db_type == 'postgresql':
            conn = psycopg2.connect(
                host=self._param.host,
                user=self._param.username,
                password=self._param.password,
                dbname=self._param.database,
                port=self._param.port
            )
        cursor = conn.cursor()
        print(f"Connected to {self._param.db_type} database.")
    except Exception as e:
        return ExeSQL.be_output("**Error**: \nDatabase Connection Failed! \n" + str(e))

    # 執行 SQL 語句並處理結果
    sql_res = []
    for single_sql in re.split(r';', ans):
        if not single_sql:
            continue
        try:
            cursor.execute(single_sql)
            rows = cursor.fetchmany(size=self._param.top_n)
            if rows:
                # 獲取欄位名稱並將結果轉換為 DataFrame
                columns = [desc[0] for desc in cursor.description]
                single_res = pd.DataFrame(rows, columns=columns)
                sql_res.append({"content": "\nTotal: " + str(len(rows)) + "\n" + single_res.to_markdown()})
            else:
                sql_res.append({"content": "\nNo records found!"})
        except Exception as e:
            sql_res.append({"content": "**Error**:" + str(e) + "\nError SQL Statement:" + single_sql})
    
    conn.close()

    if not sql_res:
        return ExeSQL.be_output("No record in the database!")

    return pd.DataFrame(sql_res)