{
 "cells": [
  {
   "cell_type": "raw",
   "id": "4821f2ac",
   "metadata": {
    "vscode": {
     "languageId": "raw"
    }
   },
   "source": [
    "---\n",
    "title: \"Working with the Pixeltable CLI\"\n",
    "icon: \"notebook\"\n",
    "description: \"[Open in Kaggle](https://kaggle.com/kernels/welcome?src=https://github.com/pixeltable/pixeltable/blob/release/docs/release/howto/cookbooks/core/working-with-cli.ipynb) | [Open in Colab](https://colab.research.google.com/github/pixeltable/pixeltable/blob/release/docs/release/howto/cookbooks/core/working-with-cli.ipynb) | [View on GitHub](https://github.com/pixeltable/pixeltable/blob/release/docs/release/howto/cookbooks/core/working-with-cli.ipynb)\"\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c3ccd8d",
   "metadata": {},
   "source": [
    "## Problem\n",
    "\n",
    "You defined a multimodal pipeline in Python and now need to inspect tables, debug computed columns, roll back changes, and expose HTTP endpoints without writing more application code. Jumping into a REPL or building a custom admin UI for every project does not scale, especially when AI agents need stable, machine-readable output."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42cca2b0",
   "metadata": {},
   "source": [
    "## Solution\n",
    "\n",
    "**What's in this recipe:**\n",
    "\n",
    "- Inspect catalogs with `pxt ls`, `describe`, `columns`, and `idxs`\n",
    "- Query and debug rows with `pxt rows`, `count`, `get`, and `errors`\n",
    "- Manage versions with `pxt history` and `pxt revert`\n",
    "- Script and automate with `--json`, `-f`, and `pxt shell`\n",
    "- Validate declarative HTTP serving with `pxt serve --dry-run`\n",
    "\n",
    "The `pxt` CLI ships with Pixeltable (v0.6.5+). Catalog commands talk to a local daemon (~40 ms per call after the first invocation). Use Python to define schema once, then operate the catalog from the terminal.\n",
    "\n",
    "See the [CLI reference](https://docs.pixeltable.com/platform/cli) for every flag."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7952d420",
   "metadata": {},
   "source": [
    "### Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a8eb132e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:04.888546Z",
     "iopub.status.busy": "2026-06-12T23:39:04.888220Z",
     "iopub.status.idle": "2026-06-12T23:39:07.273654Z",
     "shell.execute_reply": "2026-06-12T23:39:07.273174Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install -qU 'pixeltable[serve]'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "89db5d46",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:07.276092Z",
     "iopub.status.busy": "2026-06-12T23:39:07.275919Z",
     "iopub.status.idle": "2026-06-12T23:39:08.387085Z",
     "shell.execute_reply": "2026-06-12T23:39:08.386807Z"
    },
    "output": false
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import pixeltable as pxt\n",
    "import subprocess\n",
    "from pixeltable.functions.video import frame_iterator\n",
    "\n",
    "\n",
    "def pxt_json(*args: str) -> object:\n",
    "    \"\"\"Run pxt with --json and parse stdout.\"\"\"\n",
    "    return json.loads(subprocess.check_output(['pxt', *args], text=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6911926b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:08.389013Z",
     "iopub.status.busy": "2026-06-12T23:39:08.388855Z",
     "iopub.status.idle": "2026-06-12T23:39:09.703457Z",
     "shell.execute_reply": "2026-06-12T23:39:09.703179Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/private/var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home/pgdata\n",
      "Created directory 'cli_demo'.\n",
      "Created table 'videos'.\n",
      "Added 0 column values with 0 errors in 0.00 s\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inserted 20 rows with 0 errors in 0.89 s (22.39 rows/s)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "20 rows inserted."
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SAMPLE_VIDEO = 'https://raw.githubusercontent.com/pixeltable/pixeltable/release/docs/resources/bangkok.mp4'\n",
    "\n",
    "pxt.drop_dir('cli_demo', force=True)\n",
    "pxt.create_dir('cli_demo')\n",
    "\n",
    "videos = pxt.create_table(\n",
    "    'cli_demo/videos', {'video': pxt.Video, 'title': pxt.String}\n",
    ")\n",
    "frames = pxt.create_view(\n",
    "    'cli_demo/frames',\n",
    "    videos,\n",
    "    iterator=frame_iterator(videos.video, fps=1),\n",
    ")\n",
    "frames.add_computed_column(thumb=frames.frame.thumbnail((320, 180)))\n",
    "\n",
    "videos.insert([{'video': SAMPLE_VIDEO, 'title': 'Bangkok'}])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b8bfb9c",
   "metadata": {},
   "source": [
    "### Step 1: Inspect the catalog\n",
    "\n",
    "List directories and tables, then drill into schema and computed columns. Flag letters in `pxt ls -l`: `c` = computed column, `i` = index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c6ed9ab6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:09.705308Z",
     "iopub.status.busy": "2026-06-12T23:39:09.705113Z",
     "iopub.status.idle": "2026-06-12T23:39:10.885760Z",
     "shell.execute_reply": "2026-06-12T23:39:10.885283Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "path             kind   cols  version  flags\r\n",
      "cli_demo/frames  view      6        2  c\r\n",
      "cli_demo/videos  table     2        1  i\r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "table 'cli_demo/videos'\r\n",
      "\r\n",
      " Column Name    Type  Source Computed With Comment\r\n",
      "--------------------------------------------------\r\n",
      "       video   Video  videos                      \r\n",
      "       title  String  videos                      \r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cli_demo/frames\tthumb\tRequired[Image]\tcomputed\tframe.thumbnail([320, 180])\r\n"
     ]
    }
   ],
   "source": [
    "!pxt ls -l cli_demo\n",
    "!pxt describe cli_demo/videos\n",
    "!pxt columns cli_demo/frames --computed\n",
    "!pxt idxs cli_demo/frames"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "207c8b58",
   "metadata": {},
   "source": [
    "### Step 2: Query rows\n",
    "\n",
    "Peek at stored data from the terminal. Pass computed columns explicitly with `--cols`; unstored computed columns are skipped by default. Thumbnails may take a moment to compute after insert."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4fc1e845",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:10.887843Z",
     "iopub.status.busy": "2026-06-12T23:39:10.887708Z",
     "iopub.status.idle": "2026-06-12T23:39:11.495885Z",
     "shell.execute_reply": "2026-06-12T23:39:11.495499Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19\r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pos\tthumb\r\n",
      "0\t<Image 320x180 RGB>\r\n"
     ]
    }
   ],
   "source": [
    "!pxt count cli_demo/frames\n",
    "!pxt rows cli_demo/frames -n 1 --cols pos,thumb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8d78ba5",
   "metadata": {},
   "source": [
    "### Step 3: Debug computed-column failures\n",
    "\n",
    "When a stored computed column fails, `pxt errors` lists the failing rows by primary key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3bfa7651",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:11.498149Z",
     "iopub.status.busy": "2026-06-12T23:39:11.497763Z",
     "iopub.status.idle": "2026-06-12T23:39:11.840486Z",
     "shell.execute_reply": "2026-06-12T23:39:11.839988Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created table 'failures'.\n",
      "Added 0 column values with 0 errors in 0.01 s\n",
      "Inserted 2 rows with 2 errors across 2 columns (failures.result, failures.None) in 0.00 s (400.31 rows/s)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{k: 0}\tresult\tValueError\tboom\r\n"
     ]
    }
   ],
   "source": [
    "\n",
    "@pxt.udf\n",
    "def boom_if_zero(x: int) -> int:\n",
    "    if x == 0:\n",
    "        raise ValueError('boom')\n",
    "    return x\n",
    "\n",
    "\n",
    "failures = pxt.create_table(\n",
    "    'cli_demo/failures', {'k': pxt.Required[pxt.Int]}, primary_key='k'\n",
    ")\n",
    "failures.add_computed_column(\n",
    "    result=boom_if_zero(failures.k), on_error='ignore'\n",
    ")\n",
    "failures.insert([{'k': 0}, {'k': 1}], on_error='ignore')\n",
    "\n",
    "!pxt errors cli_demo/failures"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1da4efe6",
   "metadata": {},
   "source": [
    "### Step 4: Version control\n",
    "\n",
    "Every insert and schema change creates a new table version. Inspect the timeline, then roll back if needed. See [Track changes and revert](/howto/cookbooks/core/version-control-history) for the Python API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bc19900b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:11.842629Z",
     "iopub.status.busy": "2026-06-12T23:39:11.842458Z",
     "iopub.status.idle": "2026-06-12T23:39:12.747785Z",
     "shell.execute_reply": "2026-06-12T23:39:12.747193Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 1 column value with 0 errors in 0.02 s (51.20 rows/s)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "version\tcreated_at\tchange_type\tinserts\tupdates\tdeletes\terrors\tschema_change\r\n",
      "2\t2026-06-12T23:39:11.848683Z\tschema\t0\t1\t0\t0\tAdded: label\r\n",
      "1\t2026-06-12T23:39:08.810158Z\tdata\t20\t0\t0\t0\t\r\n",
      "0\t2026-06-12T23:39:08.490587Z\tschema\t0\t0\t0\t0\tInitial Version\r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "reverted cli_demo/videos: v2 -> v1\r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "version\tcreated_at\tchange_type\tinserts\tupdates\tdeletes\terrors\tschema_change\r\n",
      "1\t2026-06-12T23:39:08.810158Z\tdata\t20\t0\t0\t0\t\r\n",
      "0\t2026-06-12T23:39:08.490587Z\tschema\t0\t0\t0\t0\tInitial Version\r\n"
     ]
    }
   ],
   "source": [
    "videos.add_computed_column(label=videos.title.upper())\n",
    "\n",
    "!pxt history cli_demo/videos -n 5\n",
    "!pxt revert cli_demo/videos -f\n",
    "!pxt history cli_demo/videos -n 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5934ec6e",
   "metadata": {},
   "source": [
    "### Step 5: Agent-friendly scripting\n",
    "\n",
    "Most catalog commands accept `--json` for stable, machine-readable output. Use `-f` to skip confirmation prompts in non-interactive contexts.\n",
    "\n",
    "For many commands in one session, `pxt shell` keeps the daemon warm:\n",
    "\n",
    "```bash\n",
    "pxt shell\n",
    "pxt> ls cli_demo\n",
    "pxt> count cli_demo/frames\n",
    "pxt> exit\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "17a5304a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:12.750812Z",
     "iopub.status.busy": "2026-06-12T23:39:12.750632Z",
     "iopub.status.idle": "2026-06-12T23:39:12.891464Z",
     "shell.execute_reply": "2026-06-12T23:39:12.891043Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tables: ['cli_demo/failures', 'cli_demo/videos']\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "frame count: 0\n"
     ]
    }
   ],
   "source": [
    "tables = [\n",
    "    e['path']\n",
    "    for e in pxt_json('ls', 'cli_demo', '--json')['entries']\n",
    "    if e['kind'] == 'table'\n",
    "]\n",
    "print('tables:', tables)\n",
    "print(\n",
    "    'frame count:',\n",
    "    pxt_json('count', 'cli_demo/frames', '--json')['count'],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88380f81",
   "metadata": {},
   "source": [
    "### Step 6: Config and health\n",
    "\n",
    "Check daemon health, runtime status, and resolved configuration (API keys show as `<redacted>` when set). See [Configure API keys](/howto/cookbooks/core/workflow-api-keys) for credential setup."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "fa87deb9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:12.893033Z",
     "iopub.status.busy": "2026-06-12T23:39:12.892915Z",
     "iopub.status.idle": "2026-06-12T23:39:13.700135Z",
     "shell.execute_reply": "2026-06-12T23:39:13.699661Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\r\n",
      "  \"ok\": true,\r\n",
      "  \"service\": \"pxt\",\r\n",
      "  \"pxt_version\": \"0.6.5\",\r\n",
      "  \"pid\": 52061,\r\n",
      "  \"started_at\": \"2026-06-12T23:09:23.075920+00:00\",\r\n",
      "  \"pxt_install_dir\": \"/opt/miniconda3/envs/pxt/lib/python3.10/site-packages/pixeltable\",\r\n",
      "  \"python_executable\": \"/opt/miniconda3/envs/pxt/bin/python\",\r\n",
      "  \"pixeltable_home\": \"/private/var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home\",\r\n",
      "  \"pixeltable_pgdata\": \"/private/var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home/pgdata\",\r\n",
      "  \"pixeltable_config_file\": \"/private/var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home/config.toml\",\r\n",
      "  \"pixeltable_env\": {\r\n",
      "    \"PIXELTABLE_HOME\": \"/var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home\"\r\n",
      "  }\r\n",
      "}\r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pxt_version     0.6.5.dev24+8a39208c\r\n",
      "daemon_pid      52061\r\n",
      "daemon_started  2026-06-12T23:09:23.075920+00:00\r\n",
      "home            /var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home\r\n",
      "db_url          postgresql+psycopg://postgres:***@/pixeltable?host=%2Fprivate%2Fvar%2Ffolders%2Fs4%2F0zdx499s6sv3_0jll6ccdbh00000gn%2FT%2Ftmp.8rYgq6oxHN%2F.pxt-home%2Fpgdata\r\n",
      "media_dir       /var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home/media\r\n",
      "file_cache_dir  /var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home/file_cache\r\n",
      "total_tables    7\r\n",
      "total_errors    2\r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "config_file  /var/folders/s4/0zdx499s6sv3_0jll6ccdbh00000gn/T/tmp.8rYgq6oxHN/.pxt-home/config.toml\r\n",
      "not set: openai.api_key, openai.base_url, openai.api_version, openai.rate_limits, openai.max_connections, openai.max_keepalive_connections, openai.read_timeout, openai.write_timeout\r\n"
     ]
    }
   ],
   "source": [
    "!pxt health\n",
    "!pxt status\n",
    "!pxt config --section openai"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b1e389e",
   "metadata": {},
   "source": [
    "### Step 7: Serve without application code\n",
    "\n",
    "Validate an insert endpoint with `--dry-run --json` (no server started). For production, declare routes in `pyproject.toml` — see [HTTP Serving](https://docs.pixeltable.com/howto/deployment/serving).\n",
    "\n",
    "Full live flow:\n",
    "\n",
    "```bash\n",
    "pxt serve insert --table cli_demo/videos --path /videos --inputs video title --outputs title\n",
    "curl -X POST localhost:8000/videos -H 'Content-Type: application/json' \\\n",
    "  -d '{\"video\": \"https://raw.githubusercontent.com/pixeltable/pixeltable/release/docs/resources/bangkok.mp4\", \"title\": \"Bangkok\"}'\n",
    "pxt rows cli_demo/frames -n 1 --cols pos,thumb\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f17e9319",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-06-12T23:39:13.702465Z",
     "iopub.status.busy": "2026-06-12T23:39:13.702299Z",
     "iopub.status.idle": "2026-06-12T23:39:15.090613Z",
     "shell.execute_reply": "2026-06-12T23:39:15.090073Z"
    },
    "output": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\r\n",
      "  \"name\": \"pxt-serve\",\r\n",
      "  \"prefix\": \"\",\r\n",
      "  \"host\": \"0.0.0.0\",\r\n",
      "  \"port\": 8000,\r\n",
      "  \"routes\": [\r\n",
      "    {\r\n",
      "      \"path\": \"/videos\",\r\n",
      "      \"background\": false,\r\n",
      "      \"type\": \"insert\",\r\n",
      "      \"table\": \"cli_demo/videos\",\r\n",
      "      \"inputs\": [\r\n",
      "        \"video\",\r\n",
      "        \"title\"\r\n",
      "      ],\r\n",
      "      \"uploadfile_inputs\": null,\r\n",
      "      \"outputs\": [\r\n",
      "        \"title\"\r\n",
      "      ],\r\n",
      "      \"return_fileresponse\": false,\r\n",
      "      \"export_sql\": null\r\n",
      "    }\r\n",
      "  ]\r\n",
      "}\r\n"
     ]
    }
   ],
   "source": [
    "!pxt serve insert --table cli_demo/videos --path /videos --inputs video title --outputs title --dry-run --json"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6474f840",
   "metadata": {},
   "source": [
    "### Next steps\n",
    "\n",
    "- [CLI reference](https://docs.pixeltable.com/platform/cli): every command and flag\n",
    "- [Dashboard](https://docs.pixeltable.com/platform/dashboard): browse tables and preview media in the browser\n",
    "- [HTTP Serving](https://docs.pixeltable.com/howto/deployment/serving): production TOML and `FastAPIRouter`\n",
    "- [AI coding agents](https://docs.pixeltable.com/overview/building-pixeltable-with-llms): agent skills, MCP, and `pxt --json` workflows"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}