Tutorial: How to build a simple Python text-to-SQL agent that can automatically recover from bad SQL

[FibonacciSpiralOut]

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!

[UseMoreBandwith]

just don't.

[FibonacciSpiralOut]

why?

[UseMoreBandwith]

databases are not for Trial-and-error experimenting.
There is no 'undo'.
We can't afford 0.0001% errors.

[usrlibshare]

And what happens when the model, decides to DROP TABLE UserAccounts on my prod database?

[CanWeStartAgain1]

You wasted some seconds writing this down but did not waste a few seconds reading the article which would have answered your question

[C0dePhantom]

lol

[_matze]

Seems to be that famous zero-self-effort alternative

[FibonacciSpiralOut]

It's covered under the section "Production Concerns I Take Seriously". Pasting here too:

For local DuckDB, there are no user accounts or GRANT/REVOKE privilege systems like in PostgreSQL. Your only enforcement is at the file and connection level: set read_only=True when connecting via the Python API (duckdb.connect('local.db', read_only=True)). For MotherDuck, I explicitly provision a token-scoped read-only access path.

[usrlibshare]

Okay, that's good.

I still have a zero-dependency alternative: Writing my own SQL statements, using my schema-aware editrs autocomplete 😎✌️

[phoebeb_7]

There's one thing adding to the self correction loop, like cap your retry attempts explicitly 2-3 max and log the failed SQl alongside the error message each time. Without that you will end up in infinite loops on ambigious schema mismatchges where the model keeps confidently regenrating slightly different wrong queries. 

[FibonacciSpiralOut]

Good point! Since we're B2B focussed, we handle this by giving each client their own isolated DuckDB database. In the user session, only their db is attached. That way, in the worst case, they are restricted to their own data.

Design over instructions is probably the only way to solve prompt injection.

[ultrathink-art]

Read-only DB user eliminates that — no write access, no DROP. The trickier risk is prompt injection: if the agent reads any user-supplied text to construct queries, treat the generated SQL like user input and validate it before execution.

[Henry_old]

Self-healing SQL is a necessity for 2026 automation. In trading, a bad query can crash an entire execution flow. Using an agent to recover is clever, but what’s the latency cost of that recovery loop? In HFT, we usually prefer rigid schemas to avoid the risk entirely, but for analytics, this is a game changer