1
# ext/declarative/__init__.py
2
# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
4
# This module is part of SQLAlchemy and is released under
5
# the MIT License: http://www.opensource.org/licenses/mit-license.php
11
SQLAlchemy object-relational configuration involves the
12
combination of :class:`.Table`, :func:`.mapper`, and class
13
objects to define a mapped class.
14
:mod:`~sqlalchemy.ext.declarative` allows all three to be
15
expressed at once within the class declaration. As much as
16
possible, regular SQLAlchemy schema and ORM constructs are
17
used directly, so that configuration between "classical" ORM
18
usage and declarative remain highly similar.
22
from sqlalchemy.ext.declarative import declarative_base
24
Base = declarative_base()
26
class SomeClass(Base):
27
__tablename__ = 'some_table'
28
id = Column(Integer, primary_key=True)
29
name = Column(String(50))
31
Above, the :func:`declarative_base` callable returns a new base class from
32
which all mapped classes should inherit. When the class definition is
33
completed, a new :class:`.Table` and :func:`.mapper` will have been generated.
35
The resulting table and mapper are accessible via
36
``__table__`` and ``__mapper__`` attributes on the
39
# access the mapped Table
48
In the previous example, the :class:`.Column` objects are
49
automatically named with the name of the attribute to which they are
52
To name columns explicitly with a name distinct from their mapped attribute,
53
just give the column a name. Below, column "some_table_id" is mapped to the
54
"id" attribute of `SomeClass`, but in SQL will be represented as
57
class SomeClass(Base):
58
__tablename__ = 'some_table'
59
id = Column("some_table_id", Integer, primary_key=True)
61
Attributes may be added to the class after its construction, and they will be
62
added to the underlying :class:`.Table` and
63
:func:`.mapper` definitions as appropriate::
65
SomeClass.data = Column('data', Unicode)
66
SomeClass.related = relationship(RelatedInfo)
68
Classes which are constructed using declarative can interact freely
69
with classes that are mapped explicitly with :func:`.mapper`.
71
It is recommended, though not required, that all tables
72
share the same underlying :class:`~sqlalchemy.schema.MetaData` object,
73
so that string-configured :class:`~sqlalchemy.schema.ForeignKey`
74
references can be resolved without issue.
76
Accessing the MetaData
77
=======================
79
The :func:`declarative_base` base class contains a
80
:class:`.MetaData` object where newly defined
81
:class:`.Table` objects are collected. This object is
82
intended to be accessed directly for
83
:class:`.MetaData`-specific operations. Such as, to issue
84
CREATE statements for all tables::
86
engine = create_engine('sqlite://')
87
Base.metadata.create_all(engine)
89
:func:`declarative_base` can also receive a pre-existing
90
:class:`.MetaData` object, which allows a
91
declarative setup to be associated with an already
92
existing traditional collection of :class:`~sqlalchemy.schema.Table`
95
mymetadata = MetaData()
96
Base = declarative_base(metadata=mymetadata)
98
Configuring Relationships
99
=========================
101
Relationships to other classes are done in the usual way, with the added
102
feature that the class specified to :func:`~sqlalchemy.orm.relationship`
103
may be a string name. The "class registry" associated with ``Base``
104
is used at mapper compilation time to resolve the name into the actual
105
class object, which is expected to have been defined once the mapper
106
configuration is used::
109
__tablename__ = 'users'
111
id = Column(Integer, primary_key=True)
112
name = Column(String(50))
113
addresses = relationship("Address", backref="user")
116
__tablename__ = 'addresses'
118
id = Column(Integer, primary_key=True)
119
email = Column(String(50))
120
user_id = Column(Integer, ForeignKey('users.id'))
122
Column constructs, since they are just that, are immediately usable,
123
as below where we define a primary join condition on the ``Address``
127
__tablename__ = 'addresses'
129
id = Column(Integer, primary_key=True)
130
email = Column(String(50))
131
user_id = Column(Integer, ForeignKey('users.id'))
132
user = relationship(User, primaryjoin=user_id == User.id)
134
In addition to the main argument for :func:`~sqlalchemy.orm.relationship`,
135
other arguments which depend upon the columns present on an as-yet
136
undefined class may also be specified as strings. These strings are
137
evaluated as Python expressions. The full namespace available within
138
this evaluation includes all classes mapped for this declarative base,
139
as well as the contents of the ``sqlalchemy`` package, including
140
expression functions like :func:`~sqlalchemy.sql.expression.desc` and
141
:attr:`~sqlalchemy.sql.expression.func`::
145
addresses = relationship("Address",
146
order_by="desc(Address.email)",
147
primaryjoin="Address.user_id==User.id")
149
For the case where more than one module contains a class of the same name,
150
string class names can also be specified as module-qualified paths
151
within any of these string expressions::
155
addresses = relationship("myapp.model.address.Address",
156
order_by="desc(myapp.model.address.Address.email)",
157
primaryjoin="myapp.model.address.Address.user_id=="
158
"myapp.model.user.User.id")
160
The qualified path can be any partial path that removes ambiguity between
161
the names. For example, to disambiguate between
162
``myapp.model.address.Address`` and ``myapp.model.lookup.Address``,
163
we can specify ``address.Address`` or ``lookup.Address``::
167
addresses = relationship("address.Address",
168
order_by="desc(address.Address.email)",
169
primaryjoin="address.Address.user_id=="
172
.. versionadded:: 0.8
173
module-qualified paths can be used when specifying string arguments
174
with Declarative, in order to specify specific modules.
176
Two alternatives also exist to using string-based attributes. A lambda
177
can also be used, which will be evaluated after all mappers have been
182
addresses = relationship(lambda: Address,
183
order_by=lambda: desc(Address.email),
184
primaryjoin=lambda: Address.user_id==User.id)
186
Or, the relationship can be added to the class explicitly after the classes
189
User.addresses = relationship(Address,
190
primaryjoin=Address.user_id==User.id)
195
Configuring Many-to-Many Relationships
196
======================================
198
Many-to-many relationships are also declared in the same way
199
with declarative as with traditional mappings. The
200
``secondary`` argument to
201
:func:`.relationship` is as usual passed a
202
:class:`.Table` object, which is typically declared in the
203
traditional way. The :class:`.Table` usually shares
204
the :class:`.MetaData` object used by the declarative base::
207
'keywords', Base.metadata,
208
Column('author_id', Integer, ForeignKey('authors.id')),
209
Column('keyword_id', Integer, ForeignKey('keywords.id'))
213
__tablename__ = 'authors'
214
id = Column(Integer, primary_key=True)
215
keywords = relationship("Keyword", secondary=keywords)
217
Like other :func:`~sqlalchemy.orm.relationship` arguments, a string is accepted
218
as well, passing the string name of the table as defined in the
219
``Base.metadata.tables`` collection::
222
__tablename__ = 'authors'
223
id = Column(Integer, primary_key=True)
224
keywords = relationship("Keyword", secondary="keywords")
226
As with traditional mapping, its generally not a good idea to use
227
a :class:`.Table` as the "secondary" argument which is also mapped to
228
a class, unless the :func:`.relationship` is declared with ``viewonly=True``.
229
Otherwise, the unit-of-work system may attempt duplicate INSERT and
230
DELETE statements against the underlying table.
232
.. _declarative_sql_expressions:
234
Defining SQL Expressions
235
========================
237
See :ref:`mapper_sql_expressions` for examples on declaratively
238
mapping attributes to SQL expressions.
240
.. _declarative_table_args:
245
Table arguments other than the name, metadata, and mapped Column
246
arguments are specified using the ``__table_args__`` class attribute.
247
This attribute accommodates both positional as well as keyword
248
arguments that are normally sent to the
249
:class:`~sqlalchemy.schema.Table` constructor.
250
The attribute can be specified in one of two forms. One is as a
254
__tablename__ = 'sometable'
255
__table_args__ = {'mysql_engine':'InnoDB'}
257
The other, a tuple, where each argument is positional
258
(usually constraints)::
261
__tablename__ = 'sometable'
263
ForeignKeyConstraint(['id'], ['remote_table.id']),
264
UniqueConstraint('foo'),
267
Keyword arguments can be specified with the above form by
268
specifying the last argument as a dictionary::
271
__tablename__ = 'sometable'
273
ForeignKeyConstraint(['id'], ['remote_table.id']),
274
UniqueConstraint('foo'),
278
Using a Hybrid Approach with __table__
279
=======================================
281
As an alternative to ``__tablename__``, a direct
282
:class:`~sqlalchemy.schema.Table` construct may be used. The
283
:class:`~sqlalchemy.schema.Column` objects, which in this case require
284
their names, will be added to the mapping just like a regular mapping
288
__table__ = Table('my_table', Base.metadata,
289
Column('id', Integer, primary_key=True),
290
Column('name', String(50))
293
``__table__`` provides a more focused point of control for establishing
294
table metadata, while still getting most of the benefits of using declarative.
295
An application that uses reflection might want to load table metadata elsewhere
296
and pass it to declarative classes::
298
from sqlalchemy.ext.declarative import declarative_base
300
Base = declarative_base()
301
Base.metadata.reflect(some_engine)
304
__table__ = metadata.tables['user']
307
__table__ = metadata.tables['address']
309
Some configuration schemes may find it more appropriate to use ``__table__``,
310
such as those which already take advantage of the data-driven nature of
311
:class:`.Table` to customize and/or automate schema definition.
313
Note that when the ``__table__`` approach is used, the object is immediately
314
usable as a plain :class:`.Table` within the class declaration body itself,
315
as a Python class is only another syntactical block. Below this is illustrated
316
by using the ``id`` column in the ``primaryjoin`` condition of a
317
:func:`.relationship`::
320
__table__ = Table('my_table', Base.metadata,
321
Column('id', Integer, primary_key=True),
322
Column('name', String(50))
325
widgets = relationship(Widget,
326
primaryjoin=Widget.myclass_id==__table__.c.id)
328
Similarly, mapped attributes which refer to ``__table__`` can be placed inline,
329
as below where we assign the ``name`` column to the attribute ``_name``,
330
generating a synonym for ``name``::
332
from sqlalchemy.ext.declarative import synonym_for
335
__table__ = Table('my_table', Base.metadata,
336
Column('id', Integer, primary_key=True),
337
Column('name', String(50))
340
_name = __table__.c.name
342
@synonym_for("_name")
344
return "Name: %s" % _name
346
Using Reflection with Declarative
347
=================================
349
It's easy to set up a :class:`.Table` that uses ``autoload=True``
350
in conjunction with a mapped class::
353
__table__ = Table('mytable', Base.metadata,
354
autoload=True, autoload_with=some_engine)
356
However, one improvement that can be made here is to not
357
require the :class:`.Engine` to be available when classes are
358
being first declared. To achieve this, use the
359
:class:`.DeferredReflection` mixin, which sets up mappings
360
only after a special ``prepare(engine)`` step is called::
362
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
364
Base = declarative_base(cls=DeferredReflection)
367
__tablename__ = 'foo'
368
bars = relationship("Bar")
371
__tablename__ = 'bar'
373
# illustrate overriding of "bar.foo_id" to have
374
# a foreign key constraint otherwise not
375
# reflected, such as when using MySQL
376
foo_id = Column(Integer, ForeignKey('foo.id'))
380
.. versionadded:: 0.8
381
Added :class:`.DeferredReflection`.
386
Declarative makes use of the :func:`~.orm.mapper` function internally
387
when it creates the mapping to the declared table. The options
388
for :func:`~.orm.mapper` are passed directly through via the
389
``__mapper_args__`` class attribute. As always, arguments which reference
390
locally mapped columns can reference them directly from within the
393
from datetime import datetime
396
__tablename__ = 'widgets'
398
id = Column(Integer, primary_key=True)
399
timestamp = Column(DateTime, nullable=False)
402
'version_id_col': timestamp,
403
'version_id_generator': lambda v:datetime.now()
406
.. _declarative_inheritance:
408
Inheritance Configuration
409
=========================
411
Declarative supports all three forms of inheritance as intuitively
412
as possible. The ``inherits`` mapper keyword argument is not needed
413
as declarative will determine this from the class itself. The various
414
"polymorphic" keyword arguments are specified using ``__mapper_args__``.
416
Joined Table Inheritance
417
~~~~~~~~~~~~~~~~~~~~~~~~
419
Joined table inheritance is defined as a subclass that defines its own
423
__tablename__ = 'people'
424
id = Column(Integer, primary_key=True)
425
discriminator = Column('type', String(50))
426
__mapper_args__ = {'polymorphic_on': discriminator}
428
class Engineer(Person):
429
__tablename__ = 'engineers'
430
__mapper_args__ = {'polymorphic_identity': 'engineer'}
431
id = Column(Integer, ForeignKey('people.id'), primary_key=True)
432
primary_language = Column(String(50))
434
Note that above, the ``Engineer.id`` attribute, since it shares the
435
same attribute name as the ``Person.id`` attribute, will in fact
436
represent the ``people.id`` and ``engineers.id`` columns together,
437
with the "Engineer.id" column taking precedence if queried directly.
438
To provide the ``Engineer`` class with an attribute that represents
439
only the ``engineers.id`` column, give it a different attribute name::
441
class Engineer(Person):
442
__tablename__ = 'engineers'
443
__mapper_args__ = {'polymorphic_identity': 'engineer'}
444
engineer_id = Column('id', Integer, ForeignKey('people.id'),
446
primary_language = Column(String(50))
449
.. versionchanged:: 0.7 joined table inheritance favors the subclass
450
column over that of the superclass, such as querying above
451
for ``Engineer.id``. Prior to 0.7 this was the reverse.
453
.. _declarative_single_table:
455
Single Table Inheritance
456
~~~~~~~~~~~~~~~~~~~~~~~~
458
Single table inheritance is defined as a subclass that does not have
459
its own table; you just leave out the ``__table__`` and ``__tablename__``
463
__tablename__ = 'people'
464
id = Column(Integer, primary_key=True)
465
discriminator = Column('type', String(50))
466
__mapper_args__ = {'polymorphic_on': discriminator}
468
class Engineer(Person):
469
__mapper_args__ = {'polymorphic_identity': 'engineer'}
470
primary_language = Column(String(50))
472
When the above mappers are configured, the ``Person`` class is mapped
473
to the ``people`` table *before* the ``primary_language`` column is
474
defined, and this column will not be included in its own mapping.
475
When ``Engineer`` then defines the ``primary_language`` column, the
476
column is added to the ``people`` table so that it is included in the
477
mapping for ``Engineer`` and is also part of the table's full set of
478
columns. Columns which are not mapped to ``Person`` are also excluded
479
from any other single or joined inheriting classes using the
480
``exclude_properties`` mapper argument. Below, ``Manager`` will have
481
all the attributes of ``Person`` and ``Manager`` but *not* the
482
``primary_language`` attribute of ``Engineer``::
484
class Manager(Person):
485
__mapper_args__ = {'polymorphic_identity': 'manager'}
486
golf_swing = Column(String(50))
488
The attribute exclusion logic is provided by the
489
``exclude_properties`` mapper argument, and declarative's default
490
behavior can be disabled by passing an explicit ``exclude_properties``
491
collection (empty or otherwise) to the ``__mapper_args__``.
493
Resolving Column Conflicts
494
^^^^^^^^^^^^^^^^^^^^^^^^^^
496
Note above that the ``primary_language`` and ``golf_swing`` columns
497
are "moved up" to be applied to ``Person.__table__``, as a result of their
498
declaration on a subclass that has no table of its own. A tricky case
499
comes up when two subclasses want to specify *the same* column, as below::
502
__tablename__ = 'people'
503
id = Column(Integer, primary_key=True)
504
discriminator = Column('type', String(50))
505
__mapper_args__ = {'polymorphic_on': discriminator}
507
class Engineer(Person):
508
__mapper_args__ = {'polymorphic_identity': 'engineer'}
509
start_date = Column(DateTime)
511
class Manager(Person):
512
__mapper_args__ = {'polymorphic_identity': 'manager'}
513
start_date = Column(DateTime)
515
Above, the ``start_date`` column declared on both ``Engineer`` and ``Manager``
516
will result in an error::
518
sqlalchemy.exc.ArgumentError: Column 'start_date' on class
519
<class '__main__.Manager'> conflicts with existing
520
column 'people.start_date'
522
In a situation like this, Declarative can't be sure
523
of the intent, especially if the ``start_date`` columns had, for example,
524
different types. A situation like this can be resolved by using
525
:class:`.declared_attr` to define the :class:`.Column` conditionally, taking
526
care to return the **existing column** via the parent ``__table__`` if it
529
from sqlalchemy.ext.declarative import declared_attr
532
__tablename__ = 'people'
533
id = Column(Integer, primary_key=True)
534
discriminator = Column('type', String(50))
535
__mapper_args__ = {'polymorphic_on': discriminator}
537
class Engineer(Person):
538
__mapper_args__ = {'polymorphic_identity': 'engineer'}
542
"Start date column, if not present already."
543
return Person.__table__.c.get('start_date', Column(DateTime))
545
class Manager(Person):
546
__mapper_args__ = {'polymorphic_identity': 'manager'}
550
"Start date column, if not present already."
551
return Person.__table__.c.get('start_date', Column(DateTime))
553
Above, when ``Manager`` is mapped, the ``start_date`` column is
554
already present on the ``Person`` class. Declarative lets us return
555
that :class:`.Column` as a result in this case, where it knows to skip
556
re-assigning the same column. If the mapping is mis-configured such
557
that the ``start_date`` column is accidentally re-assigned to a
558
different table (such as, if we changed ``Manager`` to be joined
559
inheritance without fixing ``start_date``), an error is raised which
560
indicates an existing :class:`.Column` is trying to be re-assigned to
561
a different owning :class:`.Table`.
563
.. versionadded:: 0.8 :class:`.declared_attr` can be used on a non-mixin
564
class, and the returned :class:`.Column` or other mapped attribute
565
will be applied to the mapping as any other attribute. Previously,
566
the resulting attribute would be ignored, and also result in a warning
567
being emitted when a subclass was created.
569
.. versionadded:: 0.8 :class:`.declared_attr`, when used either with a
570
mixin or non-mixin declarative class, can return an existing
571
:class:`.Column` already assigned to the parent :class:`.Table`,
572
to indicate that the re-assignment of the :class:`.Column` should be
573
skipped, however should still be mapped on the target class,
574
in order to resolve duplicate column conflicts.
576
The same concept can be used with mixin classes (see
577
:ref:`declarative_mixins`)::
580
__tablename__ = 'people'
581
id = Column(Integer, primary_key=True)
582
discriminator = Column('type', String(50))
583
__mapper_args__ = {'polymorphic_on': discriminator}
585
class HasStartDate(object):
588
return cls.__table__.c.get('start_date', Column(DateTime))
590
class Engineer(HasStartDate, Person):
591
__mapper_args__ = {'polymorphic_identity': 'engineer'}
593
class Manager(HasStartDate, Person):
594
__mapper_args__ = {'polymorphic_identity': 'manager'}
596
The above mixin checks the local ``__table__`` attribute for the column.
597
Because we're using single table inheritance, we're sure that in this case,
598
``cls.__table__`` refers to ``People.__table__``. If we were mixing joined-
599
and single-table inheritance, we might want our mixin to check more carefully
600
if ``cls.__table__`` is really the :class:`.Table` we're looking for.
602
Concrete Table Inheritance
603
~~~~~~~~~~~~~~~~~~~~~~~~~~
605
Concrete is defined as a subclass which has its own table and sets the
606
``concrete`` keyword argument to ``True``::
609
__tablename__ = 'people'
610
id = Column(Integer, primary_key=True)
611
name = Column(String(50))
613
class Engineer(Person):
614
__tablename__ = 'engineers'
615
__mapper_args__ = {'concrete':True}
616
id = Column(Integer, primary_key=True)
617
primary_language = Column(String(50))
618
name = Column(String(50))
620
Usage of an abstract base class is a little less straightforward as it
621
requires usage of :func:`~sqlalchemy.orm.util.polymorphic_union`,
622
which needs to be created with the :class:`.Table` objects
623
before the class is built::
625
engineers = Table('engineers', Base.metadata,
626
Column('id', Integer, primary_key=True),
627
Column('name', String(50)),
628
Column('primary_language', String(50))
630
managers = Table('managers', Base.metadata,
631
Column('id', Integer, primary_key=True),
632
Column('name', String(50)),
633
Column('golf_swing', String(50))
636
punion = polymorphic_union({
637
'engineer':engineers,
643
__mapper_args__ = {'polymorphic_on':punion.c.type}
645
class Engineer(Person):
646
__table__ = engineers
647
__mapper_args__ = {'polymorphic_identity':'engineer', 'concrete':True}
649
class Manager(Person):
651
__mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True}
653
.. _declarative_concrete_helpers:
655
Using the Concrete Helpers
656
^^^^^^^^^^^^^^^^^^^^^^^^^^^
658
Helper classes provides a simpler pattern for concrete inheritance.
659
With these objects, the ``__declare_last__`` helper is used to configure the
660
"polymorphic" loader for the mapper after all subclasses have been declared.
662
.. versionadded:: 0.7.3
664
An abstract base can be declared using the
665
:class:`.AbstractConcreteBase` class::
667
from sqlalchemy.ext.declarative import AbstractConcreteBase
669
class Employee(AbstractConcreteBase, Base):
672
To have a concrete ``employee`` table, use :class:`.ConcreteBase` instead::
674
from sqlalchemy.ext.declarative import ConcreteBase
676
class Employee(ConcreteBase, Base):
677
__tablename__ = 'employee'
678
employee_id = Column(Integer, primary_key=True)
679
name = Column(String(50))
681
'polymorphic_identity':'employee',
685
Either ``Employee`` base can be used in the normal fashion::
687
class Manager(Employee):
688
__tablename__ = 'manager'
689
employee_id = Column(Integer, primary_key=True)
690
name = Column(String(50))
691
manager_data = Column(String(40))
693
'polymorphic_identity':'manager',
696
class Engineer(Employee):
697
__tablename__ = 'engineer'
698
employee_id = Column(Integer, primary_key=True)
699
name = Column(String(50))
700
engineer_info = Column(String(40))
701
__mapper_args__ = {'polymorphic_identity':'engineer',
705
.. _declarative_mixins:
707
Mixin and Custom Base Classes
708
==============================
710
A common need when using :mod:`~sqlalchemy.ext.declarative` is to
711
share some functionality, such as a set of common columns, some common
712
table options, or other mapped properties, across many
713
classes. The standard Python idioms for this is to have the classes
714
inherit from a base which includes these common features.
716
When using :mod:`~sqlalchemy.ext.declarative`, this idiom is allowed
717
via the usage of a custom declarative base class, as well as a "mixin" class
718
which is inherited from in addition to the primary base. Declarative
719
includes several helper features to make this work in terms of how
720
mappings are declared. An example of some commonly mixed-in
723
from sqlalchemy.ext.declarative import declared_attr
725
class MyMixin(object):
728
def __tablename__(cls):
729
return cls.__name__.lower()
731
__table_args__ = {'mysql_engine': 'InnoDB'}
732
__mapper_args__= {'always_refresh': True}
734
id = Column(Integer, primary_key=True)
736
class MyModel(MyMixin, Base):
737
name = Column(String(1000))
739
Where above, the class ``MyModel`` will contain an "id" column
740
as the primary key, a ``__tablename__`` attribute that derives
741
from the name of the class itself, as well as ``__table_args__``
742
and ``__mapper_args__`` defined by the ``MyMixin`` mixin class.
744
There's no fixed convention over whether ``MyMixin`` precedes
745
``Base`` or not. Normal Python method resolution rules apply, and
746
the above example would work just as well with::
748
class MyModel(Base, MyMixin):
749
name = Column(String(1000))
751
This works because ``Base`` here doesn't define any of the
752
variables that ``MyMixin`` defines, i.e. ``__tablename__``,
753
``__table_args__``, ``id``, etc. If the ``Base`` did define
754
an attribute of the same name, the class placed first in the
755
inherits list would determine which attribute is used on the
761
In addition to using a pure mixin, most of the techniques in this
762
section can also be applied to the base class itself, for patterns that
763
should apply to all classes derived from a particular base. This is achieved
764
using the ``cls`` argument of the :func:`.declarative_base` function::
766
from sqlalchemy.ext.declarative import declared_attr
770
def __tablename__(cls):
771
return cls.__name__.lower()
773
__table_args__ = {'mysql_engine': 'InnoDB'}
775
id = Column(Integer, primary_key=True)
777
from sqlalchemy.ext.declarative import declarative_base
779
Base = declarative_base(cls=Base)
782
name = Column(String(1000))
784
Where above, ``MyModel`` and all other classes that derive from ``Base`` will
785
have a table name derived from the class name, an ``id`` primary key column,
786
as well as the "InnoDB" engine for MySQL.
791
The most basic way to specify a column on a mixin is by simple
794
class TimestampMixin(object):
795
created_at = Column(DateTime, default=func.now())
797
class MyModel(TimestampMixin, Base):
798
__tablename__ = 'test'
800
id = Column(Integer, primary_key=True)
801
name = Column(String(1000))
803
Where above, all declarative classes that include ``TimestampMixin``
804
will also have a column ``created_at`` that applies a timestamp to
807
Those familiar with the SQLAlchemy expression language know that
808
the object identity of clause elements defines their role in a schema.
809
Two ``Table`` objects ``a`` and ``b`` may both have a column called
810
``id``, but the way these are differentiated is that ``a.c.id``
811
and ``b.c.id`` are two distinct Python objects, referencing their
812
parent tables ``a`` and ``b`` respectively.
814
In the case of the mixin column, it seems that only one
815
:class:`.Column` object is explicitly created, yet the ultimate
816
``created_at`` column above must exist as a distinct Python object
817
for each separate destination class. To accomplish this, the declarative
818
extension creates a **copy** of each :class:`.Column` object encountered on
819
a class that is detected as a mixin.
821
This copy mechanism is limited to simple columns that have no foreign
822
keys, as a :class:`.ForeignKey` itself contains references to columns
823
which can't be properly recreated at this level. For columns that
824
have foreign keys, as well as for the variety of mapper-level constructs
825
that require destination-explicit context, the
826
:class:`~.declared_attr` decorator is provided so that
827
patterns common to many classes can be defined as callables::
829
from sqlalchemy.ext.declarative import declared_attr
831
class ReferenceAddressMixin(object):
834
return Column(Integer, ForeignKey('address.id'))
836
class User(ReferenceAddressMixin, Base):
837
__tablename__ = 'user'
838
id = Column(Integer, primary_key=True)
840
Where above, the ``address_id`` class-level callable is executed at the
841
point at which the ``User`` class is constructed, and the declarative
842
extension can use the resulting :class:`.Column` object as returned by
843
the method without the need to copy it.
845
.. versionchanged:: > 0.6.5
846
Rename 0.6.5 ``sqlalchemy.util.classproperty``
847
into :class:`~.declared_attr`.
849
Columns generated by :class:`~.declared_attr` can also be
850
referenced by ``__mapper_args__`` to a limited degree, currently
851
by ``polymorphic_on`` and ``version_id_col``, by specifying the
852
classdecorator itself into the dictionary - the declarative extension
853
will resolve them at class construction time::
858
return Column(String(50))
860
__mapper_args__= {'polymorphic_on':type_}
862
class MyModel(MyMixin, Base):
864
id = Column(Integer, primary_key=True)
868
Mixing in Relationships
869
~~~~~~~~~~~~~~~~~~~~~~~
871
Relationships created by :func:`~sqlalchemy.orm.relationship` are provided
872
with declarative mixin classes exclusively using the
873
:class:`.declared_attr` approach, eliminating any ambiguity
874
which could arise when copying a relationship and its possibly column-bound
875
contents. Below is an example which combines a foreign key column and a
876
relationship so that two classes ``Foo`` and ``Bar`` can both be configured to
877
reference a common target class via many-to-one::
879
class RefTargetMixin(object):
882
return Column('target_id', ForeignKey('target.id'))
886
return relationship("Target")
888
class Foo(RefTargetMixin, Base):
889
__tablename__ = 'foo'
890
id = Column(Integer, primary_key=True)
892
class Bar(RefTargetMixin, Base):
893
__tablename__ = 'bar'
894
id = Column(Integer, primary_key=True)
897
__tablename__ = 'target'
898
id = Column(Integer, primary_key=True)
900
:func:`~sqlalchemy.orm.relationship` definitions which require explicit
901
primaryjoin, order_by etc. expressions should use the string forms
902
for these arguments, so that they are evaluated as late as possible.
903
To reference the mixin class in these expressions, use the given ``cls``
906
class RefTargetMixin(object):
909
return Column('target_id', ForeignKey('target.id'))
913
return relationship("Target",
914
primaryjoin="Target.id==%s.target_id" % cls.__name__
917
Mixing in deferred(), column_property(), and other MapperProperty classes
918
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
920
Like :func:`~sqlalchemy.orm.relationship`, all
921
:class:`~sqlalchemy.orm.interfaces.MapperProperty` subclasses such as
922
:func:`~sqlalchemy.orm.deferred`, :func:`~sqlalchemy.orm.column_property`,
923
etc. ultimately involve references to columns, and therefore, when
924
used with declarative mixins, have the :class:`.declared_attr`
925
requirement so that no reliance on copying is needed::
927
class SomethingMixin(object):
931
return deferred(Column(Integer))
933
class Something(SomethingMixin, Base):
934
__tablename__ = "something"
936
Mixing in Association Proxy and Other Attributes
937
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
939
Mixins can specify user-defined attributes as well as other extension
940
units such as :func:`.association_proxy`. The usage of
941
:class:`.declared_attr` is required in those cases where the attribute must
942
be tailored specifically to the target subclass. An example is when
943
constructing multiple :func:`.association_proxy` attributes which each
944
target a different type of child object. Below is an
945
:func:`.association_proxy` / mixin example which provides a scalar list of
946
string values to an implementing class::
948
from sqlalchemy import Column, Integer, ForeignKey, String
949
from sqlalchemy.orm import relationship
950
from sqlalchemy.ext.associationproxy import association_proxy
951
from sqlalchemy.ext.declarative import declarative_base, declared_attr
953
Base = declarative_base()
955
class HasStringCollection(object):
958
class StringAttribute(Base):
959
__tablename__ = cls.string_table_name
960
id = Column(Integer, primary_key=True)
961
value = Column(String(50), nullable=False)
962
parent_id = Column(Integer,
963
ForeignKey('%s.id' % cls.__tablename__),
965
def __init__(self, value):
968
return relationship(StringAttribute)
972
return association_proxy('_strings', 'value')
974
class TypeA(HasStringCollection, Base):
975
__tablename__ = 'type_a'
976
string_table_name = 'type_a_strings'
977
id = Column(Integer(), primary_key=True)
979
class TypeB(HasStringCollection, Base):
980
__tablename__ = 'type_b'
981
string_table_name = 'type_b_strings'
982
id = Column(Integer(), primary_key=True)
984
Above, the ``HasStringCollection`` mixin produces a :func:`.relationship`
985
which refers to a newly generated class called ``StringAttribute``. The
986
``StringAttribute`` class is generated with it's own :class:`.Table`
987
definition which is local to the parent class making usage of the
988
``HasStringCollection`` mixin. It also produces an :func:`.association_proxy`
989
object which proxies references to the ``strings`` attribute onto the ``value``
990
attribute of each ``StringAttribute`` instance.
992
``TypeA`` or ``TypeB`` can be instantiated given the constructor
993
argument ``strings``, a list of strings::
995
ta = TypeA(strings=['foo', 'bar'])
996
tb = TypeA(strings=['bat', 'bar'])
998
This list will generate a collection
999
of ``StringAttribute`` objects, which are persisted into a table that's
1000
local to either the ``type_a_strings`` or ``type_b_strings`` table::
1002
>>> print ta._strings
1003
[<__main__.StringAttribute object at 0x10151cd90>,
1004
<__main__.StringAttribute object at 0x10151ce10>]
1006
When constructing the :func:`.association_proxy`, the
1007
:class:`.declared_attr` decorator must be used so that a distinct
1008
:func:`.association_proxy` object is created for each of the ``TypeA``
1009
and ``TypeB`` classes.
1011
.. versionadded:: 0.8 :class:`.declared_attr` is usable with non-mapped
1012
attributes, including user-defined attributes as well as
1013
:func:`.association_proxy`.
1016
Controlling table inheritance with mixins
1017
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1019
The ``__tablename__`` attribute in conjunction with the hierarchy of
1020
classes involved in a declarative mixin scenario controls what type of
1021
table inheritance, if any,
1022
is configured by the declarative extension.
1024
If the ``__tablename__`` is computed by a mixin, you may need to
1025
control which classes get the computed attribute in order to get the
1026
type of table inheritance you require.
1028
For example, if you had a mixin that computes ``__tablename__`` but
1029
where you wanted to use that mixin in a single table inheritance
1030
hierarchy, you can explicitly specify ``__tablename__`` as ``None`` to
1031
indicate that the class should not have a table mapped::
1033
from sqlalchemy.ext.declarative import declared_attr
1037
def __tablename__(cls):
1038
return cls.__name__.lower()
1040
class Person(Tablename, Base):
1041
id = Column(Integer, primary_key=True)
1042
discriminator = Column('type', String(50))
1043
__mapper_args__ = {'polymorphic_on': discriminator}
1045
class Engineer(Person):
1046
__tablename__ = None
1047
__mapper_args__ = {'polymorphic_identity': 'engineer'}
1048
primary_language = Column(String(50))
1050
Alternatively, you can make the mixin intelligent enough to only
1051
return a ``__tablename__`` in the event that no table is already
1052
mapped in the inheritance hierarchy. To help with this, a
1053
:func:`~sqlalchemy.ext.declarative.has_inherited_table` helper
1054
function is provided that returns ``True`` if a parent class already
1057
As an example, here's a mixin that will only allow single table
1060
from sqlalchemy.ext.declarative import declared_attr
1061
from sqlalchemy.ext.declarative import has_inherited_table
1063
class Tablename(object):
1065
def __tablename__(cls):
1066
if has_inherited_table(cls):
1068
return cls.__name__.lower()
1070
class Person(Tablename, Base):
1071
id = Column(Integer, primary_key=True)
1072
discriminator = Column('type', String(50))
1073
__mapper_args__ = {'polymorphic_on': discriminator}
1075
class Engineer(Person):
1076
primary_language = Column(String(50))
1077
__mapper_args__ = {'polymorphic_identity': 'engineer'}
1079
If you want to use a similar pattern with a mix of single and joined
1080
table inheritance, you would need a slightly different mixin and use
1081
it on any joined table child classes in addition to their parent
1084
from sqlalchemy.ext.declarative import declared_attr
1085
from sqlalchemy.ext.declarative import has_inherited_table
1087
class Tablename(object):
1089
def __tablename__(cls):
1090
if (has_inherited_table(cls) and
1091
Tablename not in cls.__bases__):
1093
return cls.__name__.lower()
1095
class Person(Tablename, Base):
1096
id = Column(Integer, primary_key=True)
1097
discriminator = Column('type', String(50))
1098
__mapper_args__ = {'polymorphic_on': discriminator}
1100
# This is single table inheritance
1101
class Engineer(Person):
1102
primary_language = Column(String(50))
1103
__mapper_args__ = {'polymorphic_identity': 'engineer'}
1105
# This is joined table inheritance
1106
class Manager(Tablename, Person):
1107
id = Column(Integer, ForeignKey('person.id'), primary_key=True)
1108
preferred_recreation = Column(String(50))
1109
__mapper_args__ = {'polymorphic_identity': 'engineer'}
1111
Combining Table/Mapper Arguments from Multiple Mixins
1112
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1114
In the case of ``__table_args__`` or ``__mapper_args__``
1115
specified with declarative mixins, you may want to combine
1116
some parameters from several mixins with those you wish to
1117
define on the class iteself. The
1118
:class:`.declared_attr` decorator can be used
1119
here to create user-defined collation routines that pull
1120
from multiple collections::
1122
from sqlalchemy.ext.declarative import declared_attr
1124
class MySQLSettings(object):
1125
__table_args__ = {'mysql_engine':'InnoDB'}
1127
class MyOtherMixin(object):
1128
__table_args__ = {'info':'foo'}
1130
class MyModel(MySQLSettings, MyOtherMixin, Base):
1131
__tablename__='my_model'
1134
def __table_args__(cls):
1136
args.update(MySQLSettings.__table_args__)
1137
args.update(MyOtherMixin.__table_args__)
1140
id = Column(Integer, primary_key=True)
1142
Creating Indexes with Mixins
1143
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1145
To define a named, potentially multicolumn :class:`.Index` that applies to all
1146
tables derived from a mixin, use the "inline" form of :class:`.Index` and
1147
establish it as part of ``__table_args__``::
1149
class MyMixin(object):
1154
def __table_args__(cls):
1155
return (Index('test_idx_%s' % cls.__tablename__, 'a', 'b'),)
1157
class MyModel(MyMixin, Base):
1158
__tablename__ = 'atable'
1159
c = Column(Integer,primary_key=True)
1164
``__declare_last__()``
1165
~~~~~~~~~~~~~~~~~~~~~~
1167
The ``__declare_last__()`` hook allows definition of
1168
a class level function that is automatically called by the
1169
:meth:`.MapperEvents.after_configured` event, which occurs after mappings are
1170
assumed to be completed and the 'configure' step has finished::
1172
class MyClass(Base):
1174
def __declare_last__(cls):
1176
# do something with mappings
1178
.. versionadded:: 0.7.3
1180
.. _declarative_abstract:
1185
``__abstract__`` causes declarative to skip the production
1186
of a table or mapper for the class entirely. A class can be added within a
1187
hierarchy in the same way as mixin (see :ref:`declarative_mixins`), allowing
1188
subclasses to extend just from the special class::
1190
class SomeAbstractBase(Base):
1193
def some_helpful_method(self):
1197
def __mapper_args__(cls):
1198
return {"helpful mapper arguments":True}
1200
class MyMappedClass(SomeAbstractBase):
1203
One possible use of ``__abstract__`` is to use a distinct
1204
:class:`.MetaData` for different bases::
1206
Base = declarative_base()
1208
class DefaultBase(Base):
1210
metadata = MetaData()
1212
class OtherBase(Base):
1214
metadata = MetaData()
1216
Above, classes which inherit from ``DefaultBase`` will use one
1217
:class:`.MetaData` as the registry of tables, and those which inherit from
1218
``OtherBase`` will use a different one. The tables themselves can then be
1219
created perhaps within distinct databases::
1221
DefaultBase.metadata.create_all(some_engine)
1222
OtherBase.metadata_create_all(some_other_engine)
1224
.. versionadded:: 0.7.3
1229
As a convenience feature, the :func:`declarative_base` sets a default
1230
constructor on classes which takes keyword arguments, and assigns them
1231
to the named attributes::
1233
e = Engineer(primary_language='python')
1238
Note that ``declarative`` does nothing special with sessions, and is
1239
only intended as an easier way to configure mappers and
1240
:class:`~sqlalchemy.schema.Table` objects. A typical application
1241
setup using :class:`~sqlalchemy.orm.scoped_session` might look like::
1243
engine = create_engine('postgresql://scott:tiger@localhost/test')
1244
Session = scoped_session(sessionmaker(autocommit=False,
1247
Base = declarative_base()
1249
Mapped instances then make usage of
1250
:class:`~sqlalchemy.orm.session.Session` in the usual way.
1254
from .api import declarative_base, synonym_for, comparable_using, \
1255
instrument_declarative, ConcreteBase, AbstractConcreteBase, \
1256
DeclarativeMeta, DeferredReflection, has_inherited_table,\
1260
__all__ = ['declarative_base', 'synonym_for', 'has_inherited_table',
1261
'comparable_using', 'instrument_declarative', 'declared_attr',
1262
'ConcreteBase', 'AbstractConcreteBase', 'DeclarativeMeta',
1263
'DeferredReflection']
added
removed
lib/sqlalchemy/ext/declarative/__init__.py
