SQLDatabaseChain did not read PostgreSQL database table information correctly

[Chesterguan]

System Info

langchain version: 0.0.168 OS: Mac

Who can help?

@eyurtsev

Information

• [X] The official example notebooks/scripts
• [ ] My own modified scripts

Related Components

• [ ] LLMs/Chat Models
• [ ] Embedding Models
• [ ] Prompts / Prompt Templates / Prompt Selectors
• [ ] Output Parsers
• [ ] Document Loaders
• [ ] Vector Stores / Retrievers
• [ ] Memory
• [X] Agents / Agent Executors
• [ ] Tools / Toolkits
• [X] Chains
• [ ] Callbacks/Tracing
• [ ] Async

Reproduction

Hi, I am trying to play with SQLDatabaseChain and I tried to connect it with the PostgreSQL database.

I tested with the URL, and it works well with the SQLAlchemy engine and I was able to execute queries successfully.

Here are my codes to use SQLDatabasechain:

db = SQLDatabase.from_uri(url, 
                              sample_rows_in_table_info = 10, 
                              )

However, it keeps showing that there are no tables. I used db.get_table_info(), it always return an empty set.

Do you have any ideas ?

Appreciate!

Expected behavior

I expected it can inspect the schema correctly.

[MiroslawOlszak]

Same issues when using MS SQL #4565 Based on this aswer https://github.com/hwchase17/langchain/issues/4565#issuecomment-1545752527

1. Add this method to sql_database.py - modifaction for postgresql NOT TESTED :

def _populate_all_tables(self) -> None:
        """Populate the _all_tables attribute with table names from the database."""
        if self.dialect == "sqlite":
            self._all_tables = {table.name for table in self._metadata.tables.values() if not table.name.startswith("sqlite_")}
        elif self.dialect.startswith("mssql"):
            # Add support for MS SQL databases
            table_names = self._engine.execute(f"SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_catalog = '{self._engine.url.database}'")
            self._all_tables = {table_name[0] for table_name in table_names}
        elif self.dialect.startswith("postgresql"):
            # Add support for PostgreSQL databases
            table_names = self._engine.execute(f"SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'public';'")
            self._all_tables = {table_name[0] for table_name in table_names}
        else:
            self._all_tables = {table.name for table in self._metadata.tables.values()}

2. Update method get_usable_table_names

    def get_usable_table_names(self) -> Iterable[str]:
        self._populate_all_tables()
        """Get names of tables available."""
        if self._include_tables:
            return self._include_tables
        return self._all_tables - self._ignore_tables

Please note that this code will not work with SQLAlchemy 2.x due to the removal of Engine.execute starting from version 2.x.

[dosubot[bot]]

Hi, @Chesterguan! I'm Dosu, and I'm here to help the LangChain team manage their backlog. I wanted to let you know that we are marking this issue as stale.

From what I understand, you reported an issue regarding the SQLDatabaseChain in langchain not reading table information correctly from a PostgreSQL database. MiroslawOlszak has provided a potential solution by adding a method to sql_database.py and updating the get_usable_table_names method. However, it is mentioned that the code will not work with SQLAlchemy 2.x.

Before we close this issue, we wanted to check with you 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 to the LangChain repository!

[anurag9099]

@dosu-beta This problem still persist in the latest repository of langchain.

[devstein]

@anurag9099 Hi sorry for the inconvenience. Dosu can't re-open issues yet (nor can I 😅). We're actively working to add support for re-opening stale issues.

Dosu will re-open this issue once it's rolled out!

[dosubot[bot]]

@baskaryan Could you please help @anurag9099 with the issue they mentioned? They indicated that the problem still persists in the latest repository of langchain. Thank you!

[dosubot[bot]]

Hi, @Chesterguan,

I'm helping the LangChain team manage their backlog and am marking this issue as stale. The reported issue involves the SQLDatabaseChain not reading table information correctly from a PostgreSQL database. There have been discussions about a potential solution, but it's noted that the code will not work with SQLAlchemy 2.x. Anurag9099 confirmed that the problem still persists in the latest repository.

Could you please confirm if this issue is still relevant to the latest version of the LangChain repository? If it is, please let the LangChain team 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 understanding and cooperation.