splurge-sql-generator 2025.4.0

A Python tool for generating Python classes from SQL files

splurge-sql-generator

A Python library for generating SQLAlchemy classes from SQL template files with sophisticated SQL parsing and statement type detection.

Features

• SQL Template Parsing: Parse SQL files with method name comments to extract queries
• Schema-Based Type Inference: Automatically infer Python types from SQL schema files (.schema) for accurate type annotations
• Custom SQL Type Mapping: Support for custom SQL-to-Python type mappings via configurable YAML files
• Statement Type Detection: Automatically detect if SQL statements return rows (fetch) or perform operations (execute)
• Code Generation: Generate Python classes with SQLAlchemy methods and precise type hints
• Parameter Extraction: Extract and map SQL parameters to Python method signatures with inferred types
• Multi-Database Support: Built-in support for SQLite, PostgreSQL, MySQL, MSSQL, and Oracle SQL types
• CLI Interface: Command-line tool for batch processing with flexible configuration options
• Comprehensive Error Handling: Robust error handling for file operations and SQL parsing

SQL File Format Requirement

Important: The first line of every SQL file must be a class comment specifying the class name, e.g.:

# UserRepository

This class name will be used for the generated Python class. The filename is no longer used for class naming.

Installation

pip install splurge-sql-generator

Or install from source:

git clone https://github.com/yourusername/splurge-sql-generator.git
cd splurge-sql-generator
pip install -e .

Quick Start

1. Create a SQL Template File

Create a file named UserRepository.sql:

# UserRepository
#get_user_by_id
SELECT id, username, email, created_at 
FROM users 
WHERE id = :user_id;

#create_user
INSERT INTO users (username, email, password_hash, status) 
VALUES (:username, :email, :password_hash, :status) 
RETURNING id;

#update_user_status
UPDATE users 
SET status = :new_status, updated_at = CURRENT_TIMESTAMP 
WHERE id = :user_id;

2. Generate Python Class

Using the CLI:

# Module invocation
python -m splurge_sql_generator.cli UserRepository.sql --output generated/

# Or via the installed console script
splurge-sql-gen UserRepository.sql --output generated/

Or using Python:

from splurge_sql_generator import PythonCodeGenerator, generate_class

generator = PythonCodeGenerator()
code = generator.generate_class(
    'UserRepository.sql',
    output_file_path='generated/UserRepository.py',
    schema_file_path='database.schema',  # Optional: shared schema file
)

# Or use the convenience function
code2 = generate_class(
    'UserRepository.sql',
    output_file_path='generated/UserRepository.py',
    schema_file_path='database.schema',  # Optional: shared schema file
)

3. Use the Generated Class

from sqlalchemy import create_engine
from generated.user_repository import UserRepository

# Create database connection
engine = create_engine('sqlite:///example.db')
connection = engine.connect()

# Use the generated class methods (class methods only)
users = UserRepository.get_user_by_id(
    connection=connection,
    user_id=1,
)

# For data modification operations, use transaction blocks
with connection.begin():
    result = UserRepository.create_user(
        connection=connection,
        username='john_doe',
        email='[e-mail]',
        password_hash='hashed_password',
        status='active',
    )
    # Transaction commits automatically when context exits

# For multiple operations in a single transaction:
with connection.begin():
    UserRepository.create_user(
        connection=connection,
        username='jane',
        email='[e-mail]',
        password_hash='pw',
        status='active',
    )
    UserRepository.update_user_status(
        connection=connection,
        user_id=1,
        new_status='active',
    )
    # All operations commit together, or all rollback on error

# Note: All generated methods now use a class-level logger automatically. There is no optional logger parameter. To customize logging, configure the class logger as needed:
# import logging
# UserRepository.logger.setLevel(logging.INFO)

Note: All generated methods are class methods. You must always pass the connection and parameters as named arguments. For data modification operations (INSERT, UPDATE, DELETE), use with connection.begin(): blocks to manage transactions explicitly. This gives you full control over transaction boundaries and ensures data consistency.

Schema-Based Type Inference

Starting with version 2025.4.0, splurge-sql-generator supports schema-based type inference for accurate Python type annotations. This feature automatically infers parameter types from SQL schema definitions.

How It Works

1. Create a schema file with the .schema extension (can be shared across multiple SQL files)
2. Define your table schemas using standard SQL CREATE TABLE statements
3. The generator automatically maps SQL column types to Python types based on the schema

Schema File Options

You have two options for schema files:

Option 1: Individual schema files (default)

• Create a .schema file with the same stem as each SQL file
• Example: UserRepository.sql → UserRepository.schema

Option 2: Shared schema file (recommended for multiple SQL files)

• Create one schema file that covers all your tables
• Use the --schema option to specify the shared schema file
• Example: --schema database.schema for all SQL files

Example

UserRepository.schema:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INTEGER,
    salary DECIMAL(10,2),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

UserRepository.sql:

# UserRepository

#get_user_by_id
SELECT id, username, email, age, salary, is_active, created_at
FROM users 
WHERE id = :id;

#create_user
INSERT INTO users (username, email, age, salary, is_active)
VALUES (:username, :email, :age, :salary, :is_active)
RETURNING id;

Generated method signatures:

@classmethod
def get_user_by_id(
    cls,
    *,
    connection: Connection,
    id: int,  # Inferred from INTEGER in schema
) -> List[Row]:

@classmethod
def create_user(
    cls,
    *,
    connection: Connection,
    username: str,     # Inferred from VARCHAR in schema
    email: str,        # Inferred from VARCHAR in schema
    age: int,          # Inferred from INTEGER in schema
    salary: float,     # Inferred from DECIMAL in schema
    is_active: bool,   # Inferred from BOOLEAN in schema
) -> Result:

Custom SQL Type Mappings

You can customize the SQL-to-Python type mappings using a custom YAML file:

custom_types.yaml:

# Custom SQL Type to Python Type Mapping
CUSTOM_ID: int
CUSTOM_NAME: str
CUSTOM_AMOUNT: float
CUSTOM_FLAG: bool
CUSTOM_DATA: dict
DEFAULT: Any

Use it with the CLI:

python -m splurge_sql_generator.cli UserRepository.sql --types custom_types.yaml

Usage Examples

Statement Type Detection

from splurge_sql_generator import detect_statement_type, is_fetch_statement

# Detect statement types
sql1 = "SELECT * FROM users WHERE id = :user_id"
print(detect_statement_type(sql1))  # 'fetch'

sql2 = "INSERT INTO users (name) VALUES (:name)"
print(detect_statement_type(sql2))  # 'execute'

# Convenience functions
print(is_fetch_statement(sql1))     # True
print(is_fetch_statement(sql2))     # False

Complex SQL with CTEs

# UserStats
#get_user_stats
WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.name, uo.order_count
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id
WHERE u.id = :user_id AND u.status = :status;

The generator will correctly detect this as a fetch statement and generate appropriate Python code.

CLI Usage

# Generate single class (module)
python -m splurge_sql_generator.cli UserRepository.sql --output generated/

# Generate single class (console script)
splurge-sql-gen UserRepository.sql --output generated/

# Generate multiple classes (globs expanded by shell)
splurge-sql-gen *.sql --output generated/

# Generate from a directory recursively
splurge-sql-gen path/to/sqls/ --output generated/

# Preview generated code without saving
splurge-sql-gen UserRepository.sql --dry-run

# Strict mode: treat warnings (e.g., non-.sql inputs, empty dir) as errors
splurge-sql-gen path/to/sqls/ --output generated/ --strict

# Generate to specific output directory
splurge-sql-gen UserRepository.sql -o src/repositories/

# Use custom SQL type mapping file
splurge-sql-gen UserRepository.sql --output generated/ --types custom_types.yaml

# Use custom SQL type mapping file (short form)
splurge-sql-gen UserRepository.sql --output generated/ -t custom_types.yaml

# Use shared schema file for multiple SQL files
splurge-sql-gen *.sql --output generated/ --schema database.schema

# Use shared schema file with custom type mapping
splurge-sql-gen *.sql --output generated/ --schema database.schema --types custom_types.yaml

API Reference

Core Classes

PythonCodeGenerator

Main class for generating Python code from SQL templates.

# Use default types.yaml
generator = PythonCodeGenerator()

# Use custom SQL type mapping file
generator = PythonCodeGenerator(sql_type_mapping_file="custom_types.yaml")

# Generate code
code = generator.generate_class(sql_file_path, output_file_path=None, schema_file_path=None)
classes = generator.generate_multiple_classes(sql_files, output_dir=None, schema_file_path=None)

Parameters:

• sql_type_mapping_file (optional): Path to custom SQL type mapping YAML file. Defaults to types.yaml.

Methods:

• generate_class(sql_file_path, *, output_file_path=None, schema_file_path=None): Generate a single Python class
• generate_multiple_classes(sql_files, *, output_dir=None, schema_file_path=None): Generate multiple Python classes

SqlParser

Parser for SQL template files.

parser = SqlParser()
class_name, method_queries = parser.parse_file(sql_file_path)
method_info = parser.get_method_info(sql_query)

SQL Helper Functions

detect_statement_type(sql: str) -> str

Detect if a SQL statement returns rows ('fetch') or performs operations ('execute').

is_fetch_statement(sql: str) -> bool

Convenience function to check if a statement returns rows.

is_execute_statement(sql: str) -> bool

Convenience function to check if a statement performs operations.

remove_sql_comments(sql_text: str) -> str

Remove SQL comments from a SQL string.

parse_sql_statements(sql_text: str, strip_semicolon: bool = False) -> List[str]

Parse a SQL string containing multiple statements into individual statements.

split_sql_file(file_path: str, strip_semicolon: bool = False) -> List[str]

Read a SQL file and split it into individual statements.

Supported SQL Features

• Basic DML: SELECT, INSERT, UPDATE, DELETE
• CTEs: Common Table Expressions (WITH clauses)
• Complex Queries: Subqueries, JOINs, aggregations
• Database-Specific: SHOW, EXPLAIN, DESCRIBE, VALUES
• Parameters: Named parameters with :param_name syntax
• Comments: Single-line (--) and multi-line (/* */) comments

Generated Code Features

• Accurate Type Hints: Schema-based type inference for precise parameter and return value annotations
• Custom Type Support: Configurable SQL-to-Python type mappings for project-specific needs
• Multi-Database Types: Built-in support for SQLite, PostgreSQL, MySQL, MSSQL, and Oracle types
• Docstrings: Comprehensive documentation for each method
• Error Handling: Proper SQLAlchemy result handling
• Parameter Mapping: Automatic mapping of SQL parameters to Python arguments with inferred types
• Statement Type Detection: Correct return types based on SQL statement type
• Auto-Generated Headers: Clear identification of generated files

Development

Running Tests

python -m unittest discover -s tests -v

Project Structure

splurge-sql-generator/
├── splurge_sql_generator/
│   ├── __init__.py          # Main package exports
│   ├── sql_helper.py        # SQL parsing utilities
│   ├── sql_parser.py        # SQL template parser
│   ├── schema_parser.py     # SQL schema parser for type inference
│   ├── code_generator.py    # Python code generator
│   ├── cli.py               # Command-line interface
│   └── templates/           # Jinja2 templates (python_class.j2)
├── tests/                   # Test suite
├── examples/                # Example SQL templates and schemas
│   ├── *.sql                # SQL template files
│   ├── *.schema             # SQL schema files for type inference
│   └── custom_types.yaml    # Example custom type mapping
├── output/                  # Generated code examples
└── types.yaml               # Default SQL type mappings

License

MIT License - see LICENSE file for details.

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Run the test suite
6. Submit a pull request

---

Changelog

[2025.4.0] - 2025-08-23

Added

• Custom SQL Type Mapping: New CLI option --types / -t to specify custom SQL type mapping YAML files
• Schema-Based Type Inference: Enhanced type inference system using dedicated schema files (.schema) for accurate Python type annotations
• Comprehensive Database Support: Added support for MSSQL and Oracle-specific SQL types in addition to existing SQLite, PostgreSQL, and MySQL support
• Case-Insensitive Type Lookups: SQL type matching now works regardless of case (e.g., INTEGER, integer, Integer all work)
• Schema File Requirements: Schema files are now mandatory for code generation, ensuring consistent type inference

Changed

• CLI Option Names: Updated -st | --sql-types to -t | --types for better consistency
• Default Type Mapping File: Changed default from sql-types.yaml to types.yaml for cleaner naming
• Schema File Extension: Updated schema file lookup from *.sql.schema to *.schema for simpler naming
• Schema File Requirements: Made schema files mandatory - code generation now requires at least one schema file to be present

Enhanced

• CLI Flexibility: Users can now specify custom type mappings per project using --types custom_types.yaml
• Type Mapping Configuration: Sortable and maintainable types.yaml file with alphabetical ordering and DEFAULT fallback
• Schema Parser Improvements: Enhanced schema parsing with better error handling and fallback mechanisms
• Test Coverage: Added comprehensive tests for custom type mapping functionality and schema file requirements
• Test Organization: Refactored test modules to follow "one test class per module" principle for better maintainability

Technical Improvements

• PythonCodeGenerator: Updated to accept sql_type_mapping_file parameter for custom type mappings
• SchemaParser: Enhanced with Oracle types in default mapping for better fallback support
• CLI Integration: Seamless integration of custom type mapping files with existing CLI workflow
• Schema Validation: Enhanced schema file validation with clear error messages for missing schema files
• Test Utilities: Fixed schema file naming in test utilities to match expected patterns

Usage Examples

# Use default types.yaml
python -m splurge_sql_generator.cli examples/User.sql --dry-run

# Use custom type mapping file (long form)
python -m splurge_sql_generator.cli examples/User.sql --dry-run --types custom_types.yaml

# Use custom type mapping file (short form)
python -m splurge_sql_generator.cli examples/User.sql --dry-run -t custom_types.yaml

# Use shared schema file for multiple SQL files
python -m splurge_sql_generator.cli *.sql --output generated/ --schema database.schema

Database Type Support

• SQLite: INTEGER, TEXT, REAL, BLOB, etc.
• PostgreSQL: JSON, JSONB, UUID, SERIAL, BIGSERIAL, etc.
• MySQL: TINYINT, MEDIUMINT, LONGTEXT, ENUM, etc.
• MSSQL: BIT, MONEY, NVARCHAR, UNIQUEIDENTIFIER, XML, SQL_VARIANT, etc.
• Oracle: NUMBER, VARCHAR2, CLOB, RAW, ROWID, INTERVAL, etc.

Development Experience

• Custom Type Definitions: Projects can define application-specific SQL types and their Python mappings
• Type Consistency: Ensures consistent type inference across different database dialects
• Project Flexibility: Different projects can use different type mapping strategies
• Maintainable Configuration: YAML-based configuration for easy type mapping management
• Schema File Management: Support for both individual schema files and shared schema files across multiple SQL files

[2025.3.1] - 2025-08-19

Changed

• Enhanced test robustness: Updated all test assertions to use pattern matching instead of exact string matching
• Improved test maintainability: Tests now validate behavior and patterns rather than exact textual strings
• Better error message flexibility: CLI and parser tests are more tolerant of minor text changes and formatting variations
• Cross-platform test compatibility: Enhanced tests to handle different line endings and platform-specific formatting

Technical Improvements

• CLI test enhancements:
  • Replaced exact error message matching with flexible pattern matching
  • Used regex patterns for generated class count validation (r'Generated \d+ Python classes?')
  • Simplified error message assertions to focus on key phrases rather than exact text
  • Added regex import for advanced pattern matching capabilities
• SQL helper test improvements:
  • Changed from exact SQL statement equality to pattern matching with in operator
  • Enhanced whitespace tolerance for SQL statement validation
  • Improved semicolon handling flexibility in statement parsing tests
• SQL parser test updates:
  • Simplified error message validation to focus on core content
  • Removed exact punctuation and spacing requirements from error assertions
  • Made tests more resilient to UI/UX improvements and message refinements

Test Quality Enhancements

• Reduced test brittleness: Tests are less likely to break due to minor text changes or formatting updates
• Better maintainability: Error message improvements and UI changes won't require test updates
• Clearer test intent: Tests now focus on validating behavior rather than implementation details
• Future-proof validation: Accommodates improvements to error messages and user interface without breaking tests

Development Experience

• Maintained coverage: All 108 tests pass with 91% overall coverage (97% CLI coverage)
• No functionality loss: All test scenarios still validated with improved robustness
• Enhanced reliability: Tests handle minor text variations gracefully while maintaining validation rigor

[2025.3.0] - 2025-08-13

Changed

• Package renamed and unified under splurge-sql-generator / splurge_sql_generator
• Keyword-only, fully typed public APIs (Python 3.10+ unions, precise return types)
• CLI improvements: directory input recursion and --strict mode
• Narrowed public exports to user-facing helpers and classes
• Consistent exceptions: SqlValidationError for validation; now inherits from ValueError
• Generated classes add a NullHandler to the class logger to avoid warnings
• Faster generation: preloaded Jinja template and single-parse batch generation
• Documentation updates and examples migrated to new package/module names

Note: Backwards-compatibility with positional arguments was intentionally removed. Use keyword-only parameters (output_file_path=..., output_dir=...).

[0.2.4] - 2025-06-30

Changed

• Strict Python identifier enforcement: Method names, class names, and parameter names extracted from SQL templates must now be valid Python identifiers. Invalid names will raise errors during code generation.
• Improved error messages: Clearer error reporting when invalid names are detected in SQL templates.
• Documentation update: The requirement for valid Python names is now explicitly documented in the README and enforced in the code generator.

Note: All method names, class names, and parameter names in your SQL templates must be valid Python identifiers (letters, numbers, and underscores, not starting with a number, and not a Python keyword). This ensures the generated code is always valid and importable.

[0.2.3] - 2025-06-29

Changed

• Updated pyproject.toml: Modernized build configuration with current best practices and comprehensive metadata
• Enhanced development setup: Added optional dependency groups for dev, test, and docs tools
• Improved package metadata: Added keywords, comprehensive classifiers, and better project description
• Type safety improvements: Fixed all mypy type checking issues throughout the codebase
• Enhanced test coverage: Added comprehensive test cases to achieve 81% overall coverage

Technical Improvements

• Modern build system: Upgraded to setuptools 68.0+ for better compatibility
• Development dependencies: Added pytest, black, isort, flake8, mypy, and sphinx for development workflow
• Type annotations: Added missing return type annotations and fixed type compatibility issues
• Code quality tools: Integrated Black (formatter), isort (import sorter), flake8 (linter), and mypy (type checker)
• Comprehensive testing: Added tests for CLI error handling, edge cases, and public API coverage
• Cross-platform compatibility: Ensured all tests work reliably on Windows and Unix systems

Development Experience

• One-command setup: pip install -e ".[dev]" installs all development tools
• Automated code formatting: Black and isort ensure consistent code style
• Static analysis: flake8 and mypy provide comprehensive code quality checks
• Test automation: pytest with coverage reporting for quality assurance
• Documentation support: Sphinx integration for generating project documentation

[0.2.2] - 2025-06-29

Changed

• Simplified logger handling: Removed optional logger parameter from all generated methods to always use class-level logger
• Cleaner API: Reduced parameter clutter by eliminating the optional logger parameter
• Consistent logging: All methods now use the same class-level logger for uniform logging behavior
• Updated test suite: Modified tests to reflect the simplified logger approach

Technical Improvements

• Simplified method signatures: Generated methods now have fewer parameters and cleaner interfaces
• Consistent logging pattern: Class-level logger approach follows common Python utility class patterns
• Reduced complexity: Eliminated conditional logger assignment logic in generated code
• Better maintainability: Generated code is simpler and easier to understand

[0.2.1] - 2025-06-29

Changed

• Refactored sql_helper.py: Improved error handling and input validation for file operations.
• Stricter type checks: Functions now validate input types and raise clear exceptions for invalid arguments.
• Robust SQL parsing utilities: Enhanced parsing and comment removal logic for more accurate statement detection and splitting.
• Improved documentation: Expanded and clarified docstrings for all helper functions.

[0.2.0] - 2025-06-28

Changed

• Switched to class-methods-only approach: Removed instance methods and constructors for better transaction control
• Removed automatic commit/rollback: Data modification operations no longer automatically commit or rollback, allowing explicit transaction management
• Added comprehensive logging: All operations now include debug and error logging with customizable logger support
• Improved parameter formatting: All method parameters are now on separate lines with proper PEP8 formatting
• Enhanced error handling: Simplified exception handling without automatic rollback interference

Technical Improvements

• Explicit transaction control: Users must use with connection.begin(): blocks for data modifications
• Better encapsulation: No shared state between method calls, improving thread safety
• Named parameters required: All methods use * to force named parameter usage for clarity
• Class-level logger: Default logger with optional override per method call
• Production-ready: Generated code is now suitable for production use with proper transaction management

Generated Code Enhancements

• Transaction safety: No automatic commits that could interfere with user-managed transactions
• Cleaner API: Class methods only, no instance state to manage
• Better error propagation: Exceptions are logged but not automatically rolled back
• Maintained API compatibility: Public API remains consistent while improving safety

[0.1.1] - 2025-06-28

Changed

• Refactored code generator to use Jinja2 templates: Replaced string concatenation with template-based code generation for better maintainability and flexibility
• Added Jinja2 dependency: Added jinja2>=3.1.0 to requirements.txt
• Created templates directory: Added splurge_sql_generator/templates/ with python_class.j2 template
• Simplified code generation logic: Removed individual method generation functions in favor of template rendering
• Updated test suite: Modified tests to work with new template-based approach while maintaining full functionality

Technical Improvements

• Template-based generation: All Python code is now generated using Jinja2 templates, making it easier to modify output format
• Better separation of concerns: Template logic is separated from Python generation logic
• Maintained API compatibility: Public API remains unchanged, ensuring backward compatibility
• Enhanced maintainability: Code generation format can now be modified by editing templates without touching Python logic

[0.1.0] - 2025-06-28

Added

• Initial release of splurge-sql-generator
• SQL template parsing with method name extraction
• Sophisticated SQL statement type detection (fetch vs execute)
• Support for Common Table Expressions (CTEs)
• Python code generation with SQLAlchemy integration
• Command-line interface for batch processing
• Comprehensive parameter extraction and mapping
• Support for complex SQL features (subqueries, JOINs, aggregations)
• Auto-generated file headers with tool attribution
• Robust error handling for file operations
• Comprehensive test suite with edge case coverage

Features

• SQL Helper Utilities: detect_statement_type(), remove_sql_comments(), parse_sql_statements()
• Template Parser: Extract method names and SQL queries from template files
• Code Generator: Generate Python classes with proper type hints and docstrings
• CLI Tool: Command-line interface with dry-run and batch processing options
• Statement Detection: Automatic detection of fetch vs execute statements
• Parameter Handling: Deduplication and proper mapping of SQL parameters

Technical Details

• Uses sqlparse for robust SQL parsing
• Supports all major SQL statement types
• Generates Python code with SQLAlchemy best practices
• Comprehensive error handling and validation
• MIT licensed with clear copyright attribution