Why does Langchain SQLDatabase connection detects only a few of existing tables?

Question

I have successfully connected to a Redshift database like below and got all the table names;

conn = psycopg2.connect(host,db,port,username,password)
cursor.execute("SELECT tablename FROM pg_tables GROUP BY tablename ORDER BY tablename")

However, when I connect using langchain and sqlalchemy like below, get_usable_table_names returns few of many tables in the database;

pg_url = f"postgresql+psycopg2://{db_user}:{db_password}@{db_host}:{port_}/{db_}"
db_engine = create_engine(pg_url)
db = SQLDatabase(db_engine)
llm = OpenAI(temperature=0.0, openai_api_key=OPENAI_API_KEY, model='gpt-3.5-turbo')

table_names = "\n".join(db.get_usable_table_names())

Anyone has any suggestions on what might be the issue?

I have tried querying a missing table by;

db.run("SELECT * FROM db_schema.missing_table_name") 

and this works. However, I need SQLDatabase from langchain.sql_database module to detect the tables right without specifying one by one. (Because I would like to Chat With Sql Database Using Langchain & OpenAI)

Answer 1

Thanks for all the answers.

I managed to get it running by replacing db_engine = create_engine(pg_uri) db=SQLDatabase(db_engine)

with this db = SQLDatabase.from_uri(pg_uri,schema="schema_name", view_support=True)

I believe I wasn't specifying the schema correctly.