Use Cookiecutter to automate the creation of projects

[ikerson]

What is a Cookiecutter Template?

A Cookiecutter template is a structured project template that allows users to quickly generate new projects with a predefined folder structure, files, and customizable settings. It is especially useful for standardizing project setups, such as your SQL Report Template, where users might want to personalize aspects like:

• Repository name
• Database connection details
• Default SQL query folder
• Output report location

The tool Cookiecutter automates the creation of these projects by prompting users for inputs and generating a project based on a predefined template.

---

Why Use Cookiecutter?

• 🚀 Customization → Users can input their own values (e.g., database credentials, report names).
• 🔄 Reproducibility → Ensures a standardized structure for all reports.
• 🏗️ Scalability → Can be used for multiple teams or projects.
• ⏳ Time-Saving → Avoids repetitive setup steps.

---

How to Create a Cookiecutter Template for Your SQL Report Project

Instead of manually cloning and renaming files, you can create a Cookiecutter template so that users just run:

cookiecutter https://github.com/yourusername/sql-report-template

This will generate a fresh project with their chosen settings.

---

Steps to Convert sql-report-template into a Cookiecutter Template

1. Structure Your Repository as a Template
   Create a new directory structure with Jinja2 placeholders ({{ cookiecutter.project_name }}) where users will provide their own values.

cookiecutter-sql-report-template/
│── {{ cookiecutter.project_name }}/       # Project folder will be named based on user input
│   ├── report_generator/                  # Core report scripts
│   │   ├── __init__.py
│   │   ├── query_runner.py
│   │   ├── excel_report_generator.py
│   ├── working_queries/                    # SQL query directory
│   │   ├── example_query.sql
│   ├── report/                              # Output directory
│   ├── config.py                            # Config file (templated)
│   ├── main.py                              # Main script (templated)
│   ├── environment.yml                      # Conda environment
│   ├── requirements.txt                     # Pip dependencies
│   ├── README.md                            # Documentation
│── cookiecutter.json                        # Defines user prompts

---

2. Create cookiecutter.json
   This file defines the prompts users will see when generating their project.

{
  "project_name": "sql-report",
  "repo_name": "sql-report-template",
  "author_name": "Your Name",
  "database_user": "your_username",
  "database_dsn": "your_dsn",
  "database_lib_dir": "/path/to/oracle/lib",
  "default_sql_folder": "./working_queries",
  "output_file": "./report/Program_Report.xlsx"
}

---

3. Modify config.py and main.py to Use Placeholders
   Replace hardcoded values with Jinja2-style placeholders.

config.py

"""
config.py

Database connection settings for the SQL report generator.
Modify these values or use environment variables for security.
"""

user = "{{ cookiecutter.database_user }}"
dsn = "{{ cookiecutter.database_dsn }}"
lib_dir = "{{ cookiecutter.database_lib_dir }}"

main.py

import os
from config import user, dsn, lib_dir
from report_generator import QueryRunner, ExcelReportGenerator
import argparse

# Introduction text
intro_text = [
    "This workbook contains detailed enrollment and retention reports.",
    "Report generated automatically.",
    "",
    "Each SQL file has been processed into its own sheet."
]

def main():
    parser = argparse.ArgumentParser(description="Run SQL queries and generate an Excel report.")
    parser.add_argument(
        "--sql_folder_path", 
        type=str, 
        default="{{ cookiecutter.default_sql_folder }}", 
        help="Path to the folder containing SQL files."
    )
    parser.add_argument(
        "--output_file", 
        type=str, 
        default="{{ cookiecutter.output_file }}", 
        help="Path to save the output Excel report."
    )
    
    args = parser.parse_args()
    os.makedirs(os.path.dirname(args.output_file), exist_ok=True)

    runner = QueryRunner(user, dsn, lib_dir)
    results = runner.run_queries_from_folder(args.sql_folder_path)
    
    report_generator = ExcelReportGenerator(results, intro_text)
    report_generator.generate_workbook(args.output_file)

if __name__ == '__main__':
    main()

---

How Users Can Use This Template

Once you've set up the repository as a Cookiecutter template:

1. Users install Cookiecutter:

   pip install cookiecutter

2. Run the template generator:

   cookiecutter https://github.com/yourusername/sql-report-template

3. They answer the prompts:

   project_name [sql-report]: my-custom-report
   repo_name [sql-report-template]: custom-report
   author_name [Your Name]: Alice
   database_user [your_username]: alice_db
   database_dsn [your_dsn]: mydb.example.com
   database_lib_dir [/path/to/oracle/lib]: /opt/oracle/lib
   

4. A new project is generated with their inputs in a folder named my-custom-report.

---

Benefits of Using Cookiecutter for Your SQL Report Template

✅ Fully Customizable – Users input their own database settings, output file paths, and folder names.
✅ Quick Setup – New projects are generated instantly without manual renaming.
✅ Reproducibility – Ensures all generated projects follow the same structure.
✅ Scalability – Easily extendable to support additional configurations.

---

Next Steps

• Convert sql-report-template into a Cookiecutter-ready repository.
• Test by generating a new project.
• Push the changes to GitHub and document how users can use it.