spacr.qt.screens.db_browser

Workflow inputs and outputs

Database Browser

Browse selected tables and export rows; table inspection does not rerun image analysis.

Open: the application’s Help/tools menus.

Inputs and outputs below include conditional alternatives. The guidance and handoff notes say which route applies.

Inputs

• Measured objects — measurements/measurements.db; object tables depend on the enabled cell, nucleus, pathogen and organelle masks. Relevant tables, depending on the route: cell, nucleus, pathogen, cytoplasm. Relevant columns, depending on the route: plateID, rowID, columnID, fieldID.

Outputs

• Figures and table exports — The output location chosen by the tool; exports describe the selected data and filters.

Before this module

• Measure: Inspect actual tables before exporting.

API reference.

Module tutorial.

Database Browser — a read-only query panel for a spaCR measurements.db.

Answering “how many cells are in plate1?” or “what does the png_list table actually contain?” used to mean dropping to a terminal and typing sqlite3 .../measurements/measurements.db. This screen puts the same questions one click away, and — unless you deliberately arm edit mode — without ever giving the GUI a way to write to the file.

Layout:

┌───────────────────────────────────────────────────────────────────┐
│ /data/plate1/measurements/measurements.db   [DB…] [Run folder…]   │
│ ☐ Edit mode   Read-only — enable editing in Preferences first.    │
├──────────────┬────────────────────────────────────────────────────┤
│ Tables       │ Columns [search…]      (120 of 512 columns)        │
│  cell   40   │ ┌────────────────────────────────────────────────┐ │
│  nucleus     │ │ prc          cell_area   cell_channel_1_mean…  │ │
│  png_list    │ │ plate1_A01_1 1204.5      3311.2                │ │
│              │ └────────────────────────────────────────────────┘ │
│              │ showing 100 of ≈412 003 rows (estimate) [Load more]│
├──────────────┴────────────────────────────────────────────────────┤
│ Filter: [column ▾] [op ▾] [value]  ☐ raw SQL  [Apply] [Clear]     │
│                                              [Export filtered CSV]│
└───────────────────────────────────────────────────────────────────┘

Design notes that matter for real spaCR databases:

• Never SELECT * the whole table. Measurement tables run to hundreds of thousands of rows and hundreds of feature columns. The first chunk (100 rows) is fetched and painted on its own; the rest arrives as the user scrolls, through canFetchMore / fetchMore.

• Keyset paging, not OFFSET. Each chunk asks for rowid > <last rowid seen> and ORDER BY rowid. LIMIT ? OFFSET ? makes SQLite walk (and throw away) every skipped row, so chunk 500 of a 400 k-row table would cost 500× chunk 1 — the “fast” version would get slower the further you scrolled. Only tables with no usable key (views, composite-primary-key WITHOUT ROWID tables) fall back to OFFSET, and those are small by construction.

• The count never blocks the first paint. SELECT COUNT(*) is a full scan. The first chunk is painted against max(rowid), which is O(1), and that number is always rendered as “≈… (estimate)”. The exact COUNT(*) follows on its own job and replaces it.

• Off the GUI thread. Every chunk, count and export goes through spacr.qt.bridge.make_thread(), the same helper the pipeline screens use, and each worker opens (and closes) its own sqlite connection — sqlite3 objects are not shareable across threads. Jobs queue and run one at a time; see DbBrowserScreen._run_job() for why two PipelineWorkers must not overlap.

• Cancellable. Switching table or database bumps a load token. Results tagged with a stale token are dropped, so an in-flight load can never paint the previous table’s rows into the new view, and a job that has not started yet is dropped without spending a thread. Nothing is ever killed mid-flight.

• Read-only by default, structurally. Browsing connections are opened with the file:…?mode=ro URI and PRAGMA query_only = ON. A write is rejected by SQLite itself, not by a check we could forget. Editing is a separate, opt-in path (see below) with its own connection.

• No string-formatted values. Everything the user types is bound as a ? parameter. Identifiers (table + column names) never come from free text — they are matched against the live schema and only then double-quoted.

• No modal dialogs on any error path. Problems land in an inline status label; a headless run can never block on a message box. The single exception is the edit-mode confirmation, which is injectable (DbBrowserScreen.confirm_edit_mode).

Editing (opt-in, and guarded five ways)

An UPDATE against measurements.db is unrecoverable, so every guard below has to fail open to read-only:

1. spacr.qt.preferences.get_db_browser_editable() must be on — it is off by default and lives in Preferences, not on this screen.

2. The database must have been chosen explicitly in this session (set_database(..., explicit=True)).

3. The user must tick “Edit mode” and confirm; ticking alone does nothing.

4. The row must be addressable by rowid or a primary key. Without one, the edit is refused — an UPDATE matching on column values can hit many rows, which on a measurements table is silent mass corruption. The write also probes COUNT(*) for the row address first and rolls back unless rowcount == 1.

5. The typed text must be coercible to the column’s declared type. SQLite will cheerfully store 'abc' in an INTEGER column; coerce_for_column() refuses instead.

Loading a different database always resets edit mode to off.

Exceptions

EditRefused

An edit was rejected before anything was written.

Classes

DbBrowserScreen	Browser for a spaCR measurements database — read-only by default.
PreviewModel	Holds the rows fetched so far plus the column-visibility mask.
ReadOnlyDb	A read-only handle on a sqlite database.
WritableDb	A read-write handle used only by an armed edit mode.

Functions

build_update(→ str)	Return the one statement this screen is ever willing to run.
build_where(→ Tuple[str, tuple])	Build a (where_sql, params) pair from the structured filter row.
coerce_for_column(→ Any)	Return the value to bind for text in a column of decl_type.
column_affinity(→ str)	Return SQLite's type affinity for a declared column type.
install_folds(...)	Put db_browser's fold strip on screen's masthead.
quote_ident(→ str)	Double-quote a SQL identifier, escaping embedded quotes.
resolve_db_path(→ str)	Return the absolute path of the sqlite file path refers to.
validate_raw_predicate(→ str)	Return text stripped, or raise if it is not a lone predicate.

Module Contents

exception spacr.qt.screens.db_browser.EditRefused

Bases: Exception

An edit was rejected before anything was written.

Distinct from sqlite3.Error so the screen can tell “we refused this” (a guard fired, nothing happened) from “SQLite refused this” (a constraint, a locked file).

Initialize self. See help(type(self)) for accurate signature.

class spacr.qt.screens.db_browser.DbBrowserScreen(parent=None, threaded: bool = True)

Bases: spacr.qt.linked_selection.LinkedView, PySide6.QtWidgets.QWidget

Browser for a spaCR measurements database — read-only by default.

Joined to the shared selection as "db_browser", in both directions:

• selecting rows publishes them, so a row picked out here lights up on the plate heatmap and in the UMAP;

• a selection published elsewhere selects and scrolls to the same rows;

• a filter published elsewhere HIDES rows, which a selection never does.

All three act on the rows already fetched. Nothing here re-queries: the shared filter is a lens over the page in memory, and the row-count label keeps saying how much of the table that page is.

Parameters:

• parent – optional Qt owner responsible for the screen’s lifetime.

• threaded – run queries on a worker thread (the default). Tests pass False to get deterministic, synchronous behaviour.

Variables:

• last_error – text of the most recent failure, "" when the last operation succeeded. Errors are only ever reported here and in the inline status label — never in a modal dialog.

• confirm_edit_mode – callable taking the confirmation text and returning a bool. Replace it to arm edit mode without a dialog (every test does). The default opens the one QMessageBox this screen owns.

• auto_count – when True (the default) the exact COUNT(*) follows the first chunk on its own job. Set False to browse a very large table on the max(rowid) estimate alone.

Build the browser: the table list, the preview and the controls.

Parameters:

parent – parent widget.

active_jobs() → int

How many query/export threads are still winding down.

apply_filter() → bool

Read the filter row, validate it, and reload from the first chunk.

Returns False (and reports inline) when the filter is malformed.

apply_seed(seed: Dict[str, Any]) → None

Open a database (and optionally a table) another screen sent here.

The generic hand-off seam MainWindow._on_train_requested looks for. Everything is optional and anything unusable is ignored rather than raised: this is a convenience jump, and a screen that refuses to open because a seed was stale is worse than one that opens on the wrong table.

Parameters:

seed – db_path, and optionally table and column.

clear_filter() → None

Drop the WHERE clause and reload.

closeEvent(event)

Let every in-flight query thread finish before the widget dies.

Destroying a QThread that is still running aborts the process, so we wait (briefly) rather than hope. Jobs that have not started yet are dropped — nothing has been spawned for them.

The shared link outlives this screen, so let go of it too.

Parameters:

event – Qt close event forwarded after worker cleanup.

current_table() → str

Which table the browser is showing.

Returns:

the table’s name, or "" when none is open.

database_path() → str

Path of the open database, or ''.

disable_edit_mode(quiet: bool = False) → None

Drop back to read-only. Safe to call when already read-only.

Parameters:

quiet – suppress the read-only status message when true.

edit_cell(row: int, column: str, text: Any) → bool

Write one cell of one row. Returns False, with a reason, if refused.

This is the only path in the screen that can write, and it is also what PreviewModel.setData() calls when a cell editor closes.

Parameters:

• row – zero-based loaded model row to address.

• column – schema column whose value the user edited.

• text – raw editor value to coerce against the declared type.

Returns:

whether exactly one database row was updated or already held the requested value.

edit_mode_enabled() → bool

True when this screen currently holds a licence to write.

editing_allowed_by_preference() → bool

Whether Preferences permits edit mode at all (read fresh).

enable_edit_mode() → bool

Arm edit mode for the open database, if every guard allows it.

Requires the Preferences opt-in, a database the user chose in this session, and an explicit confirmation. Returns False and explains inline otherwise; never raises.

export_csv(out_path: str) → bool

Write the current table + filter + visible columns to out_path.

Runs off the GUI thread and streams the result, so a 400 000-row export neither freezes the window nor loads the table into memory. Reports inline on failure and returns False.

Parameters:

out_path – destination CSV path selected by the user.

Returns:

whether the export job was accepted for execution.

fetch_more() → bool

Fetch the next chunk. Called by the view when it scrolls to the end.

Returns False when there is nothing to fetch or a chunk is already in flight — a scroll must never queue ten of them.

hidden_rows() → List[int]

Model rows the shared filter is hiding, ascending.

is_busy() → bool

True while any query, count or export job is queued or running.

is_fully_loaded() → bool

True when every row of the current table + filter is in memory.

loaded_rows() → int

How many rows have been fetched so far.

on_linked_filter_changed(data_filter: spacr.selection.DataFilter) → None

Apply a newly published shared filter to the loaded rows.

Parameters:

data_filter – current process-wide filter; the linked-view state already owns it, so this callback only refreshes row visibility.

on_linked_selection_changed(selection: spacr.selection.Selection) → None

Select and scroll to the rows somebody else picked.

Nothing is hidden: rows the selection does not name stay exactly where they are. Only the shared filter removes rows from view.

Parameters:

selection – process-wide object selection published elsewhere.

page_size() → int

Rows fetched per chunk.

pending_edit_sql() → str

The last statement shown to the user (test/introspection helper).

preview_columns() → List[str]

Every column of the loaded rows, ignoring the column search.

preview_rows() → List[tuple]

Rows loaded so far, as tuples in schema-column order.

queued_jobs() → int

How many jobs are waiting for the worker to free up.

refresh() → None

Abandon any load in flight and read the first chunk again.

refresh_count() → None

Replace the estimate with an exact COUNT(*).

row_count() → int

Best known total for the current table + filter.

Exact once COUNT(*) has landed or the table has been read to the end; otherwise the max(rowid) estimate, or the number of rows loaded so far. Ask row_count_is_estimate() before quoting it as a fact.

row_count_is_estimate() → bool

True while row_count() is not known to be exact.

rows_for_selection(selection: spacr.selection.Selection) → List[int]

The loaded rows selection names, ascending.

Empty — not an error — for a table with no object identity in it (png_list keyed on a path, a summary, a view). A shared selection does not identify those rows.

Parameters:

selection – shared object identity selection to match.

select_rows(rows: Sequence[int]) → List[int]

Select rows as a user would, publishing them to every view.

Parameters:

rows – zero-based model row indices requested by the caller.

Returns:

the rows that were in range and got selected.

select_table(name: str) → bool

Make name the previewed table and reset load and filter state.

Parameters:

name – live table or view name from the open database.

Returns:

whether the schema was read and the table load was started.

selected_rows() → List[int]

The rows the user currently has selected, ascending.

set_column_filter(text: str) → None

Narrow the displayed columns to those containing text.

Purely a view operation — no re-query — so it stays instant on a table with hundreds of feature columns. An empty string restores every column.

Parameters:

text – case-insensitive substring required in visible names.

set_database(path: str, explicit: bool = True) → bool

Open path read-only and list its tables.

Accepts the database file or a run src folder. Any problem (missing file, not a sqlite database, unreadable) is reported in the status label and returns False — this never raises.

Always resets edit mode to off: a database the user armed for editing is that database, never the next one.

Parameters:

• path – SQLite database file or run src directory containing one; paths are expanded and resolved by ReadOnlyDb.

• explicit – True when the user chose this database themselves. Pass False when spaCR opens one on their behalf (a remembered path, a folder handed over by another screen); such a database can be browsed but never edited.

Returns:

True when a database was opened.

set_filter(column: str, op: str, value: Any = '') → bool

Programmatic equivalent of filling in the filter row and applying.

Parameters:

• column – live schema column to filter.

• op – structured operator label from OPERATORS.

• value – raw filter value; operators such as is null ignore it.

Returns:

whether validation succeeded and the reload was started.

set_raw_filter(predicate: str) → bool

Programmatic equivalent of the raw-SQL box and Apply.

Parameters:

predicate – lone read-only WHERE-clause fragment.

Returns:

whether validation succeeded and the reload was started.

sql_text() → str

The statement shown to the user before it runs (or '').

status_text() → str

Current inline status message (test/introspection helper).

tables() → List[str]

Table names currently listed in the sidebar.

visible_columns() → List[str]

Column names currently shown in the preview.

visible_rows() → List[int]

Model rows the shared filter keeps on screen, ascending.

where_clause() → str | None

The active WHERE fragment, or None.

class spacr.qt.screens.db_browser.PreviewModel(parent=None)

Bases: PySide6.QtCore.QAbstractTableModel

Holds the rows fetched so far plus the column-visibility mask.

Two jobs beyond the obvious one:

• Incremental fetch. canFetchMore/fetchMore are the Qt way to say “there is more where that came from”; the view calls them when the user scrolls to the bottom and the model asks the screen for another chunk. While a chunk is in flight canFetchMore is False, so a scroll cannot queue ten of them.

• Row identity. Every row carries the key tuple it was fetched with (rowid, or the primary key). That is what makes an edit addressable — and its absence is what makes an edit refusable.

Chunks are fetched with every column, and the column search only changes which of them are mapped into the view. Typing in the search box therefore never re-queries the database — which is what makes it usable on a table with 500 feature columns.

Parameters:

parent – optional Qt owner responsible for the model’s lifetime.

Build an empty preview.

all_columns() → List[str]

Every column the table has, filtered or not.

Returns:

the column names.

append_rows(rows: Sequence[Sequence[Any]], keys: Sequence[tuple | None] | None = None) → int

Add a fetched chunk to the end and return how many rows landed.

Parameters:

• rows – complete schema-order rows to append.

• keys – stable row addresses paired with rows; None supplies an uneditable None key for every appended row.

canFetchMore(parent=QModelIndex()) → bool

Return whether the root model can request another chunk.

Parameters:

parent – Qt parent index; valid child indexes cannot fetch.

clear() → None

Drop every row and column.

columnCount(parent=QModelIndex()) → int

Return visible columns for the root model and zero for children.

Parameters:

parent – Qt parent index whose child-column count is requested.

column_filter() → str

The text currently filtering the columns.

Returns:

the filter text.

data(index, role=Qt.DisplayRole)

Return the display, tooltip, or exact editor value for a cell.

Parameters:

• index – model index identifying the requested cell.

• role – Qt data role; unsupported roles return None.

fetchMore(parent=QModelIndex()) → None

Request the next chunk through the configured fetch hook.

Parameters:

parent – Qt parent index; valid child indexes are ignored.

flags(index)

Return Qt item flags for index, including armed editability.

Parameters:

index – model index whose interaction flags are requested.

headerData(section, orientation, role=Qt.DisplayRole)

Return a column name or one-based absolute row number.

Parameters:

• section – zero-based visible column or row-header section.

• orientation – horizontal for names, vertical for row numbers.

• role – Qt data role; only Qt.DisplayRole is served.

is_editable() → bool

Whether this preview allows edits.

OFF UNLESS CHOSEN. The browser reads a project’s real measurements, and an accidental edit there is a silent change to data a run already produced.

Returns:

True when editing is enabled.

rowCount(parent=QModelIndex()) → int

Return loaded rows for the root model and zero for children.

Parameters:

parent – Qt parent index whose child-row count is requested.

row_key(row: int) → tuple | None

Return the key tuple addressing row, or None.

Parameters:

row – zero-based model row index.

rows() → List[tuple]

The raw rows loaded so far, with every column (search-independent).

setData(index, value, role=Qt.EditRole) → bool

Commit an editor value through the guarded screen callback.

Parameters:

• index – valid editable model index.

• value – raw editor value passed to the commit hook.

• role – Qt role, which must be Qt.EditRole.

Returns:

whether the commit hook accepted and wrote the change.

set_column_filter(text: str) → None

Show only columns whose name contains text (case-insensitive).

An empty string restores every column.

Parameters:

text – case-insensitive substring required in visible names.

set_commit_hook(hook: Callable[[int, str, Any], bool] | None) → None

Set what setData() calls to actually write a cell.

Parameters:

hook – (row, column, value) writer, or None to disable it.

set_editable(editable: bool) → None

Turn cell editing on or off and repaint the item flags.

Parameters:

editable – whether valid cells advertise Qt’s editable flag.

set_fetch_hook(hook: Callable[[], Any] | None) → None

Set what fetchMore() calls to ask for the next chunk.

Parameters:

hook – zero-argument fetch request, or None to disconnect it.

set_more(more: bool) → None

Say whether another chunk can be fetched right now.

Parameters:

more – true only while an unfetched chunk remains available.

set_page(columns: Sequence[str], rows: Sequence[Sequence[Any]], row_offset: int = 0, keys: Sequence[tuple | None] | None = None) → None

Replace the model contents, keeping the current column search.

Parameters:

• columns – schema-order column names represented by every row.

• rows – complete values for the newly loaded page.

• row_offset – zero-based database offset used for row headings.

• keys – stable row addresses, or None for uneditable rows.

set_value(row: int, column: str, value: Any) → bool

Write a value into the in-memory page after a real UPDATE.

Parameters:

• row – zero-based model row index.

• column – schema column whose displayed value changed.

• value – already-coerced value written to the database.

Returns:

whether the addressed cell exists and was updated.

sort(column: int, order=Qt.AscendingOrder) → None

Sort the loaded rows in memory.

The screen only enables view sorting once the whole table is loaded, so this never sorts a partial result and calls it the table’s order. Keys travel with their rows, so an edit after a sort still addresses the row it looks like it addresses.

Parameters:

• column – zero-based visible column to sort by.

• order – Qt sort order, defaulting to Qt.AscendingOrder.

value(row: int, column: str) → Any

Return the stored, unformatted value at row / column.

Parameters:

• row – zero-based model row index.

• column – schema column name.

visible_columns() → List[str]

The columns the filter is currently letting through.

Returns:

the visible column names.

class spacr.qt.screens.db_browser.ReadOnlyDb(path: str)

A read-only handle on a sqlite database.

Every method opens (and closes) its own connection. That costs a few microseconds and buys thread-safety for free: a chunk query runs on a worker thread while the GUI thread may still be listing tables, and sqlite3 objects are not shareable across threads.

Parameters:

path – database file, or a run folder — see resolve_db_path().

Raises:

sqlite3.DatabaseError – when the file is not a sqlite database.

Open a database for reading only.

Parameters:

path – the database file.

check_columns(table: str, columns: Sequence[str] | None) → List[str]

Return the requested columns, validated against the schema.

None or an empty sequence means “all of them”.

Parameters:

• table – validated user-table or view name.

• columns – requested names, or None/an empty sequence for the complete schema.

Returns:

validated names in requested or declaration order.

Raises:

ValueError – when any requested name is absent.

check_table(table: str) → str

Return table if the schema really has it, else raise.

This is the gate that keeps identifiers out of the “user input” category: a name that isn’t in sqlite_master never reaches SQL.

Parameters:

table – candidate user-table or view name.

Returns:

table unchanged after validation.

Raises:

ValueError – when the live schema has no such table or view.

chunk(table: str, columns: Sequence[str] | None = None, where: str | None = None, params: Sequence = (), limit: int = DEFAULT_PAGE_SIZE, after: tuple | None = None, loaded: int = 0, order_by: Tuple[str, bool] | None = None) → Tuple[List[str], List[tuple], List[tuple | None]]

Return (columns, rows, keys) for the next limit rows.

after is the key tuple of the last row already loaded; None asks for the first chunk. keys[i] addresses rows[i] uniquely (or is None when the table has no key, in which case the caller must refuse to edit it).

Parameters:

• table – validated user-table name to page through. Its schema determines both the returned columns and each row’s stable key.

• columns – requested schema columns, or None/an empty sequence for every column.

• where – optional validated predicate without WHERE.

• params – bound values consumed by predicate placeholders.

• limit – maximum number of rows requested for this chunk.

• after – stable key tuple of the last row already loaded, or None for the first chunk.

• loaded – only used by the OFFSET fallback for tables with no single-column key.

• order_by – optional (column, descending) whole-table order.

chunk_sql(table: str, columns: Sequence[str], key_columns: Sequence[str], where: str | None = None, after: bool = False, use_offset: bool = False, order_by: Tuple[str, bool] | None = None) → str

Build the paged chunk SELECT. Exposed so tests can read it.

after says whether a key > ? clause is wanted (i.e. this is not the first chunk). use_offset is the fallback for tables that have no single-column key.

order_by is (column, descending) when the user has clicked a column header. It sorts in SQL, over the whole table, which is the only way to sort a table the view has only partly loaded – sorting the rows fetched so far and presenting that as the table’s order is the one option worse than not sorting at all.

Two details that are not optional:

• the key column is appended as a tiebreak. Without it, rows sharing a value come back in whatever order SQLite happens to produce, and that order is free to differ between two chunks of the same scroll – so a row could be shown twice and another not at all.

• paging falls back to OFFSET. Keyset paging needs the ordering column to be the one being compared, and (value, rowid) > (?, ?) over an arbitrary user-chosen column is a different and much more delicate query. OFFSET makes deep scrolling of a sorted view progressively slower, which is a real cost and the reason it is not the default; it is bounded here because a sort is an explicit act on a table the user is looking at.

Parameters:

• table – schema-validated source table or view.

• columns – visible data columns to select after the key columns.

• key_columns – stable row-address columns prepended to each row.

• where – optional validated predicate without WHERE.

• after – whether to add the next-page key comparison.

• use_offset – use OFFSET paging when no single key is available.

• order_by – optional (column, descending) whole-table order.

Returns:

parameterized SELECT containing a bounded LIMIT and, when required, OFFSET.

column_types(table: str) → Dict[str, str]

Return {column: declared type} — '' for untyped columns.

Cached: the edit path asks for this on every keystroke-committed cell, and the schema cannot change under a read-only connection.

Parameters:

table – validated user-table or view name.

columns(table: str) → List[str]

Return the column names of table in declaration order.

Parameters:

table – validated user-table or view name.

connect() → sqlite3.Connection

Return a fresh read-only connection. The caller closes it.

count(table: str, where: str | None = None, params: Sequence = ()) → int

Return the exact COUNT(*) under the current filter.

This one is a full scan — never call it on the path that paints the first chunk.

Parameters:

• table – schema-validated table or view to count.

• where – optional validated predicate without WHERE.

• params – bound values consumed by the predicate placeholders.

estimate_count(table: str) → int | None

Return an O(1) estimate of the row count, or None.

max(rowid) is answered from the right-hand edge of the table’s b-tree without scanning, which is the whole point: a real COUNT(*) on a 400 k-row measurement table takes long enough to be felt. It is only an estimate — deleted rows leave gaps — so every caller must label it as one.

None when the table has no rowid, or is empty.

Parameters:

table – validated table or view to estimate.

export_csv(out_path: str, table: str, columns: Sequence[str] | None = None, where: str | None = None, params: Sequence = (), chunk: int = 5000) → int

Stream the filtered result to out_path as CSV.

Rows are pulled in chunk-sized batches and written straight out, so exporting a 400 k-row table costs a constant amount of memory.

Parameters:

• out_path – destination CSV path; missing parent folders are made.

• table – schema-validated table or view to export.

• columns – selected columns, or None for every column.

• where – optional validated predicate without WHERE.

• params – bound values consumed by predicate placeholders.

• chunk – cursor batch size used while streaming rows.

Returns:

number of data rows written (header excluded).

row_key(table: str) → Tuple[str, List[str]]

Return how one row of table can be addressed uniquely.

("rowid", ["rowid"]) for an ordinary table, ("pk", [...]) for a WITHOUT ROWID table (its declared primary key), and ("", []) for anything with neither — a view, or a table created WITHOUT ROWID with no primary key.

Two things hang off this: paging (keyset needs an ordered key) and editing (an UPDATE without a unique address is refused).

Parameters:

table – validated table or view whose identity is inspected.

select_sql(table: str, columns: Sequence[str], where: str | None = None) → str

Build the unbounded SELECT used by the CSV export.

Parameters:

• table – schema-validated source table or view.

• columns – schema-validated columns to export.

• where – optional validated predicate without the WHERE word.

Returns:

SELECT statement containing no user-formatted values.

table_info(table: str) → List[tuple]

Return raw PRAGMA table_info rows for table.

Parameters:

table – validated user-table or view name.

tables(refresh: bool = False) → List[str]

Return the user tables and views alphabetically.

Parameters:

refresh – bypass the cached schema inventory when true.

validate_where(table: str, where: str, params: Sequence = ()) → None

Let SQLite parse where and raise if it is malformed.

Prepared with LIMIT 0, which SQLite short-circuits before it touches a single row — so a typo costs a parse, not a full scan of a 400 000-row measurement table.

Parameters:

• table – schema-validated table or view used for name resolution.

• where – predicate fragment for SQLite to parse.

• params – bound values consumed by the predicate placeholders.

Raises:

sqlite3.Error – with SQLite’s own message (no such column: cell_are, near ">": syntax error, …).

class spacr.qt.screens.db_browser.WritableDb(path: str)

A read-write handle used only by an armed edit mode.

Deliberately tiny: it knows how to write one cell of one row and nothing else. There is no execute(), no DDL, no multi-row update — the class has no method that could touch more than a single row.

Parameters:

path – database file, or a run folder — see resolve_db_path().

Open a database for reading and writing.

Parameters:

path – the database file.

connect() → sqlite3.Connection

Return a fresh read-write connection in autocommit mode.

isolation_level = None hands transaction control back to us, so the BEGIN/COMMIT/ROLLBACK in update_cell() are the real ones and do not fight Python’s implicit transaction handling (which differs between 3.11 and 3.12+).

update_cell(table: str, column: str, value: Any, key_columns: Sequence[str], key_values: Sequence[Any]) → str

Set one column of one row. Returns the SQL that ran.

The guards, in order:

1. table must be a real table in sqlite_master — a view has no row to update.

2. column and every key column must exist in its schema.

3. the row address must match exactly one row (probed with COUNT(*) before anything is written);

4. the UPDATE itself must report rowcount == 1, or the transaction is rolled back.

Parameters:

• table – real schema table receiving the change.

• column – existing table column whose value will be replaced.

• value – already-coerced value to bind to the UPDATE.

• key_columns – rowid alias or primary-key columns addressing it.

• key_values – bound address values paired with key_columns.

Returns:

exact parameterized SQL statement that ran.

Raises:

• EditRefused – when any guard fires — nothing was written.

• sqlite3.Error – when SQLite refuses the write itself (constraint violation, read-only file, locked database).

spacr.qt.screens.db_browser.build_update(table: str, column: str, key_columns: Sequence[str]) → str

Return the one statement this screen is ever willing to run.

UPDATE "t" SET "c" = ? WHERE "rowid" = ? — one column, one row address, everything bound. Exposed so the screen can show the user the exact SQL before it runs, and so tests can assert on it without a database.

Parameters:

• table – schema-validated table to update.

• column – schema-validated column whose value will be replaced.

• key_columns – rowid alias or primary-key columns identifying one row.

Returns:

parameterized single-cell UPDATE statement.

Raises:

EditRefused – when there is no row address at all. An UPDATE without a unique key would match on values and could rewrite thousands of rows.

spacr.qt.screens.db_browser.build_where(column: str, op: str, value: Any, columns: Sequence[str]) → Tuple[str, tuple]

Build a (where_sql, params) pair from the structured filter row.

Parameters:

• column – column name; must appear in columns.

• op – one of OPERATORS.

• value – raw text from the value field — bound, never formatted.

• columns – the table’s real column list, from the schema.

Raises:

ValueError – for an unknown column or operator.

spacr.qt.screens.db_browser.coerce_for_column(text: Any, decl_type: str | None, column: str = 'value') → Any

Return the value to bind for text in a column of decl_type.

SQLite has no static typing: binding the string "abc" to an INTEGER column stores the text “abc” there, and every downstream pandas.read_sql then gets an object column where it expected a number. This function refuses instead.

• empty text → None (SQL NULL). A NOT NULL column then raises from SQLite itself and the write is rolled back.

• INTEGER affinity → int, or ValueError.

• REAL affinity → float, or ValueError.

• TEXT affinity → str, always.

• NUMERIC affinity / no declared type → int, else float, else str (which is exactly what SQLite itself would store).

• anything declared BLOB → refused; binary is not editable as text.

Parameters:

• text – editor value to convert before binding it to SQLite.

• decl_type – declared type of the destination column.

• column – destination column name used in actionable error messages.

Raises:

ValueError – when text cannot be represented in the column’s type.

spacr.qt.screens.db_browser.column_affinity(decl_type: str | None) → str

Return SQLite’s type affinity for a declared column type.

The five rules are the ones in the SQLite file-format spec, in order: INT → INTEGER, CHAR/CLOB/TEXT → TEXT, BLOB or no type at all → BLOB, REAL/FLOA/DOUB → REAL, otherwise NUMERIC. Knowing the affinity is what lets coerce_for_column() refuse a value SQLite would otherwise store with the wrong type.

Parameters:

decl_type – declared SQLite column type, or None/an empty string when untyped.

Returns:

INTEGER, TEXT, BLOB, REAL, or NUMERIC.

spacr.qt.screens.db_browser.install_folds(screen: PySide6.QtWidgets.QWidget) → spacr.qt.widgets.fold_strip.FoldStrip | None

Put db_browser’s fold strip on screen’s masthead.

Reached by the one pass over the stack that serves every host – see spacr.qt.screens.map_barcodes.FOLD_HOST_MODULES.

spacr.qt.screens.db_browser.quote_ident(name: str) → str

Double-quote a SQL identifier, escaping embedded quotes.

Only ever called with a name that has already been matched against the live schema; the quoting is belt-and-braces for identifiers that are legal but awkward (cell_channel_1 (raw)).

Parameters:

name – schema-validated table or column identifier.

Returns:

identifier surrounded by SQL double quotes.

spacr.qt.screens.db_browser.resolve_db_path(path: str) → str

Return the absolute path of the sqlite file path refers to.

Accepts either the database file itself or a run src folder, in which case <src>/measurements/measurements.db is used — the same layout every other spaCR module assumes (see spacr.measure and spacr.ml). <src>/measurements.db is accepted as a fallback so pointing at the measurements folder itself also works.

Parameters:

path – file or folder chosen by the user.

Returns:

absolute path to an existing file.

Raises:

• ValueError – when path is empty.

• FileNotFoundError – when nothing resolvable exists there.

spacr.qt.screens.db_browser.validate_raw_predicate(text: str) → str

Return text stripped, or raise if it is not a lone predicate.

The raw box is an explicit power-user escape hatch (cell_area > 1000 AND well LIKE 'A%'). We refuse statement separators, comments and DDL/DML keywords so it stays a predicate; the read-only connection plus PRAGMA query_only guarantee the rest.

Parameters:

text – raw WHERE-clause fragment entered by the user.

Returns:

stripped predicate after the safety checks.

Raises:

ValueError – when the fragment is empty or contains statement syntax, comments, or a forbidden SQL keyword.

Nested helpers

DbBrowserScreen._fetch_chunk._job() → Dict[str, Any]

Read one chunk of the table. Off the GUI thread.

spacr/qt/screens/db_browser.py:2066

DbBrowserScreen._start_count._job() → Dict[str, Any]

Count the matching rows, carrying the token that ordered it.

The token comes back with the answer so a slow count from a query the user has since changed can be recognised and dropped.

spacr/qt/screens/db_browser.py:2128

DbBrowserScreen.export_csv._done(result: Dict[str, Any]) → None

Say how many rows were exported, and where.

spacr/qt/screens/db_browser.py:2792

DbBrowserScreen.export_csv._job() → Dict[str, Any]

Write the CSV. Off the GUI thread.

spacr/qt/screens/db_browser.py:2785