SQLAlchemy Integration

from flask_admin.contrib.sqla import ModelView

class ModelView(BaseModelView):
    def __init__(self, model, session, **kwargs):
        """
        Initialize SQLAlchemy model view.
        
        Args:
            model: SQLAlchemy model class  
            session: SQLAlchemy session or session factory
            **kwargs: Additional view configuration
        """
    
    # SQLAlchemy-specific configuration
    column_auto_select_related = True      # Auto-select related models for efficiency
    column_select_related_list = None      # Explicit related model selection
    inline_models = None                   # Inline editing for related models
    
    # Advanced filtering  
    column_filters = None                  # Column-based filters
    named_filter_urls = False              # Use named URLs for filters
    
    # Query customization
    def get_query(self):
        """
        Get base query for list view.
        
        Returns:
            Query: SQLAlchemy query object
        """
    
    def get_count_query(self):
        """
        Get count query for pagination.
        
        Returns:
            Query: Count query object  
        """
    
    def get_list(self, page, sort_column, sort_desc, search, filters, execute=True, page_size=None):
        """
        Get paginated model list with sorting, searching, and filtering.
        
        Args:
            page (int): Page number (0-based)
            sort_column (str): Column to sort by
            sort_desc (bool): Sort in descending order
            search (str): Search query string
            filters (list): Active filter list
            execute (bool): Execute query immediately
            page_size (int, optional): Items per page
            
        Returns:
            tuple: (total_count, items) if execute=True, query if execute=False
        """
    
    def get_one(self, id):
        """
        Get single model instance by primary key.
        
        Args:
            id: Primary key value
            
        Returns:
            Model instance or None if not found
        """
    
    # CRUD operations
    def create_model(self, form):
        """
        Create new model instance from form data.
        
        Args:
            form: Validated form instance
            
        Returns:
            bool: True if creation successful
        """
    
    def update_model(self, form, model):
        """
        Update existing model instance from form data.
        
        Args:
            form: Validated form instance
            model: Model instance to update
            
        Returns:
            bool: True if update successful  
        """
    
    def delete_model(self, model):
        """
        Delete model instance.
        
        Args:
            model: Model instance to delete
            
        Returns:
            bool: True if deletion successful
        """
    
    # Form scaffolding
    def scaffold_form(self):
        """
        Auto-generate form class from SQLAlchemy model.
        
        Returns:
            Form class with fields for model columns
        """
    
    def scaffold_list_form(self, widget=None, validators=None):
        """
        Generate form for inline list editing.
        
        Args:
            widget: Custom widget for fields
            validators: Custom validators
            
        Returns:
            Form class for inline editing
        """
    
    def scaffold_filters(self, name):
        """
        Generate filters for model column.
        
        Args:
            name (str): Column name
            
        Returns:
            list: Available filter types for column
        """
    
    # Relationship handling
    def scaffold_auto_joins(self, query):
        """
        Automatically add joins for related models to improve performance.
        
        Args:
            query: Base query
            
        Returns:
            Query: Enhanced query with joins
        """
    
    def get_pk_value(self, model):
        """
        Get primary key value from model instance.
        
        Args:
            model: Model instance
            
        Returns:
            Primary key value
        """
    
    # Query hooks for customization
    def apply_search(self, query, search):
        """
        Apply search filters to query.
        
        Args:
            query: Base query
            search (str): Search string
            
        Returns:
            Query: Query with search filters applied
        """
    
    def apply_filters(self, query, filters):
        """
        Apply column filters to query.
        
        Args:
            query: Base query  
            filters (list): Active filters
            
        Returns:
            Query: Query with filters applied
        """
    
    def apply_sorting(self, query, sort_column, sort_desc):
        """
        Apply sorting to query.
        
        Args:
            query: Base query
            sort_column (str): Column to sort by
            sort_desc (bool): Sort descending
            
        Returns:
            Query: Sorted query
        """

class InlineFormAdmin:
    def __init__(
        self,
        model,
        form_columns=None,
        excluded_form_columns=None,
        form_args=None,
        form_widget_args=None,
        form_overrides=None
    ):
        """
        Initialize inline form admin for related model editing.
        
        Args:
            model: Related model class
            form_columns (list, optional): Columns to include in inline form
            excluded_form_columns (list, optional): Columns to exclude
            form_args (dict, optional): Form field arguments
            form_widget_args (dict, optional): Widget arguments
            form_overrides (dict, optional): Field type overrides
        """

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_admin import Admin
from flask_admin.contrib.sqla import ModelView

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SECRET_KEY'] = 'secret-key'

db = SQLAlchemy(app)

# Define models
class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    products = db.relationship('Product', backref='category', lazy='dynamic')

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    price = db.Column(db.Numeric(10, 2))
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

# Create admin views
class ProductModelView(ModelView):
    list_columns = ['id', 'name', 'price', 'category.name', 'created_at']
    column_searchable_list = ['name', 'category.name']
    column_filters = ['category', 'price', 'created_at']
    column_labels = {'category.name': 'Category'}
    
    form_columns = ['name', 'price', 'category']
    
    # Enable auto-joining for performance
    column_auto_select_related = True

class CategoryModelView(ModelView):
    list_columns = ['id', 'name', 'products']
    column_searchable_list = ['name']
    
    # Show product count in list
    def _products_formatter(view, context, model, name):
        return model.products.count()
    
    column_formatters = {
        'products': _products_formatter
    }

# Initialize admin
admin = Admin(app, name='Shop Admin')
admin.add_view(CategoryModelView(Category, db.session, name='Categories'))
admin.add_view(ProductModelView(Product, db.session, name='Products'))

from flask_admin.contrib.sqla.filters import FilterLike, FilterEqual, FilterInList

class AdvancedProductView(ModelView):
    # Searchable columns with different search strategies
    column_searchable_list = [
        'name',           # Full-text search on name
        'description',    # Full-text search on description  
        'category.name'   # Search in related model
    ]
    
    # Custom filters
    column_filters = [
        'name',                    # Automatic filters
        'price',
        'created_at',
        'category',
        FilterLike('name', 'Name Contains'),     # Custom like filter
        FilterEqual('price', 'Exact Price'),     # Custom equal filter
        FilterInList('category_id', 'Categories', 
                    options=[(1, 'Electronics'), (2, 'Books')])  # Custom in-list filter
    ]
    
    # Custom query with additional joins
    def get_query(self):
        return self.session.query(self.model).join(Category)
    
    def get_count_query(self):
        return self.session.query(func.count('*')).select_from(self.model).join(Category)
    
    # Custom search implementation
    def apply_search(self, query, search):
        if search:
            search_filter = or_(
                Product.name.contains(search),
                Product.description.contains(search),
                Category.name.contains(search)
            )
            query = query.filter(search_filter)
        return query

from flask_admin.contrib.sqla import InlineFormAdmin

class OrderItemInlineForm(InlineFormAdmin):
    form_columns = ('product', 'quantity', 'unit_price')
    form_args = {
        'quantity': {'validators': [NumberRange(min=1)]},
        'unit_price': {'validators': [NumberRange(min=0)]}
    }

class OrderModelView(ModelView):
    inline_models = [OrderItemInlineForm(OrderItem)]
    
    list_columns = ['id', 'customer_name', 'total_amount', 'order_date', 'status']
    form_columns = ['customer_name', 'order_date', 'status', 'items']
    
    def create_form(self, obj=None):
        form = super().create_form(obj)
        # Custom form processing for inline items
        return form

from markupsafe import Markup
from flask_admin.contrib.sqla.ajax import QueryAjaxModelLoader

class UserModelView(ModelView):
    # Custom column formatters
    column_formatters = {
        'email': lambda v, c, m, p: Markup(f'<a href="mailto:{m.email}">{m.email}</a>'),
        'status': lambda v, c, m, p: 'Active' if m.is_active else 'Inactive',
        'avatar': lambda v, c, m, p: Markup(f'<img src="{m.avatar_url}" width="32">') if m.avatar_url else ''
    }
    
    # Export-specific formatters (clean data for CSV)
    column_formatters_export = {
        'email': lambda v, c, m, p: m.email,
        'status': lambda v, c, m, p: 'Active' if m.is_active else 'Inactive',
        'avatar': lambda v, c, m, p: m.avatar_url or ''
    }
    
    # Enable export
    can_export = True
    export_types = ['csv', 'json', 'yaml']
    export_max_rows = 10000
    
    # AJAX loading for large datasets
    form_ajax_refs = {
        'manager': QueryAjaxModelLoader(
            'manager',
            db.session,
            User,
            fields=['name', 'email'],
            placeholder='Select manager...'
        )
    }

from flask_admin.actions import action
from flask import flash

class UserModelView(ModelView):
    @action('activate', 'Activate Users', 'Are you sure you want to activate selected users?')
    def action_activate(self, ids):
        try:
            # Bulk update using SQLAlchemy
            count = self.session.query(User).filter(User.id.in_(ids)).update(
                {User.is_active: True}, 
                synchronize_session='fetch'
            )
            self.session.commit()
            flash(f'Successfully activated {count} users.', 'success')
        except Exception as ex:
            flash(f'Failed to activate users: {str(ex)}', 'error')
    
    @action('delete', 'Delete', 'Are you sure?')
    def action_delete(self, ids):
        try:
            # Bulk delete with cascade handling
            users = self.session.query(User).filter(User.id.in_(ids)).all()
            for user in users:
                self.session.delete(user)
            self.session.commit()
            flash(f'Successfully deleted {len(users)} users.', 'success')
        except Exception as ex:
            self.session.rollback()
            flash(f'Failed to delete users: {str(ex)}', 'error')
    
    def is_action_allowed(self, name):
        # Custom action permissions
        if name == 'delete' and not current_user.is_admin:
            return False
        return super().is_action_allowed(name)

class BlogPostModelView(ModelView):
    # Display related data in list
    list_columns = ['title', 'author.name', 'category.name', 'created_at', 'published']
    
    # Efficient querying with auto-joins
    column_auto_select_related = True
    column_select_related_list = ['author', 'category']
    
    # Search across relationships
    column_searchable_list = ['title', 'content', 'author.name', 'category.name']
    
    # Filter by relationships
    column_filters = ['author', 'category', 'published', 'created_at']
    
    # Form with relationship fields
    form_columns = ['title', 'content', 'author', 'category', 'tags', 'published']
    
    # Custom query to include soft-deleted filter
    def get_query(self):
        return super().get_query().filter(BlogPost.deleted_at.is_(None))
    
    def get_count_query(self):
        return super().get_count_query().filter(BlogPost.deleted_at.is_(None))