Metadata-Version: 2.4
Name: jpy-sql-runner
Version: 0.1.4
Summary: A Python utility for executing SQL files against databases with support for multiple statements, comments, and pretty-printed results
Author: Jim Schilling
Maintainer: Jim Schilling
License-Expression: MIT
Project-URL: Homepage, https://github.com/jim-schilling/jpy-sql-runner
Project-URL: Documentation, https://github.com/jim-schilling/jpy-sql-runner#readme
Project-URL: Repository, https://github.com/jim-schilling/jpy-sql-runner.git
Project-URL: Issues, https://github.com/jim-schilling/jpy-sql-runner/issues
Project-URL: Changelog, https://github.com/jim-schilling/jpy-sql-runner/blob/main/README.md#changelog
Keywords: sql,database,cli,sqlalchemy,sqlite,postgresql,mysql
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Topic :: Utilities
Classifier: Environment :: Console
Classifier: Typing :: Typed
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sqlalchemy>=2.0.37
Requires-Dist: sqlparse>=0.4.0
Requires-Dist: tabulate>=0.9.0
Provides-Extra: dev
Requires-Dist: pytest>=7.0.0; extra == "dev"
Requires-Dist: pytest-cov>=4.0.0; extra == "dev"
Requires-Dist: black>=23.0.0; extra == "dev"
Requires-Dist: flake8>=6.0.0; extra == "dev"
Requires-Dist: mypy>=1.0.0; extra == "dev"
Requires-Dist: pre-commit>=3.0.0; extra == "dev"
Provides-Extra: test
Requires-Dist: pytest>=7.0.0; extra == "test"
Requires-Dist: pytest-cov>=4.0.0; extra == "test"
Provides-Extra: lint
Requires-Dist: black>=23.0.0; extra == "lint"
Requires-Dist: flake8>=6.0.0; extra == "lint"
Requires-Dist: mypy>=1.0.0; extra == "lint"
Provides-Extra: docs
Requires-Dist: sphinx>=7.0.0; extra == "docs"
Requires-Dist: sphinx-rtd-theme>=1.0.0; extra == "docs"
Dynamic: license-file

# jpy-sql-runner
Jim's Python SQL Runner

A Python utility for executing SQL files against databases with support for multiple statements, comments, and pretty-printed results.

## Features

- Execute SQL files with multiple statements
- Support for various database backends (SQLite, PostgreSQL, MySQL, etc.)
- Automatic comment removal and statement parsing
- Pretty-printed results with tabulated output
- Batch processing of multiple files
- Transaction support with rollback on errors
- Clean CLI interface with comprehensive error handling

## Installation

```bash
pip install jpy-sql-runner
```

## CLI Usage

The main interface is through the command-line tool:

### Basic Usage

```bash
# Execute a single SQL file
python -m jpy_sql_runner -c "sqlite:///database.db" -f "script.sql"

# Execute multiple SQL files using a pattern
python -m jpy_sql_runner -c "sqlite:///database.db" -p "*.sql"

# With verbose output
python -m jpy_sql_runner -c "sqlite:///database.db" -f "script.sql" -v
```

### Command Line Options

- `-c, --connection`: Database connection string (required)
  - SQLite: `sqlite:///database.db`
  - PostgreSQL: `postgresql://user:pass@localhost/db`
  - MySQL: `mysql://user:pass@localhost/db`
  
- `-f, --file`: Single SQL file to execute
  
- `-p, --pattern`: File pattern to match multiple SQL files (e.g., "*.sql")
  
- `-v, --verbose`: Enable verbose output
  
- `--debug`: Enable SQLAlchemy debug mode

### Examples

```bash
# SQLite example
python -m jpy_sql_runner -c "sqlite:///test.db" -f "setup.sql"

# PostgreSQL example
python -m jpy_sql_runner -c "postgresql://user:pass@localhost/mydb" -p "migrations/*.sql"

# MySQL example with verbose output
python -m jpy_sql_runner -c "mysql://user:pass@localhost/mydb" -f "data.sql" -v

# Process all SQL files in current directory
python -m jpy_sql_runner -c "sqlite:///database.db" -p "*.sql"
```

## Programmatic Usage

```python
from jpy_sql_runner.db_helper import DbEngine
from jpy_sql_runner.sql_helper import split_sql_file

# Initialize database engine
db = DbEngine("sqlite:///database.db")

# Execute SQL from file
sql_statements = split_sql_file("script.sql")
sql_content = ";\n".join(sql_statements) + ";"
results = db.batch(sql_content)

# Process results
for result in results:
    if result['type'] == 'fetch':
        print(f"Query returned {result['row_count']} rows")
    elif result['type'] == 'execute':
        print("Statement executed successfully")
    elif result['type'] == 'error':
        print(f"Error: {result['error']}")

db.shutdown()
```

## SQL File Format

The tool supports SQL files with:
- Multiple statements separated by semicolons
- Single-line comments (`-- comment`)
- Multi-line comments (`/* comment */`)
- Comments within string literals are preserved

Example SQL file:
```sql
-- Create table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Insert data
INSERT INTO users (name) VALUES ('John');
INSERT INTO users (name) VALUES ('Jane');

-- Query data
SELECT * FROM users;
```

## Output Format

The CLI provides formatted output showing:
- File being processed
- Each statement executed
- Results in tabulated format for SELECT queries
- Success/error status for each statement
- Summary of files processed

## Error Handling

- Individual statement errors don't stop the entire batch
- Failed statements are reported with error details
- Database connections are properly cleaned up
- Exit codes indicate success/failure

## License

MIT License - see LICENSE file for details.

## Changelog

### 0.1.4 (06-30-2025)
- **Improved CTE and Statement Type Detection**: Enhanced logic for parsing and detecting main SQL statement types after complex CTEs, including better handling of nested, multi-CTE, and ambiguous keyword scenarios.
- **Expanded SQL Parsing Robustness**: Improved handling of edge cases, malformed SQL, and advanced SQL features (e.g., window functions, JSON, vendor-specific statements like PRAGMA, SHOW, EXPLAIN, DESC/DESCRIBE).
- **Comprehensive Test Coverage**: Added and expanded tests for CTE ambiguity, complex/nested SQL, vendor-specific syntax, error handling, and edge cases, ensuring robust detection and parsing across a wide range of SQL inputs.
- **Documentation**: Updated and clarified docstrings and comments for maintainability and developer clarity.

### 0.1.3 (06-29-2025)
- **Major CLI Refactoring**: Separated CLI logic into dedicated `cli.py` module for better code organization
- **Improved Architecture**: Made `__main__.py` a simple stub that delegates to the CLI module
- **Enhanced Test Coverage**: Added comprehensive tests for CLI functionality, achieving 90% overall coverage
- **Better Error Handling**: Improved CLI error messages and validation
- **Code Quality**: Fixed deprecation warnings in `pyproject.toml` license format
- **Documentation**: Updated README with improved installation instructions and CLI examples

### 0.1.2 (06-29-2025)
- Update sql_helper.py

### 0.1.1 (06-28-2025)
- Refactored `detect_statement_type` into smaller, modular helper functions for clarity and maintainability.
- Improved code readability and reduced duplication in SQL type detection logic.
- Added comprehensive tests for complex, nested, malformed, and database-specific SQL cases.
- Ensured robust handling of edge cases and advanced SQL features.
