[Bug]: SQLTableSchema is not working for schemas other than dbo

[sreenivasanm6]

Bug Description

Hi Team,

We are trying to perform text to sql operation and we are using SQLTableSchema as part of that. In this case, we are passing table name without prefixing schema. Even if we pass prefix, it is giving below error:

NoSuchTableError(f"{owner}.{tablename}") sqlalchemy.exc.NoSuchTableError: None.ch.product

Schema here is ch.

How to fix this? Since by default in MS SQL server, sqlalchemy consider dbo. as schema. But, if we have different than 'dbo.', how to handle them?

Version

latest

Steps to Reproduce

Please follow the description

Relevant Logs/Tracbacks

No response

[dosubot[bot]]

To address the issue with SQLTableSchema not working for schemas other than 'dbo' in MS SQL Server, you can specify the schema explicitly when using SQLAlchemy. Here's how you can handle schemas other than 'dbo':

1. Specify the Schema in SQLAlchemy: When creating your SQLAlchemy engine or when defining your tables, make sure to specify the schema. You can do this by using the schema parameter in your table definitions or when initializing your database connection.

2. Use the schema Parameter: If you are using a class or function that allows specifying a schema, make sure to pass the correct schema name. For example, when using the SQLDatabase class, you can specify the schema like this:

   from sqlalchemy import create_engine
   from llama_index.core.utilities.sql_wrapper import SQLDatabase
   
   # Create an SQLAlchemy engine
   engine = create_engine('mssql+pyodbc://username:password@dsn_name')
   
   # Initialize the SQLDatabase with the desired schema
   database = SQLDatabase(engine=engine, schema='ch')

3. Ensure Schema Exists: Make sure that the schema you are trying to use exists in the database. You might need to create it if it doesn't exist.

By explicitly specifying the schema, you can avoid the default behavior of SQLAlchemy, which assumes 'dbo' as the default schema in MS SQL Server. This should help resolve the NoSuchTableError you are encountering.

To continue talking to Dosu, mention @dosu.

[logan-markewich]

I thiiiiink dosu is correct here? Give that a shot