odbc_str can't detect the schemas in my sql database, only detects de default schema tables

[mtruyols]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

odbc_str = 'mssql+pyodbc:///?odbc_connect=' \
                'Driver='+driver+ \
                ';Server=tcp:' + os.getenv("SQL_SERVER")+';PORT=1433' + \
                ';DATABASE=' + os.getenv("SQL_DB") + \
                ';Uid=' + os.getenv("SQL_USERNAME")+ \
                ';Pwd=' + os.getenv("SQL_PWD") + \
                ';Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=300;schema="idaddl";'
db_engine = create_engine(odbc_str)

Description

I am trying to use Chat GPT with my SQL data (create_sql_agent) and with odbc_str, but it does not detect all the tables (it only detects the tables under the default schema). Does anyone know how to specify a different schema?

Thank you very much.

System Info

...
urllib3==2.1.0
wcwidth==0.2.12
yarl==1.9.4
Note: you may need to restart the kernel to use updated packages.

[dosubot[bot]]

🤖

Hey there @mtruyols! Nice to bump into you again on these digital crossroads. Hope you’re ready to tackle some code together once more. 🚀

Based on the information provided, it seems like you're trying to specify a schema in the odbc_str when creating an engine with create_engine. However, in the LangChain framework, the schema is not specified in the odbc_str but rather when creating an instance of the SQLDatabase class.

Here's an example of how you can specify a schema when creating an SQLDatabase instance:

from langchain.sql import SQLDatabase

# Assuming 'engine' is your SQLAlchemy engine
db = SQLDatabase(engine, schema="my_schema")

In this example, "my_schema" is the name of the schema you want to use. Replace it with the name of your desired schema.

Unfortunately, the create_engine function does not support specifying a schema in the odbc_str. The connection string used with create_engine should not include a schema.

Here's an example of how you can create an engine without specifying a schema:

from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc:///?odbc_connect='Driver={driver};Server=tcp:{server};PORT=1433;DATABASE={database};Uid={username};Pwd={password};Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=300;")

In this example, replace {driver}, {server}, {database}, {username}, and {password} with your actual driver, server, database, username, and password respectively.

After creating the engine, you can then create an SQLDatabase instance and specify your schema as shown in the first example.

I hope this helps! If you have any other questions or need further clarification, feel free to ask.

Sources

• libs/langchain/tests/unit_tests/test_sql_database_schema.py

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[AlokEY21]

@dosu-bot This is really helpful in case of single schema, in my scenario I have multiple schema to be included for a single query. How can i handle that?