0
# Schema Definition and Reflection
1
2
Table, column, and constraint definition, database schema reflection, index management, and DDL generation for database structure management. Schema definition provides the foundation for both Core and ORM table mapping.
3
4
## Capabilities
5
6
### MetaData and Table Definition
7
8
Core schema container and table definition with columns and constraints.
9
10
```python { .api }
11
class MetaData:
12
"""Container for database schema information."""
13
14
def __init__(self, bind=None, reflect=False, schema=None, **kwargs):
15
"""
16
Create MetaData container.
17
18
Parameters:
19
- bind: Engine for automatic table creation/reflection
20
- reflect: bool, automatically reflect all tables
21
- schema: str, default schema name
22
"""
23
24
def create_all(self, bind, tables=None, checkfirst=True):
25
"""
26
Create all tables in database.
27
28
Parameters:
29
- bind: Engine or Connection for creation
30
- tables: specific tables to create (all if None)
31
- checkfirst: bool, check if table exists before creating
32
"""
33
34
def drop_all(self, bind, tables=None, checkfirst=True):
35
"""
36
Drop all tables from database.
37
38
Parameters:
39
- bind: Engine or Connection for dropping
40
- tables: specific tables to drop (all if None)
41
- checkfirst: bool, check if table exists before dropping
42
"""
43
44
def reflect(self, bind, schema=None, views=False, only=None, **kwargs):
45
"""
46
Reflect database schema into MetaData.
47
48
Parameters:
49
- bind: Engine or Connection for reflection
50
- schema: str, schema name to reflect
51
- views: bool, reflect views as well as tables
52
- only: callable or list, filter for table names
53
"""
54
55
class Table:
56
"""Database table definition with columns and constraints."""
57
58
def __init__(self, name, metadata, *args, **kwargs):
59
"""
60
Define database table.
61
62
Parameters:
63
- name: str, table name
64
- metadata: MetaData container
65
- args: Column and Constraint objects
66
- schema: str, schema name
67
- quote: bool, quote table name
68
- autoload_with: Engine for automatic loading
69
- extend_existing: bool, extend existing table definition
70
- keep_existing: bool, keep existing table definition
71
"""
72
73
def create(self, bind, checkfirst=True):
74
"""
75
Create table in database.
76
77
Parameters:
78
- bind: Engine or Connection
79
- checkfirst: bool, check existence before creating
80
"""
81
82
def drop(self, bind, checkfirst=True):
83
"""
84
Drop table from database.
85
86
Parameters:
87
- bind: Engine or Connection
88
- checkfirst: bool, check existence before dropping
89
"""
90
91
@property
92
def columns(self):
93
"""ColumnCollection of table columns."""
94
95
@property
96
def c(self):
97
"""Shorthand for columns attribute."""
98
99
@property
100
def primary_key(self):
101
"""PrimaryKeyConstraint for table."""
102
103
@property
104
def foreign_keys(self):
105
"""Set of ForeignKey objects in table."""
106
```
107
108
### Column Definition
109
110
Column specification with types, constraints, and default values.
111
112
```python { .api }
113
class Column:
114
"""Database column definition."""
115
116
def __init__(self, *args, **kwargs):
117
"""
118
Define database column.
119
120
Parameters:
121
- name: str, column name (optional, can be inferred)
122
- type_: TypeEngine, column data type
123
- primary_key: bool, column is primary key
124
- nullable: bool, column allows NULL (default True)
125
- default: default value or callable
126
- server_default: DefaultClause for server-side default
127
- server_onupdate: DefaultClause for server-side update
128
- unique: bool, column has unique constraint
129
- index: bool, create index on column
130
- autoincrement: bool or str, auto-increment behavior
131
- quote: bool, quote column name
132
- comment: str, column comment
133
"""
134
135
def copy(self, **kwargs):
136
"""
137
Create copy of column with modifications.
138
139
Parameters:
140
- kwargs: attributes to override
141
142
Returns:
143
Column: Copied column with changes
144
"""
145
```
146
147
### Constraints
148
149
Primary key, foreign key, unique, and check constraints for data integrity.
150
151
```python { .api }
152
class PrimaryKeyConstraint:
153
"""Primary key constraint on one or more columns."""
154
155
def __init__(self, *columns, **kwargs):
156
"""
157
Create primary key constraint.
158
159
Parameters:
160
- columns: Column objects or column names
161
- name: str, constraint name
162
"""
163
164
class ForeignKeyConstraint:
165
"""Foreign key constraint referencing another table."""
166
167
def __init__(self, columns, refcolumns, **kwargs):
168
"""
169
Create foreign key constraint.
170
171
Parameters:
172
- columns: list of local column names
173
- refcolumns: list of referenced column names
174
- name: str, constraint name
175
- onupdate: str, ON UPDATE action ('CASCADE', 'SET NULL', etc.)
176
- ondelete: str, ON DELETE action
177
- deferrable: bool, constraint is deferrable
178
- initially: str, initial constraint check timing
179
"""
180
181
class ForeignKey:
182
"""Individual foreign key reference."""
183
184
def __init__(self, column, **kwargs):
185
"""
186
Create foreign key reference.
187
188
Parameters:
189
- column: str, referenced column (table.column format)
190
- onupdate: str, ON UPDATE action
191
- ondelete: str, ON DELETE action
192
- name: str, constraint name
193
"""
194
195
class UniqueConstraint:
196
"""Unique constraint on one or more columns."""
197
198
def __init__(self, *columns, **kwargs):
199
"""
200
Create unique constraint.
201
202
Parameters:
203
- columns: Column objects or column names
204
- name: str, constraint name
205
- deferrable: bool, constraint is deferrable
206
"""
207
208
class CheckConstraint:
209
"""Check constraint with SQL expression."""
210
211
def __init__(self, sqltext, **kwargs):
212
"""
213
Create check constraint.
214
215
Parameters:
216
- sqltext: str or ClauseElement, check expression
217
- name: str, constraint name
218
"""
219
```
220
221
### Indexes
222
223
Index definition for query performance optimization.
224
225
```python { .api }
226
class Index:
227
"""Database index definition."""
228
229
def __init__(self, name, *expressions, **kwargs):
230
"""
231
Create database index.
232
233
Parameters:
234
- name: str, index name
235
- expressions: Column objects or SQL expressions
236
- unique: bool, create unique index
237
- quote: bool, quote index name
238
- postgresql_using: str, PostgreSQL index method
239
- postgresql_where: ClauseElement, partial index condition
240
- mysql_length: dict, MySQL key length specifications
241
"""
242
243
def create(self, bind, checkfirst=True):
244
"""
245
Create index in database.
246
247
Parameters:
248
- bind: Engine or Connection
249
- checkfirst: bool, check existence before creating
250
"""
251
252
def drop(self, bind, checkfirst=True):
253
"""
254
Drop index from database.
255
256
Parameters:
257
- bind: Engine or Connection
258
- checkfirst: bool, check existence before dropping
259
"""
260
```
261
262
### Default Values and Computed Columns
263
264
Column default value specification and computed column definitions.
265
266
```python { .api }
267
class DefaultClause:
268
"""Server-side default value clause."""
269
270
def __init__(self, arg, **kwargs):
271
"""
272
Create default clause.
273
274
Parameters:
275
- arg: str, ClauseElement, or callable for default value
276
- for_update: bool, default applies to UPDATE statements
277
"""
278
279
class ColumnDefault:
280
"""Column-specific default value."""
281
282
def __init__(self, arg, **kwargs):
283
"""
284
Create column default.
285
286
Parameters:
287
- arg: default value, callable, or ClauseElement
288
- for_update: bool, default for updates
289
"""
290
291
class Computed:
292
"""Computed/generated column definition."""
293
294
def __init__(self, sqltext, **kwargs):
295
"""
296
Create computed column.
297
298
Parameters:
299
- sqltext: str or ClauseElement, computation expression
300
- persisted: bool, store computed value (vs. virtual)
301
"""
302
303
class Identity:
304
"""Identity column specification (SQL standard)."""
305
306
def __init__(self, start=None, increment=None, **kwargs):
307
"""
308
Create identity column.
309
310
Parameters:
311
- start: int, starting value
312
- increment: int, increment value
313
- always: bool, ALWAYS vs BY DEFAULT generation
314
- on_null: bool, generate on NULL insertion
315
"""
316
317
class Sequence:
318
"""Database sequence for auto-incrementing values."""
319
320
def __init__(self, name, start=None, increment=None, **kwargs):
321
"""
322
Create sequence.
323
324
Parameters:
325
- name: str, sequence name
326
- start: int, starting value
327
- increment: int, increment value
328
- minvalue: int, minimum value
329
- maxvalue: int, maximum value
330
- cycle: bool, cycle when reaching max/min
331
- schema: str, schema name
332
"""
333
334
def create(self, bind, checkfirst=True):
335
"""Create sequence in database."""
336
337
def drop(self, bind, checkfirst=True):
338
"""Drop sequence from database."""
339
340
def next_value(self):
341
"""
342
Get next value expression for queries.
343
344
Returns:
345
FunctionElement: Next value function call
346
"""
347
```
348
349
### DDL Generation and Execution
350
351
Custom DDL statement creation and execution.
352
353
```python { .api }
354
class DDL:
355
"""Custom DDL statement."""
356
357
def __init__(self, statement, **kwargs):
358
"""
359
Create DDL statement.
360
361
Parameters:
362
- statement: str, DDL statement text
363
- on: str or callable, execution condition
364
- bind: Engine for parameter binding
365
"""
366
367
def execute(self, bind, target=None):
368
"""
369
Execute DDL statement.
370
371
Parameters:
372
- bind: Engine or Connection
373
- target: target object for execution context
374
"""
375
376
class CreateTable:
377
"""CREATE TABLE DDL statement."""
378
379
def __init__(self, element, **kwargs):
380
"""
381
Create table creation DDL.
382
383
Parameters:
384
- element: Table object to create
385
- include_foreign_key_constraints: bool, include FK constraints
386
"""
387
388
class DropTable:
389
"""DROP TABLE DDL statement."""
390
391
def __init__(self, element, **kwargs):
392
"""
393
Create table drop DDL.
394
395
Parameters:
396
- element: Table object to drop
397
- if_exists: bool, add IF EXISTS clause
398
"""
399
```
400
401
### Schema Utilities
402
403
Helper functions and utilities for schema operations.
404
405
```python { .api }
406
def table(name, *columns, **kw):
407
"""
408
Create ad-hoc table construct.
409
410
Parameters:
411
- name: str, table name
412
- columns: Column objects
413
414
Returns:
415
TableClause: Table construct for queries
416
"""
417
418
def column(text, type_=None, **kw):
419
"""
420
Create ad-hoc column construct.
421
422
Parameters:
423
- text: str, column name
424
- type_: TypeEngine, column type
425
426
Returns:
427
ColumnClause: Column construct for queries
428
"""
429
430
def quoted_name(value, quote):
431
"""
432
Create quoted identifier.
433
434
Parameters:
435
- value: str, identifier value
436
- quote: bool, force quoting
437
438
Returns:
439
quoted_name: Quoted identifier object
440
"""
441
```
442
443
## Usage Examples
444
445
### Basic Table Definition
446
447
```python
448
from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey
449
450
metadata = MetaData()
451
452
users = Table('users', metadata,
453
Column('id', Integer, primary_key=True),
454
Column('name', String(50), nullable=False),
455
Column('email', String(100), unique=True),
456
Column('created_at', DateTime, server_default=func.now())
457
)
458
459
orders = Table('orders', metadata,
460
Column('id', Integer, primary_key=True),
461
Column('user_id', Integer, ForeignKey('users.id')),
462
Column('amount', Numeric(10, 2)),
463
Column('status', String(20), default='pending')
464
)
465
466
# Create tables
467
engine = create_engine("postgresql://user:pass@localhost/db")
468
metadata.create_all(engine)
469
```
470
471
### Schema Reflection
472
473
```python
474
# Reflect existing database schema
475
metadata = MetaData()
476
metadata.reflect(bind=engine, schema='public')
477
478
# Access reflected tables
479
users_table = metadata.tables['users']
480
print(f"Columns: {list(users_table.columns.keys())}")
481
482
# Partial reflection
483
metadata.reflect(bind=engine, only=['users', 'orders'])
484
```
485
486
### Constraints and Indexes
487
488
```python
489
from sqlalchemy import UniqueConstraint, CheckConstraint, Index
490
491
users = Table('users', metadata,
492
Column('id', Integer, primary_key=True),
493
Column('username', String(50)),
494
Column('email', String(100)),
495
Column('age', Integer),
496
497
# Table-level constraints
498
UniqueConstraint('username', 'email', name='uq_user_identity'),
499
CheckConstraint('age >= 18', name='ck_adult_age')
500
)
501
502
# Separate index creation
503
user_email_idx = Index('idx_user_email', users.c.email)
504
user_email_idx.create(engine)
505
506
# Compound index
507
compound_idx = Index('idx_user_name_age', users.c.username, users.c.age)
508
```
509
510
### Advanced Column Features
511
512
```python
513
from sqlalchemy import Sequence, Identity, Computed
514
515
# Identity column (SQL Server, PostgreSQL 10+)
516
table_with_identity = Table('documents', metadata,
517
Column('id', Integer, Identity(start=1000, increment=1), primary_key=True),
518
Column('title', String(200))
519
)
520
521
# Sequence-based auto-increment
522
user_id_seq = Sequence('user_id_seq', start=1, increment=1)
523
users_with_seq = Table('users', metadata,
524
Column('id', Integer, user_id_seq, primary_key=True),
525
Column('name', String(50))
526
)
527
528
# Computed column
529
products = Table('products', metadata,
530
Column('price', Numeric(10, 2)),
531
Column('tax_rate', Numeric(5, 4)),
532
Column('total_price', Numeric(10, 2),
533
Computed('price * (1 + tax_rate)', persisted=True))
534
)
535
```