Sqlalchemy Orm Expert

The SQLAlchemy ORM Expert skill enables AI agents to generate, debug, and optimize Python code for database interactions using the SQLAlchemy Object-Relational Mapping framework. It handles complex schema definitions, session management, and query construction tailored to specific database backends.

When to use it

• Building new data models and tables from scratch in a Python application.
• Refactoring legacy raw SQL queries into type-safe ORM patterns.
• Debugging connection errors or inefficient database queries in existing projects.
• Generating migration scripts for schema updates without manual intervention.

Key capabilities

• Defines declarative base classes and table mappings for various databases (PostgreSQL, MySQL, SQLite).
• Constructs dynamic queries using the Query class and Core expression language.
• Manages database sessions, including commit, rollback, and autoflush behaviors.
• Implements relationships between models (one-to-one, one-to-many, many-to-many).

Example prompts

• "Create a SQLAlchemy model for a User with fields for name, email, and a list of associated posts."
• "Write a query to fetch all active users who have logged in within the last 24 hours."
• "Debug this session error: 'Instance already attached to Session' when trying to update a record."

Tips & gotchas

Ensure your target database driver (e.g., psycopg2 for PostgreSQL) is installed before running generated code. While SQLAlchemy abstracts many details, understanding the underlying SQL dialect helps in optimizing performance-critical queries.