feat: add Python cell worksheet blocks - #753

Point the worker URL at the emitted JS file

When @sqlrooms/python-cell is built/published, tsc emits dist/pyodideWorker.js but does not rewrite this string, and package.json publishes only dist. Consumers that import the built package instead of the CLI app's source aliases will therefore resolve dist/pyodideWorker.ts, which is not present, so the Pyodide adapter fails when the first cell runs. Use the emitted .js worker URL or otherwise include/rewrite the worker asset for the package output.

Useful? React with 👍 / 👎.

Bound JSON results before persisting them

For cells that assign a large JSON-serializable result (for example a big list or table preview), this path returns the JSON output unchanged, and runCell then stores it in pythonCells.config.cells[cellId].lastResult. Unlike stdout, text, and markdown, JSON results have no size cap here, so one run can write megabytes into the persisted room state and make saves/reloads or rendering very slow. Please persist a bounded preview/summary for JSON outputs as well.

Useful? React with 👍 / 👎.

Bind declared inputs before running user code

When a cell is created with inputs through worksheet.add-python-cell-block or patchCell, the worker proceeds directly to runPythonAsync(request.code) without ever reading request.inputs; named literal, sql, tableRef, or schema inputs therefore are not bound in Python (and table/sql inputs are not fetched), so code that relies on the declared input API fails with NameError or has to bypass the API with manual queries. Please resolve and bind the declared inputs before executing the cell.

Useful? React with 👍 / 👎.

Install micropip requirements with micropip

When a requirement is declared with source: 'micropip' (which the new PythonRequirementSpec and command input allow), this path groups it with Pyodide packages and calls pyodide.loadPackage, so PyPI-only packages fail during setup before user code runs. Split requirements by source and invoke micropip.install for micropip entries instead of passing them to loadPackage.

Useful? React with 👍 / 👎.

Block execution when the cell is read-only

In read-only worksheet/document renders, the editor prevents code edits but handleRun still calls runCell, and the run button is enabled whenever the cell has code. Because runCell writes lastResult back into persisted room state, a viewer of a read-only artifact can mutate it by clicking Run or pressing Mod-Enter; include readOnly in this guard and the run button disabled state.

Useful? React with 👍 / 👎.

Honor declared output names during execution

When a cell is created with an outputs declaration through worksheet.add-python-cell-block, the request carries those declarations but the worker ignores request.outputDeclarations here and only reads the special global result. For example, code that assigns foo = 42 with an output declaration {type: 'json', name: 'foo'} reports no outputs, and declared table/image outputs are never materialized. Please collect the declared output names from globals and route table/asset outputs through the host where applicable instead of relying solely on result.

Useful? React with 👍 / 👎.

Provide schema bridge for schema inputs

Cells that declare a schema input fail in the CLI because bindInputs sends a schema host request and PyodidePythonRuntimeAdapter requires host.readSchema, but this host only wires table/query access. In that scenario the run errors with SQLRooms Python schema bridge is not configured before user code executes, even though schema inputs are accepted by the command/schema API. Please implement readSchema here or reject schema inputs for the CLI runtime.

Useful? React with 👍 / 👎.

Convert declared input payloads before binding

When a declared sql input is used, this writes the raw JS host response into the Python global. Pyodide exposes JS objects as JsProxy unless converted, while the sqlrooms.query() helper explicitly calls .to_py(); cells that pass the input to pandas or return it as result therefore receive/prolong a proxy instead of a native {columns, rows, rowCount} dict. Convert the response with pyodide.toPy(...) (and do the same for table/schema inputs) before binding.

Useful? React with 👍 / 👎.

Preserve active runs when an older execution finishes

When runCell is invoked twice for the same cell through the command/direct API, the second call overwrites runtimeByCellId[cellId] with its execution id. When the first completion reaches this block, it still writes lastResult and deletes the runtime entry, making the UI/API report idle while the second run is queued or running; with non-serial adapters it can also persist a stale result. Check the current execution id before clearing or writing completion state.

Useful? React with 👍 / 👎.

Emit valid JSON for non-finite Python numbers

For outputs containing non-finite floats (result = float('nan'), pandas/NumPy data, or Vega specs with NaN), Python's json.dumps emits NaN/Infinity by default, which is not valid JSON for the subsequent JSON.parse. A successful cell is then reported as an execution error while reading outputs. Normalize or replace non-finite values with a JSON-compatible representation before parsing.

Useful? React with 👍 / 👎.

Handle qualified table references

When a declared tableRef uses the canonical SQLRooms table id produced elsewhere in the app, such as main.events or "main"."events", this quotes the whole string as one identifier ("main.events" / """main"".""events""") instead of preserving the qualified parts. That makes Python cells fail to bind schema-qualified or attached-database tables even though those are the table references agents and table tools hand around; use the existing qualified table-reference quoting/parsing behavior rather than single-identifier quoting here.

Useful? React with 👍 / 👎.

Cap stdout while collecting it

For cells that print a lot of data, this buffers every stdout/stderr chunk in memory and only applies the persisted-size truncation later in PythonCellSlice, while request.limits?.maxStdoutBytes is never enforced in the worker. A single print loop can therefore allocate very large strings/arrays and freeze or crash the browser before the bounded result summary is created; drop or truncate output as it is appended.

Useful? React with 👍 / 👎.

Enforce timeoutMs during Python execution

When a host supplies limits.timeoutMs or a user runs CPU-bound code such as while True: pass, this await has no timeout or interrupt path. Because the worker is serial, that promise never settles, runtimeByCellId stays running, and all later Python-cell executions queue behind the hung run until the worker/page is reset.

Useful? React with 👍 / 👎.

Clear declared outputs before reading globals

When a cell has a declared output and a previous run created that global, a later successful run that no longer assigns it still passes this globals() check because the Pyodide interpreter is reused and only result is cleared before execution. In that scenario the persisted lastResult shows stale output from an earlier run, which can corrupt worksheet results after code edits.

Useful? React with 👍 / 👎.

Route tableRef inputs through readTable

PythonRuntimeHost.readTable is the required table-input hook, while runReadonlySql is optional; however a tableRef input is converted into a SQL host request here, and the adapter rejects that request when runReadonlySql is not implemented. Package consumers that implement the documented readTable contract therefore cannot run table-ref inputs unless they also provide the optional SQL bridge.

Useful? React with 👍 / 👎.

Preserve SQL value types for Python rows

When a Python cell uses sqlrooms.query()/query_df() or a tableRef input over data containing NULL or booleans, this bridge serializes rows with arrowTableToJson; that helper stringifies every non-number/non-BigInt value, so NULL becomes "null" and booleans become "true"/"false". Pandas and downstream Python analysis then operate on corrupted strings rather than the database values, so this path should use a conversion that preserves JSON primitives for Python bridge rows.

Useful? React with 👍 / 👎.

Convert literal inputs before binding

When a command declares a literal input whose value is an object or array, this branch stores the raw JS value directly in Pyodide. Pyodide exposes that as a JsProxy, unlike the SQL/table/schema paths below that call bindPythonGlobal and .to_py(), so Python code expecting a normal dict/list or returning that value as result gets a proxy/stringified object instead; route literal values through the same conversion path.

Useful? React with 👍 / 👎.

Reject pending runs when terminating the worker

With per-request limits.timeoutMs, a timeout terminates the shared worker but only deletes and rejects the timed-out entry. Any other execution already posted to that worker, such as a queued run for another cell with no timeout or a longer timeout, can no longer receive a result and remains in pending/running state until an external reset; clear and reject all pending requests whenever the worker is terminated.

Useful? React with 👍 / 👎.

Start execution timeouts when work starts

When timeoutMs is configured and multiple cells are run, this timer starts as soon as execute() posts the request, but pyodideWorker serializes requests behind executionQueue. A queued cell spends part of its execution budget waiting for earlier cells; for example, if the first run takes 55s with a 60s limit, the second cell is killed after only about 5s of actual Python and the shared worker is terminated. Start the timeout when the worker begins that request, or track queue wait separately.

Useful? React with 👍 / 👎.

Preserve catalog names in schema summaries

For schema inputs without a tableName, this key uses only schema/table and even drops the main schema. In workspaces with attached DuckDB databases, information_schema.columns can return the same table_schema/table_name from different table_catalogs, so their columns are merged under one table and Python cells or agents receive an invalid schema. Include table_catalog in the query/key, or use the existing qualified table-name helpers before grouping.

Useful? React with 👍 / 👎.

Apply the row preview limit to bridge queries

When a caller configures limits.maxRowsPreview, cells that omit an explicit maxRows—for example sqlrooms.query("select * from big_table") or a tableRef input without maxRows—still send an unbounded host request because this path only normalizes the explicit argument and maxRowsPreview is otherwise never read. The CLI host then applies no LIMIT and tries to JSON-copy the full result through the 16 MiB bridge buffer, so large tables can fail with SQLRoomsBridgeResponseTooLarge or consume excessive memory before user code runs; fall back to the request preview limit when maxRows is absent, and apply the same behavior to table requests.

Useful? React with 👍 / 👎.

Bound declared text outputs before serialization

When a block declares a text/markdown/html output, this branch converts the full Python value to a string and the worker later JSON-serializes/posts it before PythonSlice truncates persisted summaries. A cell with a declared summary output and summary = 'x' * 10_000_000 can still allocate and send megabytes through the worker/main-thread boundary despite the persisted-size caps; apply the same bounded-string budget here and in the adjacent markdown/html branches before returning the output.

Useful? React with 👍 / 👎.

Reject stacked statements in readonly Python SQL

When a Python block calls sqlrooms.query() with SQL that closes the derived table and appends another statement, for example SELECT 1); DROP TABLE t; --, this wrapper produces SELECT * FROM (SELECT 1); DROP TABLE t; --) .... In the CLI path that string is forwarded to DuckDB via con.query(sql) in python/sqlrooms-server/sqlrooms/server/query.py:72, which can execute the stacked statement, so the supposedly readonly bridge can mutate or drop workspace tables. Parse/enforce a single SELECT before forwarding, or route through the existing select-only policy.

Useful? React with 👍 / 👎.

Clear stale input globals before binding

When a block's inputs are removed or renamed after a successful run, this loop only writes the current declarations and never deletes names bound on earlier executions. Because the Pyodide interpreter is reused, Python code can keep reading the old global (for example customers after that input was removed) and persist results from stale data instead of failing or using the new declarations; track and clear previously bound input names before binding the current set.

Useful? React with 👍 / 👎.

Preserve temporal SQL values for Python rows

When a DuckDB/Arrow DATE or TIMESTAMP reaches this converter as a JS Date, this turns it into an epoch-millisecond number before the rows are handed to Pyodide. sqlrooms.query_df() then builds pandas columns of integers instead of dates/timestamps, so time-based filters, grouping, and charts operate on corrupted types; preserve temporal values with type-aware conversion rather than dropping them to raw milliseconds.

Useful? React with 👍 / 👎.

Filter internal tables from schema inputs

When a schema input omits tableName, this query enumerates every non-pg_catalog/information_schema column, but the CLI's DuckDB schema UI later applies defaultLoadSchemaCatalogFilter and an explicit mosaic-schema filter. In workspaces with SQLRooms persistence tables, the temp catalog, or the mosaic preaggregation cache, Python cells/agents receive hidden internal tables in their schema context and can treat them as user data; apply the same catalog/table filters before grouping the schema summary.

Useful? React with 👍 / 👎.