0
# Database Models
1

2
SQLAlchemy integration with enhanced models, database interfaces, and application factory support providing flexible database management, model utilities, and seamless integration with Flask-AppBuilder's view and API systems.
3

4
## Capabilities
5

6
### Model Class
7

8
Base SQLAlchemy model class that provides enhanced functionality and integration with Flask-AppBuilder's features including JSON serialization and table configuration.
9

10
```python { .api }
11
from flask_appbuilder.models.sqla import Model
12
from sqlalchemy import Column, Integer, String, DateTime, Boolean, ForeignKey
13
from sqlalchemy.orm import relationship
14
import datetime
15

16
class Model(object):
17
    """
18
    Base model class for all Flask-AppBuilder models.
19
    Provides enhanced SQLAlchemy functionality.
20
    """
21
    
22
    def to_json(self):
23
        """
24
        Convert model instance to JSON-serializable dictionary.
25
        
26
        Returns:
27
        Dict with model data, handling relationships and special types
28
        """
29

30
# Base table configuration
31
__table_args__ = {"extend_existing": True}
32

33
# Usage example - Complete model definition
34
class Person(Model):
35
    __tablename__ = 'persons'
36
    
37
    # Primary key
38
    id = Column(Integer, primary_key=True)
39
    
40
    # Basic fields
41
    name = Column(String(150), unique=True, nullable=False)
42
    email = Column(String(120), unique=True, nullable=False)
43
    phone = Column(String(20))
44
    active = Column(Boolean, default=True)
45
    
46
    # Timestamps
47
    created_on = Column(DateTime, default=datetime.datetime.now)
48
    updated_on = Column(DateTime, default=datetime.datetime.now, 
49
                       onupdate=datetime.datetime.now)
50
    
51
    # Foreign key relationship
52
    department_id = Column(Integer, ForeignKey('departments.id'))
53
    department = relationship("Department", back_populates="persons")
54
    
55
    # String representation
56
    def __repr__(self):
57
        return self.name
58
    
59
    # Custom JSON serialization
60
    def to_json(self):
61
        return {
62
            'id': self.id,
63
            'name': self.name,
64
            'email': self.email,
65
            'active': self.active,
66
            'department': self.department.name if self.department else None,
67
            'created_on': self.created_on.isoformat() if self.created_on else None
68
        }
69

70
class Department(Model):
71
    __tablename__ = 'departments'
72
    
73
    id = Column(Integer, primary_key=True)
74
    name = Column(String(100), unique=True, nullable=False)
75
    description = Column(String(500))
76
    
77
    # Reverse relationship
78
    persons = relationship("Person", back_populates="department")
79
    
80
    def __repr__(self):
81
        return self.name
82
```
83

84
### SQLA Class
85

86
Enhanced Flask-SQLAlchemy class providing Flask-AppBuilder integration, application factory support, and advanced session management capabilities.
87

88
```python { .api }
89
from flask_appbuilder.models.sqla import SQLA
90
from flask_sqlalchemy import SQLAlchemy
91

92
class SQLA(SQLAlchemy):
93
    """
94
    Enhanced SQLAlchemy class with Flask-AppBuilder integration.
95
    Supports application factory pattern and custom configurations.
96
    """
97
    
98
    def make_declarative_base(self, model, metadata=None):
99
        """
100
        Create declarative base class with Flask-AppBuilder Model.
101
        
102
        Parameters:
103
        - model: Base model class (Model)
104
        - metadata: SQLAlchemy metadata instance
105
        
106
        Returns:
107
        Declarative base class
108
        """
109
    
110
    def get_tables_for_bind(self, bind=None):
111
        """
112
        Get tables for specific database bind.
113
        
114
        Parameters:  
115
        - bind: Database bind key
116
        
117
        Returns:
118
        List of table objects for the bind
119
        """
120
    
121
    def create_session(self, options):
122
        """
123
        Create custom database session with options.
124
        
125
        Parameters:
126
        - options: Session configuration dict
127
        
128
        Returns:
129
        SQLAlchemy session instance
130
        """
131

132
# Application factory usage
133
from flask import Flask
134
from flask_appbuilder import AppBuilder
135

136
# Create SQLA instance
137
db = SQLA()
138

139
def create_app():
140
    app = Flask(__name__)
141
    
142
    # Configure database
143
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
144
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
145
    
146
    # Initialize SQLA with app
147
    db.init_app(app)
148
    
149
    # Initialize AppBuilder
150
    appbuilder = AppBuilder(app, db.session)
151
    
152
    return app
153

154
# Multiple database binds example
155
app.config['SQLALCHEMY_BINDS'] = {
156
    'users': 'sqlite:///users.db',
157
    'products': 'postgresql://user:pass@localhost/products'
158
}
159

160
class User(Model):
161
    __bind_key__ = 'users'
162
    __tablename__ = 'users'
163
    
164
    id = Column(Integer, primary_key=True)
165
    username = Column(String(80), unique=True, nullable=False)
166

167
class Product(Model):
168
    __bind_key__ = 'products' 
169
    __tablename__ = 'products'
170
    
171
    id = Column(Integer, primary_key=True)
172
    name = Column(String(100), nullable=False)
173
```
174

175
### Base Alias
176

177
Backward compatibility alias for the Model class to support legacy Flask-AppBuilder applications.
178

179
```python { .api }
180
from flask_appbuilder.models.sqla import Base
181

182
# Base is an alias for Model - both are equivalent
183
Base = Model
184

185
# Legacy usage (still supported)
186
class LegacyModel(Base):
187
    __tablename__ = 'legacy_table'
188
    id = Column(Integer, primary_key=True)
189
    name = Column(String(100))
190

191
# Modern usage (recommended)  
192
class ModernModel(Model):
193
    __tablename__ = 'modern_table'
194
    id = Column(Integer, primary_key=True)
195
    name = Column(String(100))
196
```
197

198
### SQLAInterface
199

200
Data model interface providing abstraction layer between models and Flask-AppBuilder views/APIs for database operations.
201

202
```python { .api }
203
from flask_appbuilder.models.sqla.interface import SQLAInterface
204

205
class SQLAInterface:
206
    def __init__(self, obj, session=None):
207
        """
208
        Initialize interface for SQLAlchemy model.
209
        
210
        Parameters:
211
        - obj: SQLAlchemy model class
212
        - session: SQLAlchemy session (optional)
213
        """
214
    
215
    def get_query(self, filters=None, order_column='', order_direction=''):
216
        """
217
        Get SQLAlchemy query with filters and ordering.
218
        
219
        Parameters:
220
        - filters: List of filter objects
221
        - order_column: Column name for ordering  
222
        - order_direction: 'asc' or 'desc'
223
        
224
        Returns:
225
        SQLAlchemy Query object
226
        """
227
    
228
    def get_count(self, filters=None):
229
        """
230
        Get count of records matching filters.
231
        
232
        Parameters:
233
        - filters: List of filter objects
234
        
235
        Returns:
236
        Integer count of matching records
237
        """
238
    
239
    def get(self, pk):
240
        """
241
        Get single record by primary key.
242
        
243
        Parameters:
244
        - pk: Primary key value
245
        
246
        Returns:
247
        Model instance or None
248
        """
249
    
250
    def get_keys(self, lst):
251
        """
252
        Get primary key values from list of model instances.
253
        
254
        Parameters:
255
        - lst: List of model instances
256
        
257
        Returns:
258
        List of primary key values
259
        """
260
    
261
    def get_pk_value(self, item):
262
        """
263
        Get primary key value from model instance.
264
        
265
        Parameters:
266
        - item: Model instance
267
        
268
        Returns:
269
        Primary key value
270
        """
271
    
272
    def add(self, item):
273
        """
274
        Add model instance to database.
275
        
276
        Parameters:
277
        - item: Model instance to add
278
        
279
        Returns:
280
        Added model instance
281
        """
282
    
283
    def edit(self, item):
284
        """
285
        Update model instance in database.
286
        
287
        Parameters:
288
        - item: Model instance to update
289
        
290
        Returns:
291
        Updated model instance  
292
        """
293
    
294
    def delete(self, item):
295
        """
296
        Delete model instance from database.
297
        
298
        Parameters:
299
        - item: Model instance to delete
300
        
301
        Returns:
302
        Boolean success flag
303
        """
304

305
# Usage with views and APIs
306
from flask_appbuilder import ModelView, ModelRestApi
307

308
class PersonView(ModelView):
309
    datamodel = SQLAInterface(Person)
310
    list_columns = ['name', 'email', 'department']
311

312
class PersonApi(ModelRestApi):
313
    datamodel = SQLAInterface(Person)  
314
    list_columns = ['id', 'name', 'email']
315

316
# Custom interface usage
317
interface = SQLAInterface(Person)
318

319
# Get all active persons
320
filters = [FilterEqual('active', True)]
321
query = interface.get_query(filters=filters, order_column='name')
322
persons = query.all()
323

324
# Get count
325
count = interface.get_count(filters=filters)
326

327
# CRUD operations
328
new_person = Person(name="John Doe", email="john@example.com")
329
interface.add(new_person)
330

331
person = interface.get(1)
332
person.email = "newemail@example.com"  
333
interface.edit(person)
334

335
interface.delete(person)
336
```
337

338
### Database Filters
339

340
Filter classes for building complex database queries with type-safe operations and support for various data types.
341

342
```python { .api }
343
from flask_appbuilder.models.filters import BaseFilter, FilterEqual, FilterNotEqual, \
344
    FilterGreater, FilterSmaller, FilterStartsWith, FilterEndsWith, FilterContains, \
345
    FilterNotStartsWith, FilterNotEndsWith, FilterNotContains, FilterEqualFunction, \
346
    FilterInFunction, FilterConverter
347

348
# Basic filters
349
class FilterEqual(BaseFilter):
350
    """Exact match filter (column = value)"""
351
    
352
class FilterNotEqual(BaseFilter):
353
    """Not equal filter (column != value)"""
354
    
355
class FilterGreater(BaseFilter):
356
    """Greater than filter (column > value)"""
357
    
358
class FilterSmaller(BaseFilter):
359
    """Less than filter (column < value)"""
360

361
# String filters
362
class FilterStartsWith(BaseFilter):
363
    """Starts with filter (column LIKE 'value%')"""
364
    
365
class FilterEndsWith(BaseFilter):
366
    """Ends with filter (column LIKE '%value')"""
367
    
368
class FilterContains(BaseFilter):
369
    """Contains filter (column LIKE '%value%')"""
370
    
371
class FilterNotStartsWith(BaseFilter):
372
    """Does not start with filter"""
373
    
374
class FilterNotEndsWith(BaseFilter):
375
    """Does not end with filter"""
376
    
377
class FilterNotContains(BaseFilter):
378
    """Does not contain filter"""
379

380
# Function filters
381
class FilterEqualFunction(BaseFilter):
382
    """Filter using SQL function (FUNC(column) = value)"""
383
    
384
class FilterInFunction(BaseFilter):
385
    """Filter using IN with function"""
386

387
# Usage examples
388
from flask_appbuilder.models.sqla.filters import FilterEqual, FilterContains
389

390
# In ModelView or ModelRestApi
391
class PersonView(ModelView):
392
    datamodel = SQLAInterface(Person)
393
    
394
    # Base filters applied to all queries
395
    base_filters = [
396
        ['active', FilterEqual, True],           # active = True
397
        ['name', FilterContains, 'John'],        # name LIKE '%John%'
398
        ['created_on', FilterGreater, datetime.date(2023, 1, 1)]  # created_on > '2023-01-01'
399
    ]
400
    
401
    # Available search filters
402
    search_filters = {
403
        'name': [FilterEqual, FilterContains, FilterStartsWith],
404
        'email': [FilterEqual, FilterContains],
405
        'active': [FilterEqual],
406
        'created_on': [FilterEqual, FilterGreater, FilterSmaller]
407
    }
408

409
# Custom filter example
410
class FilterActiveInLastDays(BaseFilter):
411
    name = "Active in Last N Days"
412
    arg_name = "days"
413
    
414
    def apply(self, query, value):
415
        cutoff_date = datetime.datetime.now() - datetime.timedelta(days=int(value))
416
        return query.filter(self.column >= cutoff_date)
417

418
# Advanced filtering with relationships
419
class PersonView(ModelView):
420
    datamodel = SQLAInterface(Person)
421
    
422
    # Filter by related model fields
423
    base_filters = [
424
        ['department.name', FilterEqual, 'Engineering'],  # Join filter
425
        ['department.active', FilterEqual, True]           # Related field filter
426
    ]
427
```
428

429
### Model Mixins and Utilities
430

431
Utility mixins and helper functions for common model patterns and enhanced functionality.
432

433
```python { .api }
434
# Audit mixin for tracking changes
435
from flask_appbuilder.models.mixins import AuditMixin
436
from flask_appbuilder.models.decorators import renders
437
import datetime
438

439
class AuditMixin(object):
440
    """Mixin for audit trail fields."""
441
    
442
    created_on = Column(DateTime, default=datetime.datetime.now, nullable=False)
443
    changed_on = Column(DateTime, default=datetime.datetime.now, 
444
                       onupdate=datetime.datetime.now, nullable=False)
445
    created_by_fk = Column(Integer, ForeignKey('ab_user.id'), nullable=False)
446
    changed_by_fk = Column(Integer, ForeignKey('ab_user.id'), nullable=False)
447
    
448
    created_by = relationship("User", foreign_keys=[created_by_fk])
449
    changed_by = relationship("User", foreign_keys=[changed_by_fk])
450

451
# File mixin for file uploads
452
class FileColumn(Model):
453
    """Model for file storage."""
454
    
455
    id = Column(Integer, primary_key=True)
456
    file = Column(FileColumn, nullable=False)
457

458
# Image mixin  
459
class ImageColumn(Model):
460
    """Model for image storage."""
461
    
462
    id = Column(Integer, primary_key=True)
463
    image = Column(ImageColumn, nullable=False)
464

465
# Usage with audit mixin
466
class AuditedPerson(Model, AuditMixin):
467
    __tablename__ = 'audited_persons'
468
    
469
    id = Column(Integer, primary_key=True)
470
    name = Column(String(150), nullable=False)
471
    email = Column(String(120), nullable=False)
472

473
# Custom field rendering
474
@renders('name')
475
def render_name(self):
476
    """Custom rendering for name field."""
477
    return f"<strong>{self.name}</strong>"
478

479
# Model validation  
480
from marshmallow import ValidationError
481

482
class Person(Model):
483
    __tablename__ = 'persons'
484
    
485
    id = Column(Integer, primary_key=True)
486
    email = Column(String(120), nullable=False)
487
    
488
    def validate_email(self, email):
489
        """Custom email validation."""
490
        if not email or '@' not in email:
491
            raise ValidationError("Invalid email address")
492

493
# Soft delete mixin
494
class SoftDeleteMixin(object):
495
    """Mixin for soft delete functionality."""
496
    
497
    deleted = Column(Boolean, default=False, nullable=False)
498
    deleted_on = Column(DateTime)
499
    
500
    def soft_delete(self):
501
        """Mark record as deleted."""
502
        self.deleted = True
503
        self.deleted_on = datetime.datetime.now()
504

505
class SoftDeletedModel(Model, SoftDeleteMixin):
506
    __tablename__ = 'soft_deleted'
507
    
508
    id = Column(Integer, primary_key=True)
509
    name = Column(String(100))
510
    
511
    # Override queries to exclude deleted records
512
    @classmethod
513
    def query_active(cls):
514
        return cls.query.filter(cls.deleted == False)
515
```