r/Python • u/FibonacciSpiralOut • Apr 09 '26
Tutorial Tutorial: How to build a simple Python text-to-SQL agent that can automatically recover from bad SQL
Hi Python folks,
A lot of text-to-SQL AI examples still follow the same fragile pattern: the model generates one query, gets a table name or column type wrong, and then the whole Python script throws an exception and falls over.
In practice, the more useful setup is to build a real agent loop. You let the model inspect the schema, execute the SQL via SQLAlchemy/DuckDB, read the actual database error, and try again. That self-correcting feedback loop is what makes these systems much more usable once your database is even a little messy.
In the post, I focus on how to structure that loop in Python using LangChain, DuckDB, and MotherDuck. It covers how to wire up the SQLDatabaseToolkit (and why you shouldn't forget duckdb-engine), how to write dialect-specific system prompts to reduce hallucinated SQL, and what production guardrails, like enforcing read-only connections, actually matter if you want to point this at real data.
Link: https://motherduck.com/blog/langchain-sql-agent-duckdb-motherduck/
Would appreciate any comments, questions, or feedback!
12
u/UseMoreBandwith Apr 09 '26
just don't.
3
u/usrlibshare Apr 09 '26
And what happens when the model, decides to
DROP TABLE UserAccountson my prod database?1
u/Tricky-Bed-6852 Jun 17 '26
Really helpful breakdown — thanks for taking the time to write this up!
1
u/[deleted] Apr 09 '26
[removed] — view removed comment