[Bug]: view is not working with NLSQLTableQueryEngine

[akashkumar398]

Bug Description

from llama_index.core import (
SQLDatabase,
VectorStoreIndex,
Settings,
)
from llama_index.llms.openai import OpenAI
from llama_index.core.query_engine import NLSQLTableQueryEngine
from llama_index.core.callbacks import CallbackManager, TokenCountingHandler

Set OpenAI API key

openai.api_key = os.environ["OPENAI_API_KEY"]

engine = initialize_database_connection()

tables = ["customer_information_enquiry"]
sql_database = SQLDatabase(engine, include_tables=tables, sample_rows_in_table_info=1)

MODEL = 'gpt-3.5-turbo-0125'

token_counter = TokenCountingHandler(
tokenizer=tiktoken.encoding_for_model(MODEL).encode
)
callback_manager = CallbackManager([token_counter])

llm = OpenAI(temperature=0, model=MODEL)
Settings.llm = llm
Settings.callback_manager = callback_manager

query_engine = NLSQLTableQueryEngine(
sql_database=sql_database,
)

Version

llama-index==0.10.50

Steps to Reproduce

same code not supporting a view type table

tables = ["customer_information_enquiry", "view_table]

Relevant Logs/Tracbacks

No response

[dosubot]

The issue you're encountering is likely due to the view_support parameter not being set to True when initializing the SQLDatabase class. By default, views are not included in the _all_tables set unless view_support is explicitly set to True.

To resolve this, you need to modify the initialization of the SQLDatabase to include view_support=True. Here is the updated code:

from llama_index.core import (
    SQLDatabase,
    VectorStoreIndex,
    Settings,
)
from llama_index.llms.openai import OpenAI
from llama_index.core.query_engine import NLSQLTableQueryEngine
from llama_index.core.callbacks import CallbackManager, TokenCountingHandler

# Set OpenAI API key
openai.api_key = os.environ["OPENAI_API_KEY"]

engine = initialize_database_connection()

tables = ["customer_information_enquiry", "view_table"]
sql_database = SQLDatabase(engine, include_tables=tables, sample_rows_in_table_info=1, view_support=True)

MODEL = 'gpt-3.5-turbo-0125'

token_counter = TokenCountingHandler(
    tokenizer=tiktoken.encoding_for_model(MODEL).encode
)
callback_manager = CallbackManager([token_counter])

llm = OpenAI(temperature=0, model=MODEL)
Settings.llm = llm
Settings.callback_manager = callback_manager

query_engine = NLSQLTableQueryEngine(
    sql_database=sql_database,
)

This change ensures that views are included in the _all_tables set and can be used in the tables list ^[1].

To continue talking to Dosu, mention @dosu.

[akashkumar398]

---

TypeError Traceback (most recent call last)
Cell In[12], line 103
99 engine = initialize_database_connection()
101 tables = ["customer_information_enquiry","vw_jobs"]
--> 103 sql_database = SQLDatabase(engine, include_tables=tables, sample_rows_in_table_info=1, view_support=True)
104 # sql_database = SQLDatabase(engine, include_tables=tables, sample_rows_in_table_info=1)
106 MODEL = 'gpt-3.5-turbo-0125'

File ~\anaconda3\envs\dbgpt\lib\site-packages\llama_index\core\utilities\sql_wrapper.py:110, in SQLDatabase.init(self, engine, schema, metadata, ignore_tables, include_tables, sample_rows_in_table_info, indexes_in_table_info, custom_table_info, view_support, max_string_length)
108 self._metadata = metadata or MetaData()
109 # including view support if view_support = true
--> 110 self._metadata.reflect(
111 views=view_support,
112 bind=self._engine,
113 only=list(self._usable_tables),
114 schema=self._schema,
115 )

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\sql\schema.py:5782, in MetaData.reflect(self, bind, schema, views, only, extend_existing, autoload_replace, resolve_fks, **dialect_kwargs)
5775 load = [
5776 name
5777 for name in only
5778 if extend_existing or name not in current
5779 ]
5780 # pass the available tables so the inspector can
5781 # choose to ignore the filter_names
-> 5782 _reflect_info = insp._get_reflection_info(
5783 schema=schema,
5784 filter_names=load,
5785 available=available,
5786 kind=kind,
5787 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
5788 **dialect_kwargs,
5789 )
5790 reflect_opts["_reflect_info"] = _reflect_info
5792 for name in load:

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\engine\reflection.py:2008, in Inspector._get_reflection_info(self, schema, filter_names, available, _reflect_info, **kw)
2004 res = {}
2005 return res
2007 info = _ReflectionInfo(
-> 2008 columns=run(
2009 self.get_multi_columns, check_filter_names_from_meth=True
2010 ),
2011 pk_constraint=run(self.get_multi_pk_constraint),
2012 foreign_keys=run(self.get_multi_foreign_keys),
2013 indexes=run(self.get_multi_indexes),
2014 unique_constraints=run(
2015 self.get_multi_unique_constraints, optional=True
2016 ),
2017 table_comment=run(self.get_multi_table_comment, optional=True),
2018 check_constraints=run(
2019 self.get_multi_check_constraints, optional=True
2020 ),
2021 table_options=run(self.get_multi_table_options, optional=True),
2022 unreflectable=unreflectable,
2023 )
2024 if _reflect_info:
2025 _reflect_info.update(info)

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\engine\reflection.py:1994, in Inspector._get_reflection_info..run(meth, optional, check_filter_names_from_meth)
1992 try:
1993 if has_result:
-> 1994 res = meth(filter_names=_fn, **kw)
1995 if check_filter_names_from_meth and not res:
1996 # method returned no result data.
1997 # skip any future call methods
1998 has_result = False

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\engine\reflection.py:921, in Inspector.get_multi_columns(self, schema, filter_names, kind, scope, **kw)
884 r"""Return information about columns in all objects in the given
885 schema.
886
(...)
917 .. seealso:: :meth:Inspector.get_columns
918 """
920 with self._operation_context() as conn:
--> 921 table_col_defs = dict(
922 self.dialect.get_multi_columns(
923 conn,
924 schema=schema,
925 filter_names=filter_names,
926 kind=kind,
927 scope=scope,
928 info_cache=self.info_cache,
929 **kw,
930 )
931 )
932 self._instantiate_types(table_col_defs.values())
933 return table_col_defs

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\engine\default.py:1098, in DefaultDialect._default_multi_reflect(self, single_tbl_method, connection, kind, schema, filter_names, scope, **kw)
1094 key = (schema, table)
1095 try:
1096 yield (
1097 key,
-> 1098 single_tbl_method(
1099 connection, table, schema=schema, **kw
1100 ),
1101 )
1102 except exc.UnreflectableTableError as err:
1103 if key not in unreflectable:

File :2, in get_columns(self, connection, table_name, schema, **kw)

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\engine\reflection.py:97, in cache(fn, self, con, *args, **kw)
95 ret: _R = info_cache.get(key)
96 if ret is None:
---> 97 ret = fn(self, con, *args, **kw)
98 info_cache[key] = ret
99 return ret

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\dialects\mysql\base.py:2917, in MySQLDialect.get_columns(self, connection, table_name, schema, **kw)
2915 @reflection.cache
2916 def get_columns(self, connection, table_name, schema=None, **kw):
-> 2917 parsed_state = self._parsed_state_or_create(
2918 connection, table_name, schema, **kw
2919 )
2920 if parsed_state.columns:
2921 return parsed_state.columns

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\dialects\mysql\base.py:3177, in MySQLDialect._parsed_state_or_create(self, connection, table_name, schema, **kw)
3174 def _parsed_state_or_create(
3175 self, connection, table_name, schema=None, **kw
3176 ):
-> 3177 return self._setup_parser(
3178 connection,
3179 table_name,
3180 schema,
3181 info_cache=kw.get("info_cache", None),
3182 )

File :2, in _setup_parser(self, connection, table_name, schema, **kw)

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\engine\reflection.py:97, in cache(fn, self, con, *args, **kw)
95 ret: _R = info_cache.get(key)
96 if ret is None:
---> 97 ret = fn(self, con, *args, **kw)
98 info_cache[key] = ret
99 return ret

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\dialects\mysql\base.py:3212, in MySQLDialect._setup_parser(self, connection, table_name, schema, **kw)
3207 if parser._check_view(sql):
3208 # Adapt views to something table-like.
3209 columns = self._describe_table(
3210 connection, None, charset, full_name=full_name
3211 )
-> 3212 sql = parser._describe_to_create(table_name, columns)
3213 return parser.parse(sql, charset)

File ~\anaconda3\envs\dbgpt\lib\site-packages\sqlalchemy\dialects\mysql\reflection.py:350, in MySQLTableDefinitionParser._describe_to_create(self, table_name, columns)
348 line.append("NOT NULL")
349 if default:
--> 350 if "auto_increment" in default:
351 pass
352 elif col_type.startswith("timestamp") and default.startswith(
353 "C"
354 ):

TypeError: a bytes-like object is required, not 'str'

it's giving error