How to modify the intermediate output

[buttaRahul]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from langchain_ollama import ChatOllama
llm = ChatOllama(
    model="deepseek-r1:8b",
    temperature=0,
)
from langchain_community.utilities.sql_database import SQLDatabase



db_name = "moviesdb"

db = SQLDatabase.from_uri(f"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}",sample_rows_in_table_info=3)
print(db.table_info)

from langchain_experimental.sql import SQLDatabaseChain

db_chain = SQLDatabaseChain.from_llm(
    llm, 
    db, 
    verbose=False, 
    return_direct=True, 
    # return_intermediate_steps=True
) 

db_chain.invoke("fetch movie titles from Hollywood released after 2008 with an IMDb rating greater than 8.")



ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '**\n\nSELECT `title`\nFROM `movies`\nWHERE `release_year` > 2008 \n  AND `imdb_' at line 1")
[SQL: **
sql
SELECT `title`
FROM `movies`
WHERE `release_year` > 2008 
  AND `imdb_rating` > 8 
  AND `industry` = 'Hollywood'
LIMIT 5;

**]
(Background on this error at: https://sqlalche.me/e/20/f405)

### Description

****

Since the llm generates some un-necessary markdown like ```sql it is being passed on to the the sql engine of SQLDatabaseChain which results in the syntax error, is there any way to clean / modify the intermediate output before running the sql query


complete error: 
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\base.py:1964, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1963     if not evt_handled:
-> 1964         self.dialect.do_execute(
   1965             cursor, str_statement, effective_parameters, context
   1966         )
   1968 if self._has_events or self.engine._has_events:

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\default.py:942, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    941 def do_execute(self, cursor, statement, parameters, context=None):
--> 942     cursor.execute(statement, parameters)

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\cursors.py:153, in Cursor.execute(self, query, args)
    151 query = self.mogrify(query, args)
--> 153 result = self._query(query)
    154 self._executed = query

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\cursors.py:322, in Cursor._query(self, q)
    321 self._clear_result()
--> 322 conn.query(q)
    323 self._do_get_result()

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py:563, in Connection.query(self, sql, unbuffered)
    562 self._execute_command(COMMAND.COM_QUERY, sql)
--> 563 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    564 return self._affected_rows

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py:825, in Connection._read_query_result(self, unbuffered)
    824     result = MySQLResult(self)
--> 825     result.read()
    826 self._result = result

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py:1199, in MySQLResult.read(self)
   1198 try:
-> 1199     first_packet = self.connection._read_packet()
   1201     if first_packet.is_ok_packet():

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py:775, in Connection._read_packet(self, packet_type)
    774         self._result.unbuffered_active = False
--> 775     packet.raise_for_error()
    776 return packet

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\protocol.py:219, in MysqlPacket.raise_for_error(self)
    218     print("errno =", errno)
--> 219 err.raise_mysql_exception(self._data)

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\err.py:150, in raise_mysql_exception(data)
    149     errorclass = InternalError if errno < 1000 else OperationalError
--> 150 raise errorclass(errno, errval)

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '**\n```sql\nSELECT `title`\nFROM `movies`\nWHERE `release_year` > 2008 \n  AND `imdb_' at line 1")

The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
Cell In[5], line 1
----> 1 db_chain.invoke("Write a SQL query to fetch movie titles from Hollywood released after 2008 with an IMDb rating greater than 8.")

File ~\AppData\Roaming\Python\Python311\site-packages\langchain\chains\base.py:170, in Chain.invoke(self, input, config, **kwargs)
    168 except BaseException as e:
    169     run_manager.on_chain_error(e)
--> 170     raise e
    171 run_manager.on_chain_end(outputs)
    173 if include_run_info:

File ~\AppData\Roaming\Python\Python311\site-packages\langchain\chains\base.py:160, in Chain.invoke(self, input, config, **kwargs)
    157 try:
    158     self._validate_inputs(inputs)
    159     outputs = (
--> 160         self._call(inputs, run_manager=run_manager)
    161         if new_arg_supported
    162         else self._call(inputs)
    163     )
    165     final_outputs: Dict[str, Any] = self.prep_outputs(
    166         inputs, outputs, return_only_outputs
    167     )
    168 except BaseException as e:

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\langchain_experimental\sql\base.py:204, in SQLDatabaseChain._call(self, inputs, run_manager)
    200 except Exception as exc:
    201     # Append intermediate steps to exception, to aid in logging and later
    202     # improvement of few shot prompt seeds
    203     exc.intermediate_steps = intermediate_steps  # type: ignore
--> 204     raise exc

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\langchain_experimental\sql\base.py:149, in SQLDatabaseChain._call(self, inputs, run_manager)
    147     if SQL_RESULT in sql_cmd:
    148         sql_cmd = sql_cmd.split(SQL_RESULT)[0].strip()
--> 149     result = self.database.run(sql_cmd)
    150     intermediate_steps.append(str(result))  # output: sql exec
    151 else:

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\langchain_community\utilities\sql_database.py:502, in SQLDatabase.run(self, command, fetch, include_columns, parameters, execution_options)
    488 def run(
    489     self,
    490     command: Union[str, Executable],
   (...)
    495     execution_options: Optional[Dict[str, Any]] = None,
    496 ) -> Union[str, Sequence[Dict[str, Any]], Result[Any]]:
    497     """Execute a SQL command and return a string representing the results.
    498 
    499     If the statement returns rows, a string of the results is returned.
    500     If the statement returns no rows, an empty string is returned.
    501     """
--> 502     result = self._execute(
    503         command, fetch, parameters=parameters, execution_options=execution_options
    504     )
    506     if fetch == "cursor":
    507         return result

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\langchain_community\utilities\sql_database.py:467, in SQLDatabase._execute(self, command, fetch, parameters, execution_options)
    465 else:
    466     raise TypeError(f"Query expression has unknown type: {type(command)}")
--> 467 cursor = connection.execute(
    468     command,
    469     parameters,
    470     execution_options=execution_options,
    471 )
    473 if cursor.returns_rows:
    474     if fetch == "all":

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\base.py:1416, in Connection.execute(self, statement, parameters, execution_options)
   1414     raise exc.ObjectNotExecutableError(statement) from err
   1415 else:
-> 1416     return meth(
   1417         self,
   1418         distilled_parameters,
   1419         execution_options or NO_OPTIONS,
   1420     )

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\sql\elements.py:515, in ClauseElement._execute_on_connection(self, connection, distilled_params, execution_options)
    513     if TYPE_CHECKING:
    514         assert isinstance(self, Executable)
--> 515     return connection._execute_clauseelement(
    516         self, distilled_params, execution_options
    517     )
    518 else:
    519     raise exc.ObjectNotExecutableError(self)

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\base.py:1638, in Connection._execute_clauseelement(self, elem, distilled_parameters, execution_options)
   1626 compiled_cache: Optional[CompiledCacheType] = execution_options.get(
   1627     "compiled_cache", self.engine._compiled_cache
   1628 )
   1630 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1631     dialect=dialect,
   1632     compiled_cache=compiled_cache,
   (...)
   1636     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1637 )
-> 1638 ret = self._execute_context(
   1639     dialect,
   1640     dialect.execution_ctx_cls._init_compiled,
   1641     compiled_sql,
   1642     distilled_parameters,
   1643     execution_options,
   1644     compiled_sql,
   1645     distilled_parameters,
   1646     elem,
   1647     extracted_params,
   1648     cache_hit=cache_hit,
   1649 )
   1650 if has_events:
   1651     self.dispatch.after_execute(
   1652         self,
   1653         elem,
   (...)
   1657         ret,
   1658     )

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\base.py:1843, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1841     return self._exec_insertmany_context(dialect, context)
   1842 else:
-> 1843     return self._exec_single_context(
   1844         dialect, context, statement, parameters
   1845     )

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\base.py:1983, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1980     result = context._setup_result_proxy()
   1982 except BaseException as e:
-> 1983     self._handle_dbapi_exception(
   1984         e, str_statement, effective_parameters, cursor, context
   1985     )
   1987 return result

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\base.py:2352, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context, is_sub_exec)
   2350 elif should_wrap:
   2351     assert sqlalchemy_exception is not None
-> 2352     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2353 else:
   2354     assert exc_info[1] is not None

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\base.py:1964, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1962                 break
   1963     if not evt_handled:
-> 1964         self.dialect.do_execute(
   1965             cursor, str_statement, effective_parameters, context
   1966         )
   1968 if self._has_events or self.engine._has_events:
   1969     self.dispatch.after_cursor_execute(
   1970         self,
   1971         cursor,
   (...)
   1975         context.executemany,
   1976     )

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\default.py:942, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    941 def do_execute(self, cursor, statement, parameters, context=None):
--> 942     cursor.execute(statement, parameters)

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\cursors.py:153, in Cursor.execute(self, query, args)
    149     pass
    151 query = self.mogrify(query, args)
--> 153 result = self._query(query)
    154 self._executed = query
    155 return result

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\cursors.py:322, in Cursor._query(self, q)
    320 conn = self._get_db()
    321 self._clear_result()
--> 322 conn.query(q)
    323 self._do_get_result()
    324 return self.rowcount

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py:563, in Connection.query(self, sql, unbuffered)
    561     sql = sql.encode(self.encoding, "surrogateescape")
    562 self._execute_command(COMMAND.COM_QUERY, sql)
--> 563 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    564 return self._affected_rows

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py:825, in Connection._read_query_result(self, unbuffered)
    823 else:
    824     result = MySQLResult(self)
--> 825     result.read()
    826 self._result = result
    827 if result.server_status is not None:

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py:1199, in MySQLResult.read(self)
   1197 def read(self):
   1198     try:
-> 1199         first_packet = self.connection._read_packet()
   1201         if first_packet.is_ok_packet():
   1202             self._read_ok_packet(first_packet)

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py:775, in Connection._read_packet(self, packet_type)
    773     if self._result is not None and self._result.unbuffered_active is True:
    774         self._result.unbuffered_active = False
--> 775     packet.raise_for_error()
    776 return packet

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\protocol.py:219, in MysqlPacket.raise_for_error(self)
    217 if DEBUG:
    218     print("errno =", errno)
--> 219 err.raise_mysql_exception(self._data)

File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\err.py:150, in raise_mysql_exception(data)
    148 if errorclass is None:
    149     errorclass = InternalError if errno < 1000 else OperationalError
--> 150 raise errorclass(errno, errval)

ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '**\n```sql\nSELECT `title`\nFROM `movies`\nWHERE `release_year` > 2008 \n  AND `imdb_' at line 1")
[SQL: **
```sql
SELECT `title`
FROM `movies`
WHERE `release_year` > 2008 
  AND `imdb_rating` > 8 
  AND `industry` = 'Hollywood'
LIMIT 5;

**]
(Background on this error at: https://sqlalche.me/e/20/f405)

System Info

System Information

OS: Windows
OS Version: 10.0.26100
Python Version: 3.11.7 (tags/v3.11.7:fa7a6f2, Dec 4 2023, 19:24:49) [MSC v.1937 64 bit (AMD64)]

Package Information

langchain_core: 0.3.33
langchain: 0.3.15
langchain_community: 0.3.13
langsmith: 0.2.4
langchain_chroma: 0.2.0
langchain_deepseek: 0.0.3
langchain_experimental: 0.3.4
langchain_google_genai: 2.0.7
langchain_ollama: 0.2.3
langchain_openai: 0.3.3
langchain_tests: 0.3.10
langchain_text_splitters: 0.3.4

Optional packages not installed

langserve

Other Dependencies

aiohttp: 3.9.3
async-timeout: Installed. No version info available.
chromadb: 0.5.3
dataclasses-json: 0.6.7
fastapi: 0.110.2
filetype: 1.2.0
google-generativeai: 0.8.3
httpx: 0.27.0
httpx-sse: 0.4.0
jsonpatch: 1.33
langsmith-pyo3: Installed. No version info available.
numpy: 1.26.4
ollama: 0.4.7
openai: 1.58.1
orjson: 3.10.12
packaging: 23.2
pydantic: 2.9.2
pydantic-settings: 2.7.0
pytest: 7.4.4
pytest-asyncio: 0.23.8
pytest-socket: 0.7.0
pytest-watcher: 0.3.5
PyYAML: 6.0.1
requests: 2.31.0
requests-toolbelt: 1.0.0
SQLAlchemy: 2.0.37
syrupy: 4.8.1
tenacity: 9.0.0
tiktoken: 0.8.0
typing-extensions: 4.12.2