Metadata-Version: 2.4
Name: contextsql_garvit
Version: 0.1.3
Summary: Local, free, business-context-aware text-to-SQL agent
Requires-Python: >=3.9
Description-Content-Type: text/markdown
Requires-Dist: sqlalchemy>=2.0
Requires-Dist: pymysql>=1.1
Requires-Dist: huggingface_hub>=0.20
Requires-Dist: llama-cpp-python
Provides-Extra: postgres
Requires-Dist: psycopg2-binary; extra == "postgres"

Windows users: if pip install contextsql fails while building llama-cpp-python, they should first run:

pip install llama-cpp-python --prefer-binary --extra-index-url https://abetlen.github.io/llama-cpp-python/whl/cpu

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
pypi-AgEIcHlwaS5vcmcCJDNmNDYwOWQwLWNhZWQtNDkyZC1hMjUxLThiMTc2MGVkYTAwMgACKlszLCIxOTVhZjFmYy1kMjhlLTQwOTgtYjI1NS00NmQ2MzFmNjAzNmUiXQAABiB8KDxlQkkM9TV1GxvJLmr-dA_MLLgTtMYbkyN-UDsv0Q

# contextsql

Ask your database questions in plain English. **Local, free and private**: the model runs on your own machine, and your data is never sent to any cloud service.

contextsql first *learns* your database (tables, columns, value patterns, business terms). When you ask a question, it picks the relevant tables, writes the SQL, runs it, and shows the result.

```
$ contextsql ask "top 5 customers by total payments"

SQL (1 attempt, 4210 ms; tables: payments, customers, orders):
  SELECT ... LIMIT 5;

name          total
------------  --------
Customer 482  39984.38
...
```

---

## Contents

1. [Requirements](#requirements)
2. [Installation](#installation)
3. [Quick start](#quick-start)
4. [Commands](#commands)
5. [Teaching it your business terms (glossary)](#teaching-it-your-business-terms-glossary)
6. [Improving answers (good / fix)](#improving-answers-good--fix)
7. [Using other databases](#using-other-databases)
8. [Safety](#safety)
9. [Where files are stored](#where-files-are-stored)
10. [Tips for better answers](#tips-for-better-answers)
11. [Troubleshooting](#troubleshooting)
12. [Updating and uninstalling](#updating-and-uninstalling)

---

## Requirements

| Item | Requirement |
|------|-------------|
| Python | 3.9 or newer (**3.10 to 3.12 recommended**, especially on Windows) |
| Database | MySQL (default), PostgreSQL or SQLite |
| Disk space | About 2 GB for the model (downloaded once) |
| RAM | 4 GB free or more |
| Internet | Needed only for installation and the first model download |

No GPU is needed. The model runs on the CPU.

---

## Installation

### 1. Create a virtual environment (recommended)

**Windows**
```
python -m venv contextsql_env
contextsql_env\Scripts\activate
```

**Mac / Linux**
```
python3 -m venv contextsql_env
source contextsql_env/bin/activate
```

### 2. Install the model runner first (Windows)

On Windows, install the prebuilt `llama-cpp-python` before installing contextsql. This avoids a C++ compiler error (`CMAKE_C_COMPILER not set` / `nmake` failed):

```
pip install llama-cpp-python --prefer-binary --extra-index-url https://abetlen.github.io/llama-cpp-python/whl/cpu
```

Mac and Linux users can skip this step.

### 3. Install contextsql

```
pip install contextsql
```

Check that it works:
```
contextsql status
```

### Optional: PostgreSQL support
```
pip install "contextsql[postgres]"
```

---

## Quick start

Three steps: **connect**, **learn**, **ask**.

### Step 1: Connect to your database

```
contextsql connect
```

You will be asked for:

```
Host [127.0.0.1]:
Port [3306]:
User:
Password:        (hidden while typing)
Database name:
```

Or pass everything in one line:

```
contextsql connect --host 127.0.0.1 --port 3306 --user myuser --password mypass --db mydatabase
```

On the **first** connect, the model (about 1.8 GB) is downloaded from Hugging Face and cached. This happens only once, so it takes a few minutes. After that, everything works offline.

### Step 2: Learn your database (one time)

```
contextsql learn
```

contextsql reads your table structure, scans the data and builds notes about your columns and values (for example, which status values exist). It prints a summary like:

```
Done: 4 tables, 12,000 rows scanned, 7 data notes, 10 auto glossary terms, 0 glossary terms imported.
```

Run `learn` again whenever your tables or data change a lot.

### Step 3: Ask questions

One question:
```
contextsql ask "how many orders were placed last month"
```

Interactive session (the model loads once, so later questions are faster):
```
contextsql ask
> top 5 customers by revenue
> average order value per month
> (press Enter on an empty line to quit)
```

---

## Commands

### `contextsql connect`

Saves your database login and downloads the model if needed.

| Option | Meaning | Default |
|--------|---------|---------|
| `--dialect` | `mysql`, `postgresql` or `sqlite` | `mysql` |
| `--host` | Server address | `127.0.0.1` |
| `--port` | Server port | 3306 (MySQL) / 5432 (PostgreSQL) |
| `--user` | Database user | asked |
| `--password` | Database password | asked (hidden) |
| `--db` | Database name (or file path for SQLite) | asked |
| `--model` | Path to your own `.gguf` model file | auto-download |

Run it again at any time to switch databases. The old settings are overwritten. After switching, run `contextsql learn`.

### `contextsql learn`

Reads and analyzes the connected database. Run it once after `connect`, and again after big schema or data changes.

### `contextsql ask`

| Usage | What it does |
|-------|--------------|
| `contextsql ask "question"` | Answers one question and exits |
| `contextsql ask` | Starts an interactive session (empty line quits) |
| `contextsql ask --threads 8 "question"` | Uses 8 CPU threads (default is half your cores) |

Every answer shows:
- the SQL it wrote,
- how many attempts it needed (it retries automatically if the SQL fails or returns nothing),
- the tables it used,
- a **low confidence** warning when it is unsure. Always read the SQL in that case.

Results show up to 20 rows on screen.

### `contextsql glossary`

Manage your business terms. See [the next section](#teaching-it-your-business-terms-glossary).

```
contextsql glossary                      # list all terms (same as "list")
contextsql glossary list
contextsql glossary add "term" "definition" "SQL expression" --tables t1,t2
contextsql glossary remove "term"
```

### `contextsql good`

Marks the **last answer** as correct. It is saved as an example and reused for similar questions.

### `contextsql fix`

Replaces the last answer with your corrected SQL:

```
contextsql fix "SELECT COUNT(*) FROM orders WHERE status = 'completed'"
```

The SQL is checked and test-run first. It is saved only if it runs successfully.

### `contextsql status`

Shows whether you are connected, which model file is used, how many tables were learned, and how many glossary terms exist.

---

## Teaching it your business terms (glossary)

Databases rarely say what *revenue*, *active customer* or *late delivery* mean in your business. The glossary lets you define them once, and contextsql uses the definition every time you ask.

```
contextsql glossary add "revenue" "Sum of quantity times price for completed orders" "SUM(quantity*unit_price)" --tables orders,order_items
```

Arguments, in order:

1. **term**: the word you will use in questions
2. **definition**: a plain description
3. **SQL expression**: how it is calculated
4. `--tables`: tables the term needs (comma-separated, no spaces)

Then:
```
contextsql ask "revenue by month"
```

Other glossary commands:
```
contextsql glossary list
contextsql glossary remove "revenue"
```

Terms marked `(auto)` in the list were generated automatically during `learn`. Your own terms override them. You do not need to run `learn` again after adding a term.

---

## Improving answers (good / fix)

contextsql gets better the more you correct it.

1. Ask a question.
2. If the SQL and result are right, run `contextsql good`.
3. If they are wrong, run `contextsql fix "your corrected SQL"`.

Both commands apply to the **last question you asked**. Saved examples are used for similar questions in the future.

---

## Using other databases

### MySQL (default)
```
contextsql connect --user myuser --password mypass --db mydatabase
```

### PostgreSQL
```
pip install "contextsql[postgres]"
contextsql connect --dialect postgresql --host localhost --port 5432 --user myuser --password mypass --db mydatabase
```

### SQLite
```
contextsql connect --dialect sqlite --db C:\path\to\file.db
```

### Remote server
Use `--host` with the server's IP address or name. Make sure the server allows connections from your machine.

---

## Safety

- **Use a read-only database user.** Never connect with an admin account such as `root` on a production database. Example for MySQL:

```sql
CREATE USER 'ctx'@'localhost' IDENTIFIED BY 'a_strong_password';
GRANT SELECT ON mydatabase.* TO 'ctx'@'localhost';
FLUSH PRIVILEGES;
```

Then connect with `--user ctx`.

- **Always read the SQL** before trusting a result, especially when you see the low-confidence warning.
- **Your login is stored in plain text** in `~/.contextsql/` (see below). This is another reason to use a read-only user.
- **Your data stays on your machine.** The model runs locally. The only network use is the one-time model download.

---

## Where files are stored

| What | Location |
|------|----------|
| Your settings, learned data, glossary and saved examples | `.contextsql` folder in your user home (Windows: `C:\Users\<you>\.contextsql`) |
| Downloaded model | Hugging Face cache (Windows: `C:\Users\<you>\.cache\huggingface\hub`) |

**Reset everything:** delete the `.contextsql` folder, then run `connect` and `learn` again.
**Free disk space:** delete the model folder `models--Garvitarunav--contextsql-v4` inside the Hugging Face cache. It will be re-downloaded when needed.

---

## Tips for better answers

- **Run `learn`** after connecting and after major data changes.
- **Name things the way your tables do.** "orders per customer" works better when the tables are called `orders` and `customers`.
- **Add glossary terms** for your key metrics (revenue, margin, active user, churn, and so on).
- **Be specific.** "Top 10 customers by total payments in 2026" is better than "best customers".
- **Check date questions.** Phrases like "last month" are supported; others ("last 7 days", "year to date") may need a check. Read the SQL.
- **Use `good` and `fix`** so it learns your preferred answers.
- **Speed:** use the interactive `contextsql ask` session so the model loads once. Try `--threads` with your CPU core count.

---

## Troubleshooting

### `Failed building wheel for llama-cpp-python` / `CMAKE_C_COMPILER not set`
No prebuilt wheel was found, and Windows has no C++ compiler. Run this first, then install contextsql:
```
pip install llama-cpp-python --prefer-binary --extra-index-url https://abetlen.github.io/llama-cpp-python/whl/cpu
pip install contextsql
```
If it still tries to build, check `python --version`. Prebuilt wheels exist for Python 3.10 to 3.12.

### `Could not connect: (1045, "Access denied for user ...")`
The username or password is wrong, or you pressed Enter at the prompts and left them empty. Test your login outside contextsql:
```
mysql -u myuser -p
```
Then run `contextsql connect` with the same user and password. Also check that the user has access to the database (`GRANT SELECT ON mydatabase.* TO 'myuser'@'localhost';`).

### `Could not connect: (2003, "Can't connect to MySQL server")`
The host or port is wrong, or the server is not running or does not allow your machine to connect.

### `Not ready yet - run: contextsql connect` or `contextsql learn`
You skipped a step. Run `connect`, then `learn`, then `ask`.

### Model download fails or is interrupted
Check your internet connection and free disk space (about 2 GB), then run `contextsql connect` again. A partial download resumes automatically.

### Answers are wrong or "low confidence"
1. Read the SQL shown.
2. Rephrase with exact table or column names.
3. Add a glossary term for the concept.
4. Use `contextsql fix "correct SQL"` to teach it.
5. Run `contextsql learn` again if your data has changed.

### It is slow
- Use the interactive `contextsql ask` session, so the model loads only once.
- Close other heavy programs and use `--threads <number of CPU cores>`.
- The first question after loading the model is always slower than later ones.

### Everything seems stuck on an old database
Settings are shared per user. Run `contextsql status` to see what you are connected to. To start fresh, delete the `.contextsql` folder.

---

## Updating and uninstalling

**Update**
```
pip install --upgrade contextsql
```

**Uninstall**
```
pip uninstall contextsql
```
Then optionally delete the `.contextsql` folder and the Hugging Face model cache (see [Where files are stored](#where-files-are-stored)).

---

## Command cheat sheet

```
pip install contextsql
contextsql connect --user USER --password PASS --db DBNAME
contextsql learn
contextsql ask "your question"
contextsql ask                       # interactive session
contextsql glossary add "term" "definition" "SQL" --tables t1,t2
contextsql glossary list
contextsql glossary remove "term"
contextsql good                      # last answer was right
contextsql fix "SELECT ..."          # last answer was wrong
contextsql status
```
