« back to all changes in this revision
Viewing changes to doc/build/core/schema.rst
- browse files at revision 35
- compare with another revision
- compare with revision 35
- download diff
- download tarball
- view history from revision 35
- Committer: Package Import Robot
- Author(s): Piotr Ożarowski
- Date: 2013-10-28 22:29:40 UTC
- mfrom: (1.4.24)
- Revision ID: package-import@ubuntu.com-20131028222940-wvyqffl4g617caun
Tags: 0.8.3-1
New upstream release
- files added:
- doc/_sources/core/constraints.txt
- files modified:
- PKG-INFO
added
removed
doc/build/core/schema.rst
1
.. _metadata_toplevel:
1
.. _schema_toplevel:
2
2
3
3
==========================
4
4
Schema Definition Language
6
6
7
7
.. module:: sqlalchemy.schema
8
8
9
10
.. _metadata_describing:
11
12
Describing Databases with MetaData
13
==================================
9
This section references SQLAlchemy **schema metadata**, a comprehensive system of describing and inspecting
10
database schemas.
14
11
15
12
The core of SQLAlchemy's query and object mapping operations are supported by
16
13
*database metadata*, which is comprised of Python objects that describe tables
35
32
real DDL. They are therefore most intuitive to those who have some background
36
33
in creating real schema generation scripts.
37
34
38
A collection of metadata entities is stored in an object aptly named
39
:class:`~sqlalchemy.schema.MetaData`::
40
41
from sqlalchemy import *
42
43
metadata = MetaData()
44
45
:class:`~sqlalchemy.schema.MetaData` is a container object that keeps together
46
many different features of a database (or multiple databases) being described.
47
48
To represent a table, use the :class:`~sqlalchemy.schema.Table` class. Its two
49
primary arguments are the table name, then the
50
:class:`~sqlalchemy.schema.MetaData` object which it will be associated with.
51
The remaining positional arguments are mostly
52
:class:`~sqlalchemy.schema.Column` objects describing each column::
53
54
user = Table('user', metadata,
55
Column('user_id', Integer, primary_key = True),
56
Column('user_name', String(16), nullable = False),
57
Column('email_address', String(60)),
58
Column('password', String(20), nullable = False)
59
)
60
61
Above, a table called ``user`` is described, which contains four columns. The
62
primary key of the table consists of the ``user_id`` column. Multiple columns
63
may be assigned the ``primary_key=True`` flag which denotes a multi-column
64
primary key, known as a *composite* primary key.
65
66
Note also that each column describes its datatype using objects corresponding
67
to genericized types, such as :class:`~sqlalchemy.types.Integer` and
68
:class:`~sqlalchemy.types.String`. SQLAlchemy features dozens of types of
69
varying levels of specificity as well as the ability to create custom types.
70
Documentation on the type system can be found at :ref:`types`.
71
72
Accessing Tables and Columns
73
----------------------------
74
75
The :class:`~sqlalchemy.schema.MetaData` object contains all of the schema
76
constructs we've associated with it. It supports a few methods of accessing
77
these table objects, such as the ``sorted_tables`` accessor which returns a
78
list of each :class:`~sqlalchemy.schema.Table` object in order of foreign key
79
dependency (that is, each table is preceded by all tables which it
80
references)::
81
82
>>> for t in metadata.sorted_tables:
83
... print t.name
84
user
85
user_preference
86
invoice
87
invoice_item
88
89
In most cases, individual :class:`~sqlalchemy.schema.Table` objects have been
90
explicitly declared, and these objects are typically accessed directly as
91
module-level variables in an application. Once a
92
:class:`~sqlalchemy.schema.Table` has been defined, it has a full set of
93
accessors which allow inspection of its properties. Given the following
94
:class:`~sqlalchemy.schema.Table` definition::
95
96
employees = Table('employees', metadata,
97
Column('employee_id', Integer, primary_key=True),
98
Column('employee_name', String(60), nullable=False),
99
Column('employee_dept', Integer, ForeignKey("departments.department_id"))
100
)
101
102
Note the :class:`~sqlalchemy.schema.ForeignKey` object used in this table -
103
this construct defines a reference to a remote table, and is fully described
104
in :ref:`metadata_foreignkeys`. Methods of accessing information about this
105
table include::
106
107
# access the column "EMPLOYEE_ID":
108
employees.columns.employee_id
109
110
# or just
111
employees.c.employee_id
112
113
# via string
114
employees.c['employee_id']
115
116
# iterate through all columns
117
for c in employees.c:
118
print c
119
120
# get the table's primary key columns
121
for primary_key in employees.primary_key:
122
print primary_key
123
124
# get the table's foreign key objects:
125
for fkey in employees.foreign_keys:
126
print fkey
127
128
# access the table's MetaData:
129
employees.metadata
130
131
# access the table's bound Engine or Connection, if its MetaData is bound:
132
employees.bind
133
134
# access a column's name, type, nullable, primary key, foreign key
135
employees.c.employee_id.name
136
employees.c.employee_id.type
137
employees.c.employee_id.nullable
138
employees.c.employee_id.primary_key
139
employees.c.employee_dept.foreign_keys
140
141
# get the "key" of a column, which defaults to its name, but can
142
# be any user-defined string:
143
employees.c.employee_name.key
144
145
# access a column's table:
146
employees.c.employee_id.table is employees
147
148
# get the table related by a foreign key
149
list(employees.c.employee_dept.foreign_keys)[0].column.table
150
151
Creating and Dropping Database Tables
152
-------------------------------------
153
154
Once you've defined some :class:`~sqlalchemy.schema.Table` objects, assuming
155
you're working with a brand new database one thing you might want to do is
156
issue CREATE statements for those tables and their related constructs (as an
157
aside, it's also quite possible that you *don't* want to do this, if you
158
already have some preferred methodology such as tools included with your
159
database or an existing scripting system - if that's the case, feel free to
160
skip this section - SQLAlchemy has no requirement that it be used to create
161
your tables).
162
163
The usual way to issue CREATE is to use
164
:func:`~sqlalchemy.schema.MetaData.create_all` on the
165
:class:`~sqlalchemy.schema.MetaData` object. This method will issue queries
166
that first check for the existence of each individual table, and if not found
167
will issue the CREATE statements:
168
169
.. sourcecode:: python+sql
170
171
engine = create_engine('sqlite:///:memory:')
172
173
metadata = MetaData()
174
175
user = Table('user', metadata,
176
Column('user_id', Integer, primary_key = True),
177
Column('user_name', String(16), nullable = False),
178
Column('email_address', String(60), key='email'),
179
Column('password', String(20), nullable = False)
180
)
181
182
user_prefs = Table('user_prefs', metadata,
183
Column('pref_id', Integer, primary_key=True),
184
Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
185
Column('pref_name', String(40), nullable=False),
186
Column('pref_value', String(100))
187
)
188
189
{sql}metadata.create_all(engine)
190
PRAGMA table_info(user){}
191
CREATE TABLE user(
192
user_id INTEGER NOT NULL PRIMARY KEY,
193
user_name VARCHAR(16) NOT NULL,
194
email_address VARCHAR(60),
195
password VARCHAR(20) NOT NULL
196
)
197
PRAGMA table_info(user_prefs){}
198
CREATE TABLE user_prefs(
199
pref_id INTEGER NOT NULL PRIMARY KEY,
200
user_id INTEGER NOT NULL REFERENCES user(user_id),
201
pref_name VARCHAR(40) NOT NULL,
202
pref_value VARCHAR(100)
203
)
204
205
:func:`~sqlalchemy.schema.MetaData.create_all` creates foreign key constraints
206
between tables usually inline with the table definition itself, and for this
207
reason it also generates the tables in order of their dependency. There are
208
options to change this behavior such that ``ALTER TABLE`` is used instead.
209
210
Dropping all tables is similarly achieved using the
211
:func:`~sqlalchemy.schema.MetaData.drop_all` method. This method does the
212
exact opposite of :func:`~sqlalchemy.schema.MetaData.create_all` - the
213
presence of each table is checked first, and tables are dropped in reverse
214
order of dependency.
215
216
Creating and dropping individual tables can be done via the ``create()`` and
217
``drop()`` methods of :class:`~sqlalchemy.schema.Table`. These methods by
218
default issue the CREATE or DROP regardless of the table being present:
219
220
.. sourcecode:: python+sql
221
222
engine = create_engine('sqlite:///:memory:')
223
224
meta = MetaData()
225
226
employees = Table('employees', meta,
227
Column('employee_id', Integer, primary_key=True),
228
Column('employee_name', String(60), nullable=False, key='name'),
229
Column('employee_dept', Integer, ForeignKey("departments.department_id"))
230
)
231
{sql}employees.create(engine)
232
CREATE TABLE employees(
233
employee_id SERIAL NOT NULL PRIMARY KEY,
234
employee_name VARCHAR(60) NOT NULL,
235
employee_dept INTEGER REFERENCES departments(department_id)
236
)
237
{}
238
239
``drop()`` method:
240
241
.. sourcecode:: python+sql
242
243
{sql}employees.drop(engine)
244
DROP TABLE employees
245
{}
246
247
To enable the "check first for the table existing" logic, add the
248
``checkfirst=True`` argument to ``create()`` or ``drop()``::
249
250
employees.create(engine, checkfirst=True)
251
employees.drop(engine, checkfirst=False)
252
253
Altering Schemas through Migrations
254
-----------------------------------
255
256
While SQLAlchemy directly supports emitting CREATE and DROP statements for schema
257
constructs, the ability to alter those constructs, usually via the ALTER statement
258
as well as other database-specific constructs, is outside of the scope of SQLAlchemy
259
itself. While it's easy enough to emit ALTER statements and similar by hand,
260
such as by passing a string to :meth:`.Connection.execute` or by using the
261
:class:`.DDL` construct, it's a common practice to automate the maintenance of
262
database schemas in relation to application code using schema migration tools.
263
264
There are two major migration tools available for SQLAlchemy:
265
266
* `Alembic <http://alembic.readthedocs.org>`_ - Written by the author of SQLAlchemy,
267
Alembic features a highly customizable environment and a minimalistic usage pattern,
268
supporting such features as transactional DDL, automatic generation of "candidate"
269
migrations, an "offline" mode which generates SQL scripts, and support for branch
270
resolution.
271
* `SQLAlchemy-Migrate <http://code.google.com/p/sqlalchemy-migrate/>`_ - The original
272
migration tool for SQLAlchemy, SQLAlchemy-Migrate is widely used and continues
273
under active development. SQLAlchemy-Migrate includes features such as
274
SQL script generation, ORM class generation, ORM model comparison, and extensive
275
support for SQLite migrations.
276
277
278
Specifying the Schema Name
279
---------------------------
280
281
Some databases support the concept of multiple schemas. A
282
:class:`~sqlalchemy.schema.Table` can reference this by specifying the
283
``schema`` keyword argument::
284
285
financial_info = Table('financial_info', meta,
286
Column('id', Integer, primary_key=True),
287
Column('value', String(100), nullable=False),
288
schema='remote_banks'
289
)
290
291
Within the :class:`~sqlalchemy.schema.MetaData` collection, this table will be
292
identified by the combination of ``financial_info`` and ``remote_banks``. If
293
another table called ``financial_info`` is referenced without the
294
``remote_banks`` schema, it will refer to a different
295
:class:`~sqlalchemy.schema.Table`. :class:`~sqlalchemy.schema.ForeignKey`
296
objects can specify references to columns in this table using the form
297
``remote_banks.financial_info.id``.
298
299
The ``schema`` argument should be used for any name qualifiers required,
300
including Oracle's "owner" attribute and similar. It also can accommodate a
301
dotted name for longer schemes::
302
303
schema="dbo.scott"
304
305
Backend-Specific Options
306
------------------------
307
308
:class:`~sqlalchemy.schema.Table` supports database-specific options. For
309
example, MySQL has different table backend types, including "MyISAM" and
310
"InnoDB". This can be expressed with :class:`~sqlalchemy.schema.Table` using
311
``mysql_engine``::
312
313
addresses = Table('engine_email_addresses', meta,
314
Column('address_id', Integer, primary_key = True),
315
Column('remote_user_id', Integer, ForeignKey(users.c.user_id)),
316
Column('email_address', String(20)),
317
mysql_engine='InnoDB'
318
)
319
320
Other backends may support table-level options as well - these would be
321
described in the individual documentation sections for each dialect.
322
323
Column, Table, MetaData API
324
---------------------------
325
326
.. autoclass:: Column
327
:members:
328
:inherited-members:
329
:undoc-members:
330
:show-inheritance:
331
332
.. autoclass:: MetaData
333
:members:
334
:undoc-members:
335
:show-inheritance:
336
337
.. autoclass:: SchemaItem
338
:show-inheritance:
339
:members:
340
341
.. autoclass:: Table
342
:members:
343
:inherited-members:
344
:undoc-members:
345
:show-inheritance:
346
347
.. autoclass:: ThreadLocalMetaData
348
:members:
349
:undoc-members:
350
:show-inheritance:
351
352
353
.. _metadata_reflection:
354
355
Reflecting Database Objects
356
===========================
357
358
A :class:`~sqlalchemy.schema.Table` object can be instructed to load
359
information about itself from the corresponding database schema object already
360
existing within the database. This process is called *reflection*. In the
361
most simple case you need only specify the table name, a :class:`~sqlalchemy.schema.MetaData`
362
object, and the ``autoload=True`` flag. If the
363
:class:`~sqlalchemy.schema.MetaData` is not persistently bound, also add the
364
``autoload_with`` argument::
365
366
>>> messages = Table('messages', meta, autoload=True, autoload_with=engine)
367
>>> [c.name for c in messages.columns]
368
['message_id', 'message_name', 'date']
369
370
The above operation will use the given engine to query the database for
371
information about the ``messages`` table, and will then generate
372
:class:`~sqlalchemy.schema.Column`, :class:`~sqlalchemy.schema.ForeignKey`,
373
and other objects corresponding to this information as though the
374
:class:`~sqlalchemy.schema.Table` object were hand-constructed in Python.
375
376
When tables are reflected, if a given table references another one via foreign
377
key, a second :class:`~sqlalchemy.schema.Table` object is created within the
378
:class:`~sqlalchemy.schema.MetaData` object representing the connection.
379
Below, assume the table ``shopping_cart_items`` references a table named
380
``shopping_carts``. Reflecting the ``shopping_cart_items`` table has the
381
effect such that the ``shopping_carts`` table will also be loaded::
382
383
>>> shopping_cart_items = Table('shopping_cart_items', meta, autoload=True, autoload_with=engine)
384
>>> 'shopping_carts' in meta.tables:
385
True
386
387
The :class:`~sqlalchemy.schema.MetaData` has an interesting "singleton-like"
388
behavior such that if you requested both tables individually,
389
:class:`~sqlalchemy.schema.MetaData` will ensure that exactly one
390
:class:`~sqlalchemy.schema.Table` object is created for each distinct table
391
name. The :class:`~sqlalchemy.schema.Table` constructor actually returns to
392
you the already-existing :class:`~sqlalchemy.schema.Table` object if one
393
already exists with the given name. Such as below, we can access the already
394
generated ``shopping_carts`` table just by naming it::
395
396
shopping_carts = Table('shopping_carts', meta)
397
398
Of course, it's a good idea to use ``autoload=True`` with the above table
399
regardless. This is so that the table's attributes will be loaded if they have
400
not been already. The autoload operation only occurs for the table if it
401
hasn't already been loaded; once loaded, new calls to
402
:class:`~sqlalchemy.schema.Table` with the same name will not re-issue any
403
reflection queries.
404
405
Overriding Reflected Columns
406
-----------------------------
407
408
Individual columns can be overridden with explicit values when reflecting
409
tables; this is handy for specifying custom datatypes, constraints such as
410
primary keys that may not be configured within the database, etc.::
411
412
>>> mytable = Table('mytable', meta,
413
... Column('id', Integer, primary_key=True), # override reflected 'id' to have primary key
414
... Column('mydata', Unicode(50)), # override reflected 'mydata' to be Unicode
415
... autoload=True)
416
417
Reflecting Views
418
-----------------
419
420
The reflection system can also reflect views. Basic usage is the same as that
421
of a table::
422
423
my_view = Table("some_view", metadata, autoload=True)
424
425
Above, ``my_view`` is a :class:`~sqlalchemy.schema.Table` object with
426
:class:`~sqlalchemy.schema.Column` objects representing the names and types of
427
each column within the view "some_view".
428
429
Usually, it's desired to have at least a primary key constraint when
430
reflecting a view, if not foreign keys as well. View reflection doesn't
431
extrapolate these constraints.
432
433
Use the "override" technique for this, specifying explicitly those columns
434
which are part of the primary key or have foreign key constraints::
435
436
my_view = Table("some_view", metadata,
437
Column("view_id", Integer, primary_key=True),
438
Column("related_thing", Integer, ForeignKey("othertable.thing_id")),
439
autoload=True
440
)
441
442
Reflecting All Tables at Once
443
-----------------------------
444
445
The :class:`~sqlalchemy.schema.MetaData` object can also get a listing of
446
tables and reflect the full set. This is achieved by using the
447
:func:`~sqlalchemy.schema.MetaData.reflect` method. After calling it, all
448
located tables are present within the :class:`~sqlalchemy.schema.MetaData`
449
object's dictionary of tables::
450
451
meta = MetaData()
452
meta.reflect(bind=someengine)
453
users_table = meta.tables['users']
454
addresses_table = meta.tables['addresses']
455
456
``metadata.reflect()`` also provides a handy way to clear or delete all the rows in a database::
457
458
meta = MetaData()
459
meta.reflect(bind=someengine)
460
for table in reversed(meta.sorted_tables):
461
someengine.execute(table.delete())
462
463
Fine Grained Reflection with Inspector
464
--------------------------------------
465
466
A low level interface which provides a backend-agnostic system of loading
467
lists of schema, table, column, and constraint descriptions from a given
468
database is also available. This is known as the "Inspector"::
469
470
from sqlalchemy import create_engine
471
from sqlalchemy.engine import reflection
472
engine = create_engine('...')
473
insp = reflection.Inspector.from_engine(engine)
474
print insp.get_table_names()
475
476
.. autoclass:: sqlalchemy.engine.reflection.Inspector
477
:members:
478
:undoc-members:
479
:show-inheritance:
480
481
482
.. _metadata_defaults:
483
484
Column Insert/Update Defaults
485
==============================
486
487
SQLAlchemy provides a very rich featureset regarding column level events which
488
take place during INSERT and UPDATE statements. Options include:
489
490
* Scalar values used as defaults during INSERT and UPDATE operations
491
* Python functions which execute upon INSERT and UPDATE operations
492
* SQL expressions which are embedded in INSERT statements (or in some cases execute beforehand)
493
* SQL expressions which are embedded in UPDATE statements
494
* Server side default values used during INSERT
495
* Markers for server-side triggers used during UPDATE
496
497
The general rule for all insert/update defaults is that they only take effect
498
if no value for a particular column is passed as an ``execute()`` parameter;
499
otherwise, the given value is used.
500
501
Scalar Defaults
502
---------------
503
504
The simplest kind of default is a scalar value used as the default value of a column::
505
506
Table("mytable", meta,
507
Column("somecolumn", Integer, default=12)
508
)
509
510
Above, the value "12" will be bound as the column value during an INSERT if no
511
other value is supplied.
512
513
A scalar value may also be associated with an UPDATE statement, though this is
514
not very common (as UPDATE statements are usually looking for dynamic
515
defaults)::
516
517
Table("mytable", meta,
518
Column("somecolumn", Integer, onupdate=25)
519
)
520
521
522
Python-Executed Functions
523
-------------------------
524
525
The ``default`` and ``onupdate`` keyword arguments also accept Python
526
functions. These functions are invoked at the time of insert or update if no
527
other value for that column is supplied, and the value returned is used for
528
the column's value. Below illustrates a crude "sequence" that assigns an
529
incrementing counter to a primary key column::
530
531
# a function which counts upwards
532
i = 0
533
def mydefault():
534
global i
535
i += 1
536
return i
537
538
t = Table("mytable", meta,
539
Column('id', Integer, primary_key=True, default=mydefault),
540
)
541
542
It should be noted that for real "incrementing sequence" behavior, the
543
built-in capabilities of the database should normally be used, which may
544
include sequence objects or other autoincrementing capabilities. For primary
545
key columns, SQLAlchemy will in most cases use these capabilities
546
automatically. See the API documentation for
547
:class:`~sqlalchemy.schema.Column` including the ``autoincrement`` flag, as
548
well as the section on :class:`~sqlalchemy.schema.Sequence` later in this
549
chapter for background on standard primary key generation techniques.
550
551
To illustrate onupdate, we assign the Python ``datetime`` function ``now`` to
552
the ``onupdate`` attribute::
553
554
import datetime
555
556
t = Table("mytable", meta,
557
Column('id', Integer, primary_key=True),
558
559
# define 'last_updated' to be populated with datetime.now()
560
Column('last_updated', DateTime, onupdate=datetime.datetime.now),
561
)
562
563
When an update statement executes and no value is passed for ``last_updated``,
564
the ``datetime.datetime.now()`` Python function is executed and its return
565
value used as the value for ``last_updated``. Notice that we provide ``now``
566
as the function itself without calling it (i.e. there are no parenthesis
567
following) - SQLAlchemy will execute the function at the time the statement
568
executes.
569
570
Context-Sensitive Default Functions
571
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
572
573
The Python functions used by ``default`` and ``onupdate`` may also make use of
574
the current statement's context in order to determine a value. The `context`
575
of a statement is an internal SQLAlchemy object which contains all information
576
about the statement being executed, including its source expression, the
577
parameters associated with it and the cursor. The typical use case for this
578
context with regards to default generation is to have access to the other
579
values being inserted or updated on the row. To access the context, provide a
580
function that accepts a single ``context`` argument::
581
582
def mydefault(context):
583
return context.current_parameters['counter'] + 12
584
585
t = Table('mytable', meta,
586
Column('counter', Integer),
587
Column('counter_plus_twelve', Integer, default=mydefault, onupdate=mydefault)
588
)
589
590
Above we illustrate a default function which will execute for all INSERT and
591
UPDATE statements where a value for ``counter_plus_twelve`` was otherwise not
592
provided, and the value will be that of whatever value is present in the
593
execution for the ``counter`` column, plus the number 12.
594
595
While the context object passed to the default function has many attributes,
596
the ``current_parameters`` member is a special member provided only during the
597
execution of a default function for the purposes of deriving defaults from its
598
existing values. For a single statement that is executing many sets of bind
599
parameters, the user-defined function is called for each set of parameters,
600
and ``current_parameters`` will be provided with each individual parameter set
601
for each execution.
602
603
SQL Expressions
604
---------------
605
606
The "default" and "onupdate" keywords may also be passed SQL expressions,
607
including select statements or direct function calls::
608
609
t = Table("mytable", meta,
610
Column('id', Integer, primary_key=True),
611
612
# define 'create_date' to default to now()
613
Column('create_date', DateTime, default=func.now()),
614
615
# define 'key' to pull its default from the 'keyvalues' table
616
Column('key', String(20), default=keyvalues.select(keyvalues.c.type='type1', limit=1)),
617
618
# define 'last_modified' to use the current_timestamp SQL function on update
619
Column('last_modified', DateTime, onupdate=func.utc_timestamp())
620
)
621
622
Above, the ``create_date`` column will be populated with the result of the
623
``now()`` SQL function (which, depending on backend, compiles into ``NOW()``
624
or ``CURRENT_TIMESTAMP`` in most cases) during an INSERT statement, and the
625
``key`` column with the result of a SELECT subquery from another table. The
626
``last_modified`` column will be populated with the value of
627
``UTC_TIMESTAMP()``, a function specific to MySQL, when an UPDATE statement is
628
emitted for this table.
629
630
Note that when using ``func`` functions, unlike when using Python `datetime`
631
functions we *do* call the function, i.e. with parenthesis "()" - this is
632
because what we want in this case is the return value of the function, which
633
is the SQL expression construct that will be rendered into the INSERT or
634
UPDATE statement.
635
636
The above SQL functions are usually executed "inline" with the INSERT or
637
UPDATE statement being executed, meaning, a single statement is executed which
638
embeds the given expressions or subqueries within the VALUES or SET clause of
639
the statement. Although in some cases, the function is "pre-executed" in a
640
SELECT statement of its own beforehand. This happens when all of the following
641
is true:
642
643
* the column is a primary key column
644
* the database dialect does not support a usable ``cursor.lastrowid`` accessor
645
(or equivalent); this currently includes PostgreSQL, Oracle, and Firebird, as
646
well as some MySQL dialects.
647
* the dialect does not support the "RETURNING" clause or similar, or the
648
``implicit_returning`` flag is set to ``False`` for the dialect. Dialects
649
which support RETURNING currently include Postgresql, Oracle, Firebird, and
650
MS-SQL.
651
* the statement is a single execution, i.e. only supplies one set of
652
parameters and doesn't use "executemany" behavior
653
* the ``inline=True`` flag is not set on the
654
:class:`~sqlalchemy.sql.expression.Insert()` or
655
:class:`~sqlalchemy.sql.expression.Update()` construct, and the statement has
656
not defined an explicit `returning()` clause.
657
658
Whether or not the default generation clause "pre-executes" is not something
659
that normally needs to be considered, unless it is being addressed for
660
performance reasons.
661
662
When the statement is executed with a single set of parameters (that is, it is
663
not an "executemany" style execution), the returned
664
:class:`~sqlalchemy.engine.ResultProxy` will contain a collection
665
accessible via ``result.postfetch_cols()`` which contains a list of all
666
:class:`~sqlalchemy.schema.Column` objects which had an inline-executed
667
default. Similarly, all parameters which were bound to the statement,
668
including all Python and SQL expressions which were pre-executed, are present
669
in the ``last_inserted_params()`` or ``last_updated_params()`` collections on
670
:class:`~sqlalchemy.engine.ResultProxy`. The ``inserted_primary_key``
671
collection contains a list of primary key values for the row inserted (a list
672
so that single-column and composite-column primary keys are represented in the
673
same format).
674
675
Server Side Defaults
676
--------------------
677
678
A variant on the SQL expression default is the ``server_default``, which gets
679
placed in the CREATE TABLE statement during a ``create()`` operation:
680
681
.. sourcecode:: python+sql
682
683
t = Table('test', meta,
684
Column('abc', String(20), server_default='abc'),
685
Column('created_at', DateTime, server_default=text("sysdate"))
686
)
687
688
A create call for the above table will produce::
689
690
CREATE TABLE test (
691
abc varchar(20) default 'abc',
692
created_at datetime default sysdate
693
)
694
695
The behavior of ``server_default`` is similar to that of a regular SQL
696
default; if it's placed on a primary key column for a database which doesn't
697
have a way to "postfetch" the ID, and the statement is not "inlined", the SQL
698
expression is pre-executed; otherwise, SQLAlchemy lets the default fire off on
699
the database side normally.
700
701
.. _triggered_columns:
702
703
Triggered Columns
704
------------------
705
706
Columns with values set by a database trigger or other external process may be
707
called out using :class:`.FetchedValue` as a marker::
708
709
t = Table('test', meta,
710
Column('abc', String(20), server_default=FetchedValue()),
711
Column('def', String(20), server_onupdate=FetchedValue())
712
)
713
714
.. versionchanged:: 0.8.0b2,0.7.10
715
The ``for_update`` argument on :class:`.FetchedValue` is set automatically
716
when specified as the ``server_onupdate`` argument. If using an older version,
717
specify the onupdate above as ``server_onupdate=FetchedValue(for_update=True)``.
718
719
These markers do not emit a "default" clause when the table is created,
720
however they do set the same internal flags as a static ``server_default``
721
clause, providing hints to higher-level tools that a "post-fetch" of these
722
rows should be performed after an insert or update.
723
724
.. note::
725
726
It's generally not appropriate to use :class:`.FetchedValue` in
727
conjunction with a primary key column, particularly when using the
728
ORM or any other scenario where the :attr:`.ResultProxy.inserted_primary_key`
729
attribute is required. This is becaue the "post-fetch" operation requires
730
that the primary key value already be available, so that the
731
row can be selected on its primary key.
732
733
For a server-generated primary key value, all databases provide special
734
accessors or other techniques in order to acquire the "last inserted
735
primary key" column of a table. These mechanisms aren't affected by the presence
736
of :class:`.FetchedValue`. For special situations where triggers are
737
used to generate primary key values, and the database in use does not
738
support the ``RETURNING`` clause, it may be necessary to forego the usage
739
of the trigger and instead apply the SQL expression or function as a
740
"pre execute" expression::
741
742
t = Table('test', meta,
743
Column('abc', MyType, default=func.generate_new_value(), primary_key=True)
744
)
745
746
Where above, when :meth:`.Table.insert` is used,
747
the ``func.generate_new_value()`` expression will be pre-executed
748
in the context of a scalar ``SELECT`` statement, and the new value will
749
be applied to the subsequent ``INSERT``, while at the same time being
750
made available to the :attr:`.ResultProxy.inserted_primary_key`
751
attribute.
752
753
754
Defining Sequences
755
-------------------
756
757
SQLAlchemy represents database sequences using the
758
:class:`~sqlalchemy.schema.Sequence` object, which is considered to be a
759
special case of "column default". It only has an effect on databases which
760
have explicit support for sequences, which currently includes Postgresql,
761
Oracle, and Firebird. The :class:`~sqlalchemy.schema.Sequence` object is
762
otherwise ignored.
763
764
The :class:`~sqlalchemy.schema.Sequence` may be placed on any column as a
765
"default" generator to be used during INSERT operations, and can also be
766
configured to fire off during UPDATE operations if desired. It is most
767
commonly used in conjunction with a single integer primary key column::
768
769
table = Table("cartitems", meta,
770
Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True),
771
Column("description", String(40)),
772
Column("createdate", DateTime())
773
)
774
775
Where above, the table "cartitems" is associated with a sequence named
776
"cart_id_seq". When INSERT statements take place for "cartitems", and no value
777
is passed for the "cart_id" column, the "cart_id_seq" sequence will be used to
778
generate a value.
779
780
When the :class:`~sqlalchemy.schema.Sequence` is associated with a table,
781
CREATE and DROP statements issued for that table will also issue CREATE/DROP
782
for the sequence object as well, thus "bundling" the sequence object with its
783
parent table.
784
785
The :class:`~sqlalchemy.schema.Sequence` object also implements special
786
functionality to accommodate Postgresql's SERIAL datatype. The SERIAL type in
787
PG automatically generates a sequence that is used implicitly during inserts.
788
This means that if a :class:`~sqlalchemy.schema.Table` object defines a
789
:class:`~sqlalchemy.schema.Sequence` on its primary key column so that it
790
works with Oracle and Firebird, the :class:`~sqlalchemy.schema.Sequence` would
791
get in the way of the "implicit" sequence that PG would normally use. For this
792
use case, add the flag ``optional=True`` to the
793
:class:`~sqlalchemy.schema.Sequence` object - this indicates that the
794
:class:`~sqlalchemy.schema.Sequence` should only be used if the database
795
provides no other option for generating primary key identifiers.
796
797
The :class:`~sqlalchemy.schema.Sequence` object also has the ability to be
798
executed standalone like a SQL expression, which has the effect of calling its
799
"next value" function::
800
801
seq = Sequence('some_sequence')
802
nextid = connection.execute(seq)
803
804
Default Objects API
805
-------------------
806
807
.. autoclass:: ColumnDefault
808
:show-inheritance:
809
810
.. autoclass:: DefaultClause
811
:show-inheritance:
812
813
.. autoclass:: DefaultGenerator
814
:show-inheritance:
815
816
.. autoclass:: FetchedValue
817
:show-inheritance:
818
819
.. autoclass:: PassiveDefault
820
:show-inheritance:
821
822
.. autoclass:: Sequence
823
:show-inheritance:
824
:members:
825
826
Defining Constraints and Indexes
827
=================================
828
829
.. _metadata_foreignkeys:
830
.. _metadata_constraints:
831
832
Defining Foreign Keys
833
---------------------
834
835
A *foreign key* in SQL is a table-level construct that constrains one or more
836
columns in that table to only allow values that are present in a different set
837
of columns, typically but not always located on a different table. We call the
838
columns which are constrained the *foreign key* columns and the columns which
839
they are constrained towards the *referenced* columns. The referenced columns
840
almost always define the primary key for their owning table, though there are
841
exceptions to this. The foreign key is the "joint" that connects together
842
pairs of rows which have a relationship with each other, and SQLAlchemy
843
assigns very deep importance to this concept in virtually every area of its
844
operation.
845
846
In SQLAlchemy as well as in DDL, foreign key constraints can be defined as
847
additional attributes within the table clause, or for single-column foreign
848
keys they may optionally be specified within the definition of a single
849
column. The single column foreign key is more common, and at the column level
850
is specified by constructing a :class:`~sqlalchemy.schema.ForeignKey` object
851
as an argument to a :class:`~sqlalchemy.schema.Column` object::
852
853
user_preference = Table('user_preference', metadata,
854
Column('pref_id', Integer, primary_key=True),
855
Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
856
Column('pref_name', String(40), nullable=False),
857
Column('pref_value', String(100))
858
)
859
860
Above, we define a new table ``user_preference`` for which each row must
861
contain a value in the ``user_id`` column that also exists in the ``user``
862
table's ``user_id`` column.
863
864
The argument to :class:`~sqlalchemy.schema.ForeignKey` is most commonly a
865
string of the form *<tablename>.<columnname>*, or for a table in a remote
866
schema or "owner" of the form *<schemaname>.<tablename>.<columnname>*. It may
867
also be an actual :class:`~sqlalchemy.schema.Column` object, which as we'll
868
see later is accessed from an existing :class:`~sqlalchemy.schema.Table`
869
object via its ``c`` collection::
870
871
ForeignKey(user.c.user_id)
872
873
The advantage to using a string is that the in-python linkage between ``user``
874
and ``user_preference`` is resolved only when first needed, so that table
875
objects can be easily spread across multiple modules and defined in any order.
876
877
Foreign keys may also be defined at the table level, using the
878
:class:`~sqlalchemy.schema.ForeignKeyConstraint` object. This object can
879
describe a single- or multi-column foreign key. A multi-column foreign key is
880
known as a *composite* foreign key, and almost always references a table that
881
has a composite primary key. Below we define a table ``invoice`` which has a
882
composite primary key::
883
884
invoice = Table('invoice', metadata,
885
Column('invoice_id', Integer, primary_key=True),
886
Column('ref_num', Integer, primary_key=True),
887
Column('description', String(60), nullable=False)
888
)
889
890
And then a table ``invoice_item`` with a composite foreign key referencing
891
``invoice``::
892
893
invoice_item = Table('invoice_item', metadata,
894
Column('item_id', Integer, primary_key=True),
895
Column('item_name', String(60), nullable=False),
896
Column('invoice_id', Integer, nullable=False),
897
Column('ref_num', Integer, nullable=False),
898
ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id', 'invoice.ref_num'])
899
)
900
901
It's important to note that the
902
:class:`~sqlalchemy.schema.ForeignKeyConstraint` is the only way to define a
903
composite foreign key. While we could also have placed individual
904
:class:`~sqlalchemy.schema.ForeignKey` objects on both the
905
``invoice_item.invoice_id`` and ``invoice_item.ref_num`` columns, SQLAlchemy
906
would not be aware that these two values should be paired together - it would
907
be two individual foreign key constraints instead of a single composite
908
foreign key referencing two columns.
909
910
.. _use_alter:
911
912
Creating/Dropping Foreign Key Constraints via ALTER
913
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
914
915
In all the above examples, the :class:`~sqlalchemy.schema.ForeignKey` object
916
causes the "REFERENCES" keyword to be added inline to a column definition
917
within a "CREATE TABLE" statement when
918
:func:`~sqlalchemy.schema.MetaData.create_all` is issued, and
919
:class:`~sqlalchemy.schema.ForeignKeyConstraint` invokes the "CONSTRAINT"
920
keyword inline with "CREATE TABLE". There are some cases where this is
921
undesireable, particularly when two tables reference each other mutually, each
922
with a foreign key referencing the other. In such a situation at least one of
923
the foreign key constraints must be generated after both tables have been
924
built. To support such a scheme, :class:`~sqlalchemy.schema.ForeignKey` and
925
:class:`~sqlalchemy.schema.ForeignKeyConstraint` offer the flag
926
``use_alter=True``. When using this flag, the constraint will be generated
927
using a definition similar to "ALTER TABLE <tablename> ADD CONSTRAINT <name>
928
...". Since a name is required, the ``name`` attribute must also be specified.
929
For example::
930
931
node = Table('node', meta,
932
Column('node_id', Integer, primary_key=True),
933
Column('primary_element', Integer,
934
ForeignKey('element.element_id', use_alter=True, name='fk_node_element_id')
935
)
936
)
937
938
element = Table('element', meta,
939
Column('element_id', Integer, primary_key=True),
940
Column('parent_node_id', Integer),
941
ForeignKeyConstraint(
942
['parent_node_id'],
943
['node.node_id'],
944
use_alter=True,
945
name='fk_element_parent_node_id'
946
)
947
)
948
949
ON UPDATE and ON DELETE
950
~~~~~~~~~~~~~~~~~~~~~~~
951
952
Most databases support *cascading* of foreign key values, that is the when a
953
parent row is updated the new value is placed in child rows, or when the
954
parent row is deleted all corresponding child rows are set to null or deleted.
955
In data definition language these are specified using phrases like "ON UPDATE
956
CASCADE", "ON DELETE CASCADE", and "ON DELETE SET NULL", corresponding to
957
foreign key constraints. The phrase after "ON UPDATE" or "ON DELETE" may also
958
other allow other phrases that are specific to the database in use. The
959
:class:`~sqlalchemy.schema.ForeignKey` and
960
:class:`~sqlalchemy.schema.ForeignKeyConstraint` objects support the
961
generation of this clause via the ``onupdate`` and ``ondelete`` keyword
962
arguments. The value is any string which will be output after the appropriate
963
"ON UPDATE" or "ON DELETE" phrase::
964
965
child = Table('child', meta,
966
Column('id', Integer,
967
ForeignKey('parent.id', onupdate="CASCADE", ondelete="CASCADE"),
968
primary_key=True
969
)
970
)
971
972
composite = Table('composite', meta,
973
Column('id', Integer, primary_key=True),
974
Column('rev_id', Integer),
975
Column('note_id', Integer),
976
ForeignKeyConstraint(
977
['rev_id', 'note_id'],
978
['revisions.id', 'revisions.note_id'],
979
onupdate="CASCADE", ondelete="SET NULL"
980
)
981
)
982
983
Note that these clauses are not supported on SQLite, and require ``InnoDB``
984
tables when used with MySQL. They may also not be supported on other
985
databases.
986
987
988
UNIQUE Constraint
989
-----------------
990
991
Unique constraints can be created anonymously on a single column using the
992
``unique`` keyword on :class:`~sqlalchemy.schema.Column`. Explicitly named
993
unique constraints and/or those with multiple columns are created via the
994
:class:`~sqlalchemy.schema.UniqueConstraint` table-level construct.
995
996
.. sourcecode:: python+sql
997
998
meta = MetaData()
999
mytable = Table('mytable', meta,
1000
1001
# per-column anonymous unique constraint
1002
Column('col1', Integer, unique=True),
1003
1004
Column('col2', Integer),
1005
Column('col3', Integer),
1006
1007
# explicit/composite unique constraint. 'name' is optional.
1008
UniqueConstraint('col2', 'col3', name='uix_1')
1009
)
1010
1011
CHECK Constraint
1012
----------------
1013
1014
Check constraints can be named or unnamed and can be created at the Column or
1015
Table level, using the :class:`~sqlalchemy.schema.CheckConstraint` construct.
1016
The text of the check constraint is passed directly through to the database,
1017
so there is limited "database independent" behavior. Column level check
1018
constraints generally should only refer to the column to which they are
1019
placed, while table level constraints can refer to any columns in the table.
1020
1021
Note that some databases do not actively support check constraints such as
1022
MySQL.
1023
1024
.. sourcecode:: python+sql
1025
1026
meta = MetaData()
1027
mytable = Table('mytable', meta,
1028
1029
# per-column CHECK constraint
1030
Column('col1', Integer, CheckConstraint('col1>5')),
1031
1032
Column('col2', Integer),
1033
Column('col3', Integer),
1034
1035
# table level CHECK constraint. 'name' is optional.
1036
CheckConstraint('col2 > col3 + 5', name='check1')
1037
)
1038
1039
{sql}mytable.create(engine)
1040
CREATE TABLE mytable (
1041
col1 INTEGER CHECK (col1>5),
1042
col2 INTEGER,
1043
col3 INTEGER,
1044
CONSTRAINT check1 CHECK (col2 > col3 + 5)
1045
){stop}
1046
1047
Setting up Constraints when using the Declarative ORM Extension
1048
----------------------------------------------------------------
1049
1050
The :class:`.Table` is the SQLAlchemy Core construct that allows one to define
1051
table metadata, which among other things can be used by the SQLAlchemy ORM
1052
as a target to map a class. The :ref:`Declarative <declarative_toplevel>`
1053
extension allows the :class:`.Table` object to be created automatically, given
1054
the contents of the table primarily as a mapping of :class:`.Column` objects.
1055
1056
To apply table-level constraint objects such as :class:`.ForeignKeyConstraint`
1057
to a table defined using Declarative, use the ``__table_args__`` attribute,
1058
described at :ref:`declarative_table_args`.
1059
1060
Constraints API
1061
---------------
1062
.. autoclass:: Constraint
1063
:show-inheritance:
1064
1065
.. autoclass:: CheckConstraint
1066
:show-inheritance:
1067
1068
.. autoclass:: ColumnCollectionConstraint
1069
:show-inheritance:
1070
1071
.. autoclass:: ForeignKey
1072
:members:
1073
:show-inheritance:
1074
1075
.. autoclass:: ForeignKeyConstraint
1076
:members:
1077
:show-inheritance:
1078
1079
.. autoclass:: PrimaryKeyConstraint
1080
:show-inheritance:
1081
1082
.. autoclass:: UniqueConstraint
1083
:show-inheritance:
1084
1085
.. _schema_indexes:
1086
1087
Indexes
1088
-------
1089
1090
Indexes can be created anonymously (using an auto-generated name ``ix_<column
1091
label>``) for a single column using the inline ``index`` keyword on
1092
:class:`~sqlalchemy.schema.Column`, which also modifies the usage of
1093
``unique`` to apply the uniqueness to the index itself, instead of adding a
1094
separate UNIQUE constraint. For indexes with specific names or which encompass
1095
more than one column, use the :class:`~sqlalchemy.schema.Index` construct,
1096
which requires a name.
1097
1098
Below we illustrate a :class:`~sqlalchemy.schema.Table` with several
1099
:class:`~sqlalchemy.schema.Index` objects associated. The DDL for "CREATE
1100
INDEX" is issued right after the create statements for the table:
1101
1102
.. sourcecode:: python+sql
1103
1104
meta = MetaData()
1105
mytable = Table('mytable', meta,
1106
# an indexed column, with index "ix_mytable_col1"
1107
Column('col1', Integer, index=True),
1108
1109
# a uniquely indexed column with index "ix_mytable_col2"
1110
Column('col2', Integer, index=True, unique=True),
1111
1112
Column('col3', Integer),
1113
Column('col4', Integer),
1114
1115
Column('col5', Integer),
1116
Column('col6', Integer),
1117
)
1118
1119
# place an index on col3, col4
1120
Index('idx_col34', mytable.c.col3, mytable.c.col4)
1121
1122
# place a unique index on col5, col6
1123
Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)
1124
1125
{sql}mytable.create(engine)
1126
CREATE TABLE mytable (
1127
col1 INTEGER,
1128
col2 INTEGER,
1129
col3 INTEGER,
1130
col4 INTEGER,
1131
col5 INTEGER,
1132
col6 INTEGER
1133
)
1134
CREATE INDEX ix_mytable_col1 ON mytable (col1)
1135
CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
1136
CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
1137
CREATE INDEX idx_col34 ON mytable (col3, col4){stop}
1138
1139
Note in the example above, the :class:`.Index` construct is created
1140
externally to the table which it corresponds, using :class:`.Column`
1141
objects directly. :class:`.Index` also supports
1142
"inline" definition inside the :class:`.Table`, using string names to
1143
identify columns::
1144
1145
meta = MetaData()
1146
mytable = Table('mytable', meta,
1147
Column('col1', Integer),
1148
1149
Column('col2', Integer),
1150
1151
Column('col3', Integer),
1152
Column('col4', Integer),
1153
1154
# place an index on col1, col2
1155
Index('idx_col12', 'col1', 'col2'),
1156
1157
# place a unique index on col3, col4
1158
Index('idx_col34', 'col3', 'col4', unique=True)
1159
)
1160
1161
.. versionadded:: 0.7
1162
Support of "inline" definition inside the :class:`.Table`
1163
for :class:`.Index`\ .
1164
1165
The :class:`~sqlalchemy.schema.Index` object also supports its own ``create()`` method:
1166
1167
.. sourcecode:: python+sql
1168
1169
i = Index('someindex', mytable.c.col5)
1170
{sql}i.create(engine)
1171
CREATE INDEX someindex ON mytable (col5){stop}
1172
1173
.. _schema_indexes_functional:
1174
1175
Functional Indexes
1176
~~~~~~~~~~~~~~~~~~~
1177
1178
:class:`.Index` supports SQL and function expressions, as supported by the
1179
target backend. To create an index against a column using a descending
1180
value, the :meth:`.ColumnElement.desc` modifier may be used::
1181
1182
from sqlalchemy import Index
1183
1184
Index('someindex', mytable.c.somecol.desc())
1185
1186
Or with a backend that supports functional indexes such as Postgresql,
1187
a "case insensitive" index can be created using the ``lower()`` function::
1188
1189
from sqlalchemy import func, Index
1190
1191
Index('someindex', func.lower(mytable.c.somecol))
1192
1193
.. versionadded:: 0.8 :class:`.Index` supports SQL expressions and functions
1194
as well as plain columns.
1195
1196
Index API
1197
---------
1198
1199
.. autoclass:: Index
1200
:show-inheritance:
1201
:members:
1202
1203
.. _metadata_ddl:
1204
1205
Customizing DDL
1206
===============
1207
1208
In the preceding sections we've discussed a variety of schema constructs
1209
including :class:`~sqlalchemy.schema.Table`,
1210
:class:`~sqlalchemy.schema.ForeignKeyConstraint`,
1211
:class:`~sqlalchemy.schema.CheckConstraint`, and
1212
:class:`~sqlalchemy.schema.Sequence`. Throughout, we've relied upon the
1213
``create()`` and :func:`~sqlalchemy.schema.MetaData.create_all` methods of
1214
:class:`~sqlalchemy.schema.Table` and :class:`~sqlalchemy.schema.MetaData` in
1215
order to issue data definition language (DDL) for all constructs. When issued,
1216
a pre-determined order of operations is invoked, and DDL to create each table
1217
is created unconditionally including all constraints and other objects
1218
associated with it. For more complex scenarios where database-specific DDL is
1219
required, SQLAlchemy offers two techniques which can be used to add any DDL
1220
based on any condition, either accompanying the standard generation of tables
1221
or by itself.
1222
1223
.. _schema_ddl_sequences:
1224
1225
Controlling DDL Sequences
1226
-------------------------
1227
1228
The ``sqlalchemy.schema`` package contains SQL expression constructs that
1229
provide DDL expressions. For example, to produce a ``CREATE TABLE`` statement:
1230
1231
.. sourcecode:: python+sql
1232
1233
from sqlalchemy.schema import CreateTable
1234
{sql}engine.execute(CreateTable(mytable))
1235
CREATE TABLE mytable (
1236
col1 INTEGER,
1237
col2 INTEGER,
1238
col3 INTEGER,
1239
col4 INTEGER,
1240
col5 INTEGER,
1241
col6 INTEGER
1242
){stop}
1243
1244
Above, the :class:`~sqlalchemy.schema.CreateTable` construct works like any
1245
other expression construct (such as ``select()``, ``table.insert()``, etc.). A
1246
full reference of available constructs is in :ref:`schema_api_ddl`.
1247
1248
The DDL constructs all extend a common base class which provides the
1249
capability to be associated with an individual
1250
:class:`~sqlalchemy.schema.Table` or :class:`~sqlalchemy.schema.MetaData`
1251
object, to be invoked upon create/drop events. Consider the example of a table
1252
which contains a CHECK constraint:
1253
1254
.. sourcecode:: python+sql
1255
1256
users = Table('users', metadata,
1257
Column('user_id', Integer, primary_key=True),
1258
Column('user_name', String(40), nullable=False),
1259
CheckConstraint('length(user_name) >= 8',name="cst_user_name_length")
1260
)
1261
1262
{sql}users.create(engine)
1263
CREATE TABLE users (
1264
user_id SERIAL NOT NULL,
1265
user_name VARCHAR(40) NOT NULL,
1266
PRIMARY KEY (user_id),
1267
CONSTRAINT cst_user_name_length CHECK (length(user_name) >= 8)
1268
){stop}
1269
1270
The above table contains a column "user_name" which is subject to a CHECK
1271
constraint that validates that the length of the string is at least eight
1272
characters. When a ``create()`` is issued for this table, DDL for the
1273
:class:`~sqlalchemy.schema.CheckConstraint` will also be issued inline within
1274
the table definition.
1275
1276
The :class:`~sqlalchemy.schema.CheckConstraint` construct can also be
1277
constructed externally and associated with the
1278
:class:`~sqlalchemy.schema.Table` afterwards::
1279
1280
constraint = CheckConstraint('length(user_name) >= 8',name="cst_user_name_length")
1281
users.append_constraint(constraint)
1282
1283
So far, the effect is the same. However, if we create DDL elements
1284
corresponding to the creation and removal of this constraint, and associate
1285
them with the :class:`.Table` as events, these new events
1286
will take over the job of issuing DDL for the constraint. Additionally, the
1287
constraint will be added via ALTER:
1288
1289
.. sourcecode:: python+sql
1290
1291
from sqlalchemy import event
1292
1293
event.listen(
1294
users,
1295
"after_create",
1296
AddConstraint(constraint)
1297
)
1298
event.listen(
1299
users,
1300
"before_drop",
1301
DropConstraint(constraint)
1302
)
1303
1304
{sql}users.create(engine)
1305
CREATE TABLE users (
1306
user_id SERIAL NOT NULL,
1307
user_name VARCHAR(40) NOT NULL,
1308
PRIMARY KEY (user_id)
1309
)
1310
1311
ALTER TABLE users ADD CONSTRAINT cst_user_name_length CHECK (length(user_name) >= 8){stop}
1312
1313
{sql}users.drop(engine)
1314
ALTER TABLE users DROP CONSTRAINT cst_user_name_length
1315
DROP TABLE users{stop}
1316
1317
The real usefulness of the above becomes clearer once we illustrate the
1318
:meth:`.DDLElement.execute_if` method. This method returns a modified form of
1319
the DDL callable which will filter on criteria before responding to a
1320
received event. It accepts a parameter ``dialect``, which is the string
1321
name of a dialect or a tuple of such, which will limit the execution of the
1322
item to just those dialects. It also accepts a ``callable_`` parameter which
1323
may reference a Python callable which will be invoked upon event reception,
1324
returning ``True`` or ``False`` indicating if the event should proceed.
1325
1326
If our :class:`~sqlalchemy.schema.CheckConstraint` was only supported by
1327
Postgresql and not other databases, we could limit its usage to just that dialect::
1328
1329
event.listen(
1330
users,
1331
'after_create',
1332
AddConstraint(constraint).execute_if(dialect='postgresql')
1333
)
1334
event.listen(
1335
users,
1336
'before_drop',
1337
DropConstraint(constraint).execute_if(dialect='postgresql')
1338
)
1339
1340
Or to any set of dialects::
1341
1342
event.listen(
1343
users,
1344
"after_create",
1345
AddConstraint(constraint).execute_if(dialect=('postgresql', 'mysql'))
1346
)
1347
event.listen(
1348
users,
1349
"before_drop",
1350
DropConstraint(constraint).execute_if(dialect=('postgresql', 'mysql'))
1351
)
1352
1353
When using a callable, the callable is passed the ddl element, the
1354
:class:`.Table` or :class:`.MetaData`
1355
object whose "create" or "drop" event is in progress, and the
1356
:class:`.Connection` object being used for the
1357
operation, as well as additional information as keyword arguments. The
1358
callable can perform checks, such as whether or not a given item already
1359
exists. Below we define ``should_create()`` and ``should_drop()`` callables
1360
that check for the presence of our named constraint:
1361
1362
.. sourcecode:: python+sql
1363
1364
def should_create(ddl, target, connection, **kw):
1365
row = connection.execute("select conname from pg_constraint where conname='%s'" % ddl.element.name).scalar()
1366
return not bool(row)
1367
1368
def should_drop(ddl, target, connection, **kw):
1369
return not should_create(ddl, target, connection, **kw)
1370
1371
event.listen(
1372
users,
1373
"after_create",
1374
AddConstraint(constraint).execute_if(callable_=should_create)
1375
)
1376
event.listen(
1377
users,
1378
"before_drop",
1379
DropConstraint(constraint).execute_if(callable_=should_drop)
1380
)
1381
1382
{sql}users.create(engine)
1383
CREATE TABLE users (
1384
user_id SERIAL NOT NULL,
1385
user_name VARCHAR(40) NOT NULL,
1386
PRIMARY KEY (user_id)
1387
)
1388
1389
select conname from pg_constraint where conname='cst_user_name_length'
1390
ALTER TABLE users ADD CONSTRAINT cst_user_name_length CHECK (length(user_name) >= 8){stop}
1391
1392
{sql}users.drop(engine)
1393
select conname from pg_constraint where conname='cst_user_name_length'
1394
ALTER TABLE users DROP CONSTRAINT cst_user_name_length
1395
DROP TABLE users{stop}
1396
1397
Custom DDL
1398
----------
1399
1400
Custom DDL phrases are most easily achieved using the
1401
:class:`~sqlalchemy.schema.DDL` construct. This construct works like all the
1402
other DDL elements except it accepts a string which is the text to be emitted:
1403
1404
.. sourcecode:: python+sql
1405
1406
event.listen(
1407
metadata,
1408
"after_create",
1409
DDL("ALTER TABLE users ADD CONSTRAINT "
1410
"cst_user_name_length "
1411
" CHECK (length(user_name) >= 8)")
1412
)
1413
1414
A more comprehensive method of creating libraries of DDL constructs is to use
1415
custom compilation - see :ref:`sqlalchemy.ext.compiler_toplevel` for
1416
details.
1417
1418
.. _schema_api_ddl:
1419
1420
DDL Expression Constructs API
1421
-----------------------------
1422
1423
.. autoclass:: DDLElement
1424
:members:
1425
:undoc-members:
1426
:show-inheritance:
1427
1428
.. autoclass:: DDL
1429
:members:
1430
:undoc-members:
1431
:show-inheritance:
1432
1433
.. autoclass:: CreateTable
1434
:members:
1435
:undoc-members:
1436
:show-inheritance:
1437
1438
.. autoclass:: DropTable
1439
:members:
1440
:undoc-members:
1441
:show-inheritance:
1442
1443
.. autoclass:: CreateColumn
1444
:members:
1445
:undoc-members:
1446
:show-inheritance:
1447
1448
.. autoclass:: CreateSequence
1449
:members:
1450
:undoc-members:
1451
:show-inheritance:
1452
1453
.. autoclass:: DropSequence
1454
:members:
1455
:undoc-members:
1456
:show-inheritance:
1457
1458
.. autoclass:: CreateIndex
1459
:members:
1460
:undoc-members:
1461
:show-inheritance:
1462
1463
.. autoclass:: DropIndex
1464
:members:
1465
:undoc-members:
1466
:show-inheritance:
1467
1468
.. autoclass:: AddConstraint
1469
:members:
1470
:undoc-members:
1471
:show-inheritance:
1472
1473
.. autoclass:: DropConstraint
1474
:members:
1475
:undoc-members:
1476
:show-inheritance:
1477
1478
.. autoclass:: CreateSchema
1479
:members:
1480
:undoc-members:
1481
:show-inheritance:
1482
1483
.. autoclass:: DropSchema
1484
:members:
1485
:undoc-members:
1486
:show-inheritance:
35
.. toctree::
36
:maxdepth: 1
37
38
metadata
39
reflection
40
defaults
41
constraints
42
ddl
43
44
1487
45
Loggerhead is a web-based interface for Breezy
Version: 2.0.1