Closed Zafershah24 closed 3 months ago
🤖
Thank you for the detailed report, Zafershah24. The error you're encountering is due to the incorrect usage of parameterized SQL in the PostgreSQL section of the _execute
method in the SQLDatabase
class. The correct way to use parameterized SQL with PostgreSQL is to use %s
as placeholders for parameters, not ?
.
Here's the corrected code:
elif self.dialect == "postgresql": # postgresql
connection.exec_driver_sql(
"SET search_path TO %s",
(self._schema,),
)
This code sets the search_path
for the PostgreSQL connection to the schema specified in self._schema
. The %s
is a placeholder for the schema name, which is passed as a tuple (self._schema,)
to the exec_driver_sql
method.
The exec_driver_sql
method in the SQLDatabase
class is used to execute SQL commands through the underlying engine. It is specifically used in the _execute
method to set the search path or schema for the SQL session, depending on the dialect of the SQL engine being used.
For example, if the dialect is 'snowflake', it executes the SQL command "ALTER SESSION SET search_path = %s" where %s is replaced with the schema. Similarly, for 'bigquery' it executes "SET @@dataset_id=?", for 'trino' it executes "USE ?", and for 'postgresql' and other compatible dialects, it executes "SET search_path TO %s".
In the case of 'duckdb' and 'oracle', it directly sets the search_path or CURRENT_SCHEMA to the schema without using parameterized arguments.
I hope this helps! If you have any other questions or need further clarification, feel free to ask.
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.
If you want to continue the conversation, start your reply with @dosu-bot.
Checked other resources
Example Code
Error Message and Stack Trace (if applicable)
Description
When attempting to set the PostgreSQL search_path using exec_driver_sql within the SQLDatabase class, an error is thrown. The relevant code snippet is as follows:
This line attempts to set the search_path to the schema defined in the self._schema attribute. However, this results in a syntax error because the parameter substitution (%s) is not supported for the SET command in PostgreSQL.
Expected Behavior: The search_path should be set to the specified schema without errors, allowing subsequent queries to run within the context of that schema.
Actual Behavior: A syntax error is raised, indicating an issue with the SQL syntax near the parameter substitution placeholder.
Steps to Reproduce the error:
Instantiate an SQLDatabase object with the PostgreSQL dialect. Change the Postgres schema to any other schema, other that 'public' Schema. Observe the syntax error.
System Info
langchain==0.1.4 langchain-community==0.0.16 langchain-core==0.1.17 langchain-google-vertexai==0.0.3 langsmith==0.0.85 pg8000==1.29.8 SQLAlchemy==2.0.16 cloud-sql-python-connector==1.2.4 OS: Windows