Metadata-Version: 2.1
Name: sqlfile
Version: 0.1.1
Summary: Run SQL queries on CSV, Parquet, and JSON files — CLI and Python library
Home-page: UNKNOWN
License: UNKNOWN
Platform: UNKNOWN
Description-Content-Type: text/markdown
Requires-Dist: pandas (>=1.5.0)

# pysql: Query CSV, Parquet, and JSON files using SQL (CLI & Python library)

[![PyPI version](https://img.shields.io/pypi/v/sqlfile.svg)](https://pypi.org/project/sqlfile/)
[![Python versions](https://img.shields.io/pypi/pyversions/sqlfile.svg)](https://pypi.org/project/sqlfile/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)

`pysql` is a lightweight Python query engine and command-line (CLI) utility designed to **run SQL queries directly on local data files (CSV, Apache Parquet, and JSON)**. 

Whether you need to quickly **query files with SQL from your terminal** or build structured queries in your Python code using a **fluent, type-safe SQL-like API**, `pysql` provides a zero-setup, database-free solution.

### Key Use Cases (What you can do with pysql):
- **Run SQL queries on CSV files** from the command line (`sqlfile "SELECT * FROM data.csv WHERE score > 80"`).
- **Query Parquet and JSON files using SQL** without importing them into a database.
- **JOIN two files using SQL** — works just like a database, no setup needed:
  ```bash
  sqlfile "SELECT s.name as student, s.score as score, d.dept_name as department
           FROM students.csv s INNER JOIN departments.csv d ON s.dept = d.dept_id
           ORDER BY score DESC"
  ```
  ```
        student  score              department
       John Doe   92.0        Computer Science
   Sarah Connor   88.5 Artificial Intelligence
     Kyle Reese   85.0        Computer Science
  Marcus Wright   78.5     Robotics Laboratory
  ```
- **Aggregate and group** across files (`GROUP BY`, `HAVING`, `avg`, `count`, `sum`, `min`, `max`).
- **Query files programmatically in Python** using a method-chained fluent API with complete IDE autocomplete and syntax linting support.

---

## 1. Project Goal

The primary goal of `pysql` is to bridge the gap between SQL query patterns and Python data manipulation. Instead of writing raw SQL queries inside text strings (which are hard to debug and lack IDE support) or learning the verbose syntax of Pandas, `pysql` lets developers and data analysts **run SQL queries on files** using either the terminal CLI or a fluent Python API that matches SQL conventions.

---

---

## 2. Installation

You can install `pysql` directly from PyPI using `pip`:

```bash
pip install sqlfile
```

Alternatively, install the latest version directly from GitHub:

```bash
pip install git+https://github.com/vineeth-450/pysql.git
```

Or clone the repository and install locally:

```bash
git clone https://github.com/vineeth-450/pysql.git
cd pysql
pip install --user .
```

This registers the global command-line tool `pysql` and installs the library for programmatic Python use (`import pysql`).

---

## 3. Target Audience

*   **SQL-native Developers & Data Analysts**: Individuals who are highly proficient in SQL but are transitioning to Python for data science and want an immediate, intuitive way to filter, join, and aggregate CSV data.
*   **Data Science Students**: Beginners learning Python for data sciences who want a simpler, readable alternative to Pandas for basic data exploration.
*   **Developers looking for IDE-assisted querying**: Developers who want auto-completion, syntax highlighting, and linting for their queries, which is not possible when writing raw SQL strings in Python.

---

## 4. Existing Options & Limitations

| Option | How it works | Limitations |
| :--- | :--- | :--- |
| **Pandas** | Standard data science library utilizing dataframes. | Verbose syntax, steep learning curve for non-Python developers, high memory usage. |
| **DuckDB** | In-process SQL database engine that queries CSVs directly. | Requires writing queries as raw strings (e.g., `duckdb.query("SELECT...")`), meaning no IDE auto-completion or syntax checking. |
| **SQLite (`sqlite3`)** | Standard library SQL engine. | Requires loading CSVs into an in-memory database table before querying; requires writing raw SQL strings. |

---

## 5. Where `pysql` Shines (Key Value Propositions)

1.  **Type Safety & IDE Support**: Because queries are constructed using standard Python methods (`.select()`, `.where()`, `.order_by()`), modern IDEs (like VS Code or PyCharm) provide autocomplete suggestions, linting, and syntax checks.
2.  **No Boilerplate**: Unlike SQLite, there is no need to write code to create tables, infer schemas, or manage connection states. You query the CSV file directly.
3.  **Readable & Expressive**: It enforces clean formatting and matches the logical structure of SQL query planning.
4.  **Zero External Dependencies (Optional)**: Can be built using standard library modules (like `csv` and `operator`), making it lightweight and easy to deploy in constrained environments.

---

## 6. API Design Concept

Here is a comparison of how you perform a simple query across different tools:

### Using `pysql` (Our Library)
```python
from pysql import select

results = (
    select("name", "age", "department")
    .from_file("employees.csv")
    .where("age > 30")
    .order_by("age", ascending=False)
    .limit(5)
    .execute()
)
```

### Using Pandas
```python
import pandas as pd

df = pd.read_csv("employees.csv")
results = (
    df[df["age"] > 30]
    [["name", "age", "department"]]
    .sort_values("age", ascending=False)
    .head(5)
)
```

### Using DuckDB
```python
import duckdb

# Note: The query is a raw string. If you make a typo inside the string, 
# your IDE cannot warn you until runtime.
results = duckdb.query("""
    SELECT name, age, department 
    FROM 'employees.csv' 
    WHERE age > 30 
    ORDER BY age DESC 
    LIMIT 5
""").to_df()
```

---

## 7. Completed Features

*   `[x]` **Projection**: Selecting specific columns (`.select("col1", "col2")` or `.select("*")`), with support for custom aliasing via `.as_()` on both aggregated and non-aggregated fields.
*   `[x]` **Filtering**: SQL-like conditional filters with standard raw string expressions (e.g. `.where("age > 30")`), structured condition building (e.g. `.where("age").gte(30)`), parenthesized group conditioning (e.g. `.where(col("age").gte(30).or_(col("dept").eq("HR")))`), and logical operator chaining (`.and_()`, `.or_()`).
*   `[x]` **Sorting**: Ordering results across multiple columns using SQL-style direction helpers (`.order_by("dept", desc("score"))`).
*   `[x]` **Limiting**: Restricting result size via `.limit(N)`.
*   `[x]` **Aggregation & Group By**: SQL-style grouping and math (`.select(col("dept").as_("department"), avg("score").as_("avg_score")).group_by("dept")`). Supports `avg`, `sum`, `count`, `min`, and `max`.
*   `[x]` **Output Formats**: Exporting results directly to Python dictionaries (`.to_dict()`), Pandas DataFrames (`.execute()`), or writing back to a CSV file (`.to_csv(filepath)`).
*   `[x]` **Dual Query Engines**: Supports executing queries using either a high-performance **Pandas** engine or a zero-dependency **Pure Python** fallback engine.
*   `[x]` **Table Joins (`JOIN` support)**: Supporting `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN` operations between multiple CSV files using dedicated methods and structured condition builders (`.from_file("employees.csv").as_("e").inner_join("departments.csv").as_("d").on("e.dept_id").eq("d.id")`).
*   `[x]` **Nested Queries & Subqueries**: Allowing `QueryBuilder` instances to be used as data sources in `.from_file()`, `.from_query()`, and in `.inner_join()`, `.left_join()`, or `.right_join()` for multi-stage pipeline query composability.
*   `[x]` **Group Filtering (`HAVING` support)**: Restricting aggregated results post-grouping via `.having()` using raw string conditions (e.g., `.having("avg_score >= 85")`) or structured aggregation builders (e.g., `.having(avg("score").gte(85))`).
*   `[x]` **Multi-Format Input**: Expanding input sources to support reading from Parquet (`.from_parquet()`) and JSON (`.from_json()`) files.
*   `[x]` **Multi-Format Output**: Expanding output sources to support writing back to Parquet (`.to_parquet()`) and JSON (`.to_json()`) files.
*   `[x]` **Query Optimization**: Query plan optimization and predicate pushdown (projection and predicate pushdowns).
*   `[x]` **Raw SQL Parser**: Translates raw SQL strings directly into `QueryBuilder` instances.
*   `[x]` **Command-Line Interface (CLI)**: Enables running queries directly on files with table, CSV, and JSON formatting.

---

## 8. Running the Demo

A comprehensive demonstration script is available in the `demo/` directory. To run it:

```bash
python3 demo/run.py
```

This script will automatically generate mock data files (`students.csv`, `departments.csv`) inside the `demo/` folder, run 14 demo queries showcasing filters, sorting, aggregations, aliases, joins, subqueries, and multi-format files, and print their output.

---

## 9. Command-Line Interface (CLI)

`sqlfile` is the command-line executable that allows you to query local CSV, Parquet, or JSON files directly from your terminal using raw SQL. It is part of the `pysql` Python library.

### Installation

Install via PyPI (registers the `sqlfile` command globally):
```bash
pip install sqlfile
```

### Usage

Run queries by passing the SQL statement directly:
```bash
sqlfile "SELECT name, score FROM demo/students.csv WHERE score >= 85"
```

#### Flags

- `--engine`: Choose between `pandas`, `pure`, or `auto` (default: `auto`, uses Pandas if available, else Pure Python).
- `--format`: Output format, either `table` (default), `csv`, or `json`.

#### Example Outputs

**Table Format (Default)**:
```bash
sqlfile "SELECT name, score FROM demo/students.csv WHERE score >= 85" --engine pure
name          score
------------  -----
Sarah Connor  88.5 
John Doe      92.0 
Kyle Reese    85.0 
Ellen Ripley  95.5 
```

**JSON Format**:
```bash
sqlfile "SELECT name, score FROM demo/students.csv WHERE score >= 90" --format json
[
  {
    "name": "John Doe",
    "score": 92.0
  },
  {
    "name": "Ellen Ripley",
    "score": 95.5
  }
]
```

---

## 10. Upcoming / Planned Features

*   `[ ]` **Additional Formats**: Support for reading/writing Excel (`.xlsx`) or XML formats.




