session.txt

SQLAlchemy. 经典的Python ORM框架。学习必看。

    {python}
    # immediately re-load the attributes 'hello', 'world' on obj1, obj2
    session.refresh(obj1, ['hello', 'world'])
    session.refresh(obj2, ['hello', 'world'])
    
    # expire the attributes 'hello', 'world' objects obj1, obj2, attributes will be reloaded
    # on the next access:
    session.expire(obj1, ['hello', 'world'])
    session.expire(obj2, ['hello', 'world'])

## Cascades

Mappers support the concept of configurable *cascade* behavior on `relation()`s.  This behavior controls how the Session should treat the instances that have a parent-child relationship with another instance that is operated upon by the Session.  Cascade is indicated as a comma-separated list of string keywords, with the possible values `all`, `delete`, `save-update`, `refresh-expire`, `merge`, `expunge`, and `delete-orphan`.

Cascading is configured by setting the `cascade` keyword argument on a `relation()`:

    {python}
    mapper(Order, order_table, properties={
        'items' : relation(Item, items_table, cascade="all, delete-orphan"),
        'customer' : relation(User, users_table, user_orders_table, cascade="save-update"),
    })

The above mapper specifies two relations, `items` and `customer`.  The `items` relationship specifies "all, delete-orphan" as its `cascade` value, indicating that all  `save`, `update`, `merge`, `expunge`, `refresh` `delete` and `expire` operations performed on a parent `Order` instance should also be performed on the child `Item` instances attached to it (`save` and `update` are cascaded using the `save_or_update()` method, so that the database identity of the instance doesn't matter).  The `delete-orphan` cascade value additionally indicates that if an `Item` instance is no longer associated with an `Order`, it should also be deleted.  The "all, delete-orphan" cascade argument allows a so-called *lifecycle* relationship between an `Order` and an `Item` object.

The `customer` relationship specifies only the "save-update" cascade value, indicating most operations will not be cascaded from a parent `Order` instance to a child `User` instance, except for if the `Order` is attached with a particular session, either via the `save()`, `update()`, or `save-update()` method.

Additionally, when a child item is attached to a parent item that specifies the "save-update" cascade value on the relationship, the child is automatically passed to `save_or_update()` (and the operation is further cascaded to the child item).

Note that cascading doesn't do anything that isn't possible by manually calling Session methods on individual instances within a hierarchy, it merely automates common operations on a group of associated instances.

The default value for `cascade` on `relation()`s is `save-update, merge`.

## Managing Transactions

The Session can manage transactions automatically, including across multiple engines.  When the Session is in a transaction, as it receives requests to execute SQL statements, it adds each individual Connection/Engine encountered to its transactional state.  At commit time, all unflushed data is flushed, and each individual transaction is committed.  If the underlying databases support two-phase semantics, this may be used by the Session as well if two-phase transactions are enabled.

The easiest way to use a Session with transactions is just to declare it as transactional.  The session will remain in a transaction at all times:

    {python}
    # transactional session
    Session = sessionmaker(transactional=True)
    sess = Session()
    try:
        item1 = sess.query(Item).get(1)
        item2 = sess.query(Item).get(2)
        item1.foo = 'bar'
        item2.bar = 'foo'
    
        # commit- will immediately go into a new transaction afterwards
        sess.commit()
    except:
        # rollback - will immediately go into a new transaction afterwards.
        sess.rollback()

Things to note above:

  * When using a transactional session, either a `rollback()` or a `close()` call **is required** when an error is raised by `flush()` or `commit()`.  The `flush()` error condition will issue a ROLLBACK to the database automatically, but the state of the `Session` itself remains in an "undefined" state until the user decides whether to rollback or close.
  * The `commit()` call unconditionally issues a `flush()`.  Particularly when using `transactional=True` in conjunction with `autoflush=True`, explicit `flush()` calls are usually not needed.
  
Alternatively, a transaction can be begun explicitly using `begin()`:

    {python}
    # non transactional session
    Session = sessionmaker(transactional=False)
    sess = Session()
    sess.begin()
    try:
        item1 = sess.query(Item).get(1)
        item2 = sess.query(Item).get(2)
        item1.foo = 'bar'
        item2.bar = 'foo'
        sess.commit()
    except:
        sess.rollback()
        raise

Like the `transactional` example, the same rules apply; an explicit `rollback()` or `close()` is required when an error occurs, and the `commit()` call issues a `flush()` as well.

Session also supports Python 2.5's with statement so that the example above can be written as:

    {python}
    Session = sessionmaker(transactional=False)
    sess = Session()
    with sess.begin():
        item1 = sess.query(Item).get(1)
        item2 = sess.query(Item).get(2)
        item1.foo = 'bar'
        item2.bar = 'foo'

Subtransactions can be created by calling the `begin()` method repeatedly. For each transaction you `begin()` you must always call either `commit()` or `rollback()`. Note that this includes the implicit transaction created by the transactional session. When a subtransaction is created the current transaction of the session is set to that transaction. Commiting the subtransaction will return you to the next outer transaction. Rolling it back will also return you to the next outer transaction, but in addition it will roll back database state to the innermost transaction that supports rolling back to. Usually this means the root transaction, unless you use the nested transaction functionality via the `begin_nested()` method. MySQL and Postgres (and soon Oracle) support using "nested" transactions by creating SAVEPOINTs, :

    {python}
    Session = sessionmaker(transactional=False)
    sess = Session()
    sess.begin()
    sess.save(u1)
    sess.save(u2)
    sess.flush()

    sess.begin_nested() # establish a savepoint
    sess.save(u3)
    sess.rollback()  # rolls back u3, keeps u1 and u2

    sess.commit() # commits u1 and u2

Finally, for MySQL, Postgres, and soon Oracle as well, the session can be instructed to use two-phase commit semantics. This will coordinate the commiting of transactions across databases so that the transaction is either committed or rolled back in all databases. You can also `prepare()` the session for interacting with transactions not managed by SQLAlchemy. To use two phase transactions set the flag `twophase=True` on the session:

    {python}
    engine1 = create_engine('postgres://db1')
    engine2 = create_engine('postgres://db2')
    
    Session = sessionmaker(twophase=True, transactional=True)

    # bind User operations to engine 1, Account operations to engine 2
    Session.configure(binds={User:engine1, Account:engine2})

    sess = Session()
    
    # .... work with accounts and users
    
    # commit.  session will issue a flush to all DBs, and a prepare step to all DBs,
    # before committing both transactions
    sess.commit()

Be aware that when a crash occurs in one of the databases while the the transactions are prepared you have to manually commit or rollback the prepared transactions in your database as appropriate.

## Embedding SQL Insert/Update Expressions into a Flush {@name=flushsql}

This feature allows the value of a database column to be set to a SQL expression instead of a literal value.  It's especially useful for atomic updates, calling stored procedures, etc.  All you do is assign an expression to an attribute:

    {python}
    class SomeClass(object):
        pass
    mapper(SomeClass, some_table)
    
    someobject = session.query(SomeClass).get(5)
    
    # set 'value' attribute to a SQL expression adding one
    someobject.value = some_table.c.value + 1
    
    # issues "UPDATE some_table SET value=value+1"
    session.commit()
    
This works both for INSERT and UPDATE statements.  After the flush/commit operation, the `value` attribute on `someobject` gets "deferred", so that when you again access it the newly generated value will be loaded from the database.  This is the same mechanism at work when database-side column defaults fire off.

## Using SQL Expressions with Sessions {@name=sql}

SQL constructs and string statements can be executed via the `Session`.  You'd want to do this normally when your `Session` is transactional and you'd like your free-standing SQL statements to participate in the same transaction.

The two ways to do this are to use the connection/execution services of the Session, or to have your Session participate in a regular SQL transaction.

First, a Session thats associated with an Engine or Connection can execute statements immediately (whether or not it's transactional):

    {python}
    Session = sessionmaker(bind=engine, transactional=True)
    sess = Session()
    result = sess.execute("select * from table where id=:id", {'id':7})
    result2 = sess.execute(select([mytable], mytable.c.id==7))

To get at the current connection used by the session, which will be part of the current transaction if one is in progress, use `connection()`:

    {python}
    connection = sess.connection()
    
A second scenario is that of a Session which is not directly bound to a connectable.  This session executes statements relative to a particular `Mapper`, since the mappers are bound to tables which are in turn bound to connectables via their `MetaData` (either the session or the mapped tables need to be bound).  In this case, the Session can conceivably be associated with multiple databases through different mappers; so it wants you to send along a `mapper` argument, which can be any mapped class or mapper instance:

    {python}
    # session is *not* bound to an engine or connection
    Session = sessionmaker(transactional=True)
    sess = Session()
    
    # need to specify mapper or class when executing
    result = sess.execute("select * from table where id=:id", {'id':7}, mapper=MyMappedClass)
    result2 = sess.execute(select([mytable], mytable.c.id==7), mapper=MyMappedClass)

    # need to specify mapper or class when you call connection()
    connection = sess.connection(MyMappedClass)

The third scenario is when you are using `Connection` and `Transaction` yourself, and want the `Session` to participate.  This is easy, as you just bind the `Session` to the connection:

    {python}
    # non-transactional session
    Session = sessionmaker(transactional=False)
    
    # non-ORM connection + transaction
    conn = engine.connect()
    trans = conn.begin()
    
    # bind the Session *instance* to the connection
    sess = Session(bind=conn)
    
    # ... etc
    
    trans.commit()
    
It's safe to use a `Session` which is transactional or autoflushing, as well as to call `begin()`/`commit()` on the session too; the outermost Transaction object, the one we declared explicitly, controls the scope of the transaction.

When using the `threadlocal` engine context, things are that much easier; the `Session` uses the same connection/transaction as everyone else in the current thread, whether or not you explicitly bind it:

    {python}
    engine = create_engine('postgres://mydb', strategy="threadlocal")
    engine.begin()
    
    sess = Session()  # session takes place in the transaction like everyone else