[Python] Gemini not answering questions about SQL

I am using Google Gemini API to take inputs of a SQL table from a database and answers questions based on the table. It takes input in the form of a table, then it takes a question about the database, the program then converts the question into a SQL query which the program then runs and it gives the output, ideally in the form of the answer to the question. However, it is not doing that, it is instead answering -

SQL Answer: SQL Error: Expected a string but received a list.

Could anyone look into the code and help me in deciphering the code and help me figure out where the code is failing and how to fix it ?

Here is the code of the program -

import mysql.connector
from google.generativeai import configure, generate_text

# Configure Google Gemini API
configure(api_key="your_google_gemini_api_key")

# Function to convert natural language question to SQL query using Gemini
def generate_sql_query(question):
prompt = f"Convert the following question into a MySQL query:\n\n{question}\n\nEnsure the query is compatible with the schema 'mydb' and does not delete or modify any data."

try:
response = generate_text(prompt=prompt)
sql_query = response.text.strip()
return sql_query
except Exception as e:
return f"Error generating SQL query: {str(e)}"

# Function to execute SQL query and fetch results
def query_sql(question):
# Generate SQL query from natural language question
sql_query = generate_sql_query(question)

if "Error" in sql_query:
return sql_query # Return error message if query generation fails

# Connect to MySQL database
conn = mysql.connector.connect(
host="your_host",
user="your_user",
password="your_password",
database="mydb"
)
cursor = conn.cursor()

try:
# Execute the generated SQL query
cursor.execute(sql_query)
result = cursor.fetchall()

# Format the result
if result:
return "\n".join([str(row) for row in result])
else:
return "No results found."

except Exception as e:
return f"SQL Error: {str(e)}"

finally:
cursor.close()
conn.close()


Thank you in advance

I debugged the program and there seems to be some issue when it comes to the flow of the program, sometimes the generate SQL query isn't getting called which means that the question is going into the query_sql function instead of the SQL query which should go. This is what the debug and question was, the sql database contains information about sales of video games in different regions of the planet, the table contains their names, their publishers, years of release and sales in different regions (north america, europe, japan, other regions). The question which was asked was -

Which game sold the most copies in Europe ?

The answer it gave was -

SQL Answer: ❌ SQL Error: Expected a string but received a list.

This is what the debug was when I did it -

Testing Intent Classification

['sql']

Entering query_sql() function...

Received sql_query: ['In which year did Craig Lowndes dominate the Australian Touring Car series?', 'Which game sold the most copies in Europe?', 'In which year Marie Curie won the Nobel Prize in Physics?'] (Type: <class 'list'>)

❌ ERROR: query_sql received a LIST: ['In which year did Craig Lowndes dominate the Australian Touring Car series?', 'Which game sold the most copies in Europe?', 'In which year Marie Curie won the Nobel Prize in Physics?']

The generate_sql_query function is not getting called. it is instead sending the questions straight into the query_sql function which is giving the error.

The database is being called from its connection string where I am specifying my host connection and my password and it is connecting with the database and the schema mydb.

Ideally the code should take the input of the question, convert it into a sql query and then answer the said query and give the output the question seeks.

Continue reading...