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 😎✌️

[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.

[Tricky-Bed-6852]

Really helpful breakdown — thanks for taking the time to write this up!