Issue: db.get_usable_table_names() return nothing

[MiroslawOlszak]

Issue you'd like to raise.

Describe the bug

When using the db.get_usable_table_names() function with a MS SQL database, it doesn't return any table names. However, when using the same function with SQLite3, it works as expected. Interestingly, the db.run() method works correctly, returning expected records for direct SQL queries like 'select * from Shops'.

To Reproduce

db = SQLDatabase.from_uri("mssql+pymssql://user:[email protected]:port/KK_ANA")

• Call db.get_table_names(). The return value is an empty set. [return "set()"]
• Run a direct SQL query using db.run('select * from Shops'). It correctly returns the expected records.

Run the SQLDatabaseSequentialChain: llm = ChatOpenAI(temperature=0) db_chain = SQLDatabaseSequentialChain.from_llm(llm, db, verbose=True) db_chain.run('show me list of tables')

Output

Environment

• Langchain version: 0.0.165
• Python version: 3.10
• SQLAlchemy Version: 2.0.12 (problem also occurs with version 1.4.x)
• pymssql Version: 2.2.7

Suggestion:

No response

[zvadaadam]

🤖 AI-generated response by Steercode - chat with Langchain codebase Disclaimer: SteerCode Chat may provide inaccurate information about the Langchain codebase.

It seems that the issue is related to the _all_tables attribute in the SQLDatabase class, which is not populated correctly when using MS SQL database. This causes the get_usable_table_names() method to return an empty set. To fix this issue, you can update the _populate_all_tables method in the SQLDatabase class to handle MS SQL databases correctly. You can modify the method as follows: python COPY

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("SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_catalog = ?", self._database_name)
        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()}

This modification adds a conditional branch to handle MS SQL databases, where it retrieves the table names using an SQL query and populates the _all_tables attribute accordingly. After making this change, the get_usable_table_names() method should return the correct table names when using an MS SQL database.

[Ramasrikanth]

I am stuck with this issue , is this fixed or any work around in place?

[dosubot[bot]]

Hi, @MiroslawOlszak. 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 you reported is that the db.get_usable_table_names() function does not return any table names when used with a MS SQL database. zvadaadam suggested a fix by updating the _populate_all_tables method in the SQLDatabase class to handle MS SQL databases correctly. It seems that this fix has been implemented and the issue has been resolved.

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!

[daviddwlee84]

I am facing this issue when connecting with DuckDB.

import duckdb
from langchain.sql_database import SQLDatabase

conn = duckdb.connect(':memory:')
df = conn.read_csv('../demo/demo.csv')
conn.register('tbl', df)

# Successfully run and print the table schema
# Shows the table "tbl" is in the database
conn.execute('DESCRIBE tbl;').df()
# Print "tbl" as names
conn.execute('SHOW TABLES;').df()

# This will fail => ValueError: include_table {'tbl'} not found in database
db = SQLDatabase.from_uri('duckdb:///:memory:', include_tables=['tbl'])

# This can work but cannot get the table name
db = SQLDatabase.from_uri('duckdb:///:memory:', sample_rows_in_table_info=3)
# This will get an empty result
db.get_usable_table_names()

# The weird part (maybe DuckDB feature...?)
db.run('SELECT * FROM tbl;') # This will fail
db.run('SELECT * FROM df;') # Success

# But still, we can't do things like this => ValueError: include_table {'df'} not found in database
db = SQLDatabase.from_uri('duckdb:///:memory:', include_tables=['df'])

[anshumankmr]

@daviddwlee84 did you figure it out?