sqlfile 0.1.4

Run SQL queries on CSV, Parquet, and JSON files — CLI and Python library

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

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:

  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:

pip install sqlfile

Alternatively, install the latest version directly from GitHub:

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

Or clone the repository and install locally:

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)

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

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

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:

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):

pip install sqlfile

Usage

Run queries by passing the SQL statement directly:

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):

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:

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.