[Python] sqlite table does not exist within gradio blocks or GradioUI even after creating said...

I am trying this out: https://huggingface.co/docs/smolagents/examples/text_to_sql in my hf space as a pro user. For some reason GradioUI(agent).launch() can't detect the sqlite tables. even though the prints in the tool function returns the correct engine.

@tool
def sql_engine_tool(query: str) -> str:
"""
Allows you to perform SQL queries on the table. Returns a string representation of the result.
The table is named 'receipts'. Its description is as follows:
Columns:
- receipt_id: INTEGER
- customer_name: VARCHAR(16)
- price: FLOAT
- tip: FLOAT

Args:
query: The query to perform. This should be correct SQL.

"""
output = ""
print("debug sql_engine_tool")
print(engine)
with engine.connect() as con:
print(con.connection)
print(metadata_objects.tables.keys())
result = con.execute(
text(
"SELECT name FROM sqlite_master WHERE type='table' AND name='receipts'"
)
)
print("tables available:", result.fetchone())

rows = con.execute(text(query))
for row in rows:
output += "\n" + str(row)
return output


def init_db(engine):

metadata_obj = MetaData()

def insert_rows_into_table(rows, table, engine=engine):
for row in rows:
stmt = insert(table).values(**row)
with engine.begin() as connection:
connection.execute(stmt)

table_name = "receipts"
receipts = Table(
table_name,
metadata_obj,
Column("receipt_id", Integer, primary_key=True),
Column("customer_name", String(16), primary_key=True),
Column("price", Float),
Column("tip", Float),
)
metadata_obj.create_all(engine)

rows = [
{"receipt_id": 1, "customer_name": "Alan Payne", "price": 12.06, "tip": 1.20},
{"receipt_id": 2, "customer_name": "Alex Mason", "price": 23.86, "tip": 0.24},
{
"receipt_id": 3,
"customer_name": "Woodrow Wilson",
"price": 53.43,
"tip": 5.43,
},
{
"receipt_id": 4,
"customer_name": "Margaret James",
"price": 21.11,
"tip": 1.00,
},
]
insert_rows_into_table(rows, receipts)
with engine.begin() as conn:
print("SELECT test", conn.execute(text("SELECT * FROM receipts")).fetchall())
print("init_db debug")
print(engine)
print()
return engine, metadata_obj


if __name__ == "__main__":
engine = create_engine("sqlite:///:memory:")
engine, metadata_objects = init_db(engine)
model = HfApiModel(
model_id="meta-llama/Meta-Llama-3.1-8B-Instruct",
token=os.getenv("my_first_agents_hf_tokens"),
)

agent = CodeAgent(
tools=[sql_engine_tool],
# system_prompt="""
# You are a text to sql converter
# """,
model=model,
max_steps=1,
verbosity_level=1,
)
# agent.run("What is the average each customer paid?")
GradioUI(agent).launch()


I may need to just use gr.blocks instead and reimplement some things. I am not the most familiar with this library, this will be tricky for me.

Log messages:

debug sql_engine_tool
Engine(sqlite:///:memory
<sqlalchemy.pool.base._ConnectionFairy object at 0x7f9228250ee0>
dict_keys(['receipts'])
tables available: None
Code execution failed at line 'customer_total = sql_engine_tool(engine=engine,
query=query)' due to: OperationalError: (sqlite3.OperationalError) no such
table: receipts


I have tried gr.Blocks(), stream_to_gradio(), they are not working. if I directly use the tool function to SELECT * FROM receipts, it works.

Continue reading...