How to pass private schema while creating SQL ALchemy Engine for SQLDatabase

[Aniket-ByteIQ]

Hi! Team,

I have done the below configuration:
connection_url = URL.create(
drivername="redshift+redshift_connector",
username=self.postgres_config.get("username"),
password=self.postgres_config.get("password"),
host=self.postgres_config.get("host"),
port=self.postgres_config.get("port"),
database=database
)

        return create_engine(connection_url)
        
     async def create_sql_database(engine):
    """
    :return:
    """
    return SQLDatabase(
        engine=engine,
        schema='fl_lms'
    )

async def sql_query_engine(self, sql_db):
    """
    :return:
    """
    print(sql_db.get_usable_table_names())
    return NLSQLTableQueryEngine(
        service_context=self.service_context,
        sql_database=sql_db,
        tables=sql_db.get_usable_table_names(),
        synthesize_response=False
    )
    
 but getting the below error while quering myt database. I want to connect and query my database that present in the fl_lms schema
 error:   "detail": "Internal Server Error from RelationKey(name='accrual_balances', schema='public')"

[dosubot[bot]]

🤖

Hey @Aniket-ByteIQ, good to see you back here! Hope you're doing great.

Based on the error message you're encountering, it seems like the SQL Alchemy Engine is trying to access the 'accrual_balances' table in the 'public' schema, but it's not able to find it. This could be due to a few reasons:

1. The 'accrual_balances' table does not exist in the 'public' schema.
2. The user provided in the connection does not have the necessary permissions to access the 'accrual_balances' table in the 'public' schema.
3. The 'accrual_balances' table exists in a different schema, not 'public'.

To resolve this issue, you need to ensure that the 'accrual_balances' table exists in the 'public' schema and the user has the necessary permissions to access it. If the table exists in a different schema, you need to specify the correct schema in your query.

If you want to connect and query your database in the 'fl_lms' schema, you need to specify the schema in your query. Here's an example of how you can do this:

query = "SELECT * FROM fl_lms.accrual_balances"
documents = DatabaseReader.load_data(query)

In this example, 'fl_lms' is the schema and 'accrual_balances' is the table. This query will select all records from the 'accrual_balances' table in the 'fl_lms' schema.

Regarding your question about specifying a schema when creating an SQL Alchemy Engine, the LlamaIndex codebase does not provide any functionality to specify a schema when creating an SQL Alchemy Engine. The SQLStructStoreIndex class in the provided context does not have any parameters or methods that allow for the specification of a schema during the creation of an SQL Alchemy Engine. The SQL Alchemy Engine is created using the SQLDatabase object passed to the SQLStructStoreIndex class, but there is no parameter or method in the provided context that allows for the specification of a schema during this process.

You can refer to the following sources for more information:

• llama_index/readers/database.py
• llama_index/indices/struct_store/sql.py

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

Sources

• llama_index/readers/database.py
• llama_index/indices/struct_store/sql.py

About Dosu

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.

[Aniket-ByteIQ]

@dosu-bot I have provided the private schema while creating SQLDatabse object and also the table names in that specific schema are provided in the NLSQLTableQueryEngine, why is it still searching for those tables in public schema when different schema name is provided.

[dosubot[bot]]

🤖

Based on the information you've provided and the similar issues I found in the LlamaIndex repository, it seems like the system is still searching for tables in the public schema because the SQL command being executed in the run_sql method is not correctly replacing the schema in the FROM clause of the SQL command.

In the run_sql method, the schema is being replaced in the SQL command with the following line of code:

if self._schema:
    command = command.replace("FROM ", f"FROM {self._schema}.")

This line of code is replacing "FROM " with "FROM {self._schema}.", which means it's only replacing the "FROM " part of the SQL command and not the actual table name. This means that if the table name in the SQL command is not fully qualified (i.e., it doesn't include the schema), the system will still search for the table in the public schema.

To fix this issue, you need to replace the table name in the SQL command with the fully qualified table name, which includes the schema. Here's how you can do it:

if self._schema:
    command = re.sub(r'FROM (\w+)', f'FROM {self._schema}.\\1', command)

This line of code uses a regular expression to find the table name in the SQL command and replace it with the fully qualified table name. The \w+ in the regular expression matches one or more word characters (equivalent to [a-zA-Z0-9_]), which is the typical format of a table name. The \\1 in the replacement string is a backreference to the matched table name.

You can find more details about this in the llama_index/utilities/sql_wrapper.py file.

As for your question about specifying a private schema while creating the SQLDatabase object, I wasn't able to find an answer within the LlamaIndex repository. It's possible that the answer may be available elsewhere or I could have missed it. If you have more information about this, it would be helpful in providing a more accurate answer.

Sources

• [Bug]: not able to find the SQL table
• llama_index/utilities/sql_wrapper.py

About Dosu

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.