Problem with sql_chain and quotation marks #3305
Comments
|
Here is the code to reproduce. It is taken from https://github.com/pinecone-io/examples/blob/master/generation/langchain/handbook/06-langchain-agents.ipynb from getpass import getpass
OPENAI_API_KEY = getpass()
##
from langchain import OpenAI
llm = OpenAI(
openai_api_key=OPENAI_API_KEY,
temperature=0
)
##
from langchain.callbacks import get_openai_callback
def count_tokens(agent, query):
with get_openai_callback() as cb:
result = agent(query)
print(f'Spent a total of {cb.total_tokens} tokens')
return result
##
from sqlalchemy import MetaData
metadata_obj = MetaData()
##
from sqlalchemy import Column, Integer, String, Table, Date, Float
stocks = Table(
"stocks",
metadata_obj,
Column("obs_id", Integer, primary_key=True),
Column("stock_ticker", String(4), nullable=False),
Column("price", Float, nullable=False),
Column("date", Date, nullable=False),
)
##
from sqlalchemy import create_engine
engine = create_engine("sqlite:///:memory:")
metadata_obj.create_all(engine)
##
from datetime import datetime
observations = [
[1, 'ABC', 200, datetime(2023, 1, 1)],
[2, 'ABC', 208, datetime(2023, 1, 2)],
[3, 'ABC', 232, datetime(2023, 1, 3)],
[4, 'ABC', 225, datetime(2023, 1, 4)],
[5, 'ABC', 226, datetime(2023, 1, 5)],
[6, 'XYZ', 810, datetime(2023, 1, 1)],
[7, 'XYZ', 803, datetime(2023, 1, 2)],
[8, 'XYZ', 798, datetime(2023, 1, 3)],
[9, 'XYZ', 795, datetime(2023, 1, 4)],
[10, 'XYZ', 791, datetime(2023, 1, 5)],
]
##
from sqlalchemy import insert
def insert_obs(obs):
stmt = insert(stocks).values(
obs_id=obs[0],
stock_ticker=obs[1],
price=obs[2],
date=obs[3]
)
with engine.begin() as conn:
conn.execute(stmt)
##
for obs in observations:
insert_obs(obs)
##
from langchain.sql_database import SQLDatabase
from langchain.chains import SQLDatabaseChain
db = SQLDatabase(engine)
sql_chain = SQLDatabaseChain(llm=llm, database=db, verbose=True)
##
from langchain.agents import Tool
sql_tool = Tool(
name='Stock DB',
func=sql_chain.run,
description="Useful for when you need to answer questions about stocks " \
"and their prices."
)
##
from langchain.agents import load_tools
tools = load_tools(
["llm-math"],
llm=llm
)
##
tools.append(sql_tool)
##
from langchain.agents import initialize_agent
zero_shot_agent = initialize_agent(
agent="zero-shot-react-description",
tools=tools,
llm=llm,
verbose=True,
max_iterations=3,
)
##
result = count_tokens(
zero_shot_agent,
"What is the multiplication of the ratio between stock prices for 'ABC' and 'XYZ' in January 3rd and the ratio between the same stock prices in January the 4th?"
)The error message is: |
|
IMO after this line: We should add a normalization that:
|
|
Started a PR: #3385 |
|
For what its worth adding this into the default prompt template for a database chain worked |
|
Duplicate of #2027 |
|
I'm not sure, but to me the problem is that the prompt template is wrong in asking for an SQL-statement with quotes. I think it is better to just not do that: |
hwchase17
pushed a commit
that referenced
this issue
May 13, 2023
fixes a syntax error mentioned in #2027 and #3305 another PR to remedy is in #3385, but I believe that is not tacking the core problem. Also #2027 mentions a solution that works: add to the prompt: 'The SQL query should be outputted plainly, do not surround it in quotes or anything else.' To me it seems strange to first ask for: SQLQuery: "SQL Query to run" and then to tell the LLM not to put the quotes around it. Other templates (than the sql one) do not use quotes in their steps. This PR changes that to: SQLQuery: SQL Query to run
|
Hi, @PhilipMay! I'm Dosu, and I'm helping the LangChain team manage their backlog. I wanted to let you know that we are marking this issue as stale. From what I understand, the issue is with the Before we close this issue, we wanted to check if it is still relevant to the latest version of the LangChain repository. If it is, please let us know by commenting on the issue. Otherwise, feel free to close the issue yourself, or it will be automatically closed in 7 days. Thank you for your contribution and understanding! |
dosubot
bot
added
the
stale
dosubot
bot
removed
the
stale
|
Facing same issue. Also, Mixed case columns is a problem on OracleDB |
14 tasks
14 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi,
I observed an issue with sql_chain and quotation marks.
The SQL that was send had quotation marks around and triggered an error in the DB.
This is the DB engine:
The solution is very simple. Just detect and remove quotation marks from
the beginning and the end of the generated SQL statement.
What do you think?
PS:
I can not replicate the error at the moment. So can not not provide any concrete error message. Sorry.PPS: see code to reproduce and error message below
The text was updated successfully, but these errors were encountered: