csv2cte 0.1.2
Convert CSV files to PostgreSQL VALUES‑based CTEs.
License: MIT
Requires Python: >=3.9
Keywords: csv, sql, postgresql, cli

# 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.
```bash
pip install csv2cte
```

You can also install from source
```bash
# 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

```bash
$ 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)

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

Result (excerpt):

```sql
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

```bash
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

```bash
# 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:
```bash
black .
ruff check .
```

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