langchain
langchain copied to clipboard
langchain-ai
'datetime' is not a recognized built-in function name. with angen_executor
Checked other resources
- [X] I added a very descriptive title to this issue.
- [X] I searched the LangChain documentation with the integrated search.
- [X] I used the GitHub search to find a similar question and didn't find it.
- [X] I am sure that this is a bug in LangChain rather than my code.
- [X] The bug is not resolved by updating to the latest stable version of LangChain (or the specific integration package).
Example Code
from langchain.sql_database import SQLDatabase from langchain.prompts import ChatPromptTemplate, MessagesPlaceholder,PromptTemplate from langchain.tools import BaseTool from langchain.tools.render import format_tool_to_openai_function from langchain.schema.runnable import Runnable,RunnableLambda,RunnableParallel from langchain.chat_models import ChatOpenAI from langchain.agents.format_scratchpad import format_to_openai_function_messages from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser from langchain.agents import AgentExecutor from pydantic import BaseModel, Field import os from secret_key import openapi_key from sqlalchemy import create_engine import constants from datetime import datetime
os.environ['OPENAI_API_KEY'] = openapi_key SQL_DML_CHAIN_PROMPT = """You are expert in SQLITE. Your main objective is to construct Data manipulation SQLITE query give the user question: {user_question}.
You need to construct the Data manipulation SQLITE query for the following Database Schema: {table_info}
Only Output the final SQL-Query and nothing else.
SQL-Query:"""
prompt = PromptTemplate(template = SQL_DML_CHAIN_PROMPT,input_variables = ['user_question','table_info'])
from urllib.parse import quote_plus server_name = constants.server_name database_name = constants.database_name username = constants.username password = constants.password encoded_password = quote_plus(password) connection_uri = f"mssql+pyodbc://{username}:{encoded_password}@{server_name}/{database_name}?driver=ODBC+Driver+17+for+SQL+Server"
engine = create_engine(connection_uri) model_name = "get-3.5-turbo-16k"
db = SQLDatabase(engine, view_support=True, include_tables=['PAY_transaction_settingallow', 'PAY_mst_allowance','employee_details'],sample_rows_in_table_info=5)
sql_dml_chain = RunnableParallel ({"user_question":lambda x:x["user_question"],
"table_info":lambda _: db.get_table_info()}) |
prompt |
ChatOpenAI().bind(stop='SQL-Query:') |
RunnableLambda(lambda x:x.content.strip().replace('```sql',''))
agent_prompt = ChatPromptTemplate.from_messages(
[
("system", """
You are expert in SQL whose main objective is to mainpulate the Database for which you have
been given access. You can use the tool sql_db_manipulation to interact with Database and
mainpulate the database as per the user requirement.
Wrapped column names: All column names should be wrapped in square brackets [] as delimiters.
Use GETDATE() to get the current date and time instead of DATETIME('now').
For each record processed, retrieve the (employeeEuid) as 'euid' from the employee_details table where the employeeName matches, and retrieve the allowance ID (alw_id) from the pay_mst_allowance table where the alw_desc matches.no changes has to be made in "pay_mst_allowance" table and "employee_details" table.
perform JOIN operation to fetch euid and alw_id from respective tables.
Selected table: Specify PAY_transaction_settingallow as the table to update.
Employee and allowance selection: Use the WHERE clause to filter employees based on employeeName and allowances based on alw_desc.
Date handling: Maintain the logic for createDate, effect_date, and to_date using SQL Server functions.
Currency: Assume the amount to be in rupees.
Removed newlines: Write the query as a single string without newlines (\n).
Ensure the query executes efficiently and without errors.
"""), ("user", "{input}"), MessagesPlaceholder(variable_name="agent_scratchpad"), ] )
class SQLDBMANIPULATION(BaseModel): user_query: str = Field(description='User question which will be translated to a Data Manipulation SQL Query and will be executed on the underlying database')
class SQLDBMANIPULATIONTool(BaseTool): name = "sql_db_manipulation" description = "Use this tool to convert and execute DML queries given the user question" args_schema: type[SQLDBMANIPULATION] = SQLDBMANIPULATION sql_dml_chain: Runnable def _run( self, user_query: str ) -> str: """Use the tool.""" query = sql_dml_chain.invoke({"user_question":user_query}) query = query.replace("DATETIME('now')", "GETDATE()") db._execute(query)
tools = [SQLDBMANIPULATIONTool(sql_dml_chain = sql_dml_chain)] llm_with_tools = ChatOpenAI().bind(functions=[format_tool_to_openai_function(t) for t in tools]) agent = ( { "input": lambda x: x["input"], "agent_scratchpad": lambda x: format_to_openai_function_messages( x["intermediate_steps"] ), } | agent_prompt | llm_with_tools | OpenAIFunctionsAgentOutputParser() )
agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=True)
Error Message and Stack Trace (if applicable)
Entering new AgentExecutor chain...
Invoking: sql_db_manipulation with {'user_query': "INSERT INTO PAY_transaction_settingallow ([euid], [alw_id], [createDate], [effect_date], [to_date], [amount], [currency]) SELECT ed.employeeEuid AS euid, pma.alw_id, GETDATE() AS createDate, '2024-03-01', '2024-03-31', 500, 'INR' FROM employee_details ed JOIN pay_mst_allowance pma ON ed.employeeName = 'jyothi' AND pma.alw_desc = 'Production Incentive'", 'user_question': 'insert Production incentive of 500 For jyothi for march 2024'}
Traceback (most recent call last): File "C:\Users\jyothi\Desktop\test\gpttest\Lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "C:\Users\jyothi\Desktop\test\gpttest\Lib\site-packages\sqlalchemy\engine\default.py", line 922, in do_execute cursor.execute(statement, parameters) pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'datetime' is not a recognized built-in function name. (195) (SQLExecDirectW)")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "c:\Users\jyothi\Desktop\test\modifying.py", line 130, in
Description
I'm using the agentExecutor from langhain to database manipulation to perform curd operation on the table, even after specifically mentioning to use GETDATE() it keep taking datetime('now') while executing the query
i have shared the complete error
while Invoking it write a correct query
Invoking: sql_db_manipulation with {'user_query': "INSERT INTO PAY_transaction_settingallow ([euid], [alw_id], [createDate], [effect_date], [to_date], [amount], [currency]) SELECT ed.employeeEuid AS euid, pma.alw_id, GETDATE() AS createDate, '2024-03-01', '2024-03-31', 500, 'INR' FROM employee_details ed JOIN pay_mst_allowance pma ON ed.employeeName = 'jyothi' AND pma.alw_desc = 'Production Incentive'", 'user_question': 'insert Production incentive of 500 For jyothi for march 2024'}
but while executing its taking [SQL: INSERT INTO PAY_transaction_settingallow ([euid], [alw_id], [createDate], [effect_date], [to_date], [amount], [currency]) SELECT ed.employeeEuid AS euid, pma.alw_id, datetime('now') AS createDate, '2024-03-01', '2024-03-31', 500, 'INR' FROM employee_details ed JOIN PAY_mst_allowance pma ON ed.employeeName = 'jyothi' AND pma.alw_desc = 'Production Incentive';]
System Info
os: windows python: 3.11 langchain: latest
