Mini SQL Query Engine

A lightweight SQL-like query engine built in Python. This project executes a small subset of SQL queries on CSV-backed tables and demonstrates core database engine concepts such as query parsing, filtering, sorting, projection, and hash-based indexing.

Features

• Load table data from CSV files
• Parse simple SQL-like queries
• Support SELECT column projection
• Support WHERE filtering with comparison operators
• Support ORDER BY sorting
• Use hash-based indexing for equality lookups
• Include unit tests with pytest

Supported Query Format

SELECT columns FROM table;
SELECT columns FROM table WHERE column operator value;
SELECT columns FROM table WHERE column operator value ORDER BY column ASC;
SELECT columns FROM table WHERE column operator value ORDER BY column DESC;

Supported Operators

=, !=, >, <, >=, <=

Example Dataset

The project includes a sample CSV file:

id,name,age,city,gpa
1,Alice,22,Phoenix,3.8
2,Bob,19,Dallas,3.2
3,Charlie,24,Phoenix,3.6
4,David,21,Seattle,3.4
5,Eva,23,Dallas,3.9
6,Frank,20,Chicago,3.1
7,Grace,25,Phoenix,3.7

Example Queries

SELECT * FROM students;

SELECT name, age FROM students WHERE age > 20;

SELECT name, city FROM students WHERE city = 'Phoenix';

SELECT name, gpa FROM students WHERE gpa >= 3.5 ORDER BY gpa DESC;

Example Output

Query:

SELECT name, gpa FROM students WHERE gpa >= 3.5 ORDER BY gpa DESC;

Output:

{'name': 'Eva', 'gpa': '3.9'}
{'name': 'Alice', 'gpa': '3.8'}
{'name': 'Grace', 'gpa': '3.7'}
{'name': 'Charlie', 'gpa': '3.6'}
4 row(s) returned.

Project Architecture

mini-sql-query-engine/
│
├── data/
│   └── students.csv
│
├── src/
│   ├── storage.py
│   ├── parser.py
│   ├── executor.py
│   ├── index.py
│   └── engine.py
│
├── tests/
│   ├── test_parser.py
│   ├── test_executor.py
│   └── test_index.py
│
├── main.py
├── requirements.txt
└── README.md

How It Works

The query engine follows a simple execution pipeline:

SQL Query
   ↓
Parser
   ↓
Structured Query Dictionary
   ↓
CSV Table Loader
   ↓
WHERE Filtering or Indexed Lookup
   ↓
ORDER BY Sorting
   ↓
Column Projection
   ↓
Result Rows

Core Components

Storage Layer

Loads CSV files and converts each row into a Python dictionary.

Parser

Converts SQL-like query strings into structured Python dictionaries containing selected columns, table name, filter conditions, and sorting instructions.

Example parsed query:

{
    "columns": ["name", "gpa"],
    "table": "students",
    "where": {
        "column": "gpa",
        "operator": ">=",
        "value": "3.5"
    },
    "order_by": {
        "column": "gpa",
        "direction": "DESC"
    }
}

Executor

Applies filtering, sorting, and column selection on the loaded table rows.

The executor handles:

• Filtering rows using WHERE
• Sorting rows using ORDER BY
• Returning only selected columns using SELECT

Index Manager

Builds a hash-based index on a selected column to optimize equality-based lookups.

Example index:

{
    "Phoenix": [
        {"id": "1", "name": "Alice", "age": "22", "city": "Phoenix", "gpa": "3.8"},
        {"id": "3", "name": "Charlie", "age": "24", "city": "Phoenix", "gpa": "3.6"},
        {"id": "7", "name": "Grace", "age": "25", "city": "Phoenix", "gpa": "3.7"}
    ],
    "Dallas": [
        {"id": "2", "name": "Bob", "age": "19", "city": "Dallas", "gpa": "3.2"},
        {"id": "5", "name": "Eva", "age": "23", "city": "Dallas", "gpa": "3.9"}
    ]
}

Hash indexing is used for equality-based queries such as:

SELECT name, city FROM students WHERE city = 'Phoenix';

Instead of scanning every row, the engine can directly retrieve matching rows from the index.

Engine

Combines all components into a complete query execution flow.

The engine:

1. Parses the SQL-like query
2. Loads the target CSV table
3. Uses an index when possible
4. Applies filtering when needed
5. Sorts the result
6. Selects the requested columns
7. Returns the final output

Installation

Clone the repository and install dependencies:

pip install -r requirements.txt

Running the Project

Run the command-line interface:

python main.py

Then enter a query:

SELECT name, age FROM students WHERE age > 20;

To exit the program:

exit

Running Tests

Run:

pytest

Expected result:

10 passed

Sample Test Coverage

The test suite validates:

• Parsing SELECT *
• Parsing selected columns
• Parsing WHERE conditions
• Parsing ORDER BY DESC
• Filtering numeric values
• Filtering string values
• Sorting rows
• Selecting specific columns
• Building hash indexes
• Handling missing index values

Limitations

This project intentionally supports only a small subset of SQL.

Currently not supported:

• JOIN
• GROUP BY
• HAVING
• AND / OR conditions
• Subqueries
• Aggregate functions
• SQL dialect support
• Persistent database storage
• Insert, update, or delete operations

Future Improvements

• Add support for AND and OR conditions
• Add aggregate functions such as COUNT, AVG, and SUM
• Add GROUP BY
• Support multiple tables and basic joins
• Improve error handling for invalid tables and columns
• Add benchmarking for indexed vs non-indexed queries
• Add support for saving query results to a file
• Add a simple web interface using Flask or FastAPI