LangChain fixed the SQL for me
Tags: ai, python, sql, beginners
Published: 2023-10-28 19:24:10

This article is a <u>simple</u> quick starter (what I did was) with SqlDatabaseChain.

Hope this ignites some interest.

Many thanks to:

sqlalchemy-iris author [@Dmitry Maslennikov](https://community.intersystems.com/user/dmitry-maslennikov-5){.mentions.mentions-11906}

Your project made this possible today.

 

The article script uses openai API so caution not to share table information and records externally, that you didn't intend to.

A local model could be plugged in , instead if needed.

 

## Creating a new virtual environment

<pre>mkdir chainsql

cd chainsql

python -m venv .

scripts\activate

pip install langchain

pip install wget

# Need to connect to IRIS so installing a fresh python driver
python -c "import wget;url='https://raw.githubusercontent.com/intersystems-community/iris-driver-distribution/main/DB-API/intersystems_irispython-3.2.0-py3-none-any.whl';wget.download(url)"

# And for more magic
pip install sqlalchemy-iris

pip install openai

set OPENAI_API_KEY=[ Your OpenAI Key ]

python</pre>

 

## Initial Test

<pre>from langchain import OpenAI, SQLDatabase, SQLDatabaseChain

db = SQLDatabase.from_uri("iris://superuser:******@localhost:51775/USER")

llm = OpenAI(temperature=0, verbose=True)

db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)

db_chain.run("How many Tables are there")</pre>

### Result error

<pre>sqlalchemy.exc.DatabaseError: (intersystems_iris.dbapi._DBAPI.DatabaseError) [SQLCODE: <-25>:<Input encountered after end of query />]
[Location: <Prepare>]
[%msg: < Input (;) encountered after end of query^SELECT COUNT ( * ) FROM information_schema . tables WHERE table_schema = :%qpar(1) ;>]
[SQL: SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
←[32;1m←[1;3mSELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';←[0m>>></pre>

## Inter-developer dialogue

IRIS didn't like being given SQL queries that end with a semicolon.

What to do now? ?

Idea: How about I tell LangChain to fix it for me

Cool. Lets do this !!

 

## Test Two

<pre>from langchain import OpenAI, SQLDatabase, SQLDatabaseChain

from langchain.prompts.prompt import PromptTemplate

_DEFAULT_TEMPLATE = """Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.

Use the following format:

Question: "Question here"
SQLQuery: "SQL Query to run"
SQLResult: "Result of the SQLQuery"
Answer: "Final answer here"

<strong><span style="color:#c0392b;">The SQL query should NOT end with semi-colon</span></strong>
Question: {input}"""

PROMPT = PromptTemplate(
     input_variables=["input", "dialect"], template=_DEFAULT_TEMPLATE
)

db = SQLDatabase.from_uri("iris://superuser:******@localhost:51775/USER") llm = OpenAI(temperature=0, verbose=True)

llm = OpenAI(temperature=0, verbose=True)

db_chain = SQLDatabaseChain(llm=llm, database=db, prompt=PROMPT, verbose=True) 

db_chain.run("How many Tables are there")</pre>

 

Result Two

<pre>SQLQuery:←[32;1m←[1;3mSELECT COUNT(*) FROM information_schema.tables←[0m
SQLResult: ←[33;1m←[1;3m[(499,)]←[0m
Answer:←[32;1m←[1;3mThere are 499 tables.←[0m
←[1m> Finished chain.←[0m
'There are 499 tables.'</pre>

I said it would be quick.

 

References:

https://walkingtree.tech/natural-language-to-query-your-sql-database-using-langchain-powered-by-llms/

https://python.langchain.com/en/latest/modules/chains/examples/sqlite.html#sqldatabasesequentialchain

https://python.langchain.com/en/latest/modules/agents/plan\_and\_execute.html