1
==============================
2
What's New in SQLAlchemy 0.6?
3
==============================
5
.. admonition:: About this Document
7
This document describes changes between SQLAlchemy version 0.5,
8
last released January 16, 2010, and SQLAlchemy version 0.6,
9
last released May 5, 2012.
11
Document date: June 6, 2010
13
This guide documents API changes which affect users
14
migrating their applications from the 0.5 series of
15
SQLAlchemy to 0.6. Note that SQLAlchemy 0.6 removes some
16
behaviors which were deprecated throughout the span of the
17
0.5 series, and also deprecates more behaviors specific to
23
* cPython versions 2.4 and upwards throughout the 2.xx
26
* Jython 2.5.1 - using the zxJDBC DBAPI included with
29
* cPython 3.x - see [source:sqlalchemy/trunk/README.py3k]
30
for information on how to build for python3.
35
Dialect modules are now broken up into distinct
36
subcomponents, within the scope of a single database
37
backend. Dialect implementations are now in the
38
``sqlalchemy.dialects`` package. The
39
``sqlalchemy.databases`` package still exists as a
40
placeholder to provide some level of backwards compatibility
43
For each supported database, a sub-package exists within
44
``sqlalchemy.dialects`` where several files are contained.
45
Each package contains a module called ``base.py`` which
46
defines the specific SQL dialect used by that database. It
47
also contains one or more "driver" modules, each one
48
corresponding to a specific DBAPI - these files are named
49
corresponding to the DBAPI itself, such as ``pysqlite``,
50
``cx_oracle``, or ``pyodbc``. The classes used by
51
SQLAlchemy dialects are first declared in the ``base.py``
52
module, defining all behavioral characteristics defined by
53
the database. These include capability mappings, such as
54
"supports sequences", "supports returning", etc., type
55
definitions, and SQL compilation rules. Each "driver"
56
module in turn provides subclasses of those classes as
57
needed which override the default behavior to accommodate
58
the additional features, behaviors, and quirks of that
59
DBAPI. For DBAPIs that support multiple backends (pyodbc,
60
zxJDBC, mxODBC), the dialect module will use mixins from the
61
``sqlalchemy.connectors`` package, which provide
62
functionality common to that DBAPI across all backends, most
63
typically dealing with connect arguments. This means that
64
connecting using pyodbc, zxJDBC or mxODBC (when implemented)
65
is extremely consistent across supported backends.
67
The URL format used by ``create_engine()`` has been enhanced
68
to handle any number of DBAPIs for a particular backend,
69
using a scheme that is inspired by that of JDBC. The
70
previous format still works, and will select a "default"
71
DBAPI implementation, such as the Postgresql URL below that
76
create_engine('postgresql://scott:tiger@localhost/test')
78
However to specify a specific DBAPI backend such as pg8000,
79
add it to the "protocol" section of the URL using a plus
84
create_engine('postgresql+pg8000://scott:tiger@localhost/test')
86
Important Dialect Links:
88
* Documentation on connect arguments:
89
http://www.sqlalchemy.org/docs/06/dbengine.html#create-
92
* Reference documentation for individual dialects: http://ww
93
w.sqlalchemy.org/docs/06/reference/dialects/index.html
95
* The tips and tricks at DatabaseNotes.
98
Other notes regarding dialects:
100
* the type system has been changed dramatically in
101
SQLAlchemy 0.6. This has an impact on all dialects
102
regarding naming conventions, behaviors, and
103
implementations. See the section on "Types" below.
105
* the ``ResultProxy`` object now offers a 2x speed
106
improvement in some cases thanks to some refactorings.
108
* the ``RowProxy``, i.e. individual result row object, is
109
now directly pickleable.
111
* the setuptools entrypoint used to locate external dialects
112
is now called ``sqlalchemy.dialects``. An external
113
dialect written against 0.4 or 0.5 will need to be
114
modified to work with 0.6 in any case so this change does
115
not add any additional difficulties.
117
* dialects now receive an initialize() event on initial
118
connection to determine connection properties.
120
* Functions and operators generated by the compiler now use
121
(almost) regular dispatch functions of the form
122
"visit_<opname>" and "visit_<funcname>_fn" to provide
123
customed processing. This replaces the need to copy the
124
"functions" and "operators" dictionaries in compiler
125
subclasses with straightforward visitor methods, and also
126
allows compiler subclasses complete control over
127
rendering, as the full _Function or _BinaryExpression
133
The import structure of dialects has changed. Each dialect
134
now exports its base "dialect" class as well as the full set
135
of SQL types supported on that dialect via
136
``sqlalchemy.dialects.<name>``. For example, to import a
141
from sqlalchemy.dialects.postgresql import INTEGER, BIGINT, SMALLINT,\
142
VARCHAR, MACADDR, DATE, BYTEA
144
Above, ``INTEGER`` is actually the plain ``INTEGER`` type
145
from ``sqlalchemy.types``, but the PG dialect makes it
146
available in the same way as those types which are specific
147
to PG, such as ``BYTEA`` and ``MACADDR``.
149
Expression Language Changes
150
===========================
152
An Important Expression Language Gotcha
153
---------------------------------------
155
There's one quite significant behavioral change to the
156
expression language which may affect some applications.
157
The boolean value of Python boolean expressions, i.e.
158
``==``, ``!=``, and similar, now evaluates accurately with
159
regards to the two clause objects being compared.
161
As we know, comparing a ``ClauseElement`` to any other
162
object returns another ``ClauseElement``:
166
>>> from sqlalchemy.sql import column
167
>>> column('foo') == 5
168
<sqlalchemy.sql.expression._BinaryExpression object at 0x1252490>
170
This so that Python expressions produce SQL expressions when
171
converted to strings:
175
>>> str(column('foo') == 5)
178
But what happens if we say this?
182
>>> if column('foo') == 5:
186
In previous versions of SQLAlchemy, the returned
187
``_BinaryExpression`` was a plain Python object which
188
evaluated to ``True``. Now it evaluates to whether or not
189
the actual ``ClauseElement`` should have the same hash value
190
as to that being compared. Meaning:
194
>>> bool(column('foo') == 5)
196
>>> bool(column('foo') == column('foo'))
198
>>> c = column('foo')
203
That means code such as the following:
208
print "the expression is:", expression
210
Would not evaluate if ``expression`` was a binary clause.
211
Since the above pattern should never be used, the base
212
``ClauseElement`` now raises an exception if called in a
218
Traceback (most recent call last):
219
File "<stdin>", line 1, in <module>
221
raise TypeError("Boolean value of this clause is not defined")
222
TypeError: Boolean value of this clause is not defined
224
Code that wants to check for the presence of a
225
``ClauseElement`` expression should instead say:
229
if expression is not None:
230
print "the expression is:", expression
232
Keep in mind, **this applies to Table and Column objects
235
The rationale for the change is twofold:
237
* Comparisons of the form ``if c1 == c2: <do something>``
238
can actually be written now
240
* Support for correct hashing of ``ClauseElement`` objects
241
now works on alternate platforms, namely Jython. Up until
242
this point SQLAlchemy relied heavily on the specific
243
behavior of cPython in this regard (and still had
244
occasional problems with it).
246
Stricter "executemany" Behavior
247
-------------------------------
249
An "executemany" in SQLAlchemy corresponds to a call to
250
``execute()``, passing along a collection of bind parameter
255
connection.execute(table.insert(), {'data':'row1'}, {'data':'row2'}, {'data':'row3'})
257
When the ``Connection`` object sends off the given
258
``insert()`` construct for compilation, it passes to the
259
compiler the keynames present in the first set of binds
260
passed along to determine the construction of the
261
statement's VALUES clause. Users familiar with this
262
construct will know that additional keys present in the
263
remaining dictionaries don't have any impact. What's
264
different now is that all subsequent dictionaries need to
265
include at least *every* key that is present in the first
266
dictionary. This means that a call like this no longer
271
connection.execute(table.insert(),
272
{'timestamp':today, 'data':'row1'},
273
{'timestamp':today, 'data':'row2'},
276
Because the third row does not specify the 'timestamp'
277
column. Previous versions of SQLAlchemy would simply insert
278
NULL for these missing columns. However, if the
279
``timestamp`` column in the above example contained a
280
Python-side default value or function, it would *not* be
281
used. This because the "executemany" operation is optimized
282
for maximum performance across huge numbers of parameter
283
sets, and does not attempt to evaluate Python-side defaults
284
for those missing keys. Because defaults are often
285
implemented either as SQL expressions which are embedded
286
inline with the INSERT statement, or are server side
287
expressions which again are triggered based on the structure
288
of the INSERT string, which by definition cannot fire off
289
conditionally based on each parameter set, it would be
290
inconsistent for Python side defaults to behave differently
291
vs. SQL/server side defaults. (SQL expression based
292
defaults are embedded inline as of the 0.5 series, again to
293
minimize the impact of huge numbers of parameter sets).
295
SQLAlchemy 0.6 therefore establishes predictable consistency
296
by forbidding any subsequent parameter sets from leaving any
297
fields blank. That way, there's no more silent failure of
298
Python side default values and functions, which additionally
299
are allowed to remain consistent in their behavior versus
300
SQL and server side defaults.
302
UNION and other "compound" constructs parenthesize consistently
303
---------------------------------------------------------------
305
A rule that was designed to help SQLite has been removed,
306
that of the first compound element within another compound
307
(such as, a ``union()`` inside of an ``except_()``) wouldn't
308
be parenthesized. This is inconsistent and produces the
309
wrong results on Postgresql, which has precedence rules
310
regarding INTERSECTION, and its generally a surprise. When
311
using complex composites with SQLite, you now need to turn
312
the first element into a subquery (which is also compatible
313
on PG). A new example is in the SQL expression tutorial at
315
[http://www.sqlalchemy.org/docs/06/sqlexpression.html
316
#unions-and-other-set-operations]. See :ticket:`1665` and
317
r6690 for more background.
319
C Extensions for Result Fetching
320
================================
322
The ``ResultProxy`` and related elements, including most
323
common "row processing" functions such as unicode
324
conversion, numerical/boolean conversions and date parsing,
325
have been re-implemented as optional C extensions for the
326
purposes of performance. This represents the beginning of
327
SQLAlchemy's path to the "dark side" where we hope to
328
continue improving performance by reimplementing critical
329
sections in C. The extensions can be built by specifying
330
``--with-cextensions``, i.e. ``python setup.py --with-
331
cextensions install``.
333
The extensions have the most dramatic impact on result
334
fetching using direct ``ResultProxy`` access, i.e. that
335
which is returned by ``engine.execute()``,
336
``connection.execute()``, or ``session.execute()``. Within
337
results returned by an ORM ``Query`` object, result fetching
338
is not as high a percentage of overhead, so ORM performance
339
improves more modestly, and mostly in the realm of fetching
340
large result sets. The performance improvements highly
341
depend on the dbapi in use and on the syntax used to access
342
the columns of each row (eg ``row['name']`` is much faster
343
than ``row.name``). The current extensions have no impact
344
on the speed of inserts/updates/deletes, nor do they improve
345
the latency of SQL execution, that is, an application that
346
spends most of its time executing many statements with very
347
small result sets will not see much improvement.
349
Performance has been improved in 0.6 versus 0.5 regardless
350
of the extensions. A quick overview of what connecting and
351
fetching 50,000 rows looks like with SQLite, using mostly
352
direct SQLite access, a ``ResultProxy``, and a simple mapped
357
sqlite select/native: 0.260s
361
sqlalchemy.sql select: 0.360s
362
sqlalchemy.orm fetch: 2.500s
366
sqlalchemy.sql select: 0.600s
367
sqlalchemy.orm fetch: 3.000s
371
sqlalchemy.sql select: 0.790s
372
sqlalchemy.orm fetch: 4.030s
374
Above, the ORM fetches the rows 33% faster than 0.5 due to
375
in-python performance enhancements. With the C extensions
376
we get another 20%. However, ``ResultProxy`` fetches
377
improve by 67% with the C extension versus not. Other
378
tests report as much as a 200% speed improvement for some
379
scenarios, such as those where lots of string conversions
382
New Schema Capabilities
383
=======================
385
The ``sqlalchemy.schema`` package has received some long-
386
needed attention. The most visible change is the newly
387
expanded DDL system. In SQLAlchemy, it was possible since
388
version 0.5 to create custom DDL strings and associate them
389
with tables or metadata objects:
393
from sqlalchemy.schema import DDL
395
DDL('CREATE TRIGGER users_trigger ...').execute_at('after-create', metadata)
397
Now the full suite of DDL constructs are available under the
398
same system, including those for CREATE TABLE, ADD
403
from sqlalchemy.schema import Constraint, AddConstraint
405
AddContraint(CheckConstraint("value > 5")).execute_at('after-create', mytable)
407
Additionally, all the DDL objects are now regular
408
``ClauseElement`` objects just like any other SQLAlchemy
413
from sqlalchemy.schema import CreateTable
415
create = CreateTable(mytable)
417
# dumps the CREATE TABLE as a string
420
# executes the CREATE TABLE statement
421
engine.execute(create)
423
and using the ``sqlalchemy.ext.compiler`` extension you can
428
from sqlalchemy.schema import DDLElement
429
from sqlalchemy.ext.compiler import compiles
431
class AlterColumn(DDLElement):
433
def __init__(self, column, cmd):
437
@compiles(AlterColumn)
438
def visit_alter_column(element, compiler, **kw):
439
return "ALTER TABLE %s ALTER COLUMN %s %s ..." % (
440
element.column.table.name,
445
engine.execute(AlterColumn(table.c.mycolumn, "SET DEFAULT 'test'"))
447
Deprecated/Removed Schema Elements
448
----------------------------------
450
The schema package has also been greatly streamlined. Many
451
options and methods which were deprecated throughout 0.5
452
have been removed. Other little known accessors and methods
453
have also been removed.
455
* the "owner" keyword argument is removed from ``Table``.
456
Use "schema" to represent any namespaces to be prepended
459
* deprecated ``MetaData.connect()`` and
460
``ThreadLocalMetaData.connect()`` have been removed - send
461
the "bind" attribute to bind a metadata.
463
* deprecated metadata.table_iterator() method removed (use
466
* the "metadata" argument is removed from
467
``DefaultGenerator`` and subclasses, but remains locally
468
present on ``Sequence``, which is a standalone construct
471
* deprecated ``PassiveDefault`` - use ``DefaultClause``.
474
* Removed public mutability from ``Index`` and
475
``Constraint`` objects:
477
* ``ForeignKeyConstraint.append_element()``
480
* ``Index.append_column()``
483
* ``UniqueConstraint.append_column()``
486
* ``PrimaryKeyConstraint.add()``
489
* ``PrimaryKeyConstraint.remove()``
492
These should be constructed declaratively (i.e. in one
495
* Other removed things:
498
* ``Table.key`` (no idea what this was for)
501
* ``Column.bind`` (get via column.table.bind)
504
* ``Column.metadata`` (get via column.table.metadata)
507
* ``Column.sequence`` (use column.default)
510
Other Behavioral Changes
511
------------------------
513
* ``UniqueConstraint``, ``Index``, ``PrimaryKeyConstraint``
514
all accept lists of column names or column objects as
517
* The ``use_alter`` flag on ``ForeignKey`` is now a shortcut
518
option for operations that can be hand-constructed using
519
the ``DDL()`` event system. A side effect of this refactor
520
is that ``ForeignKeyConstraint`` objects with
521
``use_alter=True`` will *not* be emitted on SQLite, which
522
does not support ALTER for foreign keys. This has no
523
effect on SQLite's behavior since SQLite does not actually
524
honor FOREIGN KEY constraints.
526
* ``Table.primary_key`` is not assignable - use
527
``table.append_constraint(PrimaryKeyConstraint(...))``
529
* A ``Column`` definition with a ``ForeignKey`` and no type,
530
e.g. ``Column(name, ForeignKey(sometable.c.somecol))``
531
used to get the type of the referenced column. Now support
532
for that automatic type inference is partial and may not
538
At the expense of a few extra method calls here and there,
539
you can set log levels for INFO and DEBUG after an engine,
540
pool, or mapper has been created, and logging will commence.
541
The ``isEnabledFor(INFO)`` method is now called
542
per-``Connection`` and ``isEnabledFor(DEBUG)``
543
per-``ResultProxy`` if already enabled on the parent
544
connection. Pool logging sends to ``log.info()`` and
545
``log.debug()`` with no check - note that pool
546
checkout/checkin is typically once per transaction.
548
Reflection/Inspector API
549
========================
551
The reflection system, which allows reflection of table
552
columns via ``Table('sometable', metadata, autoload=True)``
553
has been opened up into its own fine-grained API, which
554
allows direct inspection of database elements such as
555
tables, columns, constraints, indexes, and more. This API
556
expresses return values as simple lists of strings,
557
dictionaries, and ``TypeEngine`` objects. The internals of
558
``autoload=True`` now build upon this system such that the
559
translation of raw database information into
560
``sqlalchemy.schema`` constructs is centralized and the
561
contract of individual dialects greatly simplified, vastly
562
reducing bugs and inconsistencies across different backends.
568
from sqlalchemy.engine.reflection import Inspector
569
insp = Inspector.from_engine(my_engine)
571
print insp.get_schema_names()
573
the ``from_engine()`` method will in some cases provide a
574
backend-specific inspector with additional capabilities,
575
such as that of Postgresql which provides a
576
``get_table_oid()`` method:
581
my_engine = create_engine('postgresql://...')
582
pg_insp = Inspector.from_engine(my_engine)
584
print pg_insp.get_table_oid('my_table')
589
The ``insert()``, ``update()`` and ``delete()`` constructs
590
now support a ``returning()`` method, which corresponds to
591
the SQL RETURNING clause as supported by Postgresql, Oracle,
592
MS-SQL, and Firebird. It is not supported for any other
593
backend at this time.
595
Given a list of column expressions in the same manner as
596
that of a ``select()`` construct, the values of these
597
columns will be returned as a regular result set:
602
result = connection.execute(
603
table.insert().values(data='some data').returning(table.c.id, table.c.timestamp)
606
print "ID:", row['id'], "Timestamp:", row['timestamp']
608
The implementation of RETURNING across the four supported
609
backends varies wildly, in the case of Oracle requiring an
610
intricate usage of OUT parameters which are re-routed into a
611
"mock" result set, and in the case of MS-SQL using an
612
awkward SQL syntax. The usage of RETURNING is subject to
615
* it does not work for any "executemany()" style of
616
execution. This is a limitation of all supported DBAPIs.
618
* Some backends, such as Oracle, only support RETURNING that
619
returns a single row - this includes UPDATE and DELETE
620
statements, meaning the update() or delete() construct
621
must match only a single row, or an error is raised (by
622
Oracle, not SQLAlchemy).
624
RETURNING is also used automatically by SQLAlchemy, when
625
available and when not otherwise specified by an explicit
626
``returning()`` call, to fetch the value of newly generated
627
primary key values for single-row INSERT statements. This
628
means there's no more "SELECT nextval(sequence)" pre-
629
execution for insert statements where the primary key value
630
is required. Truth be told, implicit RETURNING feature
631
does incur more method overhead than the old "select
632
nextval()" system, which used a quick and dirty
633
cursor.execute() to get at the sequence value, and in the
634
case of Oracle requires additional binding of out
635
parameters. So if method/protocol overhead is proving to be
636
more expensive than additional database round trips, the
637
feature can be disabled by specifying
638
``implicit_returning=False`` to ``create_engine()``.
646
The type system has been completely reworked behind the
647
scenes to provide two goals:
649
* Separate the handling of bind parameters and result row
650
values, typically a DBAPI requirement, from the SQL
651
specification of the type itself, which is a database
652
requirement. This is consistent with the overall dialect
653
refactor that separates database SQL behavior from DBAPI.
655
* Establish a clear and consistent contract for generating
656
DDL from a ``TypeEngine`` object and for constructing
657
``TypeEngine`` objects based on column reflection.
659
Highlights of these changes include:
661
* The construction of types within dialects has been totally
662
overhauled. Dialects now define publically available types
663
as UPPERCASE names exclusively, and internal
664
implementation types using underscore identifiers (i.e.
665
are private). The system by which types are expressed in
666
SQL and DDL has been moved to the compiler system. This
667
has the effect that there are much fewer type objects
668
within most dialects. A detailed document on this
669
architecture for dialect authors is in [source:/lib/sqlalc
670
hemy/dialects/type_migration_guidelines.txt].
672
* Reflection of types now returns the exact UPPERCASE type
673
within types.py, or the UPPERCASE type within the dialect
674
itself if the type is not a standard SQL type. This means
675
reflection now returns more accurate information about
678
* User defined types that subclass ``TypeEngine`` and wish
679
to provide ``get_col_spec()`` should now subclass
682
* The ``result_processor()`` method on all type classes now
683
accepts an additional argument ``coltype``. This is the
684
DBAPI type object attached to cursor.description, and
685
should be used when applicable to make better decisions on
686
what kind of result-processing callable should be
687
returned. Ideally result processor functions would never
688
need to use ``isinstance()``, which is an expensive call
694
As more DBAPIs support returning Python unicode objects
695
directly, the base dialect now performs a check upon the
696
first connection which establishes whether or not the DBAPI
697
returns a Python unicode object for a basic select of a
698
VARCHAR value. If so, the ``String`` type and all
699
subclasses (i.e. ``Text``, ``Unicode``, etc.) will skip the
700
"unicode" check/conversion step when result rows are
701
received. This offers a dramatic performance increase for
702
large result sets. The "unicode mode" currently is known to
708
* psycopg2 - SQLA 0.6 now uses the "UNICODE" type extension
709
by default on each psycopg2 connection object
714
* cx_oracle (we use an output processor - nice feature !)
717
Other types may choose to disable unicode processing as
718
needed, such as the ``NVARCHAR`` type when used with MS-SQL.
720
In particular, if porting an application based on a DBAPI
721
that formerly returned non-unicode strings, the "native
722
unicode" mode has a plainly different default behavior -
723
columns that are declared as ``String`` or ``VARCHAR`` now
724
return unicode by default whereas they would return strings
725
before. This can break code which expects non-unicode
726
strings. The psycopg2 "native unicode" mode can be
727
disabled by passing ``use_native_unicode=False`` to
730
A more general solution for string columns that explicitly
731
do not want a unicode object is to use a ``TypeDecorator``
732
that converts unicode back to utf-8, or whatever is desired:
736
class UTF8Encoded(TypeDecorator):
737
"""Unicode type which coerces to utf-8."""
741
def process_result_value(self, value, dialect):
742
if isinstance(value, unicode):
743
value = value.encode('utf-8')
746
Note that the ``assert_unicode`` flag is now deprecated.
747
SQLAlchemy allows the DBAPI and backend database in use to
748
handle Unicode parameters when available, and does not add
749
operational overhead by checking the incoming type; modern
750
systems like sqlite and Postgresql will raise an encoding
751
error on their end if invalid data is passed. In those
752
cases where SQLAlchemy does need to coerce a bind parameter
753
from Python Unicode to an encoded string, or when the
754
Unicode type is used explicitly, a warning is raised if the
755
object is a bytestring. This warning can be suppressed or
756
converted to an exception using the Python warnings filter
757
documented at: http://docs.python.org/library/warnings.html
762
We now have an ``Enum`` in the ``types`` module. This is a
763
string type that is given a collection of "labels" which
764
constrain the possible values given to those labels. By
765
default, this type generates a ``VARCHAR`` using the size of
766
the largest label, and applies a CHECK constraint to the
767
table within the CREATE TABLE statement. When using MySQL,
768
the type by default uses MySQL's ENUM type, and when using
769
Postgresql the type will generate a user defined type using
770
``CREATE TYPE <mytype> AS ENUM``. In order to create the
771
type using Postgresql, the ``name`` parameter must be
772
specified to the constructor. The type also accepts a
773
``native_enum=False`` option which will issue the
774
VARCHAR/CHECK strategy for all databases. Note that
775
Postgresql ENUM types currently don't work with pg8000 or
778
Reflection Returns Dialect-Specific Types
779
-----------------------------------------
781
Reflection now returns the most specific type possible from
782
the database. That is, if you create a table using
783
``String``, then reflect it back, the reflected column will
784
likely be ``VARCHAR``. For dialects that support a more
785
specific form of the type, that's what you'll get. So a
786
``Text`` type would come back as ``oracle.CLOB`` on Oracle,
787
a ``LargeBinary`` might be an ``mysql.MEDIUMBLOB`` etc. The
788
obvious advantage here is that reflection preserves as much
789
information possible from what the database had to say.
791
Some applications that deal heavily in table metadata may
792
wish to compare types across reflected tables and/or non-
793
reflected tables. There's a semi-private accessor available
794
on ``TypeEngine`` called ``_type_affinity`` and an
795
associated comparison helper ``_compare_type_affinity``.
796
This accessor returns the "generic" ``types`` class which
797
the type corresponds to:
801
>>> String(50)._compare_type_affinity(postgresql.VARCHAR(50))
803
>>> Integer()._compare_type_affinity(mysql.REAL)
806
Miscellaneous API Changes
807
-------------------------
809
The usual "generic" types are still the general system in
810
use, i.e. ``String``, ``Float``, ``DateTime``. There's a
813
* Types no longer make any guesses as to default parameters.
814
In particular, ``Numeric``, ``Float``, as well as
815
subclasses NUMERIC, FLOAT, DECIMAL don't generate any
816
length or scale unless specified. This also continues to
817
include the controversial ``String`` and ``VARCHAR`` types
818
(although MySQL dialect will pre-emptively raise when
819
asked to render VARCHAR with no length). No defaults are
820
assumed, and if they are used in a CREATE TABLE statement,
821
an error will be raised if the underlying database does
822
not allow non-lengthed versions of these types.
824
* the ``Binary`` type has been renamed to ``LargeBinary``,
825
for BLOB/BYTEA/similar types. For ``BINARY`` and
826
``VARBINARY``, those are present directly as
827
``types.BINARY``, ``types.VARBINARY``, as well as in the
828
MySQL and MS-SQL dialects.
830
* ``PickleType`` now uses == for comparison of values when
831
mutable=True, unless the "comparator" argument with a
832
comparison function is specified to the type. If you are
833
pickling a custom object you should implement an
834
``__eq__()`` method so that value-based comparisons are
837
* The default "precision" and "scale" arguments of Numeric
838
and Float have been removed and now default to None.
839
NUMERIC and FLOAT will be rendered with no numeric
840
arguments by default unless these values are provided.
842
* DATE, TIME and DATETIME types on SQLite can now take
843
optional "storage_format" and "regexp" argument.
844
"storage_format" can be used to store those types using a
845
custom string format. "regexp" allows to use a custom
846
regular expression to match string values from the
849
* ``__legacy_microseconds__`` on SQLite ``Time`` and
850
``DateTime`` types is not supported anymore. You should
851
use the new "storage_format" argument instead.
853
* ``DateTime`` types on SQLite now use by a default a
854
stricter regular expression to match strings from the
855
database. Use the new "regexp" argument if you are using
856
data stored in a legacy format.
861
Upgrading an ORM application from 0.5 to 0.6 should require
862
little to no changes, as the ORM's behavior remains almost
863
identical. There are some default argument and name
864
changes, and some loading behaviors have been improved.
869
The internals for the unit of work, primarily
870
``topological.py`` and ``unitofwork.py``, have been
871
completely rewritten and are vastly simplified. This
872
should have no impact on usage, as all existing behavior
873
during flush has been maintained exactly (or at least, as
874
far as it is exercised by our testsuite and the handful of
875
production environments which have tested it heavily). The
876
performance of flush() now uses 20-30% fewer method calls
877
and should also use less memory. The intent and flow of the
878
source code should now be reasonably easy to follow, and the
879
architecture of the flush is fairly open-ended at this
880
point, creating room for potential new areas of
881
sophistication. The flush process no longer has any
882
reliance on recursion so flush plans of arbitrary size and
883
complexity can be flushed. Additionally, the mapper's
884
"save" process, which issues INSERT and UPDATE statements,
885
now caches the "compiled" form of the two statements so that
886
callcounts are further dramatically reduced with very large
889
Any changes in behavior observed with flush versus earlier
890
versions of 0.6 or 0.5 should be reported to us ASAP - we'll
891
make sure no functionality is lost.
893
Changes to ``query.update()`` and ``query.delete()``
894
----------------------------------------------------
896
* the 'expire' option on query.update() has been renamed to
897
'fetch', thus matching that of query.delete()
899
* ``query.update()`` and ``query.delete()`` both default to
900
'evaluate' for the synchronize strategy.
902
* the 'synchronize' strategy for update() and delete()
903
raises an error on failure. There is no implicit fallback
904
onto "fetch". Failure of evaluation is based on the
905
structure of criteria, so success/failure is deterministic
906
based on code structure.
908
``relation()`` is officially named ``relationship()``
909
-----------------------------------------------------
911
This to solve the long running issue that "relation" means a
912
"table or derived table" in relational algebra terms. The
913
``relation()`` name, which is less typing, will hang around
914
for the foreseeable future so this change should be entirely
917
Subquery eager loading
918
----------------------
920
A new kind of eager loading is added called "subquery"
921
loading. This is a load that emits a second SQL query
922
immediately after the first which loads full collections for
923
all the parents in the first query, joining upwards to the
924
parent using INNER JOIN. Subquery loading is used simlarly
925
to the current joined-eager loading, using the
926
```subqueryload()```` and ````subqueryload_all()```` options
927
as well as the ````lazy='subquery'```` setting on
928
````relationship()```. The subquery load is usually much
929
more efficient for loading many larger collections as it
930
uses INNER JOIN unconditionally and also doesn't re-load
933
```eagerload()````, ````eagerload_all()```` is now ````joinedload()````, ````joinedload_all()```
934
------------------------------------------------------------------------------------------------
936
To make room for the new subquery load feature, the existing
937
```eagerload()````/````eagerload_all()```` options are now
938
superceded by ````joinedload()```` and
939
````joinedload_all()````. The old names will hang around
940
for the foreseeable future just like ````relation()```.
942
```lazy=False|None|True|'dynamic'```` now accepts ````lazy='noload'|'joined'|'subquery'|'select'|'dynamic'```
943
-------------------------------------------------------------------------------------------------------------
945
Continuing on the theme of loader strategies opened up, the
946
standard keywords for the ```lazy```` option on
947
````relationship()```` are now ````select```` for lazy
948
loading (via a SELECT issued on attribute access),
949
````joined```` for joined-eager loading, ````subquery````
950
for subquery-eager loading, ````noload```` for no loading
951
should occur, and ````dynamic```` for a "dynamic"
952
relationship. The old ````True````, ````False````,
953
````None``` arguments are still accepted with the identical
956
innerjoin=True on relation, joinedload
957
--------------------------------------
959
Joined-eagerly loaded scalars and collections can now be
960
instructed to use INNER JOIN instead of OUTER JOIN. On
961
Postgresql this is observed to provide a 300-600% speedup on
962
some queries. Set this flag for any many-to-one which is
963
on a NOT NULLable foreign key, and similarly for any
964
collection where related items are guaranteed to exist.
971
mapper(Parent, parent, properties={
972
'child':relationship(Child, lazy='joined', innerjoin=True)
979
session.query(Parent).options(joinedload(Parent.child, innerjoin=True)).all()
981
The ``innerjoin=True`` flag at the ``relationship()`` level
982
will also take effect for any ``joinedload()`` option which
983
does not override the value.
985
Many-to-one Enhancements
986
------------------------
988
* many-to-one relations now fire off a lazyload in fewer
989
cases, including in most cases will not fetch the "old"
990
value when a new one is replaced.
992
* many-to-one relation to a joined-table subclass now uses
993
get() for a simple load (known as the "use_get"
994
condition), i.e. ``Related``->``Sub(Base)``, without the
995
need to redefine the primaryjoin condition in terms of the
996
base table. [ticket:1186]
998
* specifying a foreign key with a declarative column, i.e.
999
``ForeignKey(MyRelatedClass.id)`` doesn't break the
1000
"use_get" condition from taking place [ticket:1492]
1002
* relationship(), joinedload(), and joinedload_all() now
1003
feature an option called "innerjoin". Specify ``True`` or
1004
``False`` to control whether an eager join is constructed
1005
as an INNER or OUTER join. Default is ``False`` as always.
1006
The mapper options will override whichever setting is
1007
specified on relationship(). Should generally be set for
1008
many-to-one, not nullable foreign key relations to allow
1009
improved join performance. [ticket:1544]
1011
* the behavior of joined eager loading such that the main
1012
query is wrapped in a subquery when LIMIT/OFFSET are
1013
present now makes an exception for the case when all eager
1014
loads are many-to-one joins. In those cases, the eager
1015
joins are against the parent table directly along with the
1016
limit/offset without the extra overhead of a subquery,
1017
since a many-to-one join does not add rows to the result.
1019
For example, in 0.5 this query:
1023
session.query(Address).options(eagerload(Address.user)).limit(10)
1025
would produce SQL like:
1030
(SELECT * FROM addresses LIMIT 10) AS anon_1
1031
LEFT OUTER JOIN users AS users_1 ON users_1.id = anon_1.addresses_user_id
1033
This because the presence of any eager loaders suggests
1034
that some or all of them may relate to multi-row
1035
collections, which would necessitate wrapping any kind of
1036
rowcount-sensitive modifiers like LIMIT inside of a
1039
In 0.6, that logic is more sensitive and can detect if all
1040
eager loaders represent many-to-ones, in which case the
1041
eager joins don't affect the rowcount:
1045
SELECT * FROM addresses LEFT OUTER JOIN users AS users_1 ON users_1.id = addresses.user_id LIMIT 10
1047
Mutable Primary Keys with Joined Table Inheritance
1048
--------------------------------------------------
1050
A joined table inheritance config where the child table has
1051
a PK that foreign keys to the parent PK can now be updated
1052
on a CASCADE-capable database like Postgresql.
1053
``mapper()`` now has an option ``passive_updates=True``
1054
which indicates this foreign key is updated automatically.
1055
If on a non-cascading database like SQLite or MySQL/MyISAM,
1056
set this flag to ``False``. A future feature enhancement
1057
will try to get this flag to be auto-configuring based on
1058
dialect/table style in use.
1063
A promising new example of Beaker integration is in
1064
``examples/beaker_caching``. This is a straightforward
1065
recipe which applies a Beaker cache within the result-
1066
generation engine of ``Query``. Cache parameters are
1067
provided via ``query.options()``, and allows full control
1068
over the contents of the cache. SQLAlchemy 0.6 includes
1069
improvements to the ``Session.merge()`` method to support
1070
this and similar recipes, as well as to provide
1071
significantly improved performance in most scenarios.
1076
* the "row tuple" object returned by ``Query`` when multiple
1077
column/entities are selected is now picklable as well as
1080
* ``query.join()`` has been reworked to provide more
1081
consistent behavior and more flexibility (includes
1084
* ``query.select_from()`` accepts multiple clauses to
1085
produce multiple comma separated entries within the FROM
1086
clause. Useful when selecting from multiple-homed join()
1089
* the "dont_load=True" flag on ``Session.merge()`` is
1090
deprecated and is now "load=False".
1092
* added "make_transient()" helper function which transforms
1093
a persistent/ detached instance into a transient one (i.e.
1094
deletes the instance_key and removes from any session.)
1097
* the allow_null_pks flag on mapper() is deprecated and has
1098
been renamed to allow_partial_pks. It is turned "on" by
1099
default. This means that a row which has a non-null value
1100
for any of its primary key columns will be considered an
1101
identity. The need for this scenario typically only occurs
1102
when mapping to an outer join. When set to False, a PK
1103
that has NULLs in it will not be considered a primary key
1104
- in particular this means a result row will come back as
1105
None (or not be filled into a collection), and new in 0.6
1106
also indicates that session.merge() won't issue a round
1107
trip to the database for such a PK value. [ticket:1680]
1109
* the mechanics of "backref" have been fully merged into the
1110
finer grained "back_populates" system, and take place
1111
entirely within the ``_generate_backref()`` method of
1112
``RelationProperty``. This makes the initialization
1113
procedure of ``RelationProperty`` simpler and allows
1114
easier propagation of settings (such as from subclasses of
1115
``RelationProperty``) into the reverse reference. The
1116
internal ``BackRef()`` is gone and ``backref()`` returns a
1117
plain tuple that is understood by ``RelationProperty``.
1119
* the keys attribute of ``ResultProxy`` is now a method, so
1120
references to it (``result.keys``) must be changed to
1121
method invocations (``result.keys()``)
1123
* ``ResultProxy.last_inserted_ids`` is now deprecated, use
1124
``ResultProxy.inserted_primary_key`` instead.
1126
Deprecated/Removed ORM Elements
1127
-------------------------------
1129
Most elements that were deprecated throughout 0.5 and raised
1130
deprecation warnings have been removed (with a few
1131
exceptions). All elements that were marked "pending
1132
deprecation" are now deprecated and will raise a warning
1135
* 'transactional' flag on sessionmaker() and others is
1136
removed. Use 'autocommit=True' to indicate
1137
'transactional=False'.
1139
* 'polymorphic_fetch' argument on mapper() is removed.
1140
Loading can be controlled using the 'with_polymorphic'
1143
* 'select_table' argument on mapper() is removed. Use
1144
'with_polymorphic=("*", <some selectable>)' for this
1147
* 'proxy' argument on synonym() is removed. This flag did
1148
nothing throughout 0.5, as the "proxy generation"
1149
behavior is now automatic.
1151
* Passing a single list of elements to joinedload(),
1152
joinedload_all(), contains_eager(), lazyload(), defer(),
1153
and undefer() instead of multiple positional \*args is
1156
* Passing a single list of elements to query.order_by(),
1157
query.group_by(), query.join(), or query.outerjoin()
1158
instead of multiple positional \*args is deprecated.
1160
* ``query.iterate_instances()`` is removed. Use
1161
``query.instances()``.
1163
* ``Query.query_from_parent()`` is removed. Use the
1164
sqlalchemy.orm.with_parent() function to produce a
1165
"parent" clause, or alternatively ``query.with_parent()``.
1167
* ``query._from_self()`` is removed, use
1168
``query.from_self()`` instead.
1170
* the "comparator" argument to composite() is removed. Use
1171
"comparator_factory".
1173
* ``RelationProperty._get_join()`` is removed.
1176
* the 'echo_uow' flag on Session is removed. Use logging
1177
on the "sqlalchemy.orm.unitofwork" name.
1179
* ``session.clear()`` is removed. use
1180
``session.expunge_all()``.
1182
* ``session.save()``, ``session.update()``,
1183
``session.save_or_update()`` are removed. Use
1184
``session.add()`` and ``session.add_all()``.
1186
* the "objects" flag on session.flush() remains deprecated.
1189
* the "dont_load=True" flag on session.merge() is deprecated
1190
in favor of "load=False".
1192
* ``ScopedSession.mapper`` remains deprecated. See the
1193
usage recipe at http://www.sqlalchemy.org/trac/wiki/Usag
1194
eRecipes/SessionAwareMapper
1196
* passing an ``InstanceState`` (internal SQLAlchemy state
1197
object) to ``attributes.init_collection()`` or
1198
``attributes.get_history()`` is deprecated. These
1199
functions are public API and normally expect a regular
1200
mapped object instance.
1202
* the 'engine' parameter to ``declarative_base()`` is
1203
removed. Use the 'bind' keyword argument.
1211
SQLSoup has been modernized and updated to reflect common
1212
0.5/0.6 capabilities, including well defined session
1213
integration. Please read the new docs at [http://www.sqlalc
1214
hemy.org/docs/06/reference/ext/sqlsoup.html].
1219
The ``DeclarativeMeta`` (default metaclass for
1220
``declarative_base``) previously allowed subclasses to
1221
modify ``dict_`` to add class attributes (e.g. columns).
1222
This no longer works, the ``DeclarativeMeta`` constructor
1223
now ignores ``dict_``. Instead, the class attributes should
1224
be assigned directly, e.g. ``cls.id=Column(...)``, or the
1225
`MixIn class <http://www.sqlalchemy.org/docs/reference/ext/d
1226
eclarative.html#mix-in-classes>`_ approach should be used
1227
instead of the metaclass approach.
added
removed
doc/_sources/changelog/migration_06.txt
