658
662
When the statement is executed with a single set of parameters (that is, it is
659
663
not an "executemany" style execution), the returned
660
:class:`~sqlalchemy.engine.base.ResultProxy` will contain a collection
664
:class:`~sqlalchemy.engine.ResultProxy` will contain a collection
661
665
accessible via ``result.postfetch_cols()`` which contains a list of all
662
666
:class:`~sqlalchemy.schema.Column` objects which had an inline-executed
663
667
default. Similarly, all parameters which were bound to the statement,
664
668
including all Python and SQL expressions which were pre-executed, are present
665
669
in the ``last_inserted_params()`` or ``last_updated_params()`` collections on
666
:class:`~sqlalchemy.engine.base.ResultProxy`. The ``inserted_primary_key``
670
:class:`~sqlalchemy.engine.ResultProxy`. The ``inserted_primary_key``
667
671
collection contains a list of primary key values for the row inserted (a list
668
672
so that single-column and composite-column primary keys are represented in the
694
698
expression is pre-executed; otherwise, SQLAlchemy lets the default fire off on
695
699
the database side normally.
701
.. _triggered_columns:
697
703
Triggered Columns
698
704
------------------
700
706
Columns with values set by a database trigger or other external process may be
701
called out with a marker::
707
called out using :class:`.FetchedValue` as a marker::
703
709
t = Table('test', meta,
704
710
Column('abc', String(20), server_default=FetchedValue()),
705
711
Column('def', String(20), server_onupdate=FetchedValue())
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)``.
708
719
These markers do not emit a "default" clause when the table is created,
709
720
however they do set the same internal flags as a static ``server_default``
710
721
clause, providing hints to higher-level tools that a "post-fetch" of these
711
722
rows should be performed after an insert or update.
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.
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::
742
t = Table('test', meta,
743
Column('abc', MyType, default=func.generate_new_value(), primary_key=True)
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`
713
754
Defining Sequences
714
755
-------------------
1129
1170
{sql}i.create(engine)
1130
1171
CREATE INDEX someindex ON mytable (col5){stop}
1173
.. _schema_indexes_functional:
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::
1182
from sqlalchemy import Index
1184
Index('someindex', mytable.c.somecol.desc())
1186
Or with a backend that supports functional indexes such as Postgresql,
1187
a "case insensitive" index can be created using the ``lower()`` function::
1189
from sqlalchemy import func, Index
1191
Index('someindex', func.lower(mytable.c.somecol))
1193
.. versionadded:: 0.8 :class:`.Index` supports SQL expressions and functions
1194
as well as plain columns.
1132
1199
.. autoclass:: Index
1133
1200
:show-inheritance:
1247
1314
ALTER TABLE users DROP CONSTRAINT cst_user_name_length
1248
1315
DROP TABLE users{stop}
1250
The real usefulness of the above becomes clearer once we illustrate the :meth:`.DDLEvent.execute_if`
1251
method. This method returns a modified form of the DDL callable which will
1252
filter on criteria before responding to a received event. It accepts a
1253
parameter ``dialect``, which is the string name of a dialect or a tuple of such,
1254
which will limit the execution of the item to just those dialects. It also
1255
accepts a ``callable_`` parameter which may reference a Python callable which will
1256
be invoked upon event reception, returning ``True`` or ``False`` indicating if
1257
the event should proceed.
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.
1259
1326
If our :class:`~sqlalchemy.schema.CheckConstraint` was only supported by
1260
1327
Postgresql and not other databases, we could limit its usage to just that dialect::
added
removed
doc/build/core/schema.rst
