Text-2-Sql Llama3

[quartermaine]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

# chain.py code

# Import necessary modules
import os
import re
from langchain.memory import ConversationBufferMemory
from langchain_community.llms import LlamaCpp
from langchain_community.utilities import SQLDatabase
from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder
from langchain_core.pydantic_v1 import BaseModel
from langchain_core.runnables import RunnableLambda, RunnablePassthrough, RunnableParallel, RunnableMap
from langchain_openai import AzureChatOpenAI

# Define model path and name
models_path = f'C:\\Users\\<my_username>\\Desktop\\SQL_LLAMACPP\\my-app\\packages\\sql-llamacpp\\model'
model_name_phi = 'Meta-Llama-3-8B.Q2_K.gguf'

# Instantiate LlamaCpp
llm_cpp = LlamaCpp(
    model_path=f"{models_path}\\{model_name_phi}",
    n_threds=4,
    n_ctx=12048,
    max_tokens=500,
    top_p=1,
    f16_kv=True,
    verbose=True,
)

# Define PostgreSQL connection string and initialize SQLDatabase
CONNECTION_STRING = f"postgresql+psycopg2://user:password@127.0.0.1:5437/LLMs"
db = SQLDatabase.from_uri(CONNECTION_STRING,
                          schema="public",
                          view_support=True
                          )

# Define functions to interact with the database
def get_schema(_):
    return db.get_table_info()

def get_query(query):
    sql_query = query.replace("SQLQuery: ", "")
    return sql_query

def run_query(query):
    return db.run(query)

# Define prompt templates
sql_template = """
    You are a Postgres expert. Given an input question, first create a 
    syntactically correct Postgres query to run, then look at the results 
    of the query and return the answer to the input question.
    Unless the user specifies in the question a specific number of 
    examples to obtain, query for at most 5 results using the LIMIT clause 
    as per Postgres. You can order the results to return the most 
    informative data in the database.
    Never query for all columns from a table. You must query only the 
    columns that are needed to answer the question. Wrap each column name 
    in double quotes (") to denote them as delimited identifiers.
    Pay attention to use only the column names you can see in the tables 
    below. You can never make a query using columns that do not exist. Also, 
    make sure to which column is in which table.
    Pay attention to use date('now') function to get the current date, if the question involves "today".
    If you can't find an answer return a query with a polite message.
    Ensure the query follows rules:
    - No INSERT, UPDATE, DELETE instructions.
    - No CREATE, ALTER, DROP instructions.
    - Only SELECT queries for data retrieval.
    Use the following exact format:
    Question: <Question here>
    SQLQuery: <SQL Query to run>
    SQLResult: <Result of the SQLQuery>
    Answer: <Final answer here>
    Only use the following tables and columns:
    {dbschema}
"""  # noqa: E501

final_template = """
    Based on the table schema below, question, sql query, and sql response, 
    write a natural language response:
    Schema: {dbschema}
    Question: {question}
    SQLQuery: {query}
    SQLResponse: {response}
"""  # noqa: E501

# Define conversation memory
memory = ConversationBufferMemory(return_messages=True)

# Define prompt chains
sql_chain = (
    RunnablePassthrough.assign(
        dbschema=get_schema,
        history=RunnableLambda(lambda x: memory.load_memory_variables(x)["history"]),
    )
    | ChatPromptTemplate.from_messages(
        [
            ("system", sql_template),
            MessagesPlaceholder(variable_name="history"),
            ("human", "{question}"),
        ]
    )
    | llm_cpp.bind(stop=["\nSQLResult:"])
    | StrOutputParser()
)

prompt_response = ChatPromptTemplate.from_messages(
    [
        ("system", final_template),
        ("human", "{question}")
    ]
)

sql_response_memory = RunnablePassthrough.assign(output=sql_chain) | save

class InputType(BaseModel):
    question: str

chain = (
    RunnablePassthrough.assign(
        query=sql_response_memory
    ).with_types(
        input_type=InputType
    )
    | RunnablePassthrough.assign(
        dbschema=get_schema,
        response=RunnableLambda(lambda x: db.run(get_query(x["query"]))),
    )
    | prompt_response
    | llm_cpp
)

Description

I'm attempting to utilize the template provided in the Langchain repository for text-to-SQL retrieval using Llama3. Here's the link to the template: Langchain SQL LlamaCPP Template.

But I am getting the following error

| sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "20" | LINE 1: 20,5NUT" | ^ | | [SQL: 20,5NUT"] | (Background on this error at: https://sqlalche.me/e/20/f405)

System Info

output of my pip freeze in my anaconda environment

aiohttp==3.9.4
aiosignal==1.3.1
annotated-types==0.6.0
anyio==4.3.0
attrs==23.2.0
build==0.10.0
CacheControl==0.13.1
cachetools==5.3.3
certifi==2024.2.2
charset-normalizer==3.3.2
cleo==2.1.0
click==8.1.7
cohere==5.3.2
colorama==0.4.6
crashtest==0.4.1
dataclasses-json==0.6.4
diskcache==5.6.3
distlib==0.3.8
distro==1.9.0
dulwich==0.21.7
fastapi==0.110.1
fastavro==1.9.4
filelock==3.13.4
frozenlist==1.4.1
fsspec==2024.3.1
gitdb==4.0.11
GitPython==3.1.43
google-auth==2.29.0
gpt4all==2.5.1
greenlet==3.0.3
h11==0.14.0
httpcore==1.0.5
httpx==0.27.0
httpx-sse==0.4.0
huggingface-hub==0.22.2
idna==3.7
installer==0.7.0
jaraco.classes==3.4.0
Jinja2==3.1.3
joblib==1.4.0
jsonpatch==1.33
jsonpointer==2.4
jsonschema==4.17.3
keyring==24.3.1
langchain==0.1.16
langchain-cli==0.0.21
langchain-community==0.0.32
langchain-core==0.1.42
langchain-openai==0.1.3
langchain-text-splitters==0.0.1
langserve==0.0.51
langsmith==0.1.47
llama_cpp_python==0.2.61
llmlingua==0.2.2
llmx==0.0.21a0
markdown-it-py==3.0.0
MarkupSafe==2.1.5
marshmallow==3.21.1
mdurl==0.1.2
more-itertools==10.2.0
mpmath==1.3.0
msgpack==1.0.8
multidict==6.0.5
mypy-extensions==1.0.0
networkx==3.3
nltk==3.8.1
numpy==1.26.4
ollama==0.1.9
openai==1.17.1
orjson==3.10.0
packaging==23.2
pexpect==4.9.0
pkginfo==1.10.0
platformdirs==3.11.0
poetry==1.6.1
poetry-core==1.7.0
poetry-plugin-export==1.6.0
psutil==5.9.8
psycopg2==2.9.9
ptyprocess==0.7.0
pyasn1==0.6.0
pyasn1_modules==0.4.0
pycryptodome==3.20.0
pydantic==1.10.15
pydantic_core==2.18.1
Pygments==2.17.2
pyproject_hooks==1.0.0
pyrsistent==0.20.0
pywin32-ctypes==0.2.2
PyYAML==6.0.1
rapidfuzz==3.8.1
regex==2023.12.25
requests==2.31.0
requests-toolbelt==1.0.0
rich==13.7.1
rsa==4.9
safetensors==0.4.2
setuptools==68.2.2
shellingham==1.5.4
smmap==5.0.1
sniffio==1.3.1
# Editable install with no version control (sql-llamacpp==0.1.0)
-e c:\users\andreas.christopoulo\desktop\sql_llamacpp\my-app\packages\sql-llamacpp
-e git+https://incadeadev@dev.azure.com/incadeadev/ProductDev/_git/BIAI-playground@54854f2594d1a796265036f7785ec4de0a7d6171#egg=sql_pgvector&subdirectory=artificial intelligence\llms\langserve\my-app\packages\sql-pgvector
SQLAlchemy==2.0.29
sse-starlette==1.8.2
starlette==0.37.2
sympy==1.12
tenacity==8.2.3
tiktoken==0.5.2
tokenizers==0.15.2
tomlkit==0.12.4
torch==2.2.2
tqdm==4.66.2
transformers==4.39.3
trove-classifiers==2024.4.10
typer==0.9.4
types-requests==2.31.0.20240406
typing-inspect==0.9.0
typing_extensions==4.11.0
urllib3==2.2.1
uvicorn==0.23.2
virtualenv==20.25.1
wheel==0.41.2
yarl==1.9.4```

[dosubot[bot]]

The error you're encountering, indicated by syntax error at or near "20", suggests there's an issue with the formatting of numeric values or arrays in your SQL query. This is often related to how arrays of numbers are formatted for specific SQL dialects. For PostgreSQL, arrays should be formatted with curly braces {} rather than square brackets [].

For instance, if you're working with embedding vectors or any array-like data in your SQL commands, you should ensure they're formatted correctly. A common mistake in LangChain's SQL generation could be formatting arrays as:

vecs_str = "[" + ",".join(map(str, vecs)) + "]"

For PostgreSQL, the correct format would be:

vecs_str = "{" + ",".join(map(str, vecs)) + "}"

Adjusting the array formatting to match your SQL dialect's requirements is crucial. If you're using a different database system, you'll need to adapt the formatting accordingly.

For debugging, consider using LangChain's functionalities like set_debug, set_verbose, or initializing chains with verbose=True. These tools can provide detailed logs that are invaluable for diagnosing and fixing issues like the one you're facing.

If the problem persists, please share more details about the SQL query being generated and passed to the run_query function. This will help in pinpointing the exact cause of the syntax error and providing a more targeted solution.

Details

Sources

• libs/experimental/langchain_experimental/sql/vector_sql.py
• libs/experimental/langchain_experimental/sql/prompt.py
• libs/langchain/langchain/chains/sql_database/prompt.py
• docs/docs/guides/debugging.md
• docs/docs/guides/development/debugging.md
• templates/sql-llama2/sql_llama2/chain.py
• templates/sql-research-assistant/sql_research_assistant/search/sql.py
• templates/sql-ollama/sql_ollama/chain.py
• templates/sql-pgvector/sql_pgvector/chain.py
• libs/langchain/langchain/indexes/_sql_record_manager.py

About Dosu
This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

[quartermaine]

@dosubot Where should I apply the array formatting?

[dosubot[bot]]

To address the array formatting issue in your SQL queries, especially when working with PostgreSQL through SQLAlchemy, you should use the ARRAY constructor from SQLAlchemy's PostgreSQL dialect. This ensures that arrays are formatted correctly for PostgreSQL, which expects curly braces {}.

Here's how to format an array in a query correctly:

from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy import select, table, column

# Example for a table 'my_table' with an array column 'my_array_column'
my_table = table('my_table', column('my_array_column', ARRAY(Integer)))

# Constructing a query that uses an array
query = select([my_table]).where(my_table.c.my_array_column.contains([20]))

# Executing the query
result = db.run(query)

This example demonstrates using the ARRAY type and .contains() method for array operations in SQLAlchemy, ensuring compatibility with PostgreSQL's array formatting requirements.

For further details, refer to the SQLAlchemy documentation on PostgreSQL-specific data types and the PostgreSQL documentation on arrays.

If you encounter syntax errors after applying these changes, please share the specific SQL queries causing the issues for a more targeted solution.

Details

Sources

• libs/community/langchain_community/utilities/sql_database.py
• templates/sql-research-assistant/sql_research_assistant/search/sql.py
• templates/sql-llama2/sql_llama2/chain.py
• libs/community/langchain_community/agent_toolkits/sql/toolkit.py
• libs/community/langchain_community/tools/sql_database/tool.py
• libs/experimental/langchain_experimental/sql/base.py
• libs/experimental/langchain_experimental/sql/prompt.py
• templates/sql-pgvector/sql_pgvector/chain.py
• docs/docs/guides/debugging.md
• docs/docs/how_to/debugging.ipynb
• libs/community/langchain_community/chat_message_histories/postgres.py

About Dosu
This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

[mandeep-singh-lab49]

@quartermaine Did you find solution for your question ?

[quartermaine]

@mandeep-singh-lab49 I used Ollama as a solution and could execute the script