csv2cte 0.1.2

Convert CSV files to PostgreSQL VALUES‑based CTEs.

csv2cte

Convert a CSV file into a PostgreSQL VALUES‑based Common Table Expression (CTE).
Handy when you need to work with static data “in memory” inside SQL without creating real tables.

---

🎯 Why this tool?

I mainly wrote this for myself, but I wanted to be able to share with others who might be able to make use of it.

• Quick and dirty when you want it. The script automatically treats columns as numeric if every non‑empty cell looks like a number, otherwise it falls back to text.
• Full control when you need it. Provide a JSON mapping via the --coltypes flag to force any SQL type (e.g., timestamps, UUIDs).
• Tiny & pure Python – just typer, which pulls in Click and Rich for colourful help text.
• Able to publish on PyPI, with a clean package layout (src/‑based isolation).
• Vibe Coded with Love, I took my own old script that strictly required --coltypes, and ran it through GPT-OSS 120b to polish the user experiance, and what's being published is the result.

---

📦 Installation

The easiest way to install csv2cte is to install the PYPI package.

pip install csv2cte

You can also install from source

# Install the latest version from source:
pip install .

# Or directly from GitHub (replace <URL>):
pip install git+https://github.com/aricshow/csv2cte.git

After installation a console script called csv2cte is available on your PATH.

---

🚀 Quick start

Auto‑detecting column types

$ cat sample.csv
order_id,price,description,is_paid
1,1234.56,"Oak Hutch",true
2,,,
3,9870.00,"Steamboat",false

$ csv2cte -f sample.csv
WITH cte_data AS (
  SELECT *
  FROM (VALUES
    (1, 1234.56, 'Oak Hutch', 'true'),
    (2, NULL, NULL, NULL),
    (3, 9870.00, 'Steamboat', 'false')
  ) AS t(
    order_id,
    price,
    description,
    is_paid
  )
)
SELECT * FROM cte_data;

Explanation: order_id and price became numeric because all non‑empty values are numbers; everything else was treated as text (NULL for empty cells).

Supplying explicit types (recommended)

$ csv2cte -f sample.csv \
    --coltypes '{"is_paid":"boolean", "order_id":"varchar"}' \
    -n my_data -a d

Result (excerpt):

WITH my_data AS (
  SELECT *
  FROM (VALUES
    ('1', 1234.56, 'Oak Hutch', true),
    ('2', NULL, NULL, NULL),
    ('3', 9870.00, 'Steamboat', false)
  ) AS d(
    order_id,
    price,
    description,
    is_paid
  )
)
SELECT * FROM my_data;

Why it matters: The override forces is_paid to be a proper PostgreSQL boolean, so the generated SQL no longer quotes "true" / "false". Additionally, in this scenario, we are wanting to import the order_id as a varchar. (God forbid you ever encounter such a design, but we've all seen it.)

Writing output to a file

csv2cte -f sample.csv -o ./sample_cte.sql

A green “✅ CTE written …” message confirms success.

---

📋 CLI reference (generated by Typer)

Usage: csv2cte [OPTIONS]

Options:
  -f, --file TEXT        Path to the CSV file; use '-' for stdin.   [required]
  -n, --name TEXT        CTE identifier used in the generated SQL.
                          Default: cte_data
  -a, --alias TEXT       Table alias inside the CTE.                Default: t
  -c, --coltypes TEXT    JSON string mapping column names to explicit SQL types.
                          Overrides automatic detection.
  -o, --output PATH      Write the generated CTE into this file (UTF-8). If omitted,
                          prints to stdout.
  --help                  Show this message and exit.

---

🛠️ Development

# Clone + create virtual env
git clone https://github.com/aricshow/csv2cte.git
cd csv2cte
python -m venv .venv && source .venv/bin/activate

# Install editable with dev extras:
pip install -e ".[dev]"

# Run the test suite (once tests are added 😅):
pytest

Formatting & linting:

black .
ruff check .

📋 License

MIT - Have fun with it, please submit a PR if you make an improvement.