{"id":598,"date":"2025-01-11T15:28:59","date_gmt":"2025-01-11T15:28:59","guid":{"rendered":"https:\/\/smolagents.org\/?post_type=docs&p=598"},"modified":"2025-01-11T15:40:54","modified_gmt":"2025-01-11T15:40:54","password":"","slug":"text-to-sql-example","status":"publish","type":"docs","link":"https:\/\/smolagents.org\/hi\/docs\/text-to-sql-example\/","title":{"rendered":"Text-to-SQL Example"},"content":{"rendered":"

In this tutorial, we\u2019ll see how to implement an agent that leverages SQL using smolagents<\/code>.<\/p>\n\n\n\n

\n

Let\u2019s start with the golden question: why not keep it simple and use a standard text-to-SQL pipeline?<\/p>\n<\/blockquote>\n\n\n\n

A standard text-to-sql pipeline is brittle, since the generated SQL query can be incorrect. Even worse, the query could be incorrect, but not raise an error, instead giving some incorrect\/useless outputs without raising an alarm.<\/p>\n\n\n\n

\ud83d\udc49 Instead, an agent system is able to critically inspect outputs and decide if the query needs to be changed or not, thus giving it a huge performance boost.<\/p>\n\n\n\n

Let\u2019s build this agent! \ud83d\udcaa<\/p>\n\n\n\n

First, we setup the SQL environment:<\/p>\n\n\n\n

Copied<\/p>\n\n\n\n

from sqlalchemy import (\n    create_engine,\n    MetaData,\n    Table,\n    Column,\n    String,\n    Integer,\n    Float,\n    insert,\n    inspect,\n    text,\n)\n\nengine = create_engine(\"sqlite:\/\/\/:memory:\")\nmetadata_obj = MetaData()\n\n# create city SQL table<\/em>\ntable_name = \"receipts\"\nreceipts = Table(\n    table_name,\n    metadata_obj,\n    Column(\"receipt_id\", Integer, primary_key=True),\n    Column(\"customer_name\", String(16), primary_key=True),\n    Column(\"price\", Float),\n    Column(\"tip\", Float),\n)\nmetadata_obj.create_all(engine)\n\nrows = [\n    {\"receipt_id\": 1, \"customer_name\": \"Alan Payne\", \"price\": 12.06, \"tip\": 1.20},\n    {\"receipt_id\": 2, \"customer_name\": \"Alex Mason\", \"price\": 23.86, \"tip\": 0.24},\n    {\"receipt_id\": 3, \"customer_name\": \"Woodrow Wilson\", \"price\": 53.43, \"tip\": 5.43},\n    {\"receipt_id\": 4, \"customer_name\": \"Margaret James\", \"price\": 21.11, \"tip\": 1.00},\n]\nfor row in rows:\n    stmt = insert(receipts).values(**row)\n    with engine.begin() as connection:\n        cursor = connection.execute(stmt)<\/pre>\n\n\n\n
<\/a>Build our agent<\/h3>\n\n\n\n
Now let\u2019s make our SQL table retrievable by a tool.<\/p>\n\n\n\n
The tool\u2019s description attribute will be embedded in the LLM\u2019s prompt by the agent system: it gives the LLM information about how to use the tool. This is where we want to describe the SQL table.<\/p>\n\n\n\n
Copied<\/p>\n\n\n\n
inspector = inspect(engine)\ncolumns_info = [(col[\"name\"], col[\"type\"]) for col in inspector.get_columns(\"receipts\")]\n\ntable_description = \"Columns:\\n\" + \"\\n\".join([f\"  - {name}: {col_type}\" for name, col_type in columns_info])\nprint(table_description)<\/pre>\n\n\n\n
Copied<\/p>\n\n\n\n
Columns:\n  - receipt_id: INTEGER\n  - customer_name: VARCHAR(16)\n  - price: FLOAT\n  - tip: FLOAT<\/pre>\n\n\n\n
Now let\u2019s build our tool. It needs the following: (read\u00a0the tool doc<\/a>\u00a0for more detail)<\/p>\n\n\n\n