1
==============================
2
What's New in SQLAlchemy 0.7?
3
==============================
5
.. admonition:: About this Document
7
This document describes changes between SQLAlchemy version 0.6,
8
last released May 5, 2012, and SQLAlchemy version 0.7,
9
undergoing maintenance releases as of October, 2012.
11
Document date: July 27, 2011
16
This guide introduces what's new in SQLAlchemy version 0.7,
17
and also documents changes which affect users migrating
18
their applications from the 0.6 series of SQLAlchemy to 0.7.
20
To as great a degree as possible, changes are made in such a
21
way as to not break compatibility with applications built
22
for 0.6. The changes that are necessarily not backwards
23
compatible are very few, and all but one, the change to
24
mutable attribute defaults, should affect an exceedingly
25
small portion of applications - many of the changes regard
26
non-public APIs and undocumented hacks some users may have
27
been attempting to use.
29
A second, even smaller class of non-backwards-compatible
30
changes is also documented. This class of change regards
31
those features and behaviors that have been deprecated at
32
least since version 0.5 and have been raising warnings since
33
their deprecation. These changes would only affect
34
applications that are still using 0.4- or early 0.5-style
35
APIs. As the project matures, we have fewer and fewer of
36
these kinds of changes with 0.x level releases, which is a
37
product of our API having ever fewer features that are less
38
than ideal for the use cases they were meant to solve.
40
An array of existing functionalities have been superseded in
41
SQLAlchemy 0.7. There's not much difference between the
42
terms "superseded" and "deprecated", except that the former
43
has a much weaker suggestion of the old feature would ever
44
be removed. In 0.7, features like ``synonym`` and
45
``comparable_property``, as well as all the ``Extension``
46
and other event classes, have been superseded. But these
47
"superseded" features have been re-implemented such that
48
their implementations live mostly outside of core ORM code,
49
so their continued "hanging around" doesn't impact
50
SQLAlchemy's ability to further streamline and refine its
51
internals, and we expect them to remain within the API for
52
the foreseeable future.
60
SQLAlchemy started early with the ``MapperExtension`` class,
61
which provided hooks into the persistence cycle of mappers.
62
As SQLAlchemy quickly became more componentized, pushing
63
mappers into a more focused configurational role, many more
64
"extension", "listener", and "proxy" classes popped up to
65
solve various activity-interception use cases in an ad-hoc
66
fashion. Part of this was driven by the divergence of
67
activities; ``ConnectionProxy`` objects wanted to provide a
68
system of rewriting statements and parameters;
69
``AttributeExtension`` provided a system of replacing
70
incoming values, and ``DDL`` objects had events that could
71
be switched off of dialect-sensitive callables.
73
0.7 re-implements virtually all of these plugin points with
74
a new, unified approach, which retains all the
75
functionalities of the different systems, provides more
76
flexibility and less boilerplate, performs better, and
77
eliminates the need to learn radically different APIs for
78
each event subsystem. The pre-existing classes
79
``MapperExtension``, ``SessionExtension``,
80
``AttributeExtension``, ``ConnectionProxy``,
81
``PoolListener`` as well as the ``DDLElement.execute_at``
82
method are deprecated and now implemented in terms of the
83
new system - these APIs remain fully functional and are
84
expected to remain in place for the foreseeable future.
86
The new approach uses named events and user-defined
87
callables to associate activities with events. The API's
88
look and feel was driven by such diverse sources as JQuery,
89
Blinker, and Hibernate, and was also modified further on
90
several occasions during conferences with dozens of users on
91
Twitter, which appears to have a much higher response rate
92
than the mailing list for such questions.
94
It also features an open-ended system of target
95
specification that allows events to be associated with API
96
classes, such as for all ``Session`` or ``Engine`` objects,
97
with specific instances of API classes, such as for a
98
specific ``Pool`` or ``Mapper``, as well as for related
99
objects like a user- defined class that's mapped, or
100
something as specific as a certain attribute on instances of
101
a particular subclass of a mapped parent class. Individual
102
listener subsystems can apply wrappers to incoming user-
103
defined listener functions which modify how they are called
104
- an mapper event can receive either the instance of the
105
object being operated upon, or its underlying
106
``InstanceState`` object. An attribute event can opt whether
107
or not to have the responsibility of returning a new value.
109
Several systems now build upon the new event API, including
110
the new "mutable attributes" API as well as composite
111
attributes. The greater emphasis on events has also led to
112
the introduction of a handful of new events, including
113
attribute expiration and refresh operations, pickle
114
loads/dumps operations, completed mapper construction
119
:ref:`event_toplevel`
123
Hybrid Attributes, implements/supersedes synonym(), comparable_property()
124
-------------------------------------------------------------------------
126
The "derived attributes" example has now been turned into an
127
official extension. The typical use case for ``synonym()``
128
is to provide descriptor access to a mapped column; the use
129
case for ``comparable_property()`` is to be able to return a
130
``PropComparator`` from any descriptor. In practice, the
131
approach of "derived" is easier to use, more extensible, is
132
implemented in a few dozen lines of pure Python with almost
133
no imports, and doesn't require the ORM core to even be
134
aware of it. The feature is now known as the "Hybrid
135
Attributes" extension.
137
``synonym()`` and ``comparable_property()`` are still part
138
of the ORM, though their implementations have been moved
139
outwards, building on an approach that is similar to that of
140
the hybrid extension, so that the core ORM
141
mapper/query/property modules aren't really aware of them
146
:ref:`hybrids_toplevel`
153
As is customary with all major SQLA releases, a wide pass
154
through the internals to reduce overhead and callcounts has
155
been made which further reduces the work needed in common
156
scenarios. Highlights of this release include:
158
* The flush process will now bundle INSERT statements into
159
batches fed to ``cursor.executemany()``, for rows where
160
the primary key is already present. In particular this
161
usually applies to the "child" table on a joined table
162
inheritance configuration, meaning the number of calls to
163
``cursor.execute`` for a large bulk insert of joined-
164
table objects can be cut in half, allowing native DBAPI
165
optimizations to take place for those statements passed
166
to ``cursor.executemany()`` (such as re-using a prepared
169
* The codepath invoked when accessing a many-to-one
170
reference to a related object that's already loaded has
171
been greatly simplified. The identity map is checked
172
directly without the need to generate a new ``Query``
173
object first, which is expensive in the context of
174
thousands of in-memory many-to-ones being accessed. The
175
usage of constructed-per-call "loader" objects is also no
176
longer used for the majority of lazy attribute loads.
178
* The rewrite of composites allows a shorter codepath when
179
mapper internals access mapped attributes within a
182
* New inlined attribute access functions replace the
183
previous usage of "history" when the "save-update" and
184
other cascade operations need to cascade among the full
185
scope of datamembers associated with an attribute. This
186
reduces the overhead of generating a new ``History``
187
object for this speed-critical operation.
189
* The internals of the ``ExecutionContext``, the object
190
corresponding to a statement execution, have been
191
inlined and simplified.
193
* The ``bind_processor()`` and ``result_processor()``
194
callables generated by types for each statement
195
execution are now cached (carefully, so as to avoid memory
196
leaks for ad-hoc types and dialects) for the lifespan of
197
that type, further reducing per-statement call overhead.
199
* The collection of "bind processors" for a particular
200
``Compiled`` instance of a statement is also cached on
201
the ``Compiled`` object, taking further advantage of the
202
"compiled cache" used by the flush process to re-use the
203
same compiled form of INSERT, UPDATE, DELETE statements.
205
A demonstration of callcount reduction including a sample
206
benchmark script is at
207
http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-
213
The "composite" feature has been rewritten, like
214
``synonym()`` and ``comparable_property()``, to use a
215
lighter weight implementation based on descriptors and
216
events, rather than building into the ORM internals. This
217
allowed the removal of some latency from the mapper/unit of
218
work internals, and simplifies the workings of composite.
219
The composite attribute now no longer conceals the
220
underlying columns it builds upon, which now remain as
221
regular attributes. Composites can also act as a proxy for
222
``relationship()`` as well as ``Column()`` attributes.
224
The major backwards-incompatible change of composites is
225
that they no longer use the ``mutable=True`` system to
226
detect in-place mutations. Please use the `Mutation
227
Tracking <http://www.sqlalchemy.org/docs/07/orm/extensions/m
228
utable.html>`_ extension to establish in-place change events
229
to existing composite usage.
233
:ref:`mapper_composite`
235
:ref:`mutable_toplevel`
237
:ticket:`2008` :ticket:`2024`
239
More succinct form of query.join(target, onclause)
240
--------------------------------------------------
242
The default method of issuing ``query.join()`` to a target
243
with an explicit onclause is now:
247
query.join(SomeClass, SomeClass.id==ParentClass.some_id)
249
In 0.6, this usage was considered to be an error, because
250
``join()`` accepts multiple arguments corresponding to
251
multiple JOIN clauses - the two-argument form needed to be
252
in a tuple to disambiguate between single-argument and two-
253
argument join targets. In the middle of 0.6 we added
254
detection and an error message for this specific calling
255
style, since it was so common. In 0.7, since we are
256
detecting the exact pattern anyway, and since having to type
257
out a tuple for no reason is extremely annoying, the non-
258
tuple method now becomes the "normal" way to do it. The
259
"multiple JOIN" use case is exceedingly rare compared to the
260
single join case, and multiple joins these days are more
261
clearly represented by multiple calls to ``join()``.
263
The tuple form will remain for backwards compatibility.
265
Note that all the other forms of ``query.join()`` remain
270
query.join(MyClass.somerelation)
271
query.join("somerelation")
276
<http://www.sqlalchemy.org/docs/07/orm/tutorial.html
277
#querying-with-joins>`_
281
Mutation event extension, supersedes "mutable=True"
282
---------------------------------------------------
284
A new extension, `Mutation Tracking <http://www.sqlalchemy.o
285
rg/docs/07/orm/extensions/mutable.html>`_, provides a
286
mechanism by which user-defined datatypes can provide change
287
events back to the owning parent or parents. The extension
288
includes an approach for scalar database values, such as
289
those managed by ``PickleType``, ``postgresql.ARRAY``, or
290
other custom ``MutableType`` classes, as well as an approach
291
for ORM "composites", those configured using :ref:`composite()
292
<mapper_composite>`_.
296
:ref:`mutable_toplevel`
298
NULLS FIRST / NULLS LAST operators
299
----------------------------------
301
These are implemented as an extension to the ``asc()`` and
302
``desc()`` operators, called ``nullsfirst()`` and
313
select.distinct(), query.distinct() accepts \*args for Postgresql DISTINCT ON
314
-----------------------------------------------------------------------------
316
This was already available by passing a list of expressions
317
to the ``distinct`` keyword argument of ``select()``, the
318
``distinct()`` method of ``select()`` and ``Query`` now
319
accept positional arguments which are rendered as DISTINCT
320
ON when a Postgresql backend is used.
322
`distinct() <http://www.sqlalchemy.org/docs/07/core/expressi
323
on_api.html#sqlalchemy.sql.expression.Select.distinct>`_
325
`Query.distinct() <http://www.sqlalchemy.org/docs/07/orm/que
326
ry.html#sqlalchemy.orm.query.Query.distinct>`_
330
``Index()`` can be placed inline inside of ``Table``, ``__table_args__``
331
------------------------------------------------------------------------
333
The Index() construct can be created inline with a Table
334
definition, using strings as column names, as an alternative
335
to the creation of the index outside of the Table. That is:
339
Table('mytable', metadata,
340
Column('id',Integer, primary_key=True),
341
Column('name', String(50), nullable=False),
342
Index('idx_name', 'name')
345
The primary rationale here is for the benefit of declarative
346
``__table_args__``, particularly when used with mixins:
350
class HasNameMixin(object):
351
name = Column('name', String(50), nullable=False)
353
def __table_args__(cls):
354
return (Index('name'), {})
356
class User(HasNameMixin, Base):
357
__tablename__ = 'user'
358
id = Column('id', Integer, primary_key=True)
360
`Indexes <http://www.sqlalchemy.org/docs/07/core/schema.html
363
Window Function SQL Construct
364
-----------------------------
366
A "window function" provides to a statement information
367
about the result set as it's produced. This allows criteria
368
against various things like "row number", "rank" and so
369
forth. They are known to be supported at least by
370
Postgresql, SQL Server and Oracle, possibly others.
372
The best introduction to window functions is on Postgresql's
373
site, where window functions have been supported since
376
http://www.postgresql.org/docs/9.0/static/tutorial-
379
SQLAlchemy provides a simple construct typically invoked via
380
an existing function clause, using the ``over()`` method,
381
which accepts ``order_by`` and ``partition_by`` keyword
382
arguments. Below we replicate the first example in PG's
387
from sqlalchemy.sql import table, column, select, func
389
empsalary = table('empsalary',
396
func.avg(empsalary.c.salary).
397
over(partition_by=empsalary.c.depname).
407
SELECT empsalary.depname, empsalary.empno, empsalary.salary,
408
avg(empsalary.salary) OVER (PARTITION BY empsalary.depname) AS avg
411
`sqlalchemy.sql.expression.over <http://www.sqlalchemy.org/d
412
ocs/07/core/expression_api.html#sqlalchemy.sql.expression.ov
417
execution_options() on Connection accepts "isolation_level" argument
418
--------------------------------------------------------------------
420
This sets the transaction isolation level for a single
421
``Connection``, until that ``Connection`` is closed and its
422
underlying DBAPI resource returned to the connection pool,
423
upon which the isolation level is reset back to the default.
424
The default isolation level is set using the
425
``isolation_level`` argument to ``create_engine()``.
427
Transaction isolation support is currently only supported by
428
the Postgresql and SQLite backends.
430
`execution_options() <http://www.sqlalchemy.org/docs/07/core
431
/connections.html#sqlalchemy.engine.base.Connection.executio
436
``TypeDecorator`` works with integer primary key columns
437
--------------------------------------------------------
439
A ``TypeDecorator`` which extends the behavior of
440
``Integer`` can be used with a primary key column. The
441
"autoincrement" feature of ``Column`` will now recognize
442
that the underlying database column is still an integer so
443
that lastrowid mechanisms continue to function. The
444
``TypeDecorator`` itself will have its result value
445
processor applied to newly generated primary keys, including
446
those received by the DBAPI ``cursor.lastrowid`` accessor.
448
:ticket:`2005` :ticket:`2006`
450
``TypeDecorator`` is present in the "sqlalchemy" import space
451
-------------------------------------------------------------
453
No longer need to import this from ``sqlalchemy.types``,
454
it's now mirrored in ``sqlalchemy``.
459
Dialects have been added:
461
* a MySQLdb driver for the Drizzle database:
464
`Drizzle <http://www.sqlalchemy.org/docs/07/dialects/drizz
467
* support for the pymysql DBAPI:
471
<http://www.sqlalchemy.org/docs/07/dialects/mysql.html
472
#module-sqlalchemy.dialects.mysql.pymysql>`_
474
* psycopg2 now works with Python 3
477
Behavioral Changes (Backwards Compatible)
478
=========================================
480
C Extensions Build by Default
481
-----------------------------
483
This is as of 0.7b4. The exts will build if cPython 2.xx
484
is detected. If the build fails, such as on a windows
485
install, that condition is caught and the non-C install
486
proceeds. The C exts won't build if Python 3 or Pypy is
489
Query.count() simplified, should work virtually always
490
------------------------------------------------------
492
The very old guesswork which occurred within
493
``Query.count()`` has been modernized to use
494
``.from_self()``. That is, ``query.count()`` is now
499
query.from_self(func.count(literal_column('1'))).scalar()
501
Previously, internal logic attempted to rewrite the columns
502
clause of the query itself, and upon detection of a
503
"subquery" condition, such as a column-based query that
504
might have aggregates in it, or a query with DISTINCT, would
505
go through a convoluted process of rewriting the columns
506
clause. This logic failed in complex conditions,
507
particularly those involving joined table inheritance, and
508
was long obsolete by the more comprehensive ``.from_self()``
511
The SQL emitted by ``query.count()`` is now always of the
516
SELECT count(1) AS count_1 FROM (
517
SELECT user.id AS user_id, user.name AS user_name from user
520
that is, the original query is preserved entirely inside of
521
a subquery, with no more guessing as to how count should be
526
To emit a non-subquery form of count()
527
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
529
MySQL users have already reported that the MyISAM engine not
530
surprisingly falls over completely with this simple change.
531
Note that for a simple ``count()`` that optimizes for DBs
532
that can't handle simple subqueries, ``func.count()`` should
537
from sqlalchemy import func
538
session.query(func.count(MyClass.id)).scalar()
544
from sqlalchemy import func, literal_column
545
session.query(func.count(literal_column('*'))).select_from(MyClass).scalar()
547
LIMIT/OFFSET clauses now use bind parameters
548
--------------------------------------------
550
The LIMIT and OFFSET clauses, or their backend equivalents
551
(i.e. TOP, ROW NUMBER OVER, etc.), use bind parameters for
552
the actual values, for all backends which support it (most
553
except for Sybase). This allows better query optimizer
554
performance as the textual string for multiple statements
555
with differing LIMIT/OFFSET are now identical.
562
Vinay Sajip has provided a patch to our logging system such
563
that the "hex string" embedded in logging statements for
564
engines and pools is no longer needed to allow the ``echo``
565
flag to work correctly. A new system that uses filtered
566
logging objects allows us to maintain our current behavior
567
of ``echo`` being local to individual engines without the
568
need for additional identifying strings local to those
573
Simplified polymorphic_on assignment
574
------------------------------------
576
The population of the ``polymorphic_on`` column-mapped
577
attribute, when used in an inheritance scenario, now occurs
578
when the object is constructed, i.e. its ``__init__`` method
579
is called, using the init event. The attribute then behaves
580
the same as any other column-mapped attribute. Previously,
581
special logic would fire off during flush to populate this
582
column, which prevented any user code from modifying its
583
behavior. The new approach improves upon this in three
584
ways: 1. the polymorphic identity is now present on the
585
object as soon as its constructed; 2. the polymorphic
586
identity can be changed by user code without any difference
587
in behavior from any other column-mapped attribute; 3. the
588
internals of the mapper during flush are simplified and no
589
longer need to make special checks for this column.
593
contains_eager() chains across multiple paths (i.e. "all()")
594
------------------------------------------------------------
596
The ```contains_eager()```` modifier now will chain itself
597
for a longer path without the need to emit individual
598
````contains_eager()``` calls. Instead of:
602
session.query(A).options(contains_eager(A.b), contains_eager(A.b, B.c))
608
session.query(A).options(contains_eager(A.b, B.c))
612
Flushing of orphans that have no parent is allowed
613
--------------------------------------------------
615
We've had a long standing behavior that checks for a so-
616
called "orphan" during flush, that is, an object which is
617
associated with a ``relationship()`` that specifies "delete-
618
orphan" cascade, has been newly added to the session for an
619
INSERT, and no parent relationship has been established.
620
This check was added years ago to accommodate some test
621
cases which tested the orphan behavior for consistency. In
622
modern SQLA, this check is no longer needed on the Python
623
side. The equivalent behavior of the "orphan check" is
624
accomplished by making the foreign key reference to the
625
object's parent row NOT NULL, where the database does its
626
job of establishing data consistency in the same way SQLA
627
allows most other operations to do. If the object's parent
628
foreign key is nullable, then the row can be inserted. The
629
"orphan" behavior runs when the object was persisted with a
630
particular parent, and is then disassociated with that
631
parent, leading to a DELETE statement emitted for it.
635
Warnings generated when collection members, scalar referents not part of the flush
636
----------------------------------------------------------------------------------
638
Warnings are now emitted when related objects referenced via
639
a loaded ``relationship()`` on a parent object marked as
640
"dirty" are not present in the current ``Session``.
642
The ``save-update`` cascade takes effect when objects are
643
added to the ``Session``, or when objects are first
644
associated with a parent, so that an object and everything
645
related to it are usually all present in the same
646
``Session``. However, if ``save-update`` cascade is
647
disabled for a particular ``relationship()``, then this
648
behavior does not occur, and the flush process does not try
649
to correct for it, instead staying consistent to the
650
configured cascade behavior. Previously, when such objects
651
were detected during the flush, they were silently skipped.
652
The new behavior is that a warning is emitted, for the
653
purposes of alerting to a situation that more often than not
654
is the source of unexpected behavior.
658
Setup no longer installs a Nose plugin
659
--------------------------------------
661
Since we moved to nose we've used a plugin that installs via
662
setuptools, so that the ``nosetests`` script would
663
automatically run SQLA's plugin code, necessary for our
664
tests to have a full environment. In the middle of 0.6, we
665
realized that the import pattern here meant that Nose's
666
"coverage" plugin would break, since "coverage" requires
667
that it be started before any modules to be covered are
668
imported; so in the middle of 0.6 we made the situation
669
worse by adding a separate ``sqlalchemy-nose`` package to
670
the build to overcome this.
672
In 0.7 we've done away with trying to get ``nosetests`` to
673
work automatically, since the SQLAlchemy module would
674
produce a large number of nose configuration options for all
675
usages of ``nosetests``, not just the SQLAlchemy unit tests
676
themselves, and the additional ``sqlalchemy-nose`` install
677
was an even worse idea, producing an extra package in Python
678
environments. The ``sqla_nose.py`` script in 0.7 is now
679
the only way to run the tests with nose.
683
Non-``Table``-derived constructs can be mapped
684
----------------------------------------------
686
A construct that isn't against any ``Table`` at all, like a
687
function, can be mapped.
691
from sqlalchemy import select, func
692
from sqlalchemy.orm import mapper
694
class Subset(object):
696
selectable = select(["x", "y", "z"]).select_from(func.some_db_function()).alias()
697
mapper(Subset, selectable, primary_key=[selectable.c.x])
701
aliased() accepts ``FromClause`` elements
702
-----------------------------------------
704
This is a convenience helper such that in the case a plain
705
``FromClause``, such as a ``select``, ``Table`` or ``join``
706
is passed to the ``orm.aliased()`` construct, it passes
707
through to the ``.alias()`` method of that from construct
708
rather than constructing an ORM level ``AliasedClass``.
712
Session.connection(), Session.execute() accept 'bind'
713
-----------------------------------------------------
715
This is to allow execute/connection operations to
716
participate in the open transaction of an engine explicitly.
717
It also allows custom subclasses of ``Session`` that
718
implement their own ``get_bind()`` method and arguments to
719
use those custom arguments with both the ``execute()`` and
720
``connection()`` methods equally.
722
`Session.connection <http://www.sqlalchemy.org/docs/07/orm/s
723
ession.html#sqlalchemy.orm.session.Session.connection>`_
724
`Session.execute <http://www.sqlalchemy.org/docs/07/orm/sess
725
ion.html#sqlalchemy.orm.session.Session.execute>`_
729
Standalone bind parameters in columns clause auto-labeled.
730
----------------------------------------------------------
732
Bind parameters present in the "columns clause" of a select
733
are now auto-labeled like other "anonymous" clauses, which
734
among other things allows their "type" to be meaningful when
735
the row is fetched, as in result row processors.
737
SQLite - relative file paths are normalized through os.path.abspath()
738
---------------------------------------------------------------------
740
This so that a script that changes the current directory
741
will continue to target the same location as subsequent
742
SQLite connections are established.
746
MS-SQL - ``String``/``Unicode``/``VARCHAR``/``NVARCHAR``/``VARBINARY`` emit "max" for no length
747
-----------------------------------------------------------------------------------------------
749
On the MS-SQL backend, the String/Unicode types, and their
750
counterparts VARCHAR/ NVARCHAR, as well as VARBINARY
751
(:ticket:`1833`) emit "max" as the length when no length is
752
specified. This makes it more compatible with Postgresql's
753
VARCHAR type which is similarly unbounded when no length
754
specified. SQL Server defaults the length on these types
755
to '1' when no length is specified.
757
Behavioral Changes (Backwards Incompatible)
758
===========================================
760
Note again, aside from the default mutability change, most
761
of these changes are \*extremely minor* and will not affect
764
``PickleType`` and ARRAY mutability turned off by default
765
---------------------------------------------------------
767
This change refers to the default behavior of the ORM when
768
mapping columns that have either the ``PickleType`` or
769
``postgresql.ARRAY`` datatypes. The ``mutable`` flag is now
770
set to ``False`` by default. If an existing application uses
771
these types and depends upon detection of in-place
772
mutations, the type object must be constructed with
773
``mutable=True`` to restore the 0.6 behavior:
777
Table('mytable', metadata,
780
Column('pickled_data', PickleType(mutable=True))
783
The ``mutable=True`` flag is being phased out, in favor of
784
the new `Mutation Tracking <http://www.sqlalchemy.org/docs/0
785
7/orm/extensions/mutable.html>`_ extension. This extension
786
provides a mechanism by which user-defined datatypes can
787
provide change events back to the owning parent or parents.
789
The previous approach of using ``mutable=True`` does not
790
provide for change events - instead, the ORM must scan
791
through all mutable values present in a session and compare
792
them against their original value for changes every time
793
``flush()`` is called, which is a very time consuming event.
794
This is a holdover from the very early days of SQLAlchemy
795
when ``flush()`` was not automatic and the history tracking
796
system was not nearly as sophisticated as it is now.
798
Existing applications which use ``PickleType``,
799
``postgresql.ARRAY`` or other ``MutableType`` subclasses,
800
and require in-place mutation detection, should migrate to
801
the new mutation tracking system, as ``mutable=True`` is
802
likely to be deprecated in the future.
806
Mutability detection of ``composite()`` requires the Mutation Tracking Extension
807
--------------------------------------------------------------------------------
809
So-called "composite" mapped attributes, those configured
810
using the technique described at `Composite Column Types
811
<http://www.sqlalchemy.org/docs/07/orm/mapper_config.html
812
#composite-column-types>`_, have been re-implemented such
813
that the ORM internals are no longer aware of them (leading
814
to shorter and more efficient codepaths in critical
815
sections). While composite types are generally intended to
816
be treated as immutable value objects, this was never
817
enforced. For applications that use composites with
818
mutability, the `Mutation Tracking <http://www.sqlalchemy.or
819
g/docs/07/orm/extensions/mutable.html>`_ extension offers a
820
base class which establishes a mechanism for user-defined
821
composite types to send change event messages back to the
822
owning parent or parents of each object.
824
Applications which use composite types and rely upon in-
825
place mutation detection of these objects should either
826
migrate to the "mutation tracking" extension, or change the
827
usage of the composite types such that in-place changes are
828
no longer needed (i.e., treat them as immutable value
831
SQLite - the SQLite dialect now uses ``NullPool`` for file-based databases
832
--------------------------------------------------------------------------
834
This change is **99.999% backwards compatible**, unless you
835
are using temporary tables across connection pool
838
A file-based SQLite connection is blazingly fast, and using
839
``NullPool`` means that each call to ``Engine.connect``
840
creates a new pysqlite connection.
842
Previously, the ``SingletonThreadPool`` was used, which
843
meant that all connections to a certain engine in a thread
844
would be the same connection. It's intended that the new
845
approach is more intuitive, particularly when multiple
846
connections are used.
848
``SingletonThreadPool`` is still the default engine when a
849
``:memory:`` database is used.
851
Note that this change **breaks temporary tables used across
852
Session commits**, due to the way SQLite handles temp
853
tables. See the note at
854
http://www.sqlalchemy.org/docs/dialects/sqlite.html#using-
855
temporary-tables-with-sqlite if temporary tables beyond the
856
scope of one pool connection are desired.
860
``Session.merge()`` checks version ids for versioned mappers
861
------------------------------------------------------------
863
Session.merge() will check the version id of the incoming
864
state against that of the database, assuming the mapping
865
uses version ids and incoming state has a version_id
866
assigned, and raise StaleDataError if they don't match.
867
This is the correct behavior, in that if incoming state
868
contains a stale version id, it should be assumed the state
871
If merging data into a versioned state, the version id
872
attribute can be left undefined, and no version check will
875
This check was confirmed by examining what Hibernate does -
876
both the ``merge()`` and the versioning features were
877
originally adapted from Hibernate.
881
Tuple label names in Query Improved
882
-----------------------------------
884
This improvement is potentially slightly backwards
885
incompatible for an application that relied upon the old
888
Given two mapped classes ``Foo`` and ``Bar`` each with a
894
qa = session.query(Foo.spam)
895
qb = session.query(Bar.spam)
899
The name given to the single column yielded by ``qu`` will
900
be ``spam``. Previously it would be something like
901
``foo_spam`` due to the way the ``union`` would combine
902
things, which is inconsistent with the name ``spam`` in the
903
case of a non-unioned query.
907
Mapped column attributes reference the most specific column first
908
-----------------------------------------------------------------
910
This is a change to the behavior involved when a mapped
911
column attribute references multiple columns, specifically
912
when dealing with an attribute on a joined-table subclass
913
that has the same name as that of an attribute on the
916
Using declarative, the scenario is this:
921
__tablename__ = 'parent'
922
id = Column(Integer, primary_key=True)
925
__tablename__ = 'child'
926
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
928
Above, the attribute ``Child.id`` refers to both the
929
``child.id`` column as well as ``parent.id`` - this due to
930
the name of the attribute. If it were named differently on
931
the class, such as ``Child.child_id``, it then maps
932
distinctly to ``child.id``, with ``Child.id`` being the same
933
attribute as ``Parent.id``.
935
When the ``id`` attribute is made to reference both
936
``parent.id`` and ``child.id``, it stores them in an ordered
937
list. An expression such as ``Child.id`` then refers to
938
just *one* of those columns when rendered. Up until 0.6,
939
this column would be ``parent.id``. In 0.7, it is the less
940
surprising ``child.id``.
942
The legacy of this behavior deals with behaviors and
943
restrictions of the ORM that don't really apply anymore; all
944
that was needed was to reverse the order.
946
A primary advantage of this approach is that it's now easier
947
to construct ``primaryjoin`` expressions that refer to the
953
__tablename__ = 'child'
954
id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
955
some_related = relationship("SomeRelated",
956
primaryjoin="Child.id==SomeRelated.child_id")
958
class SomeRelated(Base):
959
__tablename__ = 'some_related'
960
id = Column(Integer, primary_key=True)
961
child_id = Column(Integer, ForeignKey('child.id'))
963
Prior to 0.7 the ``Child.id`` expression would reference
964
``Parent.id``, and it would be necessary to map ``child.id``
965
to a distinct attribute.
967
It also means that a query like this one changes its
972
session.query(Parent).filter(Child.id > 7)
974
In 0.6, this would render:
978
SELECT parent.id AS parent_id
980
WHERE parent.id > :id_1
986
SELECT parent.id AS parent_id
988
WHERE child.id > :id_1
990
which you'll note is a cartesian product - this behavior is
991
now equivalent to that of any other attribute that is local
992
to ``Child``. The ``with_polymorphic()`` method, or a
993
similar strategy of explicitly joining the underlying
994
``Table`` objects, is used to render a query against all
995
``Parent`` objects with criteria against ``Child``, in the
996
same manner as that of 0.5 and 0.6:
1000
print s.query(Parent).with_polymorphic([Child]).filter(Child.id > 7)
1002
Which on both 0.6 and 0.7 renders:
1006
SELECT parent.id AS parent_id, child.id AS child_id
1007
FROM parent LEFT OUTER JOIN child ON parent.id = child.id
1008
WHERE child.id > :id_1
1010
Another effect of this change is that a joined-inheritance
1011
load across two tables will populate from the child table's
1012
value, not that of the parent table. An unusual case is that
1013
a query against "Parent" using ``with_polymorphic="*"``
1014
issues a query against "parent", with a LEFT OUTER JOIN to
1015
"child". The row is located in "Parent", sees the
1016
polymorphic identity corresponds to "Child", but suppose the
1017
actual row in "child" has been *deleted*. Due to this
1018
corruption, the row comes in with all the columns
1019
corresponding to "child" set to NULL - this is now the value
1020
that gets populated, not the one in the parent table.
1024
Mapping to joins with two or more same-named columns requires explicit declaration
1025
----------------------------------------------------------------------------------
1027
This is somewhat related to the previous change in
1028
:ticket:`1892`. When mapping to a join, same-named columns
1029
must be explicitly linked to mapped attributes, i.e. as
1030
described in `Mapping a Class Against Multiple Tables <http:
1031
//www.sqlalchemy.org/docs/07/orm/mapper_config.html#mapping-
1032
a-class-against-multiple-tables>`_.
1034
Given two tables ``foo`` and ``bar``, each with a primary
1035
key column ``id``, the following now produces an error:
1040
foobar = foo.join(bar, foo.c.id==bar.c.foo_id)
1041
mapper(FooBar, foobar)
1043
This because the ``mapper()`` refuses to guess what column
1044
is the primary representation of ``FooBar.id`` - is it
1045
``foo.c.id`` or is it ``bar.c.id`` ? The attribute must be
1051
foobar = foo.join(bar, foo.c.id==bar.c.foo_id)
1052
mapper(FooBar, foobar, properties={
1053
'id':[foo.c.id, bar.c.id]
1058
Mapper requires that polymorphic_on column be present in the mapped selectable
1059
------------------------------------------------------------------------------
1061
This is a warning in 0.6, now an error in 0.7. The column
1062
given for ``polymorphic_on`` must be in the mapped
1063
selectable. This to prevent some occasional user errors
1068
mapper(SomeClass, sometable, polymorphic_on=some_lookup_table.c.id)
1070
where above the polymorphic_on needs to be on a
1071
``sometable`` column, in this case perhaps
1072
``sometable.c.some_lookup_id``. There are also some
1073
"polymorphic union" scenarios where similar mistakes
1076
Such a configuration error has always been "wrong", and the
1077
above mapping doesn't work as specified - the column would
1078
be ignored. It is however potentially backwards
1079
incompatible in the rare case that an application has been
1080
unknowingly relying upon this behavior.
1084
``DDL()`` constructs now escape percent signs
1085
---------------------------------------------
1087
Previously, percent signs in ``DDL()`` strings would have to
1088
be escaped, i.e. ``%%`` depending on DBAPI, for those DBAPIs
1089
that accept ``pyformat`` or ``format`` binds (i.e. psycopg2,
1090
mysql-python), which was inconsistent versus ``text()``
1091
constructs which did this automatically. The same escaping
1092
now occurs for ``DDL()`` as for ``text()``.
1096
``Table.c`` / ``MetaData.tables`` refined a bit, don't allow direct mutation
1097
----------------------------------------------------------------------------
1099
Another area where some users were tinkering around in such
1100
a way that doesn't actually work as expected, but still left
1101
an exceedingly small chance that some application was
1102
relying upon this behavior, the construct returned by the
1103
``.c`` attribute on ``Table`` and the ``.tables`` attribute
1104
on ``MetaData`` is explicitly non-mutable. The "mutable"
1105
version of the construct is now private. Adding columns to
1106
``.c`` involves using the ``append_column()`` method of
1107
``Table``, which ensures things are associated with the
1108
parent ``Table`` in the appropriate way; similarly,
1109
``MetaData.tables`` has a contract with the ``Table``
1110
objects stored in this dictionary, as well as a little bit
1111
of new bookkeeping in that a ``set()`` of all schema names
1112
is tracked, which is satisfied only by using the public
1113
``Table`` constructor as well as ``Table.tometadata()``.
1115
It is of course possible that the ``ColumnCollection`` and
1116
``dict`` collections consulted by these attributes could
1117
someday implement events on all of their mutational methods
1118
such that the appropriate bookkeeping occurred upon direct
1119
mutation of the collections, but until someone has the
1120
motivation to implement all that along with dozens of new
1121
unit tests, narrowing the paths to mutation of these
1122
collections will ensure no application is attempting to rely
1123
upon usages that are currently not supported.
1125
:ticket:`1893` :ticket:`1917`
1127
server_default consistently returns None for all inserted_primary_key values
1128
----------------------------------------------------------------------------
1130
Established consistency when server_default is present on an
1131
Integer PK column. SQLA doesn't pre-fetch these, nor do they
1132
come back in cursor.lastrowid (DBAPI). Ensured all backends
1133
consistently return None in result.inserted_primary_key for
1134
these - some backends may have returned a value previously.
1135
Using a server_default on a primary key column is extremely
1136
unusual. If a special function or SQL expression is used
1137
to generate primary key defaults, this should be established
1138
as a Python-side "default" instead of server_default.
1140
Regarding reflection for this case, reflection of an int PK
1141
col with a server_default sets the "autoincrement" flag to
1142
False, except in the case of a PG SERIAL col where we
1143
detected a sequence default.
1145
:ticket:`2020` :ticket:`2021`
1147
The ``sqlalchemy.exceptions`` alias in sys.modules is removed
1148
-------------------------------------------------------------
1150
For a few years we've added the string
1151
``sqlalchemy.exceptions`` to ``sys.modules``, so that a
1152
statement like "``import sqlalchemy.exceptions``" would
1153
work. The name of the core exceptions module has been
1154
``exc`` for a long time now, so the recommended import for
1159
from sqlalchemy import exc
1161
The ``exceptions`` name is still present in "``sqlalchemy``"
1162
for applications which might have said ``from sqlalchemy
1163
import exceptions``, but they should also start using the
1166
Query Timing Recipe Changes
1167
---------------------------
1169
While not part of SQLAlchemy itself, it's worth mentioning
1170
that the rework of the ``ConnectionProxy`` into the new
1171
event system means it is no longer appropriate for the
1172
"Timing all Queries" recipe. Please adjust query-timers to
1173
use the ``before_cursor_execute()`` and
1174
``after_cursor_execute()`` events, demonstrated in the
1175
updated recipe UsageRecipes/Profiling.
1180
Default constructor on types will not accept arguments
1181
------------------------------------------------------
1183
Simple types like ``Integer``, ``Date`` etc. in the core
1184
types module don't accept arguments. The default
1185
constructor that accepts/ignores a catchall ``\*args,
1186
\**kwargs`` is restored as of 0.7b4/0.7.0, but emits a
1187
deprecation warning.
1189
If arguments are being used with a core type like
1190
``Integer``, it may be that you intended to use a dialect
1191
specific type, such as ``sqlalchemy.dialects.mysql.INTEGER``
1192
which does accept a "display_width" argument for example.
1194
compile_mappers() renamed configure_mappers(), simplified configuration internals
1195
---------------------------------------------------------------------------------
1197
This system slowly morphed from something small, implemented
1198
local to an individual mapper, and poorly named into
1199
something that's more of a global "registry-" level function
1200
and poorly named, so we've fixed both by moving the
1201
implementation out of ``Mapper`` altogether and renaming it
1202
to ``configure_mappers()``. It is of course normally not
1203
needed for an application to call ``configure_mappers()`` as
1204
this process occurs on an as-needed basis, as soon as the
1205
mappings are needed via attribute or query access.
1209
Core listener/proxy superseded by event listeners
1210
-------------------------------------------------
1212
``PoolListener``, ``ConnectionProxy``,
1213
``DDLElement.execute_at`` are superseded by
1214
``event.listen()``, using the ``PoolEvents``,
1215
``EngineEvents``, ``DDLEvents`` dispatch targets,
1218
ORM extensions superseded by event listeners
1219
--------------------------------------------
1221
``MapperExtension``, ``AttributeExtension``,
1222
``SessionExtension`` are superseded by ``event.listen()``,
1223
using the ``MapperEvents``/``InstanceEvents``,
1224
``AttributeEvents``, ``SessionEvents``, dispatch targets,
1227
Sending a string to 'distinct' in select() for MySQL should be done via prefixes
1228
--------------------------------------------------------------------------------
1230
This obscure feature allows this pattern with the MySQL
1235
select([mytable], distinct='ALL', prefixes=['HIGH_PRIORITY'])
1237
The ``prefixes`` keyword or ``prefix_with()`` method should
1238
be used for non-standard or unusual prefixes:
1242
select([mytable]).prefix_with('HIGH_PRIORITY', 'ALL')
1244
``useexisting`` superseded by ``extend_existing`` and ``keep_existing``
1245
-----------------------------------------------------------------------
1247
The ``useexisting`` flag on Table has been superseded by a
1248
new pair of flags ``keep_existing`` and ``extend_existing``.
1249
``extend_existing`` is equivalent to ``useexisting`` - the
1250
existing Table is returned, and additional constructor
1251
elements are added. With ``keep_existing``, the existing
1252
Table is returned, but additional constructor elements are
1253
not added - these elements are only applied when the Table
1256
Backwards Incompatible API Changes
1257
==================================
1259
Callables passed to ``bindparam()`` don't get evaluated - affects the Beaker example
1260
------------------------------------------------------------------------------------
1264
Note this affects the Beaker caching example, where the
1265
workings of the ``_params_from_query()`` function needed a
1266
slight adjustment. If you're using code from the Beaker
1267
example, this change should be applied.
1269
types.type_map is now private, types._type_map
1270
----------------------------------------------
1272
We noticed some users tapping into this dictionary inside of
1273
``sqlalchemy.types`` as a shortcut to associating Python
1274
types with SQL types. We can't guarantee the contents or
1275
format of this dictionary, and additionally the business of
1276
associating Python types in a one-to-one fashion has some
1277
grey areas that should are best decided by individual
1278
applications, so we've underscored this attribute.
1282
Renamed the ``alias`` keyword arg of standalone ``alias()`` function to ``name``
1283
--------------------------------------------------------------------------------
1285
This so that the keyword argument ``name`` matches that of
1286
the ``alias()`` methods on all ``FromClause`` objects as
1287
well as the ``name`` argument on ``Query.subquery()``.
1289
Only code that uses the standalone ``alias()`` function, and
1290
not the method bound functions, and passes the alias name
1291
using the explicit keyword name ``alias``, and not
1292
positionally, would need modification here.
1294
Non-public ``Pool`` methods underscored
1295
---------------------------------------
1297
All methods of ``Pool`` and subclasses which are not
1298
intended for public use have been renamed with underscores.
1299
That they were not named this way previously was a bug.
1301
Pooling methods now underscored or removed:
1303
``Pool.create_connection()`` ->
1304
``Pool._create_connection()``
1306
``Pool.do_get()`` -> ``Pool._do_get()``
1308
``Pool.do_return_conn()`` -> ``Pool._do_return_conn()``
1310
``Pool.do_return_invalid()`` -> removed, was not used
1312
``Pool.return_conn()`` -> ``Pool._return_conn()``
1314
``Pool.get()`` -> ``Pool._get()``, public API is
1317
``SingletonThreadPool.cleanup()`` -> ``_cleanup()``
1319
``SingletonThreadPool.dispose_local()`` -> removed, use
1320
``conn.invalidate()``
1324
Previously Deprecated, Now Removed
1325
==================================
1327
Query.join(), Query.outerjoin(), eagerload(), eagerload_all(), others no longer allow lists of attributes as arguments
1328
----------------------------------------------------------------------------------------------------------------------
1330
Passing a list of attributes or attribute names to
1331
``Query.join``, ``eagerload()``, and similar has been
1332
deprecated since 0.5:
1336
# old way, deprecated since 0.5
1337
session.query(Houses).join([Houses.rooms, Room.closets])
1338
session.query(Houses).options(eagerload_all([Houses.rooms, Room.closets]))
1340
These methods all accept \*args as of the 0.5 series:
1344
# current way, in place since 0.5
1345
session.query(Houses).join(Houses.rooms, Room.closets)
1346
session.query(Houses).options(eagerload_all(Houses.rooms, Room.closets))
1348
``ScopedSession.mapper`` is removed
1349
-----------------------------------
1351
This feature provided a mapper extension which linked class-
1352
based functionality with a particular ``ScopedSession``, in
1353
particular providing the behavior such that new object
1354
instances would be automatically associated with that
1355
session. The feature was overused by tutorials and
1356
frameworks which led to great user confusion due to its
1357
implicit behavior, and was deprecated in 0.5.5. Techniques
1358
for replicating its functionality are at
1359
[wiki:UsageRecipes/SessionAwareMapper]
added
removed
doc/_sources/changelog/migration_07.txt
