# splurge-sql-generator
[](https://pypi.org/project/splurge-sql-generator/)
[](https://pypi.org/project/splurge-sql-generator/)
[](https://opensource.org/licenses/MIT)
[](https://github.com/jim-schilling/splurge-sql-generator/actions/workflows/ci-quick-test.yml)
[](https://github.com/jim-schilling/splurge-sql-generator/actions/workflows/ci-coverage.yml)
[](https://github.com/astral-sh/ruff)
[](https://mypy-lang.org/)
A compact summary of splurge-sql-generator — a tool to generate Python (SQLAlchemy-friendly) classes from SQL template files with schema-aware type inference.
See the full documentation and details in the repository:
- Detailed docs: `docs/README-DETAILS.md`
- API reference: `docs/api/API-REFERENCE.md`
- CLI reference: `docs/cli/CLI-REFERENCE.md`
- Full changelog: `CHANGELOG.md`
Key features
- SQL template parsing and method extraction from commented templates
- Schema-based type inference for accurate Python type annotations
- Configurable SQL-to-Python type mapping via YAML files (`types.yaml` / `--types`)
- CLI for batch generation with automatic schema discovery and `--generate-types`
- Strong SQL parsing with sqlparse and explicit error reporting
Getting started
1. Install: `pip install splurge-sql-generator` or `pip install -e .` for development
2. Read the full docs in `docs/README-DETAILS.md`
For examples and advanced usage see `examples/` in the repository.
Parse column definitions from table body using sqlparse tokens. Raises `SqlValidationError` if parsing fails or no valid columns are found.
## What's new (2025.5.0)
- Centralized Safe I/O adapter (`SafeTextFileIoAdapter`) for consistent file error handling and simplified I/O usage across the library.
- Stabilized cross-version error messages and improved validation for schema file arguments.
- Documentation: comprehensive API and CLI reference files added to `docs/`.
#### `extract_table_names(sql_query: str) -> list[str]`
Extract table names from SQL query using sqlparse. Raises `SqlValidationError` if parsing fails or no table names are found.
#### `generate_types_file(*, output_path: str | None = None) -> str`
Generate the default SQL type mapping YAML file.
## 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
- **Parameter Validation**: Optional validation of SQL parameters against schema definitions
- **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 with fail-fast validation
- **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
## Error Handling and Validation
The library provides robust error handling with a fail-fast approach to ensure data integrity and clear error reporting:
### SQL Parsing Validation
- **Strict SQL Parsing**: Functions like `parse_table_columns()` and `extract_table_names()` use sqlparse for reliable parsing
- **No Fallback Mechanisms**: Eliminates unreliable regex-based fallback parsing in favor of clear error reporting
- **Clear Error Messages**: Functions raise `SqlValidationError` with descriptive messages when parsing fails
- **Validation Checks**: Ensures valid column definitions and table names are found before processing
### Error Types
- **`SqlValidationError`**: Raised when SQL parsing fails or validation checks fail
- **`SqlFileError`**: Raised for file operation errors (file not found, permission denied, etc.)
- **Clear Context**: Error messages include file paths, referenced tables, and available columns for debugging
### Example Error Handling
```python
from splurge_sql_generator.exceptions import SqlValidationError, SqlFileError
try:
# This will raise SqlValidationError if no valid columns are found
columns = parse_table_columns("CONSTRAINT pk_id PRIMARY KEY (id)")
except SqlValidationError as e:
print(f"SQL validation failed: {e}")
try:
# This will raise SqlValidationError if no table names are found
tables = extract_table_names("SELECT 1 as value")
except SqlValidationError as e:
print(f"SQL validation failed: {e}")
try:
# This will raise SqlValidationError for empty input
columns = parse_table_columns("")
except SqlValidationError as e:
print(f"SQL validation failed: {e}")
```
## Development
### Running Tests
```bash
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
---
Raw data
{
"_id": null,
"home_page": null,
"name": "splurge-sql-generator",
"maintainer": "Jim Schilling",
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": "sql, code-generation, python, sqlalchemy, jinja2",
"author": "Jim Schilling",
"author_email": null,
"download_url": "https://files.pythonhosted.org/packages/5a/34/971b51057aae3bb553a818f10e9dc966c841bebb41bcd8c36ea86369a1c8/splurge_sql_generator-2025.5.0.tar.gz",
"platform": null,
"description": "# splurge-sql-generator\r\n\r\n[](https://pypi.org/project/splurge-sql-generator/)\r\n[](https://pypi.org/project/splurge-sql-generator/)\r\n[](https://opensource.org/licenses/MIT)\r\n\r\n[](https://github.com/jim-schilling/splurge-sql-generator/actions/workflows/ci-quick-test.yml)\r\n[](https://github.com/jim-schilling/splurge-sql-generator/actions/workflows/ci-coverage.yml)\r\n[](https://github.com/astral-sh/ruff)\r\n[](https://mypy-lang.org/)\r\n\r\nA compact summary of splurge-sql-generator \u2014 a tool to generate Python (SQLAlchemy-friendly) classes from SQL template files with schema-aware type inference.\r\n\r\nSee the full documentation and details in the repository:\r\n\r\n- Detailed docs: `docs/README-DETAILS.md`\r\n- API reference: `docs/api/API-REFERENCE.md`\r\n- CLI reference: `docs/cli/CLI-REFERENCE.md`\r\n- Full changelog: `CHANGELOG.md`\r\n\r\nKey features\r\n\r\n- SQL template parsing and method extraction from commented templates\r\n- Schema-based type inference for accurate Python type annotations\r\n- Configurable SQL-to-Python type mapping via YAML files (`types.yaml` / `--types`)\r\n- CLI for batch generation with automatic schema discovery and `--generate-types`\r\n- Strong SQL parsing with sqlparse and explicit error reporting\r\n\r\nGetting started\r\n\r\n1. Install: `pip install splurge-sql-generator` or `pip install -e .` for development\r\n2. Read the full docs in `docs/README-DETAILS.md`\r\n\r\nFor examples and advanced usage see `examples/` in the repository.\r\nParse column definitions from table body using sqlparse tokens. Raises `SqlValidationError` if parsing fails or no valid columns are found.\r\n\r\n## What's new (2025.5.0)\r\n\r\n- Centralized Safe I/O adapter (`SafeTextFileIoAdapter`) for consistent file error handling and simplified I/O usage across the library.\r\n- Stabilized cross-version error messages and improved validation for schema file arguments.\r\n- Documentation: comprehensive API and CLI reference files added to `docs/`.\r\n\r\n#### `extract_table_names(sql_query: str) -> list[str]`\r\nExtract table names from SQL query using sqlparse. Raises `SqlValidationError` if parsing fails or no table names are found.\r\n\r\n#### `generate_types_file(*, output_path: str | None = None) -> str`\r\nGenerate the default SQL type mapping YAML file.\r\n\r\n## Supported SQL Features\r\n\r\n- **Basic DML**: SELECT, INSERT, UPDATE, DELETE\r\n- **CTEs**: Common Table Expressions (WITH clauses)\r\n- **Complex Queries**: Subqueries, JOINs, aggregations\r\n- **Database-Specific**: SHOW, EXPLAIN, DESCRIBE, VALUES\r\n- **Parameters**: Named parameters with `:param_name` syntax\r\n- **Comments**: Single-line (`--`) and multi-line (`/* */`) comments\r\n\r\n## Generated Code Features\r\n\r\n- **Accurate Type Hints**: Schema-based type inference for precise parameter and return value annotations\r\n- **Custom Type Support**: Configurable SQL-to-Python type mappings for project-specific needs\r\n- **Parameter Validation**: Optional validation of SQL parameters against schema definitions\r\n- **Multi-Database Types**: Built-in support for SQLite, PostgreSQL, MySQL, MSSQL, and Oracle types\r\n- **Docstrings**: Comprehensive documentation for each method\r\n- **Error Handling**: Proper SQLAlchemy result handling with fail-fast validation\r\n- **Parameter Mapping**: Automatic mapping of SQL parameters to Python arguments with inferred types\r\n- **Statement Type Detection**: Correct return types based on SQL statement type\r\n- **Auto-Generated Headers**: Clear identification of generated files\r\n\r\n## Error Handling and Validation\r\n\r\nThe library provides robust error handling with a fail-fast approach to ensure data integrity and clear error reporting:\r\n\r\n### SQL Parsing Validation\r\n- **Strict SQL Parsing**: Functions like `parse_table_columns()` and `extract_table_names()` use sqlparse for reliable parsing\r\n- **No Fallback Mechanisms**: Eliminates unreliable regex-based fallback parsing in favor of clear error reporting\r\n- **Clear Error Messages**: Functions raise `SqlValidationError` with descriptive messages when parsing fails\r\n- **Validation Checks**: Ensures valid column definitions and table names are found before processing\r\n\r\n### Error Types\r\n- **`SqlValidationError`**: Raised when SQL parsing fails or validation checks fail\r\n- **`SqlFileError`**: Raised for file operation errors (file not found, permission denied, etc.)\r\n- **Clear Context**: Error messages include file paths, referenced tables, and available columns for debugging\r\n\r\n### Example Error Handling\r\n```python\r\nfrom splurge_sql_generator.exceptions import SqlValidationError, SqlFileError\r\n\r\ntry:\r\n # This will raise SqlValidationError if no valid columns are found\r\n columns = parse_table_columns(\"CONSTRAINT pk_id PRIMARY KEY (id)\")\r\nexcept SqlValidationError as e:\r\n print(f\"SQL validation failed: {e}\")\r\n\r\ntry:\r\n # This will raise SqlValidationError if no table names are found\r\n tables = extract_table_names(\"SELECT 1 as value\")\r\nexcept SqlValidationError as e:\r\n print(f\"SQL validation failed: {e}\")\r\n\r\ntry:\r\n # This will raise SqlValidationError for empty input\r\n columns = parse_table_columns(\"\")\r\nexcept SqlValidationError as e:\r\n print(f\"SQL validation failed: {e}\")\r\n```\r\n\r\n## Development\r\n\r\n### Running Tests\r\n\r\n```bash\r\npython -m unittest discover -s tests -v\r\n```\r\n\r\n### Project Structure\r\n\r\n```\r\nsplurge-sql-generator/\r\n\u251c\u2500\u2500 splurge_sql_generator/\r\n\u2502 \u251c\u2500\u2500 __init__.py # Main package exports\r\n\u2502 \u251c\u2500\u2500 sql_helper.py # SQL parsing utilities\r\n\u2502 \u251c\u2500\u2500 sql_parser.py # SQL template parser\r\n\u2502 \u251c\u2500\u2500 schema_parser.py # SQL schema parser for type inference\r\n\u2502 \u251c\u2500\u2500 code_generator.py # Python code generator\r\n\u2502 \u251c\u2500\u2500 cli.py # Command-line interface\r\n\u2502 \u2514\u2500\u2500 templates/ # Jinja2 templates (python_class.j2)\r\n\u251c\u2500\u2500 tests/ # Test suite\r\n\u251c\u2500\u2500 examples/ # Example SQL templates and schemas\r\n\u2502 \u251c\u2500\u2500 *.sql # SQL template files\r\n\u2502 \u251c\u2500\u2500 *.schema # SQL schema files for type inference\r\n\u2502 \u2514\u2500\u2500 custom_types.yaml # Example custom type mapping\r\n\u251c\u2500\u2500 output/ # Generated code examples\r\n\u2514\u2500\u2500 types.yaml # Default SQL type mappings\r\n```\r\n\r\n## License\r\n\r\nMIT License - see LICENSE file for details.\r\n\r\n## Contributing\r\n\r\n1. Fork the repository\r\n2. Create a feature branch\r\n3. Make your changes\r\n4. Add tests for new functionality\r\n5. Run the test suite\r\n6. Submit a pull request\r\n\r\n---\r\n",
"bugtrack_url": null,
"license": null,
"summary": "A Python tool for generating Python classes from SQL files",
"version": "2025.5.0",
"project_urls": {
"Changelog": "https://github.com/jim-schilling/splurge-sql-generator/blob/main/CHANGELOG.md",
"Documentation": "https://github.com/jim-schilling/splurge-sql-generator#readme",
"Homepage": "https://github.com/jim-schilling/splurge-sql-generator",
"Issues": "https://github.com/jim-schilling/splurge-sql-generator/issues",
"Repository": "https://github.com/jim-schilling/splurge-sql-generator"
},
"split_keywords": [
"sql",
" code-generation",
" python",
" sqlalchemy",
" jinja2"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "380b9ea42ae36c1b3c6418b5705c1334a862c4175d14b47b4147e59b5c927b98",
"md5": "8a402bd84b212ff111f0eace0b2d04ee",
"sha256": "5f97acbb83617dc27b63e85d922fd11422cac7261321e2e776178144dc9c511a"
},
"downloads": -1,
"filename": "splurge_sql_generator-2025.5.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "8a402bd84b212ff111f0eace0b2d04ee",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.10",
"size": 40902,
"upload_time": "2025-10-19T22:58:59",
"upload_time_iso_8601": "2025-10-19T22:58:59.482545Z",
"url": "https://files.pythonhosted.org/packages/38/0b/9ea42ae36c1b3c6418b5705c1334a862c4175d14b47b4147e59b5c927b98/splurge_sql_generator-2025.5.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "5a34971b51057aae3bb553a818f10e9dc966c841bebb41bcd8c36ea86369a1c8",
"md5": "d0b9d060cecca47ab17650bf624e6a77",
"sha256": "92a957d9c93668f3c92eb4d9c644ee090f54cabd9817bc39155536066c315d06"
},
"downloads": -1,
"filename": "splurge_sql_generator-2025.5.0.tar.gz",
"has_sig": false,
"md5_digest": "d0b9d060cecca47ab17650bf624e6a77",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 37964,
"upload_time": "2025-10-19T22:59:00",
"upload_time_iso_8601": "2025-10-19T22:59:00.695809Z",
"url": "https://files.pythonhosted.org/packages/5a/34/971b51057aae3bb553a818f10e9dc966c841bebb41bcd8c36ea86369a1c8/splurge_sql_generator-2025.5.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-19 22:59:00",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "jim-schilling",
"github_project": "splurge-sql-generator",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "splurge-sql-generator"
}
JSON
