dbengine.txt

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

    # execute one statement and receive results.  r1 now references a DBAPI connection resource.
    r1 = db.execute("select * from table1")

    # execute a second statement and receive results.  r2 now references a *second* DBAPI connection resource.
    r2 = db.execute("select * from table2")
    for row in r1:
        ...
    for row in r2:
        ...
    # release connection 1
    r1.close()

    # release connection 2
    r2.close()

Where above, we have two result sets in scope at the same time, therefore we have two distinct DBAPI connections, both separately checked out from the connection pool, in scope at the same time.

An option exists to `create_engine()` called `strategy="threadlocal"`, which changes this behavior.  When this option is used, the `Engine` which is returned by `create_engine()` is a special subclass of engine called `TLEngine`.  This engine, when it creates the `Connection` used by a connectionless execution, checks a **threadlocal variable** for an existing DBAPI connection that was already checked out from the pool, within the current thread.  If one exists, it uses that one. 

The usage of "threadlocal" modifies the underlying behavior of our example above, as follows:

    {python title="Threadlocal Strategy"}
    db = create_engine('mysql://localhost/test', strategy='threadlocal')

    # execute one statement and receive results.  r1 now references a DBAPI connection resource.
    r1 = db.execute("select * from table1")

    # execute a second statement and receive results.  r2 now references the *same* resource as r1
    r2 = db.execute("select * from table2")

    for row in r1:
        ...
    for row in r2:
        ...
    # close r1.  the connection is still held by r2.
    r1.close()

    # close r2.  with no more references to the underlying connection resources, they
    # are returned to the pool.
    r2.close()

Where above, we again have two result sets in scope at the same time, but because they are present in the same thread, there is only **one DBAPI connection in use**.

While the above distinction may not seem like much, it has several potentially desirable effects.  One is that you can in some cases reduce the number of concurrent connections checked out from the connection pool, in the case that a `ResultProxy` is still opened and a second statement is issued.  A second advantage is that by limiting the number of checked out connections in a thread to just one, you eliminate the issue of deadlocks within a single thread, such as when connection A locks a table, and connection B attempts to read from the same table in the same thread, it will "deadlock" on waiting for connection A to release its lock; the `threadlocal` strategy eliminates this possibility.

A third advantage to the `threadlocal` strategy is that it allows the `Transaction` object to be used in combination with connectionless execution.  Recall from the section on transactions, that the `Transaction` is returned by the `begin()` method on a `Connection`; all statements which wish to participate in this transaction must be executed by the same `Connection`, thereby forcing the usage of an explicit connection.  However, the `TLEngine` provides a `Transaction` that is local to the current thread;  using it, one can issue many "connectionless" statements within a thread and they will all automatically partake in the current transaction, as in the example below:

    {python title="threadlocal connection sharing"}
    # get a TLEngine
    engine = create_engine('mysql://localhost/test', strategy='threadlocal')

    engine.begin()
    try:
        engine.execute("insert into users values (?, ?)", 1, "john")
        users.update(users.c.user_id==5).execute(name='ed')
        engine.commit()
    except:
        engine.rollback()

Notice that no `Connection` needed to be used; the `begin()` method on `TLEngine` (which note is not available on the regular `Engine`) created a `Transaction` as well as a `Connection`, and held onto both in a context corresponding to the current thread.  Each `execute()` call made use of the same connection, allowing them all to participate in the same transaction.

Complex application flows can take advantage of the "threadlocal" strategy in order to allow many disparate parts of an application to take place in the same transaction automatically.  The example below demonstrates several forms of "connectionless execution" as well as some specialized explicit ones:

    {python title="threadlocal connection sharing"}
    engine = create_engine('mysql://localhost/test', strategy='threadlocal')

    def dosomethingimplicit():
        table1.execute("some sql")
        table1.execute("some other sql")
    
    def dosomethingelse():
        table2.execute("some sql")
        conn = engine.contextual_connect()
        # do stuff with conn
        conn.execute("some other sql")
        conn.close()

    def dosomethingtransactional():
        conn = engine.contextual_connect()
        trans = conn.begin()
         # do stuff
        trans.commit()
    
    engine.begin()
    try:
        dosomethingimplicit()
        dosomethingelse()
        dosomethingtransactional()
        engine.commit()
    except:
        engine.rollback()

In the above example, the program calls three functions `dosomethingimplicit()`, `dosomethingelse()` and `dosomethingtransactional()`.  All three functions use either connectionless execution, or a special function `contextual_connect()` which we will describe in a moment.  These two styles of execution both indicate that all executions will use the same connection object.  Additionally, the method `dosomethingtransactional()` begins and commits its own `Transaction`.  But only one transaction is used, too; it's controlled completely by the `engine.begin()`/`engine.commit()` calls at the bottom.  Recall that `Transaction` supports "nesting" behavior, whereby transactions begun on a `Connection` which already has a transaction open, will "nest" into the enclosing transaction.  Since the transaction opened in `dosomethingtransactional()` occurs using the same connection which already has a transaction begun, it "nests" into that transaction and therefore has no effect on the actual transaction scope (unless it calls `rollback()`).

Some of the functions in the above example make use of a method called `engine.contextual_connect()`.  This method is available on both `Engine` as well as `TLEngine`, and returns the `Connection` that applies to the current **connection context**.  When using the `TLEngine`, this is just another term for the "thread local connection" that is being used for all connectionless executions.  When using just the regular `Engine` (i.e. the "default" strategy), `contextual_connect()` is synonymous with `connect()`.  Below we illustrate that two connections opened via `contextual_connect()` at the same time, both reference the same underlying DBAPI connection:

    {python title="Contextual Connection"}
    # threadlocal strategy
    db = create_engine('mysql://localhost/test', strategy='threadlocal')
    
    conn1 = db.contextual_connect()
    conn2 = db.contextual_connect()

    >>> conn1.connection is conn2.connection
    True

The basic idea of `contextual_connect()` is that it's the "connection used by connectionless execution".  It's different from the `connect()` method in that `connect()` is always used when handling an explicit `Connection`, which will always reference distinct DBAPI connection.  Using `connect()` in combination with `TLEngine` allows one to "circumvent" the current thread local context, as in this example where a single statement issues data to the database externally to the current transaction:

    {python}
    engine.begin()
    engine.execute("insert into users values (?, ?)", 1, "john")
    connection = engine.connect()
    connection.execute(users.update(users.c.user_id==5).execute(name='ed'))
    engine.rollback()

In the above example, a thread-local transaction is begun, but is later rolled back.  The statement `insert into users values (?, ?)` is executed without using a connection, therefore uses the thread-local transaction.  So its data is rolled back when the transaction is rolled back.  However, the `users.update()` statement is executed using a distinct `Connection` returned by the `engine.connect()` method, so it therefore is not part of the threadlocal transaction; it autocommits immediately.

### Configuring Logging {@name=logging}

As of the 0.3 series of SQLAlchemy, Python's standard [logging](http://www.python.org/doc/lib/module-logging.html) module is used to implement informational and debug log output.  This allows SQLAlchemy's logging to integrate in a standard way with other applications and libraries.  The `echo` and `echo_pool` flags that are present on `create_engine()`, as well as the `echo_uow` flag used on `Session`, all interact with regular loggers.

This section assumes familiarity with the above linked logging module.  All logging performed by SQLAlchemy exists underneath the `sqlalchemy` namespace, as used by `logging.getLogger('sqlalchemy')`.  When logging has been configured (i.e. such as via `logging.basicConfig()`), the general namespace of SA loggers that can be turned on is as follows:

* `sqlalchemy.engine` - controls SQL echoing.  set to `logging.INFO` for SQL query output, `logging.DEBUG` for query + result set output.
* `sqlalchemy.pool` - controls connection pool logging.  set to `logging.INFO` or lower to log connection pool checkouts/checkins.
* `sqlalchemy.orm` - controls logging of various ORM functions.  set to `logging.INFO` for configurational logging as well as unit of work dumps, `logging.DEBUG` for extensive logging during query and flush() operations.  Subcategories of `sqlalchemy.orm` include:
    * `sqlalchemy.orm.attributes` - logs certain instrumented attribute operations, such as triggered callables
    * `sqlalchemy.orm.mapper` - logs Mapper configuration and operations
    * `sqlalchemy.orm.unitofwork` - logs flush() operations, including dependency sort graphs and other operations
    * `sqlalchemy.orm.strategies` - logs relation loader operations (i.e. lazy and eager loads)
    * `sqlalchemy.orm.sync` - logs synchronization of attributes from parent to child instances during a flush()

For example, to log SQL queries as well as unit of work debugging:

    {python}
    import logging
    
    logging.basicConfig()
    logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
    logging.getLogger('sqlalchemy.orm.unitofwork').setLevel(logging.DEBUG)
    
By default, the log level is set to `logging.ERROR` within the entire `sqlalchemy` namespace so that no log operations occur, even within an application that has logging enabled otherwise.

The `echo` flags present as keyword arguments to `create_engine()` and others as well as the `echo` property on `Engine`, when set to `True`, will first attempt to ensure that logging is enabled.  Unfortunately, the `logging` module provides no way of determining if output has already been configured (note we are referring to if a logging configuration has been set up, not just that the logging level is set).  For this reason, any `echo=True` flags will result in a call to `logging.basicConfig()` using sys.stdout as the destination.  It also sets up a default format using the level name, timestamp, and logger name.  Note that this configuration has the affect of being configured **in addition** to any existing logger configurations.  Therefore, **when using Python logging, ensure all echo flags are set to False at all times**, to avoid getting duplicate log lines.