在HF Space中使用GradioUI调用smolagents时SQLite内存表无法检测

问题根源 #

SQLite的:memory:数据库是每个连接独立的。当Gradio启动后，处理请求时会创建新的进程/线程，此时工具函数sql_engine_tool使用的数据库连接会指向一个全新的空内存实例，而非你初始化时创建的包含receipts表的数据库。

解决方案 #

将内存数据库替换为磁盘文件数据库，所有连接会共享同一个数据库文件，避免独立内存实例的问题。只需修改数据库连接字符串：

原代码：

engine = create_engine("sqlite:///:memory:")

修改为：

engine = create_engine("sqlite:///receipts.db")

同时确保工具函数能正确访问初始化后的engine和元数据对象，通过全局变量规避作用域问题。

修改后的完整代码 #

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Float, insert, text
from smolagents import tool, CodeAgent, HfApiModel, GradioUI
import os

# 全局变量存储引擎和元数据
engine = None
metadata_objects = None

@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.

    """
    global engine, metadata_objects
    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():
    global engine, metadata_objects
    engine = create_engine("sqlite:///receipts.db")
    metadata_obj = MetaData()

    def insert_rows_into_table(rows, table):
        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()
    metadata_objects = metadata_obj


if __name__ == "__main__":
    init_db()
    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],
        model=model,
        max_steps=1,
        verbosity_level=1,
    )
    GradioUI(agent).launch()

额外说明 #

• 使用磁盘文件数据库后，每次启动应用会检查文件是否存在：存在则直接使用，不存在则重新建表插数据。若需每次启动重置数据，可在init_db开头添加删除receipts.db的逻辑。
• 全局变量确保sql_engine_tool能获取到正确初始化的数据库实例，避免作用域导致的变量未定义问题。

内容的提问来源于stack exchange，提问作者m2h9