[Question]: Interested only in final sql statement

[vp999]

Question Validation

• I have searched both the documentation and discord for an answer.

Question

i am using NLSQLTableQueryEngine with llamacpp as backend on azure notebook. The query_engine.query() returns results as well as sql statement. how do i restrict it to return only final sql and not to execute it?

[dosubot[bot]]

🤖

Hello,

To have the query_engine.query() function return only the final SQL statement and not execute it, you can modify the _query method in the NLSQLTableQueryEngine class.

Here's how you can do it:

def _query(self, query_bundle: QueryBundle) -> Response:
    """Answer a query."""
    table_desc_str = self._get_table_context(query_bundle)
    logger.info(f"> Table desc str: {table_desc_str}")

    response_str = self._service_context.llm_predictor.predict(
        self._text_to_sql_prompt,
        query_str=query_bundle.query_str,
        schema=table_desc_str,
        dialect=self._sql_database.dialect,
    )

    sql_query_str = self._parse_response_to_sql(response_str)
    logger.debug(f"> Predicted SQL query: {sql_query_str}")

    # Comment out the line that executes the SQL query
    # raw_response_str, metadata = self._sql_database.run_sql(sql_query_str)
    # metadata["sql_query"] = sql_query_str

    # Return the SQL query string instead of executing it
    response = Response(response=sql_query_str, metadata={})
    return response

This modification will make the query_engine.query() function return the final SQL statement instead of executing it. Please note that this change will affect all uses of the query_engine.query() function in the NLSQLTableQueryEngine class. If you want to maintain the original functionality in some cases, you might need to add a parameter to the function to control this behavior.

You can find the original _query method in the NLSQLTableQueryEngine class in the sql_query.py file.

Please let me know if you have any other questions or need further clarification.

Sources

• llama_index/indices/struct_store/sql_query.py

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[vp999]

Shouldn’t there a be a feature that allows post processing every sql query before executing it on database ? This will help to add any custom logic like data authorisation, additional changes specific to sql dialect..

[logan-markewich]

@vp999 yea the bot is wrong

Simply set synthesize_response=False in the kwargs

NLSQLTableQueryEngine(..., synthesize_response=False)

source: https://github.com/jerryjliu/llama_index/blob/8611c2f0f2a53e4d46f8d76d7be7485077bb206f/llama_index/indices/struct_store/sql_query.py#L326

[vp999]

synthesize_response=False doesnt help .. it still executes the sql ..

[logan-markewich]

Oh whoops, my brain went to another thought (stopping the natural language generation)

Turns out the bot was smarter than me. There's no option for it currently. Would be welcome to having a PR though!

[logan-markewich]

The sql will always get ran currently
https://github.com/jerryjliu/llama_index/blob/8611c2f0f2a53e4d46f8d76d7be7485077bb206f/llama_index/indices/struct_store/sql_query.py#L280

[vp999]

Yes .. thats where we need a hook for modifying the sql before executing ..

[bkakadiya]

Pull request #9422

@Disiok / @azurewtl : I would appreciate if you can review, try and merge it.

Thanks,
Bakul