QueryProcessor

A lightweight, educational CLI to import tables from MySQL or PostgreSQL into local CSV files and run basic SQL-like queries (SELECT/INSERT/UPDATE/DELETE, WHERE with AND/OR, ORDER BY, and simple JOINs) offline over those CSVs. Great for demonstrating state-machine driven CLI UX and a homegrown query engine.

Highlights

• Import data from live databases (MySQL or PostgreSQL) into csv/<database>/<table>.csv
• CSV-backed query engine with:
  • SELECT column projection and wildcard *
  • WHERE with comparison operators =, <, > and boolean modifiers AND / OR
  • ORDER BY with optional DESC
  • JOIN with USING (column) or ON a.field = b.field (inner join)
  • INSERT, UPDATE, DELETE against CSV files
• Clear, menu-driven workflow using a simple State pattern
• Console table rendering for readable results

Requirements

• Python 3.9+ (tested with modern 3.x; psycopg2 requires Python ≥ 3.7)
• A MySQL and/or PostgreSQL server you can access

Python dependencies are pinned in requirements.txt:

• mysql_connector_repackaged==0.3.1
• psycopg2==2.9.6
• python-dotenv==1.0.0

Setup

1. Create and activate a virtual environment (Windows PowerShell):

python -m venv .venv
.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt

2. Create a .env file at the project root with your DB credentials:

# MySQL
HOST_MYSQL=localhost
PORT_MYSQL=3306
USER_MYSQL=root
PASSWORD_MYSQL=your_password

# PostgreSQL
HOST_POSTGRESQL=localhost
PORT_POSTGRESQL=5432
USER_POSTGRESQL=postgres
PASSWORD_POSTGRESQL=your_password

3. Run the app:

python main.py

Usage walkthrough

1. From the Initial Menu:

   • Choose "Importar dados" to bring tables from MySQL/PostgreSQL into CSV
   • Choose "Processar queries" to run queries against the already-imported CSVs

2. Importing data:

   • Select SGBD (MySQL or PostgreSQL)
   • Select a database from the live server
   • Select tables to export; CSV files are created under csv/<database>/
   • A progress bar displays export progress

3. Running queries:

   • Choose a loaded database from csv/
   • Type queries in the prompt; type exit to go back

Query examples

Note: The parser is intentionally simple. Keywords are case-insensitive. Column and table names are expected in lowercase (CSV uses lowercase headers). String quoting is not robust; values should be single tokens without spaces.

• Select all columns

SELECT * FROM products

• Select specific columns with filtering

SELECT id name price FROM products WHERE price > 100

• Order results

SELECT * FROM orders ORDER BY created_at DESC

• Join with USING

SELECT * FROM products JOIN order_items USING (product_id)

• Join with ON

SELECT * FROM customers JOIN orders ON customers.id = orders.customer_id

• Insert a row

INSERT INTO products VALUES 101 Phone 699.99

• Update with WHERE

UPDATE products SET price = 649.99 WHERE id = 101

• Delete with WHERE

DELETE FROM products WHERE id = 101

How it works (engine details)

• Tokenization: punctuation like , ( ) ' " is stripped and the query splits on spaces
• Case handling: most tokens are lowercased except field values in WHERE/INSERT/UPDATE contexts
• CSV access: reads/writes ;-delimited CSV files stored under csv/<database>/<table>.csv
• WHERE comparisons: supports =, <, > with best-effort numeric conversion; otherwise compares as strings
• Boolean logic: chains multiple conditions with AND / OR
• ORDER BY: sorts by a single column; DESC optional
• JOIN: inner join via USING (col) or ON a.col = b.col (also supports < and > in ON comparisons)
• Output: results rendered to a simple ASCII table in the console

Project structure

.
├── app.py                      # App and state loop
├── dataimporter.py             # MySQL/PostgreSQL -> CSV export
├── dataimportmenu.py           # Choose SGBD
├── databasesmenu.py            # Choose database from server
├── initialmenu.py              # Home menu
├── loadeddatabasesmenu.py      # Choose already-loaded database (csv/)
├── main.py                     # Entrypoint
├── querymenu.py                # Query prompt and execution
├── queryprocessor.py           # CSV-backed SQL-like engine
├── state.py                    # Base State class
├── tablesmenu.py               # Choose tables and export + progress bar
├── requirements.txt            # Python deps
└── csv/                        # Generated at runtime with exported data

Limitations (by design for learning)

• Not a full SQL parser; input must be simple and space-delimited
• No robust quoting/escaping; values cannot contain spaces
• Column and table names are treated as lowercase
• Only ;-delimited CSV and simple type coercion for comparisons
• Joins are inner joins only; no outer joins, no aggregates, no GROUP BY
• Uses Python eval for simple comparison evaluation inside the engine; do not expose to untrusted inputs