I am having this issue don t know what

At a glance

The community member is experiencing a ProgrammingError when running a SQL query that involves joining the players and clubs tables. The query appears to be working fine when run separately, but the issue arises when the query engine tries to execute the two queries together in a single command.

Another community member suggests that the query engine only handles the case of a single query command, and not multiple queries. They recommend adding functionality to the run_sql method to handle multiple queries and combine the results.

Useful resources

CChanh Le

I am having this issue don't know what is it issue

Plain Text

2023-08-15 22:10:48 sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT player_name, player_position\nFROM players\nJOIN clubs ON players.player_cl' at line 6")
2023-08-15 22:10:48 [SQL: SELECT COUNT(*) AS num_players
2023-08-15 22:10:48 FROM players
2023-08-15 22:10:48 JOIN clubs ON players.player_club_id = clubs.club_id
2023-08-15 22:10:48 WHERE clubs.club_name = 'Arsenal FC';
2023-08-15 22:10:48 
2023-08-15 22:10:48 SELECT player_name, player_position
2023-08-15 22:10:48 FROM players
2023-08-15 22:10:48 JOIN clubs ON players.player_club_id = clubs.club_id
2023-08-15 22:10:48 WHERE clubs.club_name = 'Arsenal FC'
2023-08-15 22:10:48 LIMIT 5;]
2023-08-15 22:10:48 (Background on this error at: https://sqlalche.me/e/20/f405)
2023-08-15 22:10:48 192.168.112.5 - - [15/Aug/2023 15:10:48] "POST /chat HTTP/1.0" 500 -

with code to create index

Plain Text

        sql_engine = get_sql_engine(env)
        sql_database = SQLDatabase(sql_engine)
        builder = SQLContextContainerBuilder(sql_database)
        context_builder = builder.build_context_container()
        service_context = ServiceContext.from_defaults(llm=api.llm)
        index = GPTSQLStructStoreIndex([],
                                       sql_database=sql_database,
                                       sql_context_container=context_builder,
                                       service_context=service_context)

I ran the query both work fine.
Have you faced it before?

2 comments

CChanh Le

I found the query engine create 2 queries but it also ask the mysql to run in 1 execute command that why it is failed. How to break it into 2 queries and combine the result for answer

CChanh Le

found the query def _query(self, query_bundle: QueryBundle) -> Response:
of class BaseSQLTableQueryEngine(BaseQueryEngine):
only handle case of 1 query command not 2 at the moment.
@Logan M
Should I add to handle 2 queries and make a change in

Plain Text

    def run_sql(self, command: str) -> Tuple[str, Dict]:
        """Execute a SQL statement and return a string representing the results.

        If the statement returns rows, a string of the results is returned.
        If the statement returns no rows, an empty string is returned.
        """
        with self._engine.connect() as connection:
            cursor = connection.execute(text(command))
            if cursor.returns_rows:
                result = cursor.fetchall()
                return str(result), {"result": result}
        return "", {}

Add a reply

Find answers from the community

I am having this issue don t know what