SQLDatabase doesn't work with named paramstyle

[alex-ber]

Checked other resources

• I added a very descriptive title to this issue.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.
• I am sure that this is a bug in LangChain rather than my code.
• The bug is not resolved by updating to the latest stable version of LangChain (or the specific integration package).

Example Code

from dataclasses import dataclass

from atexit import register as atexit_register
from sqlalchemy.event import listen
from sqlalchemy.engine import Engine

from sqlalchemy import create_engine
from sqlalchemy.sql.elements import quoted_name
from langchain.sql_database import SQLDatabase

@dataclass
class dao:
    engine: Engine
    DB_SCHEMA: str

def _set_search_path(dbapi_connection, connection_record, **kw):
    existing_autocommit = dbapi_connection.autocommit
    dbapi_connection.autocommit = True
    cursor = dbapi_connection.cursor()

    try:
        #https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
        cursor.execute(f"SET search_path TO {dao.DB_SCHEMA},public;")
        #cursor.execute("SET ROLE TO apps_engineer;")
    finally:
        cursor.close()
        dbapi_connection.autocommit = existing_autocommit



def daoInitConfig():
    print("daoInitConfig()")


    DB_USER = ...
    DB_PASSWORD = ...
    DB_HOST = ...
    DB_PORT = ...
    DB_NAME = ...
    db_schema = ...

    url = f'postgresql+pg8000://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}'

    import pg8000 as _pg800
    _pg800.paramstyle = 'named'

    import pg8000.legacy as _legacy
    _legacy.paramstyle = 'named'

    import pg8000.dbapi as _dbapi
    _dbapi.paramstyle = 'named'

    from sqlalchemy.dialects.postgresql.pg8000 import PGDialect_pg8000
    PGDialect_pg8000.default_paramstyle = 'named'

    engine = create_engine(
        url)
    atexit_register(engine.dispose)

    # Use the listen event to call _set_search_path when a connection is created
    #listen(engine, 'connect', _set_search_path)

    dao.engine = engine
    dao.DB_SCHEMA = quoted_name(db_schema, None)


def main():
    daoInitConfig()

    db = SQLDatabase(
        engine=dao.engine,
        schema=dao.DB_SCHEMA,
        sample_rows_in_table_info=0
    )

    result = db.run("select 1")
    assert "1" == result



if __name__ == '__main__':
    main()

Error Message and Stack Trace (if applicable)

Traceback (most recent call last):
File "/var/lang/lib/python3.10/site-packages/pg8000/legacy.py", line 254, in execute
self._context = self._c.execute_unnamed(
File "/var/lang/lib/python3.10/site-packages/pg8000/core.py", line 688, in execute_unnamed
self.handle_messages(context)
File "/var/lang/lib/python3.10/site-packages/pg8000/core.py", line 827, in handle_messages
raise context.error
pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "%"', 'P': '20', 'F': 'scan.l', 'L': '1236', 'R': 'scanner_yyerror'}

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
self.dialect.do_execute(
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
cursor.execute(statement, parameters)
File "/var/lang/lib/python3.10/site-packages/pg8000/legacy.py", line 281, in execute
raise cls(msg)
pg8000.dbapi.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "%"', 'P': '20', 'F': 'scan.l', 'L': '1236', 'R': 'scanner_yyerror'}

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/var/lang/lib/python3.10/contextlib.py", line 153, in exit
self.gen.throw(typ, value, traceback)
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3234, in begin
yield conn
File "/var/lang/lib/python3.10/site-packages/langchain_community/utilities/sql_database.py", line 438, in _execute
connection.exec_driver_sql(
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1778, in exec_driver_sql
ret = self._execute_context(
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
return self._exec_single_context(
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
self._handle_dbapi_exception(
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
self.dialect.do_execute(
File "/var/lang/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
cursor.execute(statement, parameters)
File "/var/lang/lib/python3.10/site-packages/pg8000/legacy.py", line 281, in execute
raise cls(msg)
sqlalchemy.exc.ProgrammingError: (pg8000.dbapi.ProgrammingError) {'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "%"', 'P': '20', 'F': 'scan.l', 'L': '1236', 'R': 'scanner_yyerror'}
[SQL: SET search_path TO %s]
[parameters: ('rag_ask_george_qa',)]
(Background on this error at: https://sqlalche.me/e/20/f405)

Process finished with exit code 1

Description

I'm using SqlAlchemy's engine as main facade to connect to my Postgress DB. I'm using pg8000 driver. I'm using named paramstyle.

So, when db.run() is called it calls _db.execute()

First thing, that he does, is breaking SqlAlchemy's engine facade and working on driver level. The code that is written specifically suggest that engine uses format paramstyle.

So, at least code should be fixed to take into account different paramstyles. I'm talking about "SET search_path TO %s"

Personally, I think that breaking engine interface is inherently wrong. SQLDatabase shouldn't manipulate on such granular level.
What I prefer to see, is some flag, that disables call to SqlAlchemy driver at all. Than in application code I'll install listener, that will set search_path on every "connect" event.

In the code above, I just need to change

# Use the listen event to call _set_search_path when a connection is created
#listen(engine, 'connect', _set_search_path)

to

# Use the listen event to call _set_search_path when a connection is created
listen(engine, 'connect', _set_search_path)

System Info

bash-4.2# python -m langchain_core.sys_info

System Information

OS: Linux
OS Version: #1 SMP Thu Oct 5 21:02:42 UTC 2023
Python Version: 3.10.13 (main, Dec 4 2023, 13:30:46) [GCC 7.3.1 20180712 (Red Hat 7.3.1-17)]

Package Information

langchain_core: 0.1.28
langchain: 0.1.10
langchain_community: 0.0.25
langsmith: 0.1.10
langchain_experimental: 0.0.50
langchain_openai: 0.0.2.post1
langchain_text_splitters: 0.0.1

Packages not installed (Not Necessarily a Problem)

The following packages were not found:

langgraph
langserve

[alex-ber]

See #17231