0
# SQLAlchemy Integration
1
2
Complete SQLAlchemy ORM integration providing database model administration with relationships, advanced filtering, and comprehensive querying capabilities.
3
4
## Capabilities
5
6
### SQLAlchemy Model View
7
8
Main model view class for SQLAlchemy models with full CRUD operations, relationship handling, and advanced querying.
9
10
```python { .api }
11
from flask_admin.contrib.sqla import ModelView
12
13
class ModelView(BaseModelView):
14
def __init__(self, model, session, **kwargs):
15
"""
16
Initialize SQLAlchemy model view.
17
18
Args:
19
model: SQLAlchemy model class
20
session: SQLAlchemy session or session factory
21
**kwargs: Additional view configuration
22
"""
23
24
# SQLAlchemy-specific configuration
25
column_auto_select_related = True # Auto-select related models for efficiency
26
column_select_related_list = None # Explicit related model selection
27
inline_models = None # Inline editing for related models
28
29
# Advanced filtering
30
column_filters = None # Column-based filters
31
named_filter_urls = False # Use named URLs for filters
32
33
# Query customization
34
def get_query(self):
35
"""
36
Get base query for list view.
37
38
Returns:
39
Query: SQLAlchemy query object
40
"""
41
42
def get_count_query(self):
43
"""
44
Get count query for pagination.
45
46
Returns:
47
Query: Count query object
48
"""
49
50
def get_list(self, page, sort_column, sort_desc, search, filters, execute=True, page_size=None):
51
"""
52
Get paginated model list with sorting, searching, and filtering.
53
54
Args:
55
page (int): Page number (0-based)
56
sort_column (str): Column to sort by
57
sort_desc (bool): Sort in descending order
58
search (str): Search query string
59
filters (list): Active filter list
60
execute (bool): Execute query immediately
61
page_size (int, optional): Items per page
62
63
Returns:
64
tuple: (total_count, items) if execute=True, query if execute=False
65
"""
66
67
def get_one(self, id):
68
"""
69
Get single model instance by primary key.
70
71
Args:
72
id: Primary key value
73
74
Returns:
75
Model instance or None if not found
76
"""
77
78
# CRUD operations
79
def create_model(self, form):
80
"""
81
Create new model instance from form data.
82
83
Args:
84
form: Validated form instance
85
86
Returns:
87
bool: True if creation successful
88
"""
89
90
def update_model(self, form, model):
91
"""
92
Update existing model instance from form data.
93
94
Args:
95
form: Validated form instance
96
model: Model instance to update
97
98
Returns:
99
bool: True if update successful
100
"""
101
102
def delete_model(self, model):
103
"""
104
Delete model instance.
105
106
Args:
107
model: Model instance to delete
108
109
Returns:
110
bool: True if deletion successful
111
"""
112
113
# Form scaffolding
114
def scaffold_form(self):
115
"""
116
Auto-generate form class from SQLAlchemy model.
117
118
Returns:
119
Form class with fields for model columns
120
"""
121
122
def scaffold_list_form(self, widget=None, validators=None):
123
"""
124
Generate form for inline list editing.
125
126
Args:
127
widget: Custom widget for fields
128
validators: Custom validators
129
130
Returns:
131
Form class for inline editing
132
"""
133
134
def scaffold_filters(self, name):
135
"""
136
Generate filters for model column.
137
138
Args:
139
name (str): Column name
140
141
Returns:
142
list: Available filter types for column
143
"""
144
145
# Relationship handling
146
def scaffold_auto_joins(self, query):
147
"""
148
Automatically add joins for related models to improve performance.
149
150
Args:
151
query: Base query
152
153
Returns:
154
Query: Enhanced query with joins
155
"""
156
157
def get_pk_value(self, model):
158
"""
159
Get primary key value from model instance.
160
161
Args:
162
model: Model instance
163
164
Returns:
165
Primary key value
166
"""
167
168
# Query hooks for customization
169
def apply_search(self, query, search):
170
"""
171
Apply search filters to query.
172
173
Args:
174
query: Base query
175
search (str): Search string
176
177
Returns:
178
Query: Query with search filters applied
179
"""
180
181
def apply_filters(self, query, filters):
182
"""
183
Apply column filters to query.
184
185
Args:
186
query: Base query
187
filters (list): Active filters
188
189
Returns:
190
Query: Query with filters applied
191
"""
192
193
def apply_sorting(self, query, sort_column, sort_desc):
194
"""
195
Apply sorting to query.
196
197
Args:
198
query: Base query
199
sort_column (str): Column to sort by
200
sort_desc (bool): Sort descending
201
202
Returns:
203
Query: Sorted query
204
"""
205
```
206
207
### Inline Model Editing
208
209
Support for editing related models inline within the parent model form.
210
211
```python { .api }
212
class InlineFormAdmin:
213
def __init__(
214
self,
215
model,
216
form_columns=None,
217
excluded_form_columns=None,
218
form_args=None,
219
form_widget_args=None,
220
form_overrides=None
221
):
222
"""
223
Initialize inline form admin for related model editing.
224
225
Args:
226
model: Related model class
227
form_columns (list, optional): Columns to include in inline form
228
excluded_form_columns (list, optional): Columns to exclude
229
form_args (dict, optional): Form field arguments
230
form_widget_args (dict, optional): Widget arguments
231
form_overrides (dict, optional): Field type overrides
232
"""
233
```
234
235
## Usage Examples
236
237
### Basic SQLAlchemy Model View
238
239
```python
240
from flask import Flask
241
from flask_sqlalchemy import SQLAlchemy
242
from flask_admin import Admin
243
from flask_admin.contrib.sqla import ModelView
244
245
app = Flask(__name__)
246
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
247
app.config['SECRET_KEY'] = 'secret-key'
248
249
db = SQLAlchemy(app)
250
251
# Define models
252
class Category(db.Model):
253
id = db.Column(db.Integer, primary_key=True)
254
name = db.Column(db.String(100), nullable=False)
255
products = db.relationship('Product', backref='category', lazy='dynamic')
256
257
class Product(db.Model):
258
id = db.Column(db.Integer, primary_key=True)
259
name = db.Column(db.String(200), nullable=False)
260
price = db.Column(db.Numeric(10, 2))
261
category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
262
created_at = db.Column(db.DateTime, default=datetime.utcnow)
263
264
# Create admin views
265
class ProductModelView(ModelView):
266
list_columns = ['id', 'name', 'price', 'category.name', 'created_at']
267
column_searchable_list = ['name', 'category.name']
268
column_filters = ['category', 'price', 'created_at']
269
column_labels = {'category.name': 'Category'}
270
271
form_columns = ['name', 'price', 'category']
272
273
# Enable auto-joining for performance
274
column_auto_select_related = True
275
276
class CategoryModelView(ModelView):
277
list_columns = ['id', 'name', 'products']
278
column_searchable_list = ['name']
279
280
# Show product count in list
281
def _products_formatter(view, context, model, name):
282
return model.products.count()
283
284
column_formatters = {
285
'products': _products_formatter
286
}
287
288
# Initialize admin
289
admin = Admin(app, name='Shop Admin')
290
admin.add_view(CategoryModelView(Category, db.session, name='Categories'))
291
admin.add_view(ProductModelView(Product, db.session, name='Products'))
292
```
293
294
### Advanced Filtering and Searching
295
296
```python
297
from flask_admin.contrib.sqla.filters import FilterLike, FilterEqual, FilterInList
298
299
class AdvancedProductView(ModelView):
300
# Searchable columns with different search strategies
301
column_searchable_list = [
302
'name', # Full-text search on name
303
'description', # Full-text search on description
304
'category.name' # Search in related model
305
]
306
307
# Custom filters
308
column_filters = [
309
'name', # Automatic filters
310
'price',
311
'created_at',
312
'category',
313
FilterLike('name', 'Name Contains'), # Custom like filter
314
FilterEqual('price', 'Exact Price'), # Custom equal filter
315
FilterInList('category_id', 'Categories',
316
options=[(1, 'Electronics'), (2, 'Books')]) # Custom in-list filter
317
]
318
319
# Custom query with additional joins
320
def get_query(self):
321
return self.session.query(self.model).join(Category)
322
323
def get_count_query(self):
324
return self.session.query(func.count('*')).select_from(self.model).join(Category)
325
326
# Custom search implementation
327
def apply_search(self, query, search):
328
if search:
329
search_filter = or_(
330
Product.name.contains(search),
331
Product.description.contains(search),
332
Category.name.contains(search)
333
)
334
query = query.filter(search_filter)
335
return query
336
```
337
338
### Inline Model Editing
339
340
```python
341
from flask_admin.contrib.sqla import InlineFormAdmin
342
343
class OrderItemInlineForm(InlineFormAdmin):
344
form_columns = ('product', 'quantity', 'unit_price')
345
form_args = {
346
'quantity': {'validators': [NumberRange(min=1)]},
347
'unit_price': {'validators': [NumberRange(min=0)]}
348
}
349
350
class OrderModelView(ModelView):
351
inline_models = [OrderItemInlineForm(OrderItem)]
352
353
list_columns = ['id', 'customer_name', 'total_amount', 'order_date', 'status']
354
form_columns = ['customer_name', 'order_date', 'status', 'items']
355
356
def create_form(self, obj=None):
357
form = super().create_form(obj)
358
# Custom form processing for inline items
359
return form
360
```
361
362
### Custom Column Formatters and Export
363
364
```python
365
from markupsafe import Markup
366
from flask_admin.contrib.sqla.ajax import QueryAjaxModelLoader
367
368
class UserModelView(ModelView):
369
# Custom column formatters
370
column_formatters = {
371
'email': lambda v, c, m, p: Markup(f'<a href="mailto:{m.email}">{m.email}</a>'),
372
'status': lambda v, c, m, p: 'Active' if m.is_active else 'Inactive',
373
'avatar': lambda v, c, m, p: Markup(f'<img src="{m.avatar_url}" width="32">') if m.avatar_url else ''
374
}
375
376
# Export-specific formatters (clean data for CSV)
377
column_formatters_export = {
378
'email': lambda v, c, m, p: m.email,
379
'status': lambda v, c, m, p: 'Active' if m.is_active else 'Inactive',
380
'avatar': lambda v, c, m, p: m.avatar_url or ''
381
}
382
383
# Enable export
384
can_export = True
385
export_types = ['csv', 'json', 'yaml']
386
export_max_rows = 10000
387
388
# AJAX loading for large datasets
389
form_ajax_refs = {
390
'manager': QueryAjaxModelLoader(
391
'manager',
392
db.session,
393
User,
394
fields=['name', 'email'],
395
placeholder='Select manager...'
396
)
397
}
398
```
399
400
### Custom Actions with SQLAlchemy
401
402
```python
403
from flask_admin.actions import action
404
from flask import flash
405
406
class UserModelView(ModelView):
407
@action('activate', 'Activate Users', 'Are you sure you want to activate selected users?')
408
def action_activate(self, ids):
409
try:
410
# Bulk update using SQLAlchemy
411
count = self.session.query(User).filter(User.id.in_(ids)).update(
412
{User.is_active: True},
413
synchronize_session='fetch'
414
)
415
self.session.commit()
416
flash(f'Successfully activated {count} users.', 'success')
417
except Exception as ex:
418
flash(f'Failed to activate users: {str(ex)}', 'error')
419
420
@action('delete', 'Delete', 'Are you sure?')
421
def action_delete(self, ids):
422
try:
423
# Bulk delete with cascade handling
424
users = self.session.query(User).filter(User.id.in_(ids)).all()
425
for user in users:
426
self.session.delete(user)
427
self.session.commit()
428
flash(f'Successfully deleted {len(users)} users.', 'success')
429
except Exception as ex:
430
self.session.rollback()
431
flash(f'Failed to delete users: {str(ex)}', 'error')
432
433
def is_action_allowed(self, name):
434
# Custom action permissions
435
if name == 'delete' and not current_user.is_admin:
436
return False
437
return super().is_action_allowed(name)
438
```
439
440
### Relationship Handling
441
442
```python
443
class BlogPostModelView(ModelView):
444
# Display related data in list
445
list_columns = ['title', 'author.name', 'category.name', 'created_at', 'published']
446
447
# Efficient querying with auto-joins
448
column_auto_select_related = True
449
column_select_related_list = ['author', 'category']
450
451
# Search across relationships
452
column_searchable_list = ['title', 'content', 'author.name', 'category.name']
453
454
# Filter by relationships
455
column_filters = ['author', 'category', 'published', 'created_at']
456
457
# Form with relationship fields
458
form_columns = ['title', 'content', 'author', 'category', 'tags', 'published']
459
460
# Custom query to include soft-deleted filter
461
def get_query(self):
462
return super().get_query().filter(BlogPost.deleted_at.is_(None))
463
464
def get_count_query(self):
465
return super().get_count_query().filter(BlogPost.deleted_at.is_(None))
466
```