Python Flask SQLite Best Practices
Expert guidance for modern Python development with Flask web framework, SQLite databases, and production-ready patterns.
Project Structure Standards
**src-layout**: Organize code in `src/your_package_name/` for proper packaging**tests/**: Place all tests parallel to `src/` directory**config/**: Store configuration files or use environment variables**requirements.txt** or **pyproject.toml**: Pin all dependencies**static/**: Static assets (CSS, JS, images)**templates/**: Jinja2 HTML templatesCode Quality
Formatting
Apply **Black** code formatter (88 character line length)Use **isort** for consistent import orderingFollow **PEP 8** naming conventions: - `snake_case` for functions and variables
- `PascalCase` for classes
- `UPPER_CASE` for constants
Prefer absolute imports over relative importsType Hints
Add type hints to all function parameters and return valuesImport from `typing` module (`Optional`, `List`, `Dict`, etc.)Use `Optional[Type]` instead of `Type | None`Define custom types in dedicated `types.py` moduleUse `TypeVar` for generics and `Protocol` for duck typingDocumentation
Write **Google-style docstrings** for all public functions and classesDocument parameters, return values, and exceptionsKeep README.md current with setup and usage instructionsUse inline comments for complex logicGenerate API documentation from docstringsFlask REST API Design
Use **Flask-RESTful** for building REST APIsValidate all incoming requests with schemas (marshmallow, pydantic)Return proper **HTTP status codes** (200, 201, 400, 404, 500)Implement consistent error response formatUse JSON for request/response bodiesImplement rate limiting (Flask-Limiter)Database Best Practices
Use **SQLite** for development and small-to-medium production workloadsImplement proper connection poolingOptimize queries with indexes and query analysisUse transactions for multi-step operationsImplement pagination for large result setsClose connections properly in error scenariosTesting with pytest
Write tests for all routes and business logicUse **pytest** as the test frameworkImplement **fixtures** for test data and setupUse **pytest-mock** for mocking external dependenciesAchieve high code coverage with **pytest-cov**Test both success and error scenariosOrganize tests to mirror source structureSecurity
**Always use HTTPS** in production environmentsConfigure **CORS** properly for browser clients**Sanitize all user inputs** to prevent injection attacksUse secure session configuration (httpOnly, secure flags)Implement comprehensive logging for security eventsFollow **OWASP Top 10** guidelinesNever commit secrets (use environment variables or secret managers)Performance Optimization
Use **Flask-Caching** for frequently accessed dataOptimize database queries (avoid N+1 queries)Implement connection pooling for database accessUse pagination for large datasetsOffload heavy operations to background tasks (Celery, RQ)Monitor application performance with APM toolsError Handling
Create **custom exception classes** for domain errorsUse `try-except` blocks strategicallyLog all errors with context (use Python `logging` module)Return user-friendly error messages in API responsesHandle edge cases explicitlyAvoid bare `except:` clauses (catch specific exceptions)Development Workflow
Use **virtual environments** (venv or virtualenv)Set up **pre-commit hooks** (black, isort, flake8)Follow Git workflow (feature branches, pull requests)**Do NOT push to main without permission**Apply **semantic versioning** (MAJOR.MINOR.PATCH)Implement CI/CD pipelines for automated testing and deploymentDependency Management
**Pin exact versions** in `requirements.txt` for reproducibilitySeparate production and dev dependenciesRegularly update dependencies and check for CVEsUse tools like `pip-audit` or `safety` for vulnerability scanningDocument any version constraints in commentsImplementation Steps
1. **Initialize project** with src-layout structure
2. **Set up virtual environment** and install dependencies
3. **Configure Black and isort** in pre-commit hooks
4. **Add type hints** to all functions
5. **Write pytest tests** for existing code
6. **Implement Flask-RESTful routes** with validation
7. **Configure Flask-Caching** for performance
8. **Set up logging** and error handling
9. **Review OWASP guidelines** and apply security measures
10. **Document all public APIs** with Google-style docstrings
Constraints
Never commit without running Black formatter and isortAll new functions must include type hintsAll routes require at least one pytest test caseNo bare except clauses allowedAll secrets must be in environment variables (never hardcoded)Do not push to main branch without explicit permission