# SQLAlchemy 2.0 Documentation and more

# SQLAlchemy 2.0 Documentation

# Changes and Migration

SQLAlchemy changelogs and migration guides are now integrated
within the main documentation.

## Current Migration Guide

For SQLAlchemy 2.0, there are two separate documents; the “Major Migration
Guide” details how to update a SQLAlchemy 1.4 application to be compatible
under SQLAlchemy 2.0. The “What’s New?” document details major new features,
capabilities and behaviors in SQLAlchemy 2.0.

- [SQLAlchemy 2.0 - Major Migration Guide](https://docs.sqlalchemy.org/en/20/changelog/migration_20.html)
- [What’s New in SQLAlchemy 2.0?](https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html)

## Change logs

- [2.0 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_20.html)
- [1.4 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_14.html)
- [1.3 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_13.html)
- [1.2 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_12.html)
- [1.1 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_11.html)
- [1.0 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_10.html)
- [0.9 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_09.html)
- [0.8 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_08.html)
- [0.7 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_07.html)
- [0.6 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_06.html)
- [0.5 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_05.html)
- [0.4 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_04.html)
- [0.3 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_03.html)
- [0.2 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_02.html)
- [0.1 Changelog](https://docs.sqlalchemy.org/en/20/changelog/changelog_01.html)

## Older Migration Guides

- [What’s New in SQLAlchemy 1.4?](https://docs.sqlalchemy.org/en/20/changelog/migration_14.html)
- [What’s New in SQLAlchemy 1.3?](https://docs.sqlalchemy.org/en/20/changelog/migration_13.html)
- [What’s New in SQLAlchemy 1.2?](https://docs.sqlalchemy.org/en/20/changelog/migration_12.html)
- [What’s New in SQLAlchemy 1.1?](https://docs.sqlalchemy.org/en/20/changelog/migration_11.html)
- [What’s New in SQLAlchemy 1.0?](https://docs.sqlalchemy.org/en/20/changelog/migration_10.html)
- [What’s New in SQLAlchemy 0.9?](https://docs.sqlalchemy.org/en/20/changelog/migration_09.html)
- [What’s New in SQLAlchemy 0.8?](https://docs.sqlalchemy.org/en/20/changelog/migration_08.html)
- [What’s New in SQLAlchemy 0.7?](https://docs.sqlalchemy.org/en/20/changelog/migration_07.html)
- [What’s New in SQLAlchemy 0.6?](https://docs.sqlalchemy.org/en/20/changelog/migration_06.html)
- [What’s new in SQLAlchemy 0.5?](https://docs.sqlalchemy.org/en/20/changelog/migration_05.html)
- [What’s new in SQLAlchemy 0.4?](https://docs.sqlalchemy.org/en/20/changelog/migration_04.html)

---

# SQLAlchemy 2.0 Documentation

# What’s new in SQLAlchemy 0.4?

About this Document

This document describes changes between SQLAlchemy version 0.3,
last released October 14, 2007, and SQLAlchemy version 0.4,
last released October 12, 2008.

Document date:  March 21, 2008

## First Things First

If you’re using any ORM features, make sure you import from
`sqlalchemy.orm`:

```
from sqlalchemy import *
from sqlalchemy.orm import *
```

Secondly, anywhere you used to say `engine=`,
`connectable=`, `bind_to=`, `something.engine`,
`metadata.connect()`, use `bind`:

```
myengine = create_engine("sqlite://")

meta = MetaData(myengine)

meta2 = MetaData()
meta2.bind = myengine

session = create_session(bind=myengine)

statement = select([table], bind=myengine)
```

Got those ?  Good!  You’re now (95%) 0.4 compatible.  If
you’re using 0.3.10, you can make these changes immediately;
they’ll work there too.

## Module Imports

In 0.3, “`from sqlalchemy import *`” would import all of
sqlalchemy’s sub-modules into your namespace. Version 0.4 no
longer imports sub-modules into the namespace. This may mean
you need to add extra imports into your code.

In 0.3, this code worked:

```
from sqlalchemy import *

class UTCDateTime(types.TypeDecorator):
    pass
```

In 0.4, one must do:

```
from sqlalchemy import *
from sqlalchemy import types

class UTCDateTime(types.TypeDecorator):
    pass
```

## Object Relational Mapping

### Querying

#### New Query API

Query is standardized on the generative interface (old
interface is still there, just deprecated).   While most of
the generative interface is available in 0.3, the 0.4 Query
has the inner guts to match the generative outside, and has
a lot more tricks.  All result narrowing is via `filter()`
and `filter_by()`, limiting/offset is either through array
slices or `limit()`/`offset()`, joining is via
`join()` and `outerjoin()` (or more manually, through
`select_from()` as well as manually-formed criteria).

To avoid deprecation warnings, you must make some changes to
your 03 code

User.query.get_by( **kwargs )

```
User.query.filter_by(**kwargs).first()
```

User.query.select_by( **kwargs )

```
User.query.filter_by(**kwargs).all()
```

User.query.select()

```
User.query.filter(xxx).all()
```

#### New Property-Based Expression Constructs

By far the most palpable difference within the ORM is that
you can now construct your query criterion using class-based
attributes directly.  The “.c.” prefix is no longer needed
when working with mapped classes:

```
session.query(User).filter(and_(User.name == "fred", User.id > 17))
```

While simple column-based comparisons are no big deal, the
class attributes have some new “higher level” constructs
available, including what was previously only available in
`filter_by()`:

```
# comparison of scalar relations to an instance
filter(Address.user == user)

# return all users who contain a particular address
filter(User.addresses.contains(address))

# return all users who *dont* contain the address
filter(~User.address.contains(address))

# return all users who contain a particular address with
# the email_address like '%foo%'
filter(User.addresses.any(Address.email_address.like("%foo%")))

# same, email address equals '[email protected]'.  can fall back to keyword
# args for simple comparisons
filter(User.addresses.any(email_address="[email protected]"))

# return all Addresses whose user attribute has the username 'ed'
filter(Address.user.has(name="ed"))

# return all Addresses whose user attribute has the username 'ed'
# and an id > 5 (mixing clauses with kwargs)
filter(Address.user.has(User.id > 5, name="ed"))
```

The `Column` collection remains available on mapped
classes in the `.c` attribute.  Note that property-based
expressions are only available with mapped properties of
mapped classes.  `.c` is still used to access columns in
regular tables and selectable objects produced from SQL
Expressions.

#### Automatic Join Aliasing

We’ve had join() and outerjoin() for a while now:

```
session.query(Order).join("items")
```

Now you can alias them:

```
session.query(Order).join("items", aliased=True).filter(Item.name="item 1").join(
    "items", aliased=True
).filter(Item.name == "item 3")
```

The above will create two joins from orders->items using
aliases.  the `filter()` call subsequent to each will
adjust its table criterion to that of the alias.  To get at
the `Item` objects, use `add_entity()` and target each
join with an `id`:

```
session.query(Order).join("items", id="j1", aliased=True).filter(
    Item.name == "item 1"
).join("items", aliased=True, id="j2").filter(Item.name == "item 3").add_entity(
    Item, id="j1"
).add_entity(
    Item, id="j2"
)
```

Returns tuples in the form: `(Order, Item, Item)`.

#### Self-referential Queries

So query.join() can make aliases now.  What does that give
us ?  Self-referential queries !   Joins can be done without
any `Alias` objects:

```
# standard self-referential TreeNode mapper with backref
mapper(
    TreeNode,
    tree_nodes,
    properties={
        "children": relation(
            TreeNode, backref=backref("parent", remote_side=tree_nodes.id)
        )
    },
)

# query for node with child containing "bar" two levels deep
session.query(TreeNode).join(["children", "children"], aliased=True).filter_by(
    name="bar"
)
```

To add criterion for each table along the way in an aliased
join, you can use `from_joinpoint` to keep joining against
the same line of aliases:

```
# search for the treenode along the path "n1/n12/n122"

# first find a Node with name="n122"
q = sess.query(Node).filter_by(name="n122")

# then join to parent with "n12"
q = q.join("parent", aliased=True).filter_by(name="n12")

# join again to the next parent with 'n1'.  use 'from_joinpoint'
# so we join from the previous point, instead of joining off the
# root table
q = q.join("parent", aliased=True, from_joinpoint=True).filter_by(name="n1")

node = q.first()
```

#### query.populate_existing()

The eager version of `query.load()` (or
`session.refresh()`).  Every instance loaded from the
query, including all eagerly loaded items, get refreshed
immediately if already present in the session:

```
session.query(Blah).populate_existing().all()
```

### Relations

#### SQL Clauses Embedded in Updates/Inserts

For inline execution of SQL clauses, embedded right in the
UPDATE or INSERT, during a `flush()`:

```
myobject.foo = mytable.c.value + 1

user.pwhash = func.md5(password)

order.hash = text("select hash from hashing_table")
```

The column-attribute is set up with a deferred loader after
the operation, so that it issues the SQL to load the new
value when you next access.

#### Self-referential and Cyclical Eager Loading

Since our alias-fu has improved, `relation()` can join
along the same table *any number of times*; you tell it how
deep you want to go.  Lets show the self-referential
`TreeNode` more clearly:

```
nodes = Table(
    "nodes",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("parent_id", Integer, ForeignKey("nodes.id")),
    Column("name", String(30)),
)

class TreeNode(object):
    pass

mapper(
    TreeNode,
    nodes,
    properties={"children": relation(TreeNode, lazy=False, join_depth=3)},
)
```

So what happens when we say:

```
create_session().query(TreeNode).all()
```

?  A join along aliases, three levels deep off the parent:

```
SELECT
nodes_3.id AS nodes_3_id, nodes_3.parent_id AS nodes_3_parent_id, nodes_3.name AS nodes_3_name,
nodes_2.id AS nodes_2_id, nodes_2.parent_id AS nodes_2_parent_id, nodes_2.name AS nodes_2_name,
nodes_1.id AS nodes_1_id, nodes_1.parent_id AS nodes_1_parent_id, nodes_1.name AS nodes_1_name,
nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.name AS nodes_name
FROM nodes LEFT OUTER JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id
LEFT OUTER JOIN nodes AS nodes_2 ON nodes_1.id = nodes_2.parent_id
LEFT OUTER JOIN nodes AS nodes_3 ON nodes_2.id = nodes_3.parent_id
ORDER BY nodes.oid, nodes_1.oid, nodes_2.oid, nodes_3.oid
```

Notice the nice clean alias names too.  The joining doesn’t
care if it’s against the same immediate table or some other
object which then cycles back to the beginning.  Any kind
of chain of eager loads can cycle back onto itself when
`join_depth` is specified.  When not present, eager
loading automatically stops when it hits a cycle.

#### Composite Types

This is one from the Hibernate camp.  Composite Types let
you define a custom datatype that is composed of more than
one column (or one column, if you wanted).   Lets define a
new type, `Point`.  Stores an x/y coordinate:

```
class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

    def __composite_values__(self):
        return self.x, self.y

    def __eq__(self, other):
        return other.x == self.x and other.y == self.y

    def __ne__(self, other):
        return not self.__eq__(other)
```

The way the `Point` object is defined is specific to a
custom type; constructor takes a list of arguments, and the
`__composite_values__()` method produces a sequence of
those arguments.  The order will match up to our mapper, as
we’ll see in a moment.

Let’s create a table of vertices storing two points per row:

```
vertices = Table(
    "vertices",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("x1", Integer),
    Column("y1", Integer),
    Column("x2", Integer),
    Column("y2", Integer),
)
```

Then, map it !  We’ll create a `Vertex` object which
stores two `Point` objects:

```
class Vertex(object):
    def __init__(self, start, end):
        self.start = start
        self.end = end

mapper(
    Vertex,
    vertices,
    properties={
        "start": composite(Point, vertices.c.x1, vertices.c.y1),
        "end": composite(Point, vertices.c.x2, vertices.c.y2),
    },
)
```

Once you’ve set up your composite type, it’s usable just
like any other type:

```
v = Vertex(Point(3, 4), Point(26, 15))
session.save(v)
session.flush()

# works in queries too
q = session.query(Vertex).filter(Vertex.start == Point(3, 4))
```

If you’d like to define the way the mapped attributes
generate SQL clauses when used in expressions, create your
own `sqlalchemy.orm.PropComparator` subclass, defining any
of the common operators (like `__eq__()`, `__le__()`,
etc.), and send it in to `composite()`.  Composite types
work as primary keys too, and are usable in `query.get()`:

```
# a Document class which uses a composite Version
# object as primary key
document = query.get(Version(1, "a"))
```

#### dynamic_loader()relations

A `relation()` that returns a live `Query` object for
all read operations.  Write operations are limited to just
`append()` and `remove()`, changes to the collection are
not visible until the session is flushed.  This feature is
particularly handy with an “autoflushing” session which will
flush before each query.

```
mapper(
    Foo,
    foo_table,
    properties={
        "bars": dynamic_loader(
            Bar,
            backref="foo",
            # <other relation() opts>
        )
    },
)

session = create_session(autoflush=True)
foo = session.query(Foo).first()

foo.bars.append(Bar(name="lala"))

for bar in foo.bars.filter(Bar.name == "lala"):
    print(bar)

session.commit()
```

#### New Options:undefer_group(),eagerload_all()

A couple of query options which are handy.
`undefer_group()` marks a whole group of “deferred”
columns as undeferred:

```
mapper(
    Class,
    table,
    properties={
        "foo": deferred(table.c.foo, group="group1"),
        "bar": deferred(table.c.bar, group="group1"),
        "bat": deferred(table.c.bat, group="group1"),
    },
)

session.query(Class).options(undefer_group("group1")).filter(...).all()
```

and `eagerload_all()` sets a chain of attributes to be
eager in one pass:

```
mapper(Foo, foo_table, properties={"bar": relation(Bar)})
mapper(Bar, bar_table, properties={"bat": relation(Bat)})
mapper(Bat, bat_table)

# eager load bar and bat
session.query(Foo).options(eagerload_all("bar.bat")).filter(...).all()
```

#### New Collection API

Collections are no longer proxied by an
{{{InstrumentedList}}} proxy, and access to members, methods
and attributes is direct.   Decorators now intercept objects
entering and leaving the collection, and it is now possible
to easily write a custom collection class that manages its
own membership.  Flexible decorators also replace the named
method interface of custom collections in 0.3, allowing any
class to be easily adapted to use as a collection container.

Dictionary-based collections are now much easier to use and
fully `dict`-like.  Changing `__iter__` is no longer
needed for `dict``s, and new built-in ``dict` types cover
many needs:

```
# use a dictionary relation keyed by a column
relation(Item, collection_class=column_mapped_collection(items.c.keyword))
# or named attribute
relation(Item, collection_class=attribute_mapped_collection("keyword"))
# or any function you like
relation(Item, collection_class=mapped_collection(lambda entity: entity.a + entity.b))
```

Existing 0.3 `dict`-like and freeform object derived
collection classes will need to be updated for the new API.
In most cases this is simply a matter of adding a couple
decorators to the class definition.

#### Mapped Relations from External Tables/Subqueries

This feature quietly appeared in 0.3 but has been improved
in 0.4 thanks to better ability to convert subqueries
against a table into subqueries against an alias of that
table; this is key for eager loading, aliased joins in
queries, etc.  It reduces the need to create mappers against
select statements when you just need to add some extra
columns or subqueries:

```
mapper(
    User,
    users,
    properties={
        "fullname": column_property(
            (users.c.firstname + users.c.lastname).label("fullname")
        ),
        "numposts": column_property(
            select([func.count(1)], users.c.id == posts.c.user_id)
            .correlate(users)
            .label("posts")
        ),
    },
)
```

a typical query looks like:

```
SELECT (SELECT count(1) FROM posts WHERE users.id = posts.user_id) AS count,
users.firstname || users.lastname AS fullname,
users.id AS users_id, users.firstname AS users_firstname, users.lastname AS users_lastname
FROM users ORDER BY users.oid
```

### Horizontal Scaling (Sharding) API

[browser:/sqlalchemy/trunk/examples/sharding/attribute_shard
.py]

### Sessions

#### New Session Create Paradigm; SessionContext, assignmapper Deprecated

That’s right, the whole shebang is being replaced with two
configurational functions.  Using both will produce the most
0.1-ish feel we’ve had since 0.1 (i.e., the least amount of
typing).

Configure your own `Session` class right where you define
your `engine` (or anywhere):

```
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("myengine://")
Session = sessionmaker(bind=engine, autoflush=True, transactional=True)

# use the new Session() freely
sess = Session()
sess.save(someobject)
sess.flush()
```

If you need to post-configure your Session, say with an
engine, add it later with `configure()`:

```
Session.configure(bind=create_engine(...))
```

All the behaviors of `SessionContext` and the `query`
and `__init__` methods of `assignmapper` are moved into
the new `scoped_session()` function, which is compatible
with both `sessionmaker` as well as `create_session()`:

```
from sqlalchemy.orm import scoped_session, sessionmaker

Session = scoped_session(sessionmaker(autoflush=True, transactional=True))
Session.configure(bind=engine)

u = User(name="wendy")

sess = Session()
sess.save(u)
sess.commit()

# Session constructor is thread-locally scoped.  Everyone gets the same
# Session in the thread when scope="thread".
sess2 = Session()
assert sess is sess2
```

When using a thread-local `Session`, the returned class
has all of `Session's` interface implemented as
classmethods, and “assignmapper“‘s functionality is
available using the `mapper` classmethod.  Just like the
old `objectstore` days….

```
# "assignmapper"-like functionality available via ScopedSession.mapper
Session.mapper(User, users_table)

u = User(name="wendy")

Session.commit()
```

#### Sessions are again Weak Referencing By Default

The weak_identity_map flag is now set to `True` by default
on Session.  Instances which are externally deferenced and
fall out of scope are removed from the session
automatically.   However, items which have “dirty” changes
present will remain strongly referenced until those changes
are flushed at which case the object reverts to being weakly
referenced (this works for ‘mutable’ types, like picklable
attributes, as well).  Setting weak_identity_map to
`False` restores the old strong-referencing behavior for
those of you using the session like a cache.

#### Auto-Transactional Sessions

As you might have noticed above, we are calling `commit()`
on `Session`.  The flag `transactional=True` means the
`Session` is always in a transaction, `commit()`
persists permanently.

#### Auto-Flushing Sessions

Also, `autoflush=True` means the `Session` will
`flush()` before each `query` as well as when you call
`flush()` or `commit()`.  So now this will work:

```
Session = sessionmaker(bind=engine, autoflush=True, transactional=True)

u = User(name="wendy")

sess = Session()
sess.save(u)

# wendy is flushed, comes right back from a query
wendy = sess.query(User).filter_by(name="wendy").one()
```

#### Transactional methods moved onto sessions

`commit()` and `rollback()`, as well as `begin()` are
now directly on `Session`.  No more need to use
`SessionTransaction` for anything (it remains in the
background).

```
Session = sessionmaker(autoflush=True, transactional=False)

sess = Session()
sess.begin()

# use the session

sess.commit()  # commit transaction
```

Sharing a `Session` with an enclosing engine-level (i.e.
non-ORM) transaction is easy:

```
Session = sessionmaker(autoflush=True, transactional=False)

conn = engine.connect()
trans = conn.begin()
sess = Session(bind=conn)

# ... session is transactional

# commit the outermost transaction
trans.commit()
```

#### Nested Session Transactions with SAVEPOINT

Available at the Engine and ORM level.  ORM docs so far:

[https://www.sqlalchemy.org/docs/04/session.html#unitofwork_managing](https://www.sqlalchemy.org/docs/04/session.html#unitofwork_managing)

#### Two-Phase Commit Sessions

Available at the Engine and ORM level.  ORM docs so far:

[https://www.sqlalchemy.org/docs/04/session.html#unitofwork_managing](https://www.sqlalchemy.org/docs/04/session.html#unitofwork_managing)

### Inheritance

#### Polymorphic Inheritance with No Joins or Unions

New docs for inheritance:  [https://www.sqlalchemy.org/docs/04](https://www.sqlalchemy.org/docs/04)
/mappers.html#advdatamapping_mapper_inheritance_joined

#### Better Polymorphic Behavior withget()

All classes within a joined-table inheritance hierarchy get
an `_instance_key` using the base class, i.e.
`(BaseClass, (1, ), None)`.  That way when you call
`get()` a `Query` against the base class, it can locate
subclass instances in the current identity map without
querying the database.

### Types

#### Custom Subclasses ofsqlalchemy.types.TypeDecorator

There is a [New API](https://www.sqlalchemy.org/docs/04/types.html#types_custom) for subclassing a TypeDecorator.
Using the 0.3 API causes compilation errors in some cases.

## SQL Expressions

### All New, Deterministic Label/Alias Generation

All the “anonymous” labels and aliases use a simple
<name>_<number> format now.  SQL is much easier to read and
is compatible with plan optimizer caches.  Just check out
some of the examples in the tutorials:
[https://www.sqlalchemy.org/docs/04/ormtutorial.html](https://www.sqlalchemy.org/docs/04/ormtutorial.html) [https://www.sqlalchemy.org/docs/04/sqlexpression.html](https://www.sqlalchemy.org/docs/04/sqlexpression.html)

### Generative select() Constructs

This is definitely the way to go with `select()`.  See htt
p://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_transf
orm .

### New Operator System

SQL operators and more or less every SQL keyword there is
are now abstracted into the compiler layer.  They now act
intelligently and are type/backend aware, see:
[https://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_operators](https://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_operators)

### AlltypeKeyword Arguments Renamed totype_

Just like it says:

```
b = bindparam("foo", type_=String)
```

### in_ Function Changed to Accept Sequence or Selectable

The in_ function now takes a sequence of values or a
selectable as its sole argument. The previous API of passing
in values as positional arguments still works, but is now
deprecated. This means that

```
my_table.select(my_table.c.id.in_(1, 2, 3))
my_table.select(my_table.c.id.in_(*listOfIds))
```

should be changed to

```
my_table.select(my_table.c.id.in_([1, 2, 3]))
my_table.select(my_table.c.id.in_(listOfIds))
```

## Schema and Reflection

### MetaData,BoundMetaData,DynamicMetaData…

In the 0.3.x series, `BoundMetaData` and
`DynamicMetaData` were deprecated in favor of `MetaData`
and `ThreadLocalMetaData`.  The older names have been
removed in 0.4.  Updating is simple:

```
+-------------------------------------+-------------------------+
|If You Had                           | Now Use                 |
+=====================================+=========================+
| ``MetaData``                        | ``MetaData``            |
+-------------------------------------+-------------------------+
| ``BoundMetaData``                   | ``MetaData``            |
+-------------------------------------+-------------------------+
| ``DynamicMetaData`` (with one       | ``MetaData``            |
| engine or threadlocal=False)        |                         |
+-------------------------------------+-------------------------+
| ``DynamicMetaData``                 | ``ThreadLocalMetaData`` |
| (with different engines per thread) |                         |
+-------------------------------------+-------------------------+
```

The seldom-used `name` parameter to `MetaData` types has
been removed.  The `ThreadLocalMetaData` constructor now
takes no arguments.  Both types can now be bound to an
`Engine` or a single `Connection`.

### One Step Multi-Table Reflection

You can now load table definitions and automatically create
`Table` objects from an entire database or schema in one
pass:

```
>>> metadata = MetaData(myengine, reflect=True)
>>> metadata.tables.keys()
['table_a', 'table_b', 'table_c', '...']
```

`MetaData` also gains a `.reflect()` method enabling
finer control over the loading process, including
specification of a subset of available tables to load.

## SQL Execution

### engine,connectable, andbind_toare all nowbind

### Transactions,NestedTransactionsandTwoPhaseTransactions

### Connection Pool Events

The connection pool now fires events when new DB-API
connections are created, checked out and checked back into
the pool.   You can use these to execute session-scoped SQL
setup statements on fresh connections, for example.

### Oracle Engine Fixed

In 0.3.11, there were bugs in the Oracle Engine on how
Primary Keys are handled.  These bugs could cause programs
that worked fine with other engines, such as sqlite, to fail
when using the Oracle Engine.  In 0.4, the Oracle Engine has
been reworked, fixing these Primary Key problems.

### Out Parameters for Oracle

```
result = engine.execute(
    text(
        "begin foo(:x, :y, :z); end;",
        bindparams=[
            bindparam("x", Numeric),
            outparam("y", Numeric),
            outparam("z", Numeric),
        ],
    ),
    x=5,
)
assert result.out_parameters == {"y": 10, "z": 75}
```

### Connection-boundMetaData,Sessions

`MetaData` and `Session` can be explicitly bound to a
connection:

```
conn = engine.connect()
sess = create_session(bind=conn)
```

### Faster, More FoolproofResultProxyObjects
