-
I am using this example. But all the time, I am getting error for every question. Do I have any working example please? or am I missing any?
|
Beta Was this translation helpful? Give feedback.
Replies: 1 comment
-
To resolve the
Here is an updated version of the def default_jsonalyzer(
list_of_dict: List[Dict[str, Any]],
query_bundle: QueryBundle,
service_context: ServiceContext,
table_name: str = DEFAULT_TABLE_NAME,
prompt: BasePromptTemplate = DEFAULT_JSONALYZE_PROMPT,
sql_parser: BaseSQLParser = DefaultSQLParser(),
) -> Tuple[str, Dict[str, Any], List[Dict[str, Any]]]:
"""Default JSONalyzer that executes a query on a list of dictionaries.
Args:
list_of_dict (List[Dict[str, Any]]): List of dictionaries to query.
query_bundle (QueryBundle): The query bundle.
service_context (ServiceContext): The service context.
table_name (str): The table name to use, defaults to DEFAULT_TABLE_NAME.
prompt (BasePromptTemplate): The prompt to use.
sql_parser (BaseSQLParser): The SQL parser to use.
Returns:
Tuple[str, Dict[str, Any], List[Dict[str, Any]]]: The SQL Query,
the Schema, and the Result.
"""
try:
import sqlite_utils
except ImportError as exc:
IMPORT_ERROR_MSG = (
"sqlite-utils is needed to use this Query Engine:\n"
"pip install sqlite-utils"
)
raise ImportError(IMPORT_ERROR_MSG) from exc
# Instantiate in-memory SQLite database
db = sqlite_utils.Database(memory=True)
try:
# Load list of dictionaries into SQLite database
db[table_name].insert_all(list_of_dict)
except sqlite_utils.db_exceptions.IntegrityError as exc:
print_text(f"Error inserting into table {table_name}, expected format:")
print_text("[{col1: val1, col2: val2, ...}, ...]")
raise ValueError("Invalid list_of_dict") from exc
# Get the table schema
table_schema = db[table_name].columns_dict
query = query_bundle.query_str
prompt = prompt or DEFAULT_JSONALYZE_PROMPT
# Get the SQL query with text-to-SQL prompt
response_str = service_context.llm.predict(
prompt=prompt,
table_name=table_name,
table_schema=table_schema,
question=query,
)
sql_parser = sql_parser or DefaultSQLParser()
sql_query = sql_parser.parse_response_to_sql(response_str, query_bundle)
# Log the generated SQL query for debugging
print_text(f"Generated SQL Query: {sql_query}")
try:
# Execute the SQL query
results = list(db.query(sql_query))
except sqlite_utils.db_exceptions.OperationalError as exc:
print_text(f"Error executing query: {sql_query}")
raise ValueError("Invalid query") from exc
return sql_query, table_schema, results By adding |
Beta Was this translation helpful? Give feedback.
To resolve the
OperationalError
related to SQL query execution in theJSONalyze_query_engine.ipynb
example, you need to ensure that the SQL query generated by the LLM is valid. Here are the steps to troubleshoot and resolve the issue:Here is an updated version of the
default_jsonalyzer
function with additional logging to help debug the issue: