I am trying this out: 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.

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.

• python
• agent
• gradio

• 1 Wild guess; gradio event loop runs on different process (or pthread) and your sqlite is "in memory" and thus cannot be shared. Change to actual file ? – rasjani Commented Apr 1 at 11:05
• I guess I may need to write a bug report to gradio github? – Ryan Commented Apr 1 at 11:09
• 1 As said, i was just guessing because i do not have any experience with Gradio. However, in-memory databases cannot be shared between processes. So move the engine off "memory" and into a filesystem. If you need auto-removal, use tmpfs or similar. – rasjani Commented Apr 1 at 11:30
• 1 Thanks I solved it, I used sqlite:///:localhost:. you can put it as an answer and i'll set it as the correct answer. – Ryan Commented Apr 1 at 11:33

1 Answer 1

I used sqlite:///:localhost: and it solved. Thanks to @rasjani for the suggestion!