{"owner":"pudo","repo":"dataset","hasSkills":true,"totalSkillsCount":1,"totalTokensCount":2939,"categories":["claude-rule"],"hasMcp":false,"mcpConfig":null,"found":["CLAUDE.md"],"skills":{"CLAUDE.md":"# dataset: databases for lazy people\n\n## Project Overview\n\n**dataset** is a lightweight Python library that makes reading and writing data in databases as simple as working with JSON files. It provides a simple abstraction layer on top of SQLAlchemy, removing most direct SQL statements without requiring a full ORM model.\n\n**Key Philosophy:** Simplicity over complexity. The library is designed for small to medium-scale datasets where you want database benefits without the overhead of complex ORM patterns.\n\n## Repository Structure\n\n```\ndataset/\n├── dataset/              # Main package\n│   ├── __init__.py      # Main entry point with connect()\n│   ├── database.py      # Database class - connection, transactions, queries\n│   ├── table.py         # Table class - CRUD operations, schema management\n│   ├── types.py         # SQLAlchemy type mapping and helpers\n│   ├── util.py          # Utility functions (ResultIter, normalization, etc.)\n│   └── chunked.py       # Chunked operations for large datasets\n├── test/                # Test suite (pytest)\n│   ├── conftest.py      # Test fixtures\n│   ├── test_database.py\n│   ├── test_table.py\n│   └── test_row_type.py\n├── docs/                # Sphinx documentation (RST format)\n└── pyproject.toml       # Package configuration (PEP 621)\n```\n\n## Core Concepts\n\n### 1. Database Connection\n- Single entry point: `dataset.connect(url)` returns a `Database` instance\n- Supports SQLite (default), PostgreSQL, and MySQL\n- Connection pooling managed by SQLAlchemy\n- Environment variable fallback: `DATABASE_URL`\n\n### 2. Automatic Schema Management\n- **Default behavior:** Tables and columns are created automatically on insert\n- Can be disabled via `ensure_schema=False` parameter\n- Column types are guessed from values (or explicitly specified)\n- Thread-safe with locking for schema operations\n\n### 3. CRUD Operations\n- **insert()**: Add single row, returns primary key\n- **insert_many()**: Bulk insert with chunking (default: 1000 rows)\n- **update()**: Update rows matching keys\n- **upsert()**: Insert or update based on key columns\n- **delete()**: Delete rows matching filters\n- **find()**: Query with filters, ordering, limit/offset\n- **find_one()**: Return single row or None\n\n### 4. Transaction Support\n- Context manager: `with dataset.connect() as tx:`\n- Manual control: `db.begin()`, `db.commit()`, `db.rollback()`\n- Nested transactions supported\n- SQLAlchemy 2.x \"autobegin\" semantics with explicit commit tracking\n\n## Important Implementation Details\n\n### Threading & Concurrency\n- Each thread gets its own database connection (thread-local storage)\n- Schema changes in transactions with multiple threads trigger warnings\n- Locking strategy:\n  - `self.lock` (RLock) protects metadata operations\n  - Keep lock scope small for performance\n  - Schema operations are fully synchronized\n\n### Transaction Handling (SQLAlchemy 2.x Migration)\n- The library supports both SQLAlchemy 1.4+ and 2.x\n- Key change: SQLAlchemy 2.x uses \"autobegin\" (transactions start on first use)\n- `db._auto_commit()` commits after writes when not in explicit transaction\n- Transaction nesting tracked via `self.local.tx` stack\n\n### Column & Table Name Normalization\n- Case-insensitive column matching via `normalize_column_key()`\n- Actual database names preserved via `_column_keys` mapping\n- Tables and columns validated for safety\n\n### Type System\n- `db.types` provides shortcuts to SQLAlchemy types\n- Type guessing from Python values: `types.guess(value)`\n- Custom types via `types` parameter in insert/update\n- MySQL-specific: text field indexing uses 10-char prefix\n\n### Type Annotations\n- The codebase passes `mypy --strict` and ships `py.typed` (PEP 561)\n- Key type aliases in `util.py`:\n  - `WriteRow = Mapping[str, SQLWriteValue]` — immutable input type for public API\n  - `MutableRow = dict[str, SQLWriteValue]` — mutable type for internal use (queue items, return values from `_sync_columns`)\n  - `OutRow = Mapping[str, Any]` — query result rows\n  - `RowFactory = Callable[[Iterable[tuple[str, Any]]], OutRow]` — the `row_type` parameter type\n- `ColumnType` in `types.py` is `TypeEngine[Any] | type[TypeEngine[Any]]` — used for `primary_type` and `create_column`\n- Use `WriteRow` at public API boundaries, `MutableRow` internally where mutation happens\n- `ensure_strings()` in `util.py` replaces `banal.ensure_list` — accepts `str | Iterable[str] | None`, returns `list[str]`\n- `QueryError` (subclass of `DatasetError`) is raised for invalid filter values in `_generate_clause`\n\n## Testing Strategy\n\n### Test Infrastructure\n- **Framework:** pytest\n- **Fixtures:**\n  - `db`: Function-scoped database connection with cleanup\n  - `table`: Pre-populated weather table with test data\n- **CI/CD:** GitHub Actions testing against SQLite, PostgreSQL, MySQL\n- **Database Cleanup:** Explicit rollback + drop tables in teardown\n\n### Running Tests\n```bash\nmake test                    # Run all tests\npytest test/test_table.py    # Run specific test file\nDATABASE_URL=\"postgresql://...\" pytest  # Test against specific DB\n```\n\n### Test Database Configuration\n- Default: SQLite in-memory (`:memory:`)\n- Override via `DATABASE_URL` environment variable\n- CI uses Docker containers for PostgreSQL and MySQL\n\n## Code Style & Quality\n\n### Tools\n- **Build System:** Hatchling (modern PEP 621 compliant)\n- **Linting & Formatting:** Ruff with default recommended rules\n- **Type Checking:** mypy `--strict` (zero errors; runs as part of `make lint`)\n\n### Running Checks\n\n```bash\nmake lint          # ruff + mypy --strict\nmake format-check  # Check formatting without applying\nmake format        # Apply formatting\n```\n\nThe codebase passes both ruff and `mypy --strict` with zero errors.\n\n### Best Practices\n1. **Keep it simple:** The library's strength is simplicity - don't over-engineer\n2. **Thread safety:** Always use locks for schema operations\n3. **Auto-commit:** Remember to call `db._auto_commit()` after writes outside transactions\n4. **Column normalization:** Use `_get_column_name()` for case-insensitive matching\n5. **Error handling:** Use `DatasetError` for dataset-specific errors; `QueryError` for invalid filters\n6. **Exception naming:** Use `Error` suffix for exception classes (e.g., `InvalidCallbackError`)\n7. **Linting:** Run `make lint` before committing (runs both ruff and mypy --strict)\n8. **Backward compatibility:** Do not rename public API parameters or methods — this library is widely deployed. Prefer `# noqa` over breaking changes (e.g., `create_column`'s `type` parameter shadows the builtin, but renaming it would break callers)\n9. **Type annotations:** All new code must pass `mypy --strict`. Use `WriteRow` for public input params, `MutableRow` for internal mutable dicts. Don't copy rows unnecessarily — only copy at the boundary where mutation is needed\n10. **Version management:** Versions are managed by `bump2version`, not by editing `__init__.py` directly\n\n## Common Development Tasks\n\n### Adding a New Table Method\n1. Add method to `Table` class in [table.py](dataset/table.py)\n2. Use `self._sync_columns()` if it modifies schema\n3. Call `self.db._auto_commit()` after database writes\n4. Add docstring with example code\n5. Update [docs/api.rst](docs/api.rst) if needed\n6. Write tests in [test/test_table.py](test/test_table.py)\n\n### Adding a New Query Operator\n1. Add operator to `_generate_clause()` in [table.py](dataset/table.py:398-430)\n2. Document in [docs/queries.rst](docs/queries.rst)\n3. Add test cases for the operator\n\n### Modifying Transaction Behavior\n- Be careful: both nested and non-nested transactions must work\n- Test with multiple threads\n- Transaction state tracked in `self.local.tx` (thread-local)\n- See `begin()`, `commit()`, `rollback()` in [database.py](dataset/database.py:134-175)\n\n## Known Limitations\n\n**Explicitly out of scope:**\n- Foreign key relationships and ORM-style relations\n- Python-wrapped JOIN queries\n- Database creation or DBMS management\n- Python 2.x support (dropped in 1.2.0)\n- Async operations\n- Database-native UPSERT (library implements via SELECT + INSERT/UPDATE)\n\n**Technical constraints:**\n- SQLite doesn't support dropping columns\n- MySQL requires prefix length for text field indexes\n- Very large text fields may not be fully indexed\n\n## Migration Notes\n\n### v2.0 (upcoming)\n- Full `mypy --strict` compliance, `py.typed` marker (PEP 561)\n- Removed `banal` dependency (replaced with `ensure_strings` in util.py)\n- `RowFactory` callable type replaces `type` for `row_type` parameter\n- `QueryError` added for invalid filter operations\n- `insert`/`insert_ignore`/`upsert` return `Any` (primary keys can be any type)\n- `primary_type` parameter typed as `ColumnType` instead of `Types`\n- `update_many` no longer mutates input rows\n\n### Modern Build System (v1.7+)\n- Migrated from setuptools to Hatchling with pyproject.toml (PEP 621)\n- Use `python -m build` instead of `setup.py sdist bdist_wheel`\n- Version automatically read from `dataset/__init__.py`\n- All configuration now in `pyproject.toml`\n\n### SQLAlchemy 2.x Support (v1.6+)\n- Supports SQLAlchemy 1.4.0 through 2.x\n- Key changes: autobegin semantics, connection handling\n- `_auto_commit()` method handles post-write commits\n- Transaction tracking via `in_transaction` property\n\n### Breaking Changes (v1.0)\n- Data export features extracted to separate `datafreeze` package\n- Text-based `primary_type` dropped - use `db.types` instead\n- Advanced `__getitem__` syntax for primary keys removed\n\n## Dependencies\n\n**Core:**\n- `sqlalchemy >= 1.4.0, < 3.0.0` - Database abstraction\n- `alembic >= 0.6.2` - Schema migrations\n\n**Development:**\n- `pytest` - Testing framework\n- `ruff` - Linting and formatting\n- `mypy` - Type checking (strict mode)\n- `build` - Modern Python package builder\n- `twine` - Package upload to PyPI\n- `psycopg2-binary` - PostgreSQL driver\n- `PyMySQL` - MySQL driver\n- `cryptography` - For secure connections\n\n## Documentation\n\n- **Official docs:** https://dataset.readthedocs.io/\n- **Source:** [docs/](docs/) folder (Sphinx/RST format)\n- Build docs: `cd docs && make html`\n\n## Release Process\n\n1. Run `bump2version patch` (or `minor`/`major`) to update version everywhere (do NOT edit `__init__.py` version manually)\n3. Update [CHANGELOG.md](CHANGELOG.md) with release notes\n4. Run full test suite: `make test`\n5. Run linting: `make lint`\n6. Build distributions: `make dists` (creates wheel and sdist)\n7. Tag release: `git tag -a v1.x.x -m \"Release 1.x.x\"`\n8. Push commits and tag: `git push && git push --tags`\n9. GitHub Actions will automatically publish to PyPI on tag push\n\n## Special Considerations for AI Assistants\n\n### When Modifying Code\n- **Test thoroughly:** Changes affect SQLite, PostgreSQL, AND MySQL\n- **Thread safety:** Schema changes require locking\n- **Backward compatibility:** This is a stable library used by many projects\n- **Document examples:** Include usage examples in docstrings\n\n### When Debugging\n- Check which database backend is being used (`db.is_sqlite`, `db.is_postgres`, `db.is_mysql`)\n- Look at thread-local state (`self.local.tx`) for transaction issues\n- Column name mismatches often due to case sensitivity - check `_column_keys`\n- SQLAlchemy version differences may affect behavior\n\n### Common Pitfalls\n1. Forgetting to call `_auto_commit()` after writes\n2. Not using locks for schema operations\n3. Assuming case-sensitive column names\n4. Not handling the difference between SQLite/PostgreSQL/MySQL\n5. Breaking nested transaction semantics\n6. Mutating input rows — always copy to `dict()` before modifying (see `update_many`, `_queue_add`)\n7. Shadowing variable names with processed versions (e.g., `columns` string list vs `columns_` Column objects) — use distinct names\n8. Using `type: ignore` without checking if the underlying issue can be fixed first — only suppress SQLAlchemy stub limitations and `**kwargs` forwarding\n\n## Use Cases\n\n**dataset** is ideal for:\n- Quick data loading scripts\n- Simple ETL operations\n- Testing and prototyping\n- Small utility databases\n- Data exploration and analysis\n- Situations where ORM overhead isn't needed\n\nWhen to use full SQLAlchemy ORM instead:\n- Complex relational models with foreign keys\n- Need for relationship management\n- Advanced query patterns with JOINs\n- Large-scale production applications\n"},"files":{"CLAUDE.md":"# dataset: databases for lazy people\n\n## Project Overview\n\n**dataset** is a lightweight Python library that makes reading and writing data in databases as simple as working with JSON files. It provides a simple abstraction layer on top of SQLAlchemy, removing most direct SQL statements without requiring a full ORM model.\n\n**Key Philosophy:** Simplicity over complexity. The library is designed for small to medium-scale datasets where you want database benefits without the overhead of complex ORM patterns.\n\n## Repository Structure\n\n```\ndataset/\n├── dataset/              # Main package\n│   ├── __init__.py      # Main entry point with connect()\n│   ├── database.py      # Database class - connection, transactions, queries\n│   ├── table.py         # Table class - CRUD operations, schema management\n│   ├── types.py         # SQLAlchemy type mapping and helpers\n│   ├── util.py          # Utility functions (ResultIter, normalization, etc.)\n│   └── chunked.py       # Chunked operations for large datasets\n├── test/                # Test suite (pytest)\n│   ├── conftest.py      # Test fixtures\n│   ├── test_database.py\n│   ├── test_table.py\n│   └── test_row_type.py\n├── docs/                # Sphinx documentation (RST format)\n└── pyproject.toml       # Package configuration (PEP 621)\n```\n\n## Core Concepts\n\n### 1. Database Connection\n- Single entry point: `dataset.connect(url)` returns a `Database` instance\n- Supports SQLite (default), PostgreSQL, and MySQL\n- Connection pooling managed by SQLAlchemy\n- Environment variable fallback: `DATABASE_URL`\n\n### 2. Automatic Schema Management\n- **Default behavior:** Tables and columns are created automatically on insert\n- Can be disabled via `ensure_schema=False` parameter\n- Column types are guessed from values (or explicitly specified)\n- Thread-safe with locking for schema operations\n\n### 3. CRUD Operations\n- **insert()**: Add single row, returns primary key\n- **insert_many()**: Bulk insert with chunking (default: 1000 rows)\n- **update()**: Update rows matching keys\n- **upsert()**: Insert or update based on key columns\n- **delete()**: Delete rows matching filters\n- **find()**: Query with filters, ordering, limit/offset\n- **find_one()**: Return single row or None\n\n### 4. Transaction Support\n- Context manager: `with dataset.connect() as tx:`\n- Manual control: `db.begin()`, `db.commit()`, `db.rollback()`\n- Nested transactions supported\n- SQLAlchemy 2.x \"autobegin\" semantics with explicit commit tracking\n\n## Important Implementation Details\n\n### Threading & Concurrency\n- Each thread gets its own database connection (thread-local storage)\n- Schema changes in transactions with multiple threads trigger warnings\n- Locking strategy:\n  - `self.lock` (RLock) protects metadata operations\n  - Keep lock scope small for performance\n  - Schema operations are fully synchronized\n\n### Transaction Handling (SQLAlchemy 2.x Migration)\n- The library supports both SQLAlchemy 1.4+ and 2.x\n- Key change: SQLAlchemy 2.x uses \"autobegin\" (transactions start on first use)\n- `db._auto_commit()` commits after writes when not in explicit transaction\n- Transaction nesting tracked via `self.local.tx` stack\n\n### Column & Table Name Normalization\n- Case-insensitive column matching via `normalize_column_key()`\n- Actual database names preserved via `_column_keys` mapping\n- Tables and columns validated for safety\n\n### Type System\n- `db.types` provides shortcuts to SQLAlchemy types\n- Type guessing from Python values: `types.guess(value)`\n- Custom types via `types` parameter in insert/update\n- MySQL-specific: text field indexing uses 10-char prefix\n\n### Type Annotations\n- The codebase passes `mypy --strict` and ships `py.typed` (PEP 561)\n- Key type aliases in `util.py`:\n  - `WriteRow = Mapping[str, SQLWriteValue]` — immutable input type for public API\n  - `MutableRow = dict[str, SQLWriteValue]` — mutable type for internal use (queue items, return values from `_sync_columns`)\n  - `OutRow = Mapping[str, Any]` — query result rows\n  - `RowFactory = Callable[[Iterable[tuple[str, Any]]], OutRow]` — the `row_type` parameter type\n- `ColumnType` in `types.py` is `TypeEngine[Any] | type[TypeEngine[Any]]` — used for `primary_type` and `create_column`\n- Use `WriteRow` at public API boundaries, `MutableRow` internally where mutation happens\n- `ensure_strings()` in `util.py` replaces `banal.ensure_list` — accepts `str | Iterable[str] | None`, returns `list[str]`\n- `QueryError` (subclass of `DatasetError`) is raised for invalid filter values in `_generate_clause`\n\n## Testing Strategy\n\n### Test Infrastructure\n- **Framework:** pytest\n- **Fixtures:**\n  - `db`: Function-scoped database connection with cleanup\n  - `table`: Pre-populated weather table with test data\n- **CI/CD:** GitHub Actions testing against SQLite, PostgreSQL, MySQL\n- **Database Cleanup:** Explicit rollback + drop tables in teardown\n\n### Running Tests\n```bash\nmake test                    # Run all tests\npytest test/test_table.py    # Run specific test file\nDATABASE_URL=\"postgresql://...\" pytest  # Test against specific DB\n```\n\n### Test Database Configuration\n- Default: SQLite in-memory (`:memory:`)\n- Override via `DATABASE_URL` environment variable\n- CI uses Docker containers for PostgreSQL and MySQL\n\n## Code Style & Quality\n\n### Tools\n- **Build System:** Hatchling (modern PEP 621 compliant)\n- **Linting & Formatting:** Ruff with default recommended rules\n- **Type Checking:** mypy `--strict` (zero errors; runs as part of `make lint`)\n\n### Running Checks\n\n```bash\nmake lint          # ruff + mypy --strict\nmake format-check  # Check formatting without applying\nmake format        # Apply formatting\n```\n\nThe codebase passes both ruff and `mypy --strict` with zero errors.\n\n### Best Practices\n1. **Keep it simple:** The library's strength is simplicity - don't over-engineer\n2. **Thread safety:** Always use locks for schema operations\n3. **Auto-commit:** Remember to call `db._auto_commit()` after writes outside transactions\n4. **Column normalization:** Use `_get_column_name()` for case-insensitive matching\n5. **Error handling:** Use `DatasetError` for dataset-specific errors; `QueryError` for invalid filters\n6. **Exception naming:** Use `Error` suffix for exception classes (e.g., `InvalidCallbackError`)\n7. **Linting:** Run `make lint` before committing (runs both ruff and mypy --strict)\n8. **Backward compatibility:** Do not rename public API parameters or methods — this library is widely deployed. Prefer `# noqa` over breaking changes (e.g., `create_column`'s `type` parameter shadows the builtin, but renaming it would break callers)\n9. **Type annotations:** All new code must pass `mypy --strict`. Use `WriteRow` for public input params, `MutableRow` for internal mutable dicts. Don't copy rows unnecessarily — only copy at the boundary where mutation is needed\n10. **Version management:** Versions are managed by `bump2version`, not by editing `__init__.py` directly\n\n## Common Development Tasks\n\n### Adding a New Table Method\n1. Add method to `Table` class in [table.py](dataset/table.py)\n2. Use `self._sync_columns()` if it modifies schema\n3. Call `self.db._auto_commit()` after database writes\n4. Add docstring with example code\n5. Update [docs/api.rst](docs/api.rst) if needed\n6. Write tests in [test/test_table.py](test/test_table.py)\n\n### Adding a New Query Operator\n1. Add operator to `_generate_clause()` in [table.py](dataset/table.py:398-430)\n2. Document in [docs/queries.rst](docs/queries.rst)\n3. Add test cases for the operator\n\n### Modifying Transaction Behavior\n- Be careful: both nested and non-nested transactions must work\n- Test with multiple threads\n- Transaction state tracked in `self.local.tx` (thread-local)\n- See `begin()`, `commit()`, `rollback()` in [database.py](dataset/database.py:134-175)\n\n## Known Limitations\n\n**Explicitly out of scope:**\n- Foreign key relationships and ORM-style relations\n- Python-wrapped JOIN queries\n- Database creation or DBMS management\n- Python 2.x support (dropped in 1.2.0)\n- Async operations\n- Database-native UPSERT (library implements via SELECT + INSERT/UPDATE)\n\n**Technical constraints:**\n- SQLite doesn't support dropping columns\n- MySQL requires prefix length for text field indexes\n- Very large text fields may not be fully indexed\n\n## Migration Notes\n\n### v2.0 (upcoming)\n- Full `mypy --strict` compliance, `py.typed` marker (PEP 561)\n- Removed `banal` dependency (replaced with `ensure_strings` in util.py)\n- `RowFactory` callable type replaces `type` for `row_type` parameter\n- `QueryError` added for invalid filter operations\n- `insert`/`insert_ignore`/`upsert` return `Any` (primary keys can be any type)\n- `primary_type` parameter typed as `ColumnType` instead of `Types`\n- `update_many` no longer mutates input rows\n\n### Modern Build System (v1.7+)\n- Migrated from setuptools to Hatchling with pyproject.toml (PEP 621)\n- Use `python -m build` instead of `setup.py sdist bdist_wheel`\n- Version automatically read from `dataset/__init__.py`\n- All configuration now in `pyproject.toml`\n\n### SQLAlchemy 2.x Support (v1.6+)\n- Supports SQLAlchemy 1.4.0 through 2.x\n- Key changes: autobegin semantics, connection handling\n- `_auto_commit()` method handles post-write commits\n- Transaction tracking via `in_transaction` property\n\n### Breaking Changes (v1.0)\n- Data export features extracted to separate `datafreeze` package\n- Text-based `primary_type` dropped - use `db.types` instead\n- Advanced `__getitem__` syntax for primary keys removed\n\n## Dependencies\n\n**Core:**\n- `sqlalchemy >= 1.4.0, < 3.0.0` - Database abstraction\n- `alembic >= 0.6.2` - Schema migrations\n\n**Development:**\n- `pytest` - Testing framework\n- `ruff` - Linting and formatting\n- `mypy` - Type checking (strict mode)\n- `build` - Modern Python package builder\n- `twine` - Package upload to PyPI\n- `psycopg2-binary` - PostgreSQL driver\n- `PyMySQL` - MySQL driver\n- `cryptography` - For secure connections\n\n## Documentation\n\n- **Official docs:** https://dataset.readthedocs.io/\n- **Source:** [docs/](docs/) folder (Sphinx/RST format)\n- Build docs: `cd docs && make html`\n\n## Release Process\n\n1. Run `bump2version patch` (or `minor`/`major`) to update version everywhere (do NOT edit `__init__.py` version manually)\n3. Update [CHANGELOG.md](CHANGELOG.md) with release notes\n4. Run full test suite: `make test`\n5. Run linting: `make lint`\n6. Build distributions: `make dists` (creates wheel and sdist)\n7. Tag release: `git tag -a v1.x.x -m \"Release 1.x.x\"`\n8. Push commits and tag: `git push && git push --tags`\n9. GitHub Actions will automatically publish to PyPI on tag push\n\n## Special Considerations for AI Assistants\n\n### When Modifying Code\n- **Test thoroughly:** Changes affect SQLite, PostgreSQL, AND MySQL\n- **Thread safety:** Schema changes require locking\n- **Backward compatibility:** This is a stable library used by many projects\n- **Document examples:** Include usage examples in docstrings\n\n### When Debugging\n- Check which database backend is being used (`db.is_sqlite`, `db.is_postgres`, `db.is_mysql`)\n- Look at thread-local state (`self.local.tx`) for transaction issues\n- Column name mismatches often due to case sensitivity - check `_column_keys`\n- SQLAlchemy version differences may affect behavior\n\n### Common Pitfalls\n1. Forgetting to call `_auto_commit()` after writes\n2. Not using locks for schema operations\n3. Assuming case-sensitive column names\n4. Not handling the difference between SQLite/PostgreSQL/MySQL\n5. Breaking nested transaction semantics\n6. Mutating input rows — always copy to `dict()` before modifying (see `update_many`, `_queue_add`)\n7. Shadowing variable names with processed versions (e.g., `columns` string list vs `columns_` Column objects) — use distinct names\n8. Using `type: ignore` without checking if the underlying issue can be fixed first — only suppress SQLAlchemy stub limitations and `**kwargs` forwarding\n\n## Use Cases\n\n**dataset** is ideal for:\n- Quick data loading scripts\n- Simple ETL operations\n- Testing and prototyping\n- Small utility databases\n- Data exploration and analysis\n- Situations where ORM overhead isn't needed\n\nWhen to use full SQLAlchemy ORM instead:\n- Complex relational models with foreign keys\n- Need for relationship management\n- Advanced query patterns with JOINs\n- Large-scale production applications\n"},"items":[{"name":"CLAUDE.md","path":"CLAUDE.md","rawUrl":"https://raw.githubusercontent.com/pudo/dataset/HEAD/CLAUDE.md","title":"Claude Agent Guidelines & System Prompt","category":"claude-rule","format":"markdown","content":"# dataset: databases for lazy people\n\n## Project Overview\n\n**dataset** is a lightweight Python library that makes reading and writing data in databases as simple as working with JSON files. It provides a simple abstraction layer on top of SQLAlchemy, removing most direct SQL statements without requiring a full ORM model.\n\n**Key Philosophy:** Simplicity over complexity. The library is designed for small to medium-scale datasets where you want database benefits without the overhead of complex ORM patterns.\n\n## Repository Structure\n\n```\ndataset/\n├── dataset/              # Main package\n│   ├── __init__.py      # Main entry point with connect()\n│   ├── database.py      # Database class - connection, transactions, queries\n│   ├── table.py         # Table class - CRUD operations, schema management\n│   ├── types.py         # SQLAlchemy type mapping and helpers\n│   ├── util.py          # Utility functions (ResultIter, normalization, etc.)\n│   └── chunked.py       # Chunked operations for large datasets\n├── test/                # Test suite (pytest)\n│   ├── conftest.py      # Test fixtures\n│   ├── test_database.py\n│   ├── test_table.py\n│   └── test_row_type.py\n├── docs/                # Sphinx documentation (RST format)\n└── pyproject.toml       # Package configuration (PEP 621)\n```\n\n## Core Concepts\n\n### 1. Database Connection\n- Single entry point: `dataset.connect(url)` returns a `Database` instance\n- Supports SQLite (default), PostgreSQL, and MySQL\n- Connection pooling managed by SQLAlchemy\n- Environment variable fallback: `DATABASE_URL`\n\n### 2. Automatic Schema Management\n- **Default behavior:** Tables and columns are created automatically on insert\n- Can be disabled via `ensure_schema=False` parameter\n- Column types are guessed from values (or explicitly specified)\n- Thread-safe with locking for schema operations\n\n### 3. CRUD Operations\n- **insert()**: Add single row, returns primary key\n- **insert_many()**: Bulk insert with chunking (default: 1000 rows)\n- **update()**: Update rows matching keys\n- **upsert()**: Insert or update based on key columns\n- **delete()**: Delete rows matching filters\n- **find()**: Query with filters, ordering, limit/offset\n- **find_one()**: Return single row or None\n\n### 4. Transaction Support\n- Context manager: `with dataset.connect() as tx:`\n- Manual control: `db.begin()`, `db.commit()`, `db.rollback()`\n- Nested transactions supported\n- SQLAlchemy 2.x \"autobegin\" semantics with explicit commit tracking\n\n## Important Implementation Details\n\n### Threading & Concurrency\n- Each thread gets its own database connection (thread-local storage)\n- Schema changes in transactions with multiple threads trigger warnings\n- Locking strategy:\n  - `self.lock` (RLock) protects metadata operations\n  - Keep lock scope small for performance\n  - Schema operations are fully synchronized\n\n### Transaction Handling (SQLAlchemy 2.x Migration)\n- The library supports both SQLAlchemy 1.4+ and 2.x\n- Key change: SQLAlchemy 2.x uses \"autobegin\" (transactions start on first use)\n- `db._auto_commit()` commits after writes when not in explicit transaction\n- Transaction nesting tracked via `self.local.tx` stack\n\n### Column & Table Name Normalization\n- Case-insensitive column matching via `normalize_column_key()`\n- Actual database names preserved via `_column_keys` mapping\n- Tables and columns validated for safety\n\n### Type System\n- `db.types` provides shortcuts to SQLAlchemy types\n- Type guessing from Python values: `types.guess(value)`\n- Custom types via `types` parameter in insert/update\n- MySQL-specific: text field indexing uses 10-char prefix\n\n### Type Annotations\n- The codebase passes `mypy --strict` and ships `py.typed` (PEP 561)\n- Key type aliases in `util.py`:\n  - `WriteRow = Mapping[str, SQLWriteValue]` — immutable input type for public API\n  - `MutableRow = dict[str, SQLWriteValue]` — mutable type for internal use (queue items, return values from `_sync_columns`)\n  - `OutRow = Mapping[str, Any]` — query result rows\n  - `RowFactory = Callable[[Iterable[tuple[str, Any]]], OutRow]` — the `row_type` parameter type\n- `ColumnType` in `types.py` is `TypeEngine[Any] | type[TypeEngine[Any]]` — used for `primary_type` and `create_column`\n- Use `WriteRow` at public API boundaries, `MutableRow` internally where mutation happens\n- `ensure_strings()` in `util.py` replaces `banal.ensure_list` — accepts `str | Iterable[str] | None`, returns `list[str]`\n- `QueryError` (subclass of `DatasetError`) is raised for invalid filter values in `_generate_clause`\n\n## Testing Strategy\n\n### Test Infrastructure\n- **Framework:** pytest\n- **Fixtures:**\n  - `db`: Function-scoped database connection with cleanup\n  - `table`: Pre-populated weather table with test data\n- **CI/CD:** GitHub Actions testing against SQLite, PostgreSQL, MySQL\n- **Database Cleanup:** Explicit rollback + drop tables in teardown\n\n### Running Tests\n```bash\nmake test                    # Run all tests\npytest test/test_table.py    # Run specific test file\nDATABASE_URL=\"postgresql://...\" pytest  # Test against specific DB\n```\n\n### Test Database Configuration\n- Default: SQLite in-memory (`:memory:`)\n- Override via `DATABASE_URL` environment variable\n- CI uses Docker containers for PostgreSQL and MySQL\n\n## Code Style & Quality\n\n### Tools\n- **Build System:** Hatchling (modern PEP 621 compliant)\n- **Linting & Formatting:** Ruff with default recommended rules\n- **Type Checking:** mypy `--strict` (zero errors; runs as part of `make lint`)\n\n### Running Checks\n\n```bash\nmake lint          # ruff + mypy --strict\nmake format-check  # Check formatting without applying\nmake format        # Apply formatting\n```\n\nThe codebase passes both ruff and `mypy --strict` with zero errors.\n\n### Best Practices\n1. **Keep it simple:** The library's strength is simplicity - don't over-engineer\n2. **Thread safety:** Always use locks for schema operations\n3. **Auto-commit:** Remember to call `db._auto_commit()` after writes outside transactions\n4. **Column normalization:** Use `_get_column_name()` for case-insensitive matching\n5. **Error handling:** Use `DatasetError` for dataset-specific errors; `QueryError` for invalid filters\n6. **Exception naming:** Use `Error` suffix for exception classes (e.g., `InvalidCallbackError`)\n7. **Linting:** Run `make lint` before committing (runs both ruff and mypy --strict)\n8. **Backward compatibility:** Do not rename public API parameters or methods — this library is widely deployed. Prefer `# noqa` over breaking changes (e.g., `create_column`'s `type` parameter shadows the builtin, but renaming it would break callers)\n9. **Type annotations:** All new code must pass `mypy --strict`. Use `WriteRow` for public input params, `MutableRow` for internal mutable dicts. Don't copy rows unnecessarily — only copy at the boundary where mutation is needed\n10. **Version management:** Versions are managed by `bump2version`, not by editing `__init__.py` directly\n\n## Common Development Tasks\n\n### Adding a New Table Method\n1. Add method to `Table` class in [table.py](dataset/table.py)\n2. Use `self._sync_columns()` if it modifies schema\n3. Call `self.db._auto_commit()` after database writes\n4. Add docstring with example code\n5. Update [docs/api.rst](docs/api.rst) if needed\n6. Write tests in [test/test_table.py](test/test_table.py)\n\n### Adding a New Query Operator\n1. Add operator to `_generate_clause()` in [table.py](dataset/table.py:398-430)\n2. Document in [docs/queries.rst](docs/queries.rst)\n3. Add test cases for the operator\n\n### Modifying Transaction Behavior\n- Be careful: both nested and non-nested transactions must work\n- Test with multiple threads\n- Transaction state tracked in `self.local.tx` (thread-local)\n- See `begin()`, `commit()`, `rollback()` in [database.py](dataset/database.py:134-175)\n\n## Known Limitations\n\n**Explicitly out of scope:**\n- Foreign key relationships and ORM-style relations\n- Python-wrapped JOIN queries\n- Database creation or DBMS management\n- Python 2.x support (dropped in 1.2.0)\n- Async operations\n- Database-native UPSERT (library implements via SELECT + INSERT/UPDATE)\n\n**Technical constraints:**\n- SQLite doesn't support dropping columns\n- MySQL requires prefix length for text field indexes\n- Very large text fields may not be fully indexed\n\n## Migration Notes\n\n### v2.0 (upcoming)\n- Full `mypy --strict` compliance, `py.typed` marker (PEP 561)\n- Removed `banal` dependency (replaced with `ensure_strings` in util.py)\n- `RowFactory` callable type replaces `type` for `row_type` parameter\n- `QueryError` added for invalid filter operations\n- `insert`/`insert_ignore`/`upsert` return `Any` (primary keys can be any type)\n- `primary_type` parameter typed as `ColumnType` instead of `Types`\n- `update_many` no longer mutates input rows\n\n### Modern Build System (v1.7+)\n- Migrated from setuptools to Hatchling with pyproject.toml (PEP 621)\n- Use `python -m build` instead of `setup.py sdist bdist_wheel`\n- Version automatically read from `dataset/__init__.py`\n- All configuration now in `pyproject.toml`\n\n### SQLAlchemy 2.x Support (v1.6+)\n- Supports SQLAlchemy 1.4.0 through 2.x\n- Key changes: autobegin semantics, connection handling\n- `_auto_commit()` method handles post-write commits\n- Transaction tracking via `in_transaction` property\n\n### Breaking Changes (v1.0)\n- Data export features extracted to separate `datafreeze` package\n- Text-based `primary_type` dropped - use `db.types` instead\n- Advanced `__getitem__` syntax for primary keys removed\n\n## Dependencies\n\n**Core:**\n- `sqlalchemy >= 1.4.0, < 3.0.0` - Database abstraction\n- `alembic >= 0.6.2` - Schema migrations\n\n**Development:**\n- `pytest` - Testing framework\n- `ruff` - Linting and formatting\n- `mypy` - Type checking (strict mode)\n- `build` - Modern Python package builder\n- `twine` - Package upload to PyPI\n- `psycopg2-binary` - PostgreSQL driver\n- `PyMySQL` - MySQL driver\n- `cryptography` - For secure connections\n\n## Documentation\n\n- **Official docs:** https://dataset.readthedocs.io/\n- **Source:** [docs/](docs/) folder (Sphinx/RST format)\n- Build docs: `cd docs && make html`\n\n## Release Process\n\n1. Run `bump2version patch` (or `minor`/`major`) to update version everywhere (do NOT edit `__init__.py` version manually)\n3. Update [CHANGELOG.md](CHANGELOG.md) with release notes\n4. Run full test suite: `make test`\n5. Run linting: `make lint`\n6. Build distributions: `make dists` (creates wheel and sdist)\n7. Tag release: `git tag -a v1.x.x -m \"Release 1.x.x\"`\n8. Push commits and tag: `git push && git push --tags`\n9. GitHub Actions will automatically publish to PyPI on tag push\n\n## Special Considerations for AI Assistants\n\n### When Modifying Code\n- **Test thoroughly:** Changes affect SQLite, PostgreSQL, AND MySQL\n- **Thread safety:** Schema changes require locking\n- **Backward compatibility:** This is a stable library used by many projects\n- **Document examples:** Include usage examples in docstrings\n\n### When Debugging\n- Check which database backend is being used (`db.is_sqlite`, `db.is_postgres`, `db.is_mysql`)\n- Look at thread-local state (`self.local.tx`) for transaction issues\n- Column name mismatches often due to case sensitivity - check `_column_keys`\n- SQLAlchemy version differences may affect behavior\n\n### Common Pitfalls\n1. Forgetting to call `_auto_commit()` after writes\n2. Not using locks for schema operations\n3. Assuming case-sensitive column names\n4. Not handling the difference between SQLite/PostgreSQL/MySQL\n5. Breaking nested transaction semantics\n6. Mutating input rows — always copy to `dict()` before modifying (see `update_many`, `_queue_add`)\n7. Shadowing variable names with processed versions (e.g., `columns` string list vs `columns_` Column objects) — use distinct names\n8. Using `type: ignore` without checking if the underlying issue can be fixed first — only suppress SQLAlchemy stub limitations and `**kwargs` forwarding\n\n## Use Cases\n\n**dataset** is ideal for:\n- Quick data loading scripts\n- Simple ETL operations\n- Testing and prototyping\n- Small utility databases\n- Data exploration and analysis\n- Situations where ORM overhead isn't needed\n\nWhen to use full SQLAlchemy ORM instead:\n- Complex relational models with foreign keys\n- Need for relationship management\n- Advanced query patterns with JOINs\n- Large-scale production applications\n","isInternal":false,"tokens":2939,"sizeBytes":12392}],"systemPromptSnippet":"<yakaai_skills repo=\"pudo/dataset\">\n<!-- File: CLAUDE.md (Tokens: ~2939 | Category: claude-rule) -->\n# dataset: databases for lazy people\n\n## Project Overview\n\n**dataset** is a lightweight Python library that makes reading and writing data in databases as simple as working with JSON files. It provides a simple abstraction layer on top of SQLAlchemy, removing most direct SQL statements without requiring a full ORM model.\n\n**Key Philosophy:** Simplicity over complexity. The library is designed for small to medium-scale datasets where you want database benefits without the overhead of complex ORM patterns.\n\n## Repository Structure\n\n```\ndataset/\n├── dataset/              # Main package\n│   ├── __init__.py      # Main entry point with connect()\n│   ├── database.py      # Database class - connection, transactions, queries\n│   ├── table.py         # Table class - CRUD operations, schema management\n│   ├── types.py         # SQLAlchemy type mapping and helpers\n│   ├── util.py          # Utility functions (ResultIter, normalization, etc.)\n│   └── chunked.py       # Chunked operations for large datasets\n├── test/                # Test suite (pytest)\n│   ├── conftest.py      # Test fixtures\n│   ├── test_database.py\n│   ├── test_table.py\n│   └── test_row_type.py\n├── docs/                # Sphinx documentation (RST format)\n└── pyproject.toml       # Package configuration (PEP 621)\n```\n\n## Core Concepts\n\n### 1. Database Connection\n- Single entry point: `dataset.connect(url)` returns a `Database` instance\n- Supports SQLite (default), PostgreSQL, and MySQL\n- Connection pooling managed by SQLAlchemy\n- Environment variable fallback: `DATABASE_URL`\n\n### 2. Automatic Schema Management\n- **Default behavior:** Tables and columns are created automatically on insert\n- Can be disabled via `ensure_schema=False` parameter\n- Column types are guessed from values (or explicitly specified)\n- Thread-safe with locking for schema operations\n\n### 3. CRUD Operations\n- **insert()**: Add single row, returns primary key\n- **insert_many()**: Bulk insert with chunking (default: 1000 rows)\n- **update()**: Update rows matching keys\n- **upsert()**: Insert or update based on key columns\n- **delete()**: Delete rows matching filters\n- **find()**: Query with filters, ordering, limit/offset\n- **find_one()**: Return single row or None\n\n### 4. Transaction Support\n- Context manager: `with dataset.connect() as tx:`\n- Manual control: `db.begin()`, `db.commit()`, `db.rollback()`\n- Nested transactions supported\n- SQLAlchemy 2.x \"autobegin\" semantics with explicit commit tracking\n\n## Important Implementation Details\n\n### Threading & Concurrency\n- Each thread gets its own database connection (thread-local storage)\n- Schema changes in transactions with multiple threads trigger warnings\n- Locking strategy:\n  - `self.lock` (RLock) protects metadata operations\n  - Keep lock scope small for performance\n  - Schema operations are fully synchronized\n\n### Transaction Handling (SQLAlchemy 2.x Migration)\n- The library supports both SQLAlchemy 1.4+ and 2.x\n- Key change: SQLAlchemy 2.x uses \"autobegin\" (transactions start on first use)\n- `db._auto_commit()` commits after writes when not in explicit transaction\n- Transaction nesting tracked via `self.local.tx` stack\n\n### Column & Table Name Normalization\n- Case-insensitive column matching via `normalize_column_key()`\n- Actual database names preserved via `_column_keys` mapping\n- Tables and columns validated for safety\n\n### Type System\n- `db.types` provides shortcuts to SQLAlchemy types\n- Type guessing from Python values: `types.guess(value)`\n- Custom types via `types` parameter in insert/update\n- MySQL-specific: text field indexing uses 10-char prefix\n\n### Type Annotations\n- The codebase passes `mypy --strict` and ships `py.typed` (PEP 561)\n- Key type aliases in `util.py`:\n  - `WriteRow = Mapping[str, SQLWriteValue]` — immutable input type for public API\n  - `MutableRow = dict[str, SQLWriteValue]` — mutable type for internal use (queue items, return values from `_sync_columns`)\n  - `OutRow = Mapping[str, Any]` — query result rows\n  - `RowFactory = Callable[[Iterable[tuple[str, Any]]], OutRow]` — the `row_type` parameter type\n- `ColumnType` in `types.py` is `TypeEngine[Any] | type[TypeEngine[Any]]` — used for `primary_type` and `create_column`\n- Use `WriteRow` at public API boundaries, `MutableRow` internally where mutation happens\n- `ensure_strings()` in `util.py` replaces `banal.ensure_list` — accepts `str | Iterable[str] | None`, returns `list[str]`\n- `QueryError` (subclass of `DatasetError`) is raised for invalid filter values in `_generate_clause`\n\n## Testing Strategy\n\n### Test Infrastructure\n- **Framework:** pytest\n- **Fixtures:**\n  - `db`: Function-scoped database connection with cleanup\n  - `table`: Pre-populated weather table with test data\n- **CI/CD:** GitHub Actions testing against SQLite, PostgreSQL, MySQL\n- **Database Cleanup:** Explicit rollback + drop tables in teardown\n\n### Running Tests\n```bash\nmake test                    # Run all tests\npytest test/test_table.py    # Run specific test file\nDATABASE_URL=\"postgresql://...\" pytest  # Test against specific DB\n```\n\n### Test Database Configuration\n- Default: SQLite in-memory (`:memory:`)\n- Override via `DATABASE_URL` environment variable\n- CI uses Docker containers for PostgreSQL and MySQL\n\n## Code Style & Quality\n\n### Tools\n- **Build System:** Hatchling (modern PEP 621 compliant)\n- **Linting & Formatting:** Ruff with default recommended rules\n- **Type Checking:** mypy `--strict` (zero errors; runs as part of `make lint`)\n\n### Running Checks\n\n```bash\nmake lint          # ruff + mypy --strict\nmake format-check  # Check formatting without applying\nmake format        # Apply formatting\n```\n\nThe codebase passes both ruff and `mypy --strict` with zero errors.\n\n### Best Practices\n1. **Keep it simple:** The library's strength is simplicity - don't over-engineer\n2. **Thread safety:** Always use locks for schema operations\n3. **Auto-commit:** Remember to call `db._auto_commit()` after writes outside transactions\n4. **Column normalization:** Use `_get_column_name()` for case-insensitive matching\n5. **Error handling:** Use `DatasetError` for dataset-specific errors; `QueryError` for invalid filters\n6. **Exception naming:** Use `Error` suffix for exception classes (e.g., `InvalidCallbackError`)\n7. **Linting:** Run `make lint` before committing (runs both ruff and mypy --strict)\n8. **Backward compatibility:** Do not rename public API parameters or methods — this library is widely deployed. Prefer `# noqa` over breaking changes (e.g., `create_column`'s `type` parameter shadows the builtin, but renaming it would break callers)\n9. **Type annotations:** All new code must pass `mypy --strict`. Use `WriteRow` for public input params, `MutableRow` for internal mutable dicts. Don't copy rows unnecessarily — only copy at the boundary where mutation is needed\n10. **Version management:** Versions are managed by `bump2version`, not by editing `__init__.py` directly\n\n## Common Development Tasks\n\n### Adding a New Table Method\n1. Add method to `Table` class in [table.py](dataset/table.py)\n2. Use `self._sync_columns()` if it modifies schema\n3. Call `self.db._auto_commit()` after database writes\n4. Add docstring with example code\n5. Update [docs/api.rst](docs/api.rst) if needed\n6. Write tests in [test/test_table.py](test/test_table.py)\n\n### Adding a New Query Operator\n1. Add operator to `_generate_clause()` in [table.py](dataset/table.py:398-430)\n2. Document in [docs/queries.rst](docs/queries.rst)\n3. Add test cases for the operator\n\n### Modifying Transaction Behavior\n- Be careful: both nested and non-nested transactions must work\n- Test with multiple threads\n- Transaction state tracked in `self.local.tx` (thread-local)\n- See `begin()`, `commit()`, `rollback()` in [database.py](dataset/database.py:134-175)\n\n## Known Limitations\n\n**Explicitly out of scope:**\n- Foreign key relationships and ORM-style relations\n- Python-wrapped JOIN queries\n- Database creation or DBMS management\n- Python 2.x support (dropped in 1.2.0)\n- Async operations\n- Database-native UPSERT (library implements via SELECT + INSERT/UPDATE)\n\n**Technical constraints:**\n- SQLite doesn't support dropping columns\n- MySQL requires prefix length for text field indexes\n- Very large text fields may not be fully indexed\n\n## Migration Notes\n\n### v2.0 (upcoming)\n- Full `mypy --strict` compliance, `py.typed` marker (PEP 561)\n- Removed `banal` dependency (replaced with `ensure_strings` in util.py)\n- `RowFactory` callable type replaces `type` for `row_type` parameter\n- `QueryError` added for invalid filter operations\n- `insert`/`insert_ignore`/`upsert` return `Any` (primary keys can be any type)\n- `primary_type` parameter typed as `ColumnType` instead of `Types`\n- `update_many` no longer mutates input rows\n\n### Modern Build System (v1.7+)\n- Migrated from setuptools to Hatchling with pyproject.toml (PEP 621)\n- Use `python -m build` instead of `setup.py sdist bdist_wheel`\n- Version automatically read from `dataset/__init__.py`\n- All configuration now in `pyproject.toml`\n\n### SQLAlchemy 2.x Support (v1.6+)\n- Supports SQLAlchemy 1.4.0 through 2.x\n- Key changes: autobegin semantics, connection handling\n- `_auto_commit()` method handles post-write commits\n- Transaction tracking via `in_transaction` property\n\n### Breaking Changes (v1.0)\n- Data export features extracted to separate `datafreeze` package\n- Text-based `primary_type` dropped - use `db.types` instead\n- Advanced `__getitem__` syntax for primary keys removed\n\n## Dependencies\n\n**Core:**\n- `sqlalchemy >= 1.4.0, < 3.0.0` - Database abstraction\n- `alembic >= 0.6.2` - Schema migrations\n\n**Development:**\n- `pytest` - Testing framework\n- `ruff` - Linting and formatting\n- `mypy` - Type checking (strict mode)\n- `build` - Modern Python package builder\n- `twine` - Package upload to PyPI\n- `psycopg2-binary` - PostgreSQL driver\n- `PyMySQL` - MySQL driver\n- `cryptography` - For secure connections\n\n## Documentation\n\n- **Official docs:** https://dataset.readthedocs.io/\n- **Source:** [docs/](docs/) folder (Sphinx/RST format)\n- Build docs: `cd docs && make html`\n\n## Release Process\n\n1. Run `bump2version patch` (or `minor`/`major`) to update version everywhere (do NOT edit `__init__.py` version manually)\n3. Update [CHANGELOG.md](CHANGELOG.md) with release notes\n4. Run full test suite: `make test`\n5. Run linting: `make lint`\n6. Build distributions: `make dists` (creates wheel and sdist)\n7. Tag release: `git tag -a v1.x.x -m \"Release 1.x.x\"`\n8. Push commits and tag: `git push && git push --tags`\n9. GitHub Actions will automatically publish to PyPI on tag push\n\n## Special Considerations for AI Assistants\n\n### When Modifying Code\n- **Test thoroughly:** Changes affect SQLite, PostgreSQL, AND MySQL\n- **Thread safety:** Schema changes require locking\n- **Backward compatibility:** This is a stable library used by many projects\n- **Document examples:** Include usage examples in docstrings\n\n### When Debugging\n- Check which database backend is being used (`db.is_sqlite`, `db.is_postgres`, `db.is_mysql`)\n- Look at thread-local state (`self.local.tx`) for transaction issues\n- Column name mismatches often due to case sensitivity - check `_column_keys`\n- SQLAlchemy version differences may affect behavior\n\n### Common Pitfalls\n1. Forgetting to call `_auto_commit()` after writes\n2. Not using locks for schema operations\n3. Assuming case-sensitive column names\n4. Not handling the difference between SQLite/PostgreSQL/MySQL\n5. Breaking nested transaction semantics\n6. Mutating input rows — always copy to `dict()` before modifying (see `update_many`, `_queue_add`)\n7. Shadowing variable names with processed versions (e.g., `columns` string list vs `columns_` Column objects) — use distinct names\n8. Using `type: ignore` without checking if the underlying issue can be fixed first — only suppress SQLAlchemy stub limitations and `**kwargs` forwarding\n\n## Use Cases\n\n**dataset** is ideal for:\n- Quick data loading scripts\n- Simple ETL operations\n- Testing and prototyping\n- Small utility databases\n- Data exploration and analysis\n- Situations where ORM overhead isn't needed\n\nWhen to use full SQLAlchemy ORM instead:\n- Complex relational models with foreign keys\n- Need for relationship management\n- Advanced query patterns with JOINs\n- Large-scale production applications\n</yakaai_skills>"}