pato-cli 0.1.4

In-memory DuckDB SQL server for querying and transforming data files

Pato

Pato is a lightweight, in-memory SQL database server built on DuckDB that allows you to query, summarize, and transform data files interactively via a simple command-line interface. It supports CSV, Parquet, and JSON/JSONL file formats and provides quick data inspection, aggregation, and export capabilities.

---

Features

• Load CSV, Parquet, JSON, and JSONL files into memory as tables.
• Inspect tables: view columns, data types, summary statistics, first/last rows, and row counts.
• Execute arbitrary SQL queries on in-memory data.
• Perform common aggregation operations (SUM, AVG, MIN, MAX) on numeric columns.
• Export tables to CSV, Parquet, or JSON formats.
• Lightweight Unix socket server for multi-process access.
• Server can be run in the background while sending commands from other processes.

---

Installation

Pato can be installed either from PyPI or directly from source.

---

Option 1: Install from PyPI (recommended)

If you just want to use Pato:

pip install pato-cli

Verify installation:

pato --help

This installs the pato command and all required dependencies.

Option 2: Install from source using uv

git clone <repo-url>
cd pato

Install dependencies and the pato CLI using uv:

uv sync

This will:

• Create an isolated environment
• Install all dependencies
• Register the pato command

Verify:

pato --help

---

Usage

Start the server

Start the Pato server in a terminal:

pato run

This will start a server listening on the default Unix socket:

~/.pato/pato.sock

The server must be running before executing any other commands from another process.

---

Sending commands

In another terminal, you can send commands to the running server using:

pato <command> [arguments]

For example:

pato load example.csv
pato head example --n 5

The --socket argument can be used to specify a custom socket path if desired.

---

Commands

Server & utility

Command	Description
run	Start the Pato server
stop	Shutdown the running server
ping	Check if the server is alive
version	Show Pato, DuckDB, and Python versions

Data loading & exporting

Command	Description
load file [--name NAME] [--format FORMAT]	Load CSV, Parquet, or JSON/JSONL into memory. Default table name is the file name.
export table file	Export a table to CSV, Parquet, or JSON.

Table inspection

Command	Description
list	List all tables currently loaded
describe table	Show table columns and data types
summarize table	Show summary statistics for numeric columns
head table [--n N]	Show first N rows of a table (default 10)
tail table [--n N]	Show last N rows of a table (default 10)
count table	Return the number of rows in a table
columns table	(Optional future) List only column names
types table	(Optional future) List only column types

Data transformation

Command	Description
exec --sql="..."	Execute arbitrary SQL query on in-memory tables. If sql is left off then you will will be prompted with multi line support. Enter a blank line to execute.
drop table	Drop a table from memory
rename old_name new_name	Rename a table in memory

Aggregation commands

Command	Description
sum column table	Compute SUM of a column
avg column table	Compute AVG of a column
min column table	Compute MIN of a column
max column table	Compute MAX of a column

---

Notes

• The server must be running in a separate process for commands to execute.
• Only Unix sockets are currently supported (Linux, macOS, WSL). Windows native is not supported.
• File paths passed to load and export can be relative or absolute. Relative paths are resolved relative to the client working directory.
• Export directories will be automatically created if they do not exist.

---

Example Workflow

1. Start server:

pato run

2. Load a CSV file:

pato load data/sales.csv

3. Inspect data:

pato list
pato describe sales
pato head sales --n 5
pato count sales

4. Perform aggregations:

pato sum revenue sales
pato avg price sales

5. Export data:

pato export sales out/sales.parquet

6. Stop server:

pato stop

---

Pato Shell

The pato shell command allows you to run enter a SQL shell to run queries without pato exec. Any pato <command> will still work to conveniently load, export, summarize, etc.

---

License

MIT License

---

Future Features

• Integration with visualization tools for quick plotting.
• Loading and exporting the Duck DB instance to a file for persistance