1
=============================
2
What's new in SQLAlchemy 0.4?
3
=============================
5
.. admonition:: About this Document
7
This document describes changes between SQLAlchemy version 0.3,
8
last released October 14, 2007, and SQLAlchemy version 0.4,
9
last released October 12, 2008.
11
Document date: March 21, 2008
16
If you're using any ORM features, make sure you import from
21
from sqlalchemy import *
22
from sqlalchemy.orm import *
24
Secondly, anywhere you used to say ``engine=``,
25
``connectable=``, ``bind_to=``, ``something.engine``,
26
``metadata.connect()``, use ``bind``:
30
myengine = create_engine('sqlite://')
32
meta = MetaData(myengine)
37
session = create_session(bind=myengine)
39
statement = select([table], bind=myengine)
41
Got those ? Good! You're now (95%) 0.4 compatible. If
42
you're using 0.3.10, you can make these changes immediately;
43
they'll work there too.
48
In 0.3, "``from sqlachemy import *``" would import all of
49
sqlachemy's sub-modules into your namespace. Version 0.4 no
50
longer imports sub-modules into the namespace. This may mean
51
you need to add extra imports into your code.
53
In 0.3, this code worked:
57
from sqlalchemy import *
59
class UTCDateTime(types.TypeDecorator):
66
from sqlalchemy import *
67
from sqlalchemy import types
69
class UTCDateTime(types.TypeDecorator):
72
Object Relational Mapping
73
=========================
81
Query is standardized on the generative interface (old
82
interface is still there, just deprecated). While most of
83
the generative interface is available in 0.3, the 0.4 Query
84
has the inner guts to match the generative outside, and has
85
a lot more tricks. All result narrowing is via ``filter()``
86
and ``filter_by()``, limiting/offset is either through array
87
slices or ``limit()``/``offset()``, joining is via
88
``join()`` and ``outerjoin()`` (or more manually, through
89
``select_from()`` as well as manually-formed criteria).
91
To avoid deprecation warnings, you must make some changes to
94
User.query.get_by( \**kwargs )
98
User.query.filter_by(**kwargs).first()
100
User.query.select_by( \**kwargs )
104
User.query.filter_by(**kwargs).all()
110
User.query.filter(xxx).all()
112
New Property-Based Expression Constructs
113
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
115
By far the most palpable difference within the ORM is that
116
you can now construct your query criterion using class-based
117
attributes directly. The ".c." prefix is no longer needed
118
when working with mapped classes:
122
session.query(User).filter(and_(User.name == 'fred', User.id > 17))
124
While simple column-based comparisons are no big deal, the
125
class attributes have some new "higher level" constructs
126
available, including what was previously only available in
131
# comparison of scalar relations to an instance
132
filter(Address.user == user)
134
# return all users who contain a particular address
135
filter(User.addresses.contains(address))
137
# return all users who *dont* contain the address
138
filter(~User.address.contains(address))
140
# return all users who contain a particular address with
141
# the email_address like '%foo%'
142
filter(User.addresses.any(Address.email_address.like('%foo%')))
144
# same, email address equals 'foo@bar.com'. can fall back to keyword
145
# args for simple comparisons
146
filter(User.addresses.any(email_address = 'foo@bar.com'))
148
# return all Addresses whose user attribute has the username 'ed'
149
filter(Address.user.has(name='ed'))
151
# return all Addresses whose user attribute has the username 'ed'
152
# and an id > 5 (mixing clauses with kwargs)
153
filter(Address.user.has(User.id > 5, name='ed'))
155
The ``Column`` collection remains available on mapped
156
classes in the ``.c`` attribute. Note that property-based
157
expressions are only available with mapped properties of
158
mapped classes. ``.c`` is still used to access columns in
159
regular tables and selectable objects produced from SQL
162
Automatic Join Aliasing
163
^^^^^^^^^^^^^^^^^^^^^^^
165
We've had join() and outerjoin() for a while now:
169
session.query(Order).join('items')...
171
Now you can alias them:
175
session.query(Order).join('items', aliased=True).
176
filter(Item.name='item 1').join('items', aliased=True).filter(Item.name=='item 3')
178
The above will create two joins from orders->items using
179
aliases. the ``filter()`` call subsequent to each will
180
adjust its table criterion to that of the alias. To get at
181
the ``Item`` objects, use ``add_entity()`` and target each
186
session.query(Order).join('items', id='j1', aliased=True).
187
filter(Item.name == 'item 1').join('items', aliased=True, id='j2').
188
filter(Item.name == 'item 3').add_entity(Item, id='j1').add_entity(Item, id='j2')
190
Returns tuples in the form: ``(Order, Item, Item)``.
192
Self-referential Queries
193
^^^^^^^^^^^^^^^^^^^^^^^^
195
So query.join() can make aliases now. What does that give
196
us ? Self-referential queries ! Joins can be done without
197
any ``Alias`` objects:
201
# standard self-referential TreeNode mapper with backref
202
mapper(TreeNode, tree_nodes, properties={
203
'children':relation(TreeNode, backref=backref('parent', remote_side=tree_nodes.id))
206
# query for node with child containing "bar" two levels deep
207
session.query(TreeNode).join(["children", "children"], aliased=True).filter_by(name='bar')
209
To add criterion for each table along the way in an aliased
210
join, you can use ``from_joinpoint`` to keep joining against
211
the same line of aliases:
215
# search for the treenode along the path "n1/n12/n122"
217
# first find a Node with name="n122"
218
q = sess.query(Node).filter_by(name='n122')
220
# then join to parent with "n12"
221
q = q.join('parent', aliased=True).filter_by(name='n12')
223
# join again to the next parent with 'n1'. use 'from_joinpoint'
224
# so we join from the previous point, instead of joining off the
226
q = q.join('parent', aliased=True, from_joinpoint=True).filter_by(name='n1')
230
``query.populate_existing()``
231
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
233
The eager version of ``query.load()`` (or
234
``session.refresh()``). Every instance loaded from the
235
query, including all eagerly loaded items, get refreshed
236
immediately if already present in the session:
240
session.query(Blah).populate_existing().all()
245
SQL Clauses Embedded in Updates/Inserts
246
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
248
For inline execution of SQL clauses, embedded right in the
249
UPDATE or INSERT, during a ``flush()``:
254
myobject.foo = mytable.c.value + 1
256
user.pwhash = func.md5(password)
258
order.hash = text("select hash from hashing_table")
260
The column-attribute is set up with a deferred loader after
261
the operation, so that it issues the SQL to load the new
262
value when you next access.
264
Self-referential and Cyclical Eager Loading
265
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
267
Since our alias-fu has improved, ``relation()`` can join
268
along the same table \*any number of times*; you tell it how
269
deep you want to go. Lets show the self-referential
270
``TreeNode`` more clearly:
274
nodes = Table('nodes', metadata,
275
Column('id', Integer, primary_key=True),
276
Column('parent_id', Integer, ForeignKey('nodes.id')),
277
Column('name', String(30)))
279
class TreeNode(object):
282
mapper(TreeNode, nodes, properties={
283
'children':relation(TreeNode, lazy=False, join_depth=3)
286
So what happens when we say:
290
create_session().query(TreeNode).all()
292
? A join along aliases, three levels deep off the parent:
297
nodes_3.id AS nodes_3_id, nodes_3.parent_id AS nodes_3_parent_id, nodes_3.name AS nodes_3_name,
298
nodes_2.id AS nodes_2_id, nodes_2.parent_id AS nodes_2_parent_id, nodes_2.name AS nodes_2_name,
299
nodes_1.id AS nodes_1_id, nodes_1.parent_id AS nodes_1_parent_id, nodes_1.name AS nodes_1_name,
300
nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.name AS nodes_name
301
FROM nodes LEFT OUTER JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id
302
LEFT OUTER JOIN nodes AS nodes_2 ON nodes_1.id = nodes_2.parent_id
303
LEFT OUTER JOIN nodes AS nodes_3 ON nodes_2.id = nodes_3.parent_id
304
ORDER BY nodes.oid, nodes_1.oid, nodes_2.oid, nodes_3.oid
306
Notice the nice clean alias names too. The joining doesn't
307
care if it's against the same immediate table or some other
308
object which then cycles back to the beginining. Any kind
309
of chain of eager loads can cycle back onto itself when
310
``join_depth`` is specified. When not present, eager
311
loading automatically stops when it hits a cycle.
316
This is one from the Hibernate camp. Composite Types let
317
you define a custom datatype that is composed of more than
318
one column (or one column, if you wanted). Lets define a
319
new type, ``Point``. Stores an x/y coordinate:
324
def __init__(self, x, y):
327
def __composite_values__(self):
328
return self.x, self.y
329
def __eq__(self, other):
330
return other.x == self.x and other.y == self.y
331
def __ne__(self, other):
332
return not self.__eq__(other)
334
The way the ``Point`` object is defined is specific to a
335
custom type; constructor takes a list of arguments, and the
336
``__composite_values__()`` method produces a sequence of
337
those arguments. The order will match up to our mapper, as
338
we'll see in a moment.
340
Let's create a table of vertices storing two points per row:
344
vertices = Table('vertices', metadata,
345
Column('id', Integer, primary_key=True),
346
Column('x1', Integer),
347
Column('y1', Integer),
348
Column('x2', Integer),
349
Column('y2', Integer),
352
Then, map it ! We'll create a ``Vertex`` object which
353
stores two ``Point`` objects:
357
class Vertex(object):
358
def __init__(self, start, end):
362
mapper(Vertex, vertices, properties={
363
'start':composite(Point, vertices.c.x1, vertices.c.y1),
364
'end':composite(Point, vertices.c.x2, vertices.c.y2)
367
Once you've set up your composite type, it's usable just
373
v = Vertex(Point(3, 4), Point(26,15))
377
# works in queries too
378
q = session.query(Vertex).filter(Vertex.start == Point(3, 4))
380
If you'd like to define the way the mapped attributes
381
generate SQL clauses when used in expressions, create your
382
own ``sqlalchemy.orm.PropComparator`` subclass, defining any
383
of the common operators (like ``__eq__()``, ``__le__()``,
384
etc.), and send it in to ``composite()``. Composite types
385
work as primary keys too, and are usable in ``query.get()``:
389
# a Document class which uses a composite Version
390
# object as primary key
391
document = query.get(Version(1, 'a'))
393
``dynamic_loader()`` relations
394
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
396
A ``relation()`` that returns a live ``Query`` object for
397
all read operations. Write operations are limited to just
398
``append()`` and ``remove()``, changes to the collection are
399
not visible until the session is flushed. This feature is
400
particularly handy with an "autoflushing" session which will
401
flush before each query.
405
mapper(Foo, foo_table, properties={
406
'bars':dynamic_loader(Bar, backref='foo', <other relation() opts>)
409
session = create_session(autoflush=True)
410
foo = session.query(Foo).first()
412
foo.bars.append(Bar(name='lala'))
414
for bar in foo.bars.filter(Bar.name=='lala'):
419
New Options: ``undefer_group()``, ``eagerload_all()``
420
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
422
A couple of query options which are handy.
423
``undefer_group()`` marks a whole group of "deferred"
424
columns as undeferred:
428
mapper(Class, table, properties={
429
'foo' : deferred(table.c.foo, group='group1'),
430
'bar' : deferred(table.c.bar, group='group1'),
431
'bat' : deferred(table.c.bat, group='group1'),
434
session.query(Class).options(undefer_group('group1')).filter(...).all()
436
and ``eagerload_all()`` sets a chain of attributes to be
441
mapper(Foo, foo_table, properties={
444
mapper(Bar, bar_table, properties={
447
mapper(Bat, bat_table)
449
# eager load bar and bat
450
session.query(Foo).options(eagerload_all('bar.bat')).filter(...).all()
455
Collections are no longer proxied by an
456
{{{InstrumentedList}}} proxy, and access to members, methods
457
and attributes is direct. Decorators now intercept objects
458
entering and leaving the collection, and it is now possible
459
to easily write a custom collection class that manages its
460
own membership. Flexible decorators also replace the named
461
method interface of custom collections in 0.3, allowing any
462
class to be easily adapted to use as a collection container.
464
Dictionary-based collections are now much easier to use and
465
fully ``dict``-like. Changing ``__iter__`` is no longer
466
needed for ``dict``s, and new built-in ``dict`` types cover
471
# use a dictionary relation keyed by a column
472
relation(Item, collection_class=column_mapped_collection(items.c.keyword))
474
relation(Item, collection_class=attribute_mapped_collection('keyword'))
475
# or any function you like
476
relation(Item, collection_class=mapped_collection(lambda entity: entity.a + entity.b))
478
Existing 0.3 ``dict``-like and freeform object derived
479
collection classes will need to be updated for the new API.
480
In most cases this is simply a matter of adding a couple
481
decorators to the class definition.
483
Mapped Relations from External Tables/Subqueries
484
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
486
This feature quietly appeared in 0.3 but has been improved
487
in 0.4 thanks to better ability to convert subqueries
488
against a table into subqueries against an alias of that
489
table; this is key for eager loading, aliased joins in
490
queries, etc. It reduces the need to create mappers against
491
select statements when you just need to add some extra
492
columns or subqueries:
496
mapper(User, users, properties={
497
'fullname': column_property((users.c.firstname + users.c.lastname).label('fullname')),
498
'numposts': column_property(
499
select([func.count(1)], users.c.id==posts.c.user_id).correlate(users).label('posts')
503
a typical query looks like:
507
SELECT (SELECT count(1) FROM posts WHERE users.id = posts.user_id) AS count,
508
users.firstname || users.lastname AS fullname,
509
users.id AS users_id, users.firstname AS users_firstname, users.lastname AS users_lastname
510
FROM users ORDER BY users.oid
512
Horizontal Scaling (Sharding) API
513
---------------------------------
515
[browser:/sqlalchemy/trunk/examples/sharding/attribute_shard
521
New Session Create Paradigm; SessionContext, assignmapper Deprecated
522
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
524
That's right, the whole shebang is being replaced with two
525
configurational functions. Using both will produce the most
526
0.1-ish feel we've had since 0.1 (i.e., the least amount of
529
Configure your own ``Session`` class right where you define
530
your ``engine`` (or anywhere):
534
from sqlalchemy import create_engine
535
from sqlalchemy.orm import sessionmaker
537
engine = create_engine('myengine://')
538
Session = sessionmaker(bind=engine, autoflush=True, transactional=True)
540
# use the new Session() freely
542
sess.save(someobject)
546
If you need to post-configure your Session, say with an
547
engine, add it later with ``configure()``:
551
Session.configure(bind=create_engine(...))
553
All the behaviors of ``SessionContext`` and the ``query``
554
and ``__init__`` methods of ``assignmapper`` are moved into
555
the new ``scoped_session()`` function, which is compatible
556
with both ``sessionmaker`` as well as ``create_session()``:
560
from sqlalchemy.orm import scoped_session, sessionmaker
562
Session = scoped_session(sessionmaker(autoflush=True, transactional=True))
563
Session.configure(bind=engine)
565
u = User(name='wendy')
571
# Session constructor is thread-locally scoped. Everyone gets the same
572
# Session in the thread when scope="thread".
577
When using a thread-local ``Session``, the returned class
578
has all of ``Session's`` interface implemented as
579
classmethods, and "assignmapper"'s functionality is
580
available using the ``mapper`` classmethod. Just like the
581
old ``objectstore`` days....
586
# "assignmapper"-like functionality available via ScopedSession.mapper
587
Session.mapper(User, users_table)
589
u = User(name='wendy')
594
Sessions are again Weak Referencing By Default
595
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
597
The weak_identity_map flag is now set to ``True`` by default
598
on Session. Instances which are externally deferenced and
599
fall out of scope are removed from the session
600
automatically. However, items which have "dirty" changes
601
present will remain strongly referenced until those changes
602
are flushed at which case the object reverts to being weakly
603
referenced (this works for 'mutable' types, like picklable
604
attributes, as well). Setting weak_identity_map to
605
``False`` restores the old strong-referencing behavior for
606
those of you using the session like a cache.
608
Auto-Transactional Sessions
609
^^^^^^^^^^^^^^^^^^^^^^^^^^^
611
As you might have noticed above, we are calling ``commit()``
612
on ``Session``. The flag ``transactional=True`` means the
613
``Session`` is always in a transaction, ``commit()``
614
persists permanently.
616
Auto-Flushing Sessions
617
^^^^^^^^^^^^^^^^^^^^^^
619
Also, ``autoflush=True`` means the ``Session`` will
620
``flush()`` before each ``query`` as well as when you call
621
``flush()`` or ``commit()``. So now this will work:
625
Session = sessionmaker(bind=engine, autoflush=True, transactional=True)
627
u = User(name='wendy')
632
# wendy is flushed, comes right back from a query
633
wendy = sess.query(User).filter_by(name='wendy').one()
635
Transactional methods moved onto sessions
636
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
638
``commit()`` and ``rollback()``, as well as ``begin()`` are
639
now directly on ``Session``. No more need to use
640
``SessionTransaction`` for anything (it remains in the
645
Session = sessionmaker(autoflush=True, transactional=False)
652
sess.commit() # commit transaction
654
Sharing a ``Session`` with an enclosing engine-level (i.e.
655
non-ORM) transaction is easy:
659
Session = sessionmaker(autoflush=True, transactional=False)
661
conn = engine.connect()
663
sess = Session(bind=conn)
665
# ... session is transactional
667
# commit the outermost transaction
670
Nested Session Transactions with SAVEPOINT
671
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
673
Available at the Engine and ORM level. ORM docs so far:
675
http://www.sqlalchemy.org/docs/04/session.html#unitofwork_ma
678
Two-Phase Commit Sessions
679
^^^^^^^^^^^^^^^^^^^^^^^^^
681
Available at the Engine and ORM level. ORM docs so far:
683
http://www.sqlalchemy.org/docs/04/session.html#unitofwork_ma
689
Polymorphic Inheritance with No Joins or Unions
690
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
692
New docs for inheritance: http://www.sqlalchemy.org/docs/04
693
/mappers.html#advdatamapping_mapper_inheritance_joined
695
Better Polymorphic Behavior with ``get()``
696
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
698
All classes within a joined-table inheritance hierarchy get
699
an ``_instance_key`` using the base class, i.e.
700
``(BaseClass, (1, ), None)``. That way when you call
701
``get()`` a ``Query`` against the base class, it can locate
702
subclass instances in the current identity map without
703
querying the database.
708
Custom Subclasses of ``sqlalchemy.types.TypeDecorator``
709
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
711
There is a `New API <http://www.sqlalchemy.org/docs/04/types
712
.html#types_custom>`_ for subclassing a TypeDecorator.
713
Using the 0.3 API causes compilation errors in some cases.
718
All New, Deterministic Label/Alias Generation
719
---------------------------------------------
721
All the "anonymous" labels and aliases use a simple
722
<name>_<number> format now. SQL is much easier to read and
723
is compatible with plan optimizer caches. Just check out
724
some of the examples in the tutorials:
725
http://www.sqlalchemy.org/docs/04/ormtutorial.html
726
http://www.sqlalchemy.org/docs/04/sqlexpression.html
728
Generative select() Constructs
729
------------------------------
731
This is definitely the way to go with ``select()``. See htt
732
p://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_transf
738
SQL operators and more or less every SQL keyword there is
739
are now abstracted into the compiler layer. They now act
740
intelligently and are type/backend aware, see: http://www.sq
741
lalchemy.org/docs/04/sqlexpression.html#sql_operators
743
All ``type`` Keyword Arguments Renamed to ``type_``
744
---------------------------------------------------
750
b = bindparam('foo', type_=String)
752
in_ Function Changed to Accept Sequence or Selectable
753
-----------------------------------------------------
755
The in_ function now takes a sequence of values or a
756
selectable as its sole argument. The previous API of passing
757
in values as positional arguments still works, but is now
758
deprecated. This means that
762
my_table.select(my_table.c.id.in_(1,2,3)
763
my_table.select(my_table.c.id.in_(*listOfIds)
769
my_table.select(my_table.c.id.in_([1,2,3])
770
my_table.select(my_table.c.id.in_(listOfIds)
772
Schema and Reflection
773
=====================
775
``MetaData``, ``BoundMetaData``, ``DynamicMetaData``...
776
-------------------------------------------------------
778
In the 0.3.x series, ``BoundMetaData`` and
779
``DynamicMetaData`` were deprecated in favor of ``MetaData``
780
and ``ThreadLocalMetaData``. The older names have been
781
removed in 0.4. Updating is simple:
785
+-------------------------------------+-------------------------+
786
|If You Had | Now Use |
787
+=====================================+=========================+
788
| ``MetaData`` | ``MetaData`` |
789
+-------------------------------------+-------------------------+
790
| ``BoundMetaData`` | ``MetaData`` |
791
+-------------------------------------+-------------------------+
792
| ``DynamicMetaData`` (with one | ``MetaData`` |
793
| engine or threadlocal=False) | |
794
+-------------------------------------+-------------------------+
795
| ``DynamicMetaData`` | ``ThreadLocalMetaData`` |
796
| (with different engines per thread) | |
797
+-------------------------------------+-------------------------+
799
The seldom-used ``name`` parameter to ``MetaData`` types has
800
been removed. The ``ThreadLocalMetaData`` constructor now
801
takes no arguments. Both types can now be bound to an
802
``Engine`` or a single ``Connection``.
804
One Step Multi-Table Reflection
805
-------------------------------
807
You can now load table definitions and automatically create
808
``Table`` objects from an entire database or schema in one
813
>>> metadata = MetaData(myengine, reflect=True)
814
>>> metadata.tables.keys()
815
['table_a', 'table_b', 'table_c', '...']
817
``MetaData`` also gains a ``.reflect()`` method enabling
818
finer control over the loading process, including
819
specification of a subset of available tables to load.
824
``engine``, ``connectable``, and ``bind_to`` are all now ``bind``
825
-----------------------------------------------------------------
827
``Transactions``, ``NestedTransactions`` and ``TwoPhaseTransactions``
828
---------------------------------------------------------------------
830
Connection Pool Events
831
----------------------
833
The connection pool now fires events when new DB-API
834
connections are created, checked out and checked back into
835
the pool. You can use these to execute session-scoped SQL
836
setup statements on fresh connections, for example.
841
In 0.3.11, there were bugs in the Oracle Engine on how
842
Primary Keys are handled. These bugs could cause programs
843
that worked fine with other engines, such as sqlite, to fail
844
when using the Oracle Engine. In 0.4, the Oracle Engine has
845
been reworked, fixing these Primary Key problems.
847
Out Parameters for Oracle
848
-------------------------
852
result = engine.execute(text("begin foo(:x, :y, :z); end;", bindparams=[bindparam('x', Numeric), outparam('y', Numeric), outparam('z', Numeric)]), x=5)
853
assert result.out_parameters == {'y':10, 'z':75}
855
Connection-bound ``MetaData``, ``Sessions``
856
-------------------------------------------
858
``MetaData`` and ``Session`` can be explicitly bound to a
863
conn = engine.connect()
864
sess = create_session(bind=conn)
866
Faster, More Foolproof ``ResultProxy`` Objects
867
----------------------------------------------
added
removed
doc/_sources/changelog/migration_04.txt
